计算列
功能的可以用 Excel 公式基于结果自身的各列算出一列,并配有 Excel 的公式栏、颜色和函数。
如果你会写 Excel 公式,你就可以在 SQLly 的任何查询结果中添加一列。比如利润率、累计值、与上个月的变化、两个日期之间的工作日天数:输入你在 Excel 中会输入的公式,新列就会出现在数据旁边。你不需要知道如何用 SQL 编写,也不需要重新运行查询。
它的工作方式类似 Excel 表格中的计算列:整列共用一个公式,每一行都会计算一次。唯一要学的新东西是怎么指向一个单元格,因为结果里用的是列名而不是字母。
添加你的第一个计算列
- 运行一个查询。 任何结果都可以;例如一个带有
Qty和Price列。 - 右键点击标题 ,让新列紧跟在它后面,然后选择 添加计算列….
- 公式栏会在结果上方打开: fx、一个 Name 字段,
=和公式字段。SQLly 会自动填一个名称(Computed)并以你点击的列作为公式的起始引用,例如=!Price. - 为列命名并输入公式 - 例如 行总计 和
=!Qty * !Price. - 按 输入 或点击 添加列. ESC 或者 取消 会在不更改任何内容的情况下关闭该栏。


从 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 上)会将其从查询中移出。


建好之后,计算列就和任何其他列一样了。可以排序、筛选、设置数字格式,还可以用 条件格式,或者把它用在 数据透视表.
读取并编辑公式
- 点击计算列中的任意单元格 同时公式栏会以只读方式显示其公式,颜色和边框与网格一致。这是查清某个数字怎么算出来的最快办法。
- 点击编辑公式 在该栏中,或右键点击列标题并选择 编辑公式…,来更改它。按钮现在显示 更新列.
- 应用编辑会替换查询中的列行 ,而不是另加一行,无论它位于何处,包括锁定块内。在“名称”字段中重命名该列,这一行也会随之重命名。


值得借鉴的公式
| 列 | 公式 |
|---|---|
| 利润率 % | =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 导出.