计算列

功能的

可以用 Excel 公式基于结果自身的各列算出一列,并配有 Excel 的公式栏、颜色和函数。

如果你会写 Excel 公式,你就可以在 SQLly 的任何查询结果中添加一列。比如利润率、累计值、与上个月的变化、两个日期之间的工作日天数:输入你在 Excel 中会输入的公式,新列就会出现在数据旁边。你不需要知道如何用 SQL 编写,也不需要重新运行查询。

它的工作方式类似 Excel 表格中的计算列:整列共用一个公式,每一行都会计算一次。唯一要学的新东西是怎么指向一个单元格,因为结果里用的是列名而不是字母。

添加你的第一个计算列

  1. 运行一个查询。 任何结果都可以;例如一个带有 Qty 和 Price 列。
  2. 右键点击标题 ,让新列紧跟在它后面,然后选择 添加计算列….
  3. 公式栏会在结果上方打开: fx、一个 Name 字段, =和公式字段。SQLly 会自动填一个名称(Computed)并以你点击的列作为公式的起始引用,例如 =!Price.
  4. 为列命名并输入公式 - 例如 行总计 和 =!Qty * !Price.
  5. 按 输入 或点击 添加列. ESC 或者 取消 会在不更改任何内容的情况下关闭该栏。
SQLly
结果上方的公式栏:公式中的每个引用各有一种颜色,它读取的单元格在网格中也用相同的颜色框出。结果上方的公式栏:公式中的每个引用各有一种颜色,它读取的单元格在网格中也用相同的颜色框出。
结果上方的公式栏:公式中的每个引用各有一种颜色,它读取的单元格在网格中也用相同的颜色框出。

从 A1 到 !Name

当 Excel 显示 B7,SQLly 会说明 !Qty: 一个感叹号和列名。公式中的其余部分就是普通的 Excel。

在 Excel 中(在第 7 行)在 SQLly 中表示
=B7*C7=!Qty * !Price此行的 Qty 乘以此行的 Price。
=B7-B6=!Qty - !Qty[1]与上一行的差异。 [2] 是往上数两行。
=B8=!Qty[-1]下一行。
=B7/B$2=!Qty / !Qty[$1]与第一行相比,该行在每一行中都固定不变。
=SUM(B5:B7)=SUM(!Qty[2]:!Qty)这一行以及它前面的两行。
=SUM(C7:E7)=SUM(!Jan:!Mar)跨 Jan 到 Mar 的各列。
=B7*TaxRate=!Qty * @rate一个命名的值。 @rate 读取一个 -- sqlly set 或者 -- sqlly param: 来自查询的值。

用双引号括起带空格的列名: !"Order Total",或 !"Order Total"[1] 针对前一行。名称不区分大小写,所以 !qty 也可以。

这些函数是 Excel 的,名称、参数和结果相同: IF, IFERROR, SUM, ROUND, TEXT, LEFT, DATE, EOMONTH, NETWORKDAYS 还有大约一百个。运算符也是如此,包括 & 用于拼接文本和 %。这个 参考页 列出每个函数。额外的一个来自 SQL: COALESCE(a, b, …) 返回第一个非空值。

查看公式读取的内容

