计算列

功能的

添加以 Excel 公式编写的列:累计总和、利润率、相对上一行的变化以及日期。

这 column: 指令会为结果添加一列,用 Excel 公式从结果自身的列计算得出。公式语言就是 Excel 的:同样的运算符、同样的优先级、同样的函数和参数,错误值也一样。唯一的区别是单元格的引用方式:结果的列是有名字的,而不是字母编号,所以引用要用 !.

-- sqlly column: Line Total = !Qty * !Price
-- sqlly column: Margin % = IF(!Revenue = 0, 0, (!Revenue - !Cost) / !Revenue); after=Cost
SELECT OrderId, Qty, Price, Revenue, Cost FROM order_lines ORDER BY OrderId;

数据库本身看不到该公式。SQLly 会在行到达时计算该列,而 Excel 导出 将其作为实时 Excel 公式编写。你无需键入指令:右键单击列标题并选择 添加计算列…,SQLly 会为你写好该行。该 计算列指南 会引导你浏览公式栏。

语法

-- sqlly column: <Name> = <formula>[; after=<column>][; result=<n>]
部分含义
<Name>新列的名称。它可以包含空格和标点,但不能包含 =.
<formula>Excel 公式。开头的 = 是可选的:Excel 需要它,SQLly 有没有都不影响。
after=<column>将新列放在此列之后。如果不指定,新列将放在末尾。
result=<n>只把它加到批次中第 n 个结果集上(从 1 开始计数)。省略时,该列会加入每个包含其所需列的结果集。

A ; 位于引号字符串或括号内的内容都属于公式的一部分,因此 TEXTJOIN("; ", TRUE, !A, !B) 按原样工作。后面的 column: 同名的行会替换早先的那一行。结果中已有的列不会被替换:如果查询返回的是 Total,计算得到的 Total 会在该结果中被跳过。

引用

行按查询返回的顺序编号,因此每当公式读取另一行时添加一个 ORDER BY 每当公式读取另一行时。列名匹配不区分大小写。

写入读取就像 Excel 的
!Qty本行的数量。B7 在第 7 行
!Qty[1]前一行的数量。B6
!Qty[3]向上三行的数量。B4
!Qty[-1]下一行的数量。B8
!Qty[$1]第一行的数量,无论你在第几行。B$2
!"Order Total"名称中包含空格或标点的列,用双引号括起来。也适用于行偏移: !"Order Total"[1].
!Qty[2]:!Qty一个范围:本行与前两行中的 Qty。B5:B7
!Jan:!Mar跨列范围:本行从 Jan 到 Mar 的每一列,按结果的列顺序。C7:E7
  • 第一行之前为空。 !Qty[1] 在第一行会读取一个空单元格,算术运算中按 0 计,而且 "" 在文本中。同样适用于 !Qty[-1] 在最后一行。
  • 一列,其公式引用了一列,但结果没有该列 会在该结果中被省略,这样一条指令就能服务于结果集各不相同的批处理。
  • 范围放在函数内部。 SUM(!Qty[2]:!Qty) 没有问题;而单独一个 !Qty[2]:!Qty 因为整个公式是 #VALUE!,就像在 Excel 中一样。

变量

@name 读取查询所声明的值,这样一个数字就能同时驱动 SQL 和公式:

-- sqlly set rate = 0.0825
-- sqlly param: @region text = West
-- sqlly column: Tax = ROUND(!Amount * @rate, 2)
-- sqlly column: Label = @region & " - " & !Customer
SELECT Customer, Amount FROM invoices WHERE Region = @region;
  • -- sqlly set 值 当它们看起来像数字时就读作数字,否则读作文本。
  • -- sqlly param: 值 保留其声明的类型: int 和 decimal 作为数字, bool 作为 TRUE 或 FALSE, date 和 datetime 作为真实日期,其余作为文本。在参数条中输入的值优先于声明的默认值,并且 null 读取为空。
  • 未声明的变量 会得到 #NAME?.

运算符

