計算欄位
功能性的新增以 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).