Excel 在你输入公式时会给每个引用上色,并在单元格周围绘制匹配的框。SQLly 也一样:

  • 每个引用都有自己的颜色 - 按 Excel 的顺序:蓝色、红色、紫色、绿色、橙色,依此类推。你使用两次的引用会保持一种颜色。带颜色的公式在输入时显示在字段下方。
  • 网格会框出它读取的单元格 - 使用相同的颜色,并给这些列加上色调。轮廓会画在你选择的行,或者如果没有选择行,则画在第一行。 !Qty[1] 会在上一行的单元格周围明显地绘制边框。
  • 错误会在字段下方逐条写明 - 比如“有一个 ) 没有匹配的 (。”或者提示将某列写为 !Name,以及 添加列 等待直到公式正确读取。

应用它会发生什么

该列直接基于屏幕上已有的行算出:不向数据库发送任何请求,也不会重新运行查询,所以即使查询本身很慢,这一列也是瞬间出结果。

SQLly 还会把公式作为一行注释写入你的查询,位于所执行语句的顶部:

-- sqlly column: Line Total = !Qty * !Price; after=Price
SELECT OrderId, Product, Qty, Price FROM order_lines ORDER BY OrderId;

列能回来靠的就是这行注释。明天再运行查询,保存 .sql 文件,发给同事即可:那一列每次都在。数据库会忽略这条注释,所以查询在别的地方照样能跑。只需这一处修改,因此 ⌘Z (CtrlZ 在 Windows 和 Linux 上)会将其从查询中移出。

SQLly
一个带有 Line Total 和 Running Total 两个计算列的结果,两个列紧挨在它们所读取的列旁边,查询顶部是 sqlly 列指令行。一个带有 Line Total 和 Running Total 两个计算列的结果,两个列紧挨在它们所读取的列旁边,查询顶部是 sqlly 列指令行。
一个带有 Line Total 和 Running Total 两个计算列的结果,两个列紧挨在它们所读取的列旁边,查询顶部是 sqlly 列指令行。

建好之后,计算列就和任何其他列一样了。可以排序、筛选、设置数字格式,还可以用 条件格式,或者把它用在 数据透视表.

读取并编辑公式

  • 点击计算列中的任意单元格 同时公式栏会以只读方式显示其公式,颜色和边框与网格一致。这是查清某个数字怎么算出来的最快办法。
  • 点击编辑公式 在该栏中,或右键点击列标题并选择 编辑公式…,来更改它。按钮现在显示 更新列.
  • 应用编辑会替换查询中的列行 ,而不是另加一行,无论它位于何处,包括锁定块内。在“名称”字段中重命名该列,这一行也会随之重命名。
SQLly
计算列中选中的单元格:公式栏以只读方式显示其公式,引用带颜色标注,并配有编辑公式按钮。计算列中选中的单元格:公式栏以只读方式显示其公式,引用带颜色标注,并配有编辑公式按钮。
计算列中选中的单元格:公式栏以只读方式显示其公式,引用带颜色标注,并配有编辑公式按钮。

值得借鉴的公式

列公式
利润率 %=IF(!Revenue = 0, 0, (!Revenue - !Cost) / !Revenue)
累计总和=!Amount + N(!"Running Total"[1]) (给列起名 滚动累计)
与上一行的变化=IF(ISBLANK(!Sales[1]), 0, !Sales - !Sales[1])
增长 %=IFERROR(!Sales / !Sales[1] - 1, 0)
以第一个月为基准=!Sales / !Sales[$1]
3 个月滚动合计=SUM(!Sales[2]:!Sales)
发货天数=!ShippedDate - !OrderDate
月底到期=EOMONTH(!InvoiceDate, 1)
逾期天数=MAX(0, TODAY() - !DueDate)
可读标签=!Customer & ": " & TEXT(!Amount, "$#,##0.00")
最佳联系人=COALESCE(!Email, !Phone, "none on file")
等级=IFS(!Amount >= 10000, "Gold", !Amount >= 1000, "Silver", TRUE, "Bronze")

累计总计之所以可行,是因为计算列可以读取自己在前一行上的值。公式还可以读取同一行上在它之前添加的任何计算列。

与电子表格的不同之处

  • 行顺序来自查询。 [1] 表示数据库在这之前发送的行。要做累计合计或与上个月相比的变化,请在查询末尾加上一个 ORDER BY 这样各行就会按你想要的顺序返回。之后再对网格排序,只会让值跟着行移动,并不会重新计算它们。
  • 每列一个公式。 和 Excel 表格中的计算列一样,每一行都用同一个公式。无法单独覆盖某个单元格。
  • 空值就是空单元格。 A NULL 来自数据库的空值在算术里按 0 计算,作为文本则是空白,就像 Excel 中的空单元格一样。第一行上方也是空的。
  • 错误看起来像 Excel 的。 除以零会显示 #DIV/0! 在该单元格中,函数拼写错误 #NAME?,等等。把公式包在 IFERROR 就像在 Excel 中一样。
  • 类型由公式决定。 日期运算给出日期列,比较给出 TRUE/FALSE,文本函数给出文本。如果一个 IF 可以返回数字或 "",列会变成文本;返回 0 来保持它的数值形式。
  • 超大结果集。 结果太大装不进内存时,会从磁盘一次读取一页,公式只能看到当前页的行。于是累计总计会在每一页重新开始。

将它与查询保存在一起

该公式已经作为 -- sqlly column: 行。如果你再点击 锁 在结果名称前面,SQLly 会把计算列连同网格的其他设置一起写入锁定块:列顺序、宽度、格式和颜色。

带进 Excel

将结果导出到 Excel,或者使用 在电子表格中打开,每个计算列都会变成真正的 Excel 公式。 =!Qty * !Price 工作表第 7 行上的会变成 =B7*C7, !Qty[1] 变为上方的行, !Qty[$1] 变为 B$2,以及 @rate 会成为工作簿中的一个命名值。在 Excel 里改一个数字,对应的列就会重新计算。详情见 Excel 导出.