与 Excel 相同的运算符,按结合力从最松到最紧:

操作员含义
= <> < > <= >=比较,返回 TRUE 或 FALSE。文本比较不区分大小写,所以 "a" = "A" 为 TRUE。
&连接文本: !First & " " & !Last.
+ -加减运算。
* /乘法和除法。
^幂。
%百分比,写在数字之后: 15% 是 0.15。
- (在前面)取反。
:范围,介于两个参考值之间。

Excel 的这些怪癖是刻意沿用的,这样公式在网格和工作簿里给出同样的答案:取负号的优先级高于 ^,因此 -2^2 是 4;同优先级的运算符从左到右依次计算,因此 2^3^2 是 64;并且 1+2&3 是文本 "33"。存疑时请使用括号。看起来像数字的文本在算术中就是数字("3"+4 是 7),TRUE 计作 1。

空白、NULL 和错误

  • A NULL 是空白单元格。 在算术中它算作 0,在文本中则算作 "" 以文本形式显示,就像 Excel 中的空单元格一样。 ISBLANK() 可以将空值与零区分开来。
  • 错误与 Excel 的一样 - 而且它们在公式中也以同样的方式流转: #DIV/0! (除以零), #VALUE! (类型错误的值,比如 "x"+1), #REF! (不存在的列), #NAME? (未知的函数或变量), #NUM! (超出范围的数字,比如 SQRT(-1)),以及 #N/A (无值,来自 NA() 或者一个 IFS 未匹配的情况)。错误会在单元格中显示;它从不会阻止查询。
  • 像在 Excel 中那样捕获它们: IFERROR(!Profit / !Units, 0).

函数

下面每个函数的参数与 Excel 相同,顺序也相同,结果也一样。SQLly 不认识的名称会给出 #NAME?.

逻辑
IF, IFS, IFERROR, IFNA, AND, OR, NOT, XOR, SWITCH, CHOOSE, TRUE, FALSE,以及 COALESCE (见下文)。
信息
ISBLANK, ISNUMBER, ISTEXT, ISLOGICAL, ISERROR, ISERR, ISNA, ISEVEN, ISODD, N, T, NA.
数学与统计
ABS, SIGN, ROUND, ROUNDUP, ROUNDDOWN, MROUND, INT, TRUNC, CEILING, CEILING.MATH, FLOOR, FLOOR.MATH, EVEN, ODD, MOD, QUOTIENT, POWER, SQRT, EXP, LN, LOG, LOG10, PI, SIN, COS, TAN, DEGREES, RADIANS, SUM, PRODUCT, AVERAGE, MEDIAN, MIN, MAX, COUNT, COUNTA, COUNTBLANK.
文本
LEFT, RIGHT, MID, LEN, UPPER, LOWER, PROPER, TRIM, CLEAN, CONCAT, CONCATENATE, TEXTJOIN, SUBSTITUTE, REPLACE, REPT, FIND, SEARCH, EXACT, TEXT, FIXED, VALUE, CHAR, CODE, TEXTBEFORE, TEXTAFTER.
日期和时间
DATE, TIME, TODAY, NOW, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, WEEKDAY, WEEKNUM, ISOWEEKNUM, EDATE, EOMONTH, DATEDIF, DAYS, DATEVALUE, TIMEVALUE, NETWORKDAYS, WORKDAY.
  • COALESCE(a, b, …) 是 SQLly 从 SQL 借鉴来的唯一新增指令:第一个既不是空白也不为 "",否则取最后一个。 COALESCE(!Nickname, !FirstName, "Customer")。在 Excel 导出中,它被写作嵌套的 IF,因为 Excel 没有这样的函数。
  • 日期的工作方式与 Excel 相同。 来自数据库的日期或时间列被读取为真实日期,因此 !ShippedOn - !OrderedOn 是天数, !Due + 30 是日期,而 EDATE, EOMONTH 和 DATEDIF 表现如你所期望的。
  • TODAY() 和 NOW() 在查询运行时读取你计算机的时钟。
  • TEXT 接受 Excel 的格式代码: "#,##0.00", "0.0%", "000", "$#,##0", "yyyy-mm-dd", "mmm d, yyyy", "dddd", "h:mm AM/PM", "hh:mm:ss".

