计算列
功能的添加以 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).