計算欄位

功能性的

新增以 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).
鎖定會保留它們。 點擊 lock 在結果上會將其計算欄位連同網格其餘設定一併寫入鎖定區塊,因此每次執行都會恢復。