示例

利润率按占收入的比例计算,已防除零,放在“成本”列之后。给它 -- sqlly format: column=Margin %; number=1; style=percent 显示为百分比:

-- sqlly column: Margin % = IF(!Revenue = 0, 0, (!Revenue - !Cost) / !Revenue); after=Cost

累计总计。该列读取前一行的自身数值; N() 将第一行上方的空白变为 0:

-- sqlly column: Running Total = !Amount + N(!"Running Total"[1])
SELECT InvoiceDate, Amount FROM invoices ORDER BY InvoiceDate;

与上一行相比的变化,以数值和百分比两种形式显示。第一行之前没有其他行,因此两个值在该处都显示为 0,而不是显示完整的第一个值或一个 #DIV/0!:

-- sqlly column: Change = IF(ISBLANK(!Sales[1]), 0, !Sales - !Sales[1])
-- sqlly column: Change % = IFERROR(!Sales / !Sales[1] - 1, 0)

各月占首月的百分比:

-- sqlly column: vs First = !Sales / !Sales[$1]

最近三行的滚动总和与平均值:

-- sqlly column: 3-Month Sales = SUM(!Sales[2]:!Sales)
-- sqlly column: 3-Month Avg = AVERAGE(!Sales[2]:!Sales)

两个日期之间的天数、工作日以及下个月月底的截止日期:

-- sqlly column: Days to Ship = !ShippedDate - !OrderDate
-- sqlly column: Working Days = NETWORKDAYS(!OrderDate, !ShippedDate)
-- sqlly column: Due = EOMONTH(!InvoiceDate, 1)

由数字和日期拼成的文本,借助 TEXT:

-- sqlly column: Summary = !Customer & ": " & TEXT(!Amount, "$#,##0.00") & " on " & TEXT(!OrderDate, "mmm d, yyyy")

列为空时的回退值,以及分级标签:

-- sqlly column: Contact = COALESCE(!Email, !Phone, "none on file")
-- sqlly column: Tier = IFS(!Amount >= 10000, "Gold", !Amount >= 1000, "Silver", TRUE, "Bronze")

列是如何计算的

  • 按照它们声明的顺序。 公式可以读取同一行里在它之前声明的计算列,也可以读取之前任何行里的计算列,包括它自己。正因如此,才做得出累计值。
  • 按照行到达的顺序。 [1] 总是指数据库在此行之前发送的行,而不是在你排序网格后位于它上面的行。对网格进行排序或过滤会将计算值随其行移动;它不会重新计算这些值。
  • 每列的类型由其公式决定。 算术运算得到数字;日期函数和日期运算得到日期(因此日期格式和日期筛选对它们有效);而比较运算 AND, OR 和 IS 函数返回 TRUE/FALSE;文本函数和 & 给出文本。一个 IF 的各分支类型不同,结果就是文本,所以 IF(…, "", !A - !B) 创建一个文本列;使用 0 代替 "" 让语句保持精简。
  • 哪些结果集会显示该列。 任何包含公式所需全部列的结果集,或仅包含结果 n 且 result=n.
  • 格式化方式与其他任何列相同。 format:, conditional:, grid: 以及透视表在内都把计算列视为数据库返回的列。
  • 超大结果集。 内存装不下的大结果会从磁盘分页读取。公式只能看到每一页加载进来的行(外加一小段回看,用于 [n] 处引用),因此累计总计会在每一页重新开始。要得到长期的累计总计,可以让数据库用窗口函数计算,例如 SUM(Amount) OVER (ORDER BY InvoiceDate).
锁会把它们保留下来。 点击 锁 在结果上,将其计算列与网格其余设置一起写入锁定块,因此每次运行时都会返回这些列。