計算欄位

功能性的

新增一個用 Excel 公式、從結果自身欄位算出的欄位,具備 Excel 的資料編輯列、顏色與函式。

如果你會寫 Excel 公式,就能在 SQLly 中為任何查詢結果新增欄位。無論是利潤、累計總和、與上月相比的變化,還是兩個日期之間的工作天數:輸入你在 Excel 中會輸入的公式,新欄位就會出現在資料旁邊。你不需要會用 SQL 寫出來,也不需要重新執行查詢。

它的運作方式就像 Excel 資料表中的計算欄位:整欄使用同一個公式,每一列都會計算。唯一需要學的新東西是如何指向某個儲存格,因為查詢結果使用欄位名稱而不是字母。

新增你的第一個計算欄位

  1. 執行查詢。 任何結果都可以;例如含 Qty 和 Price 欄位。
  2. 右鍵點擊標題 新欄位應該跟隨的欄位,然後選擇 新增計算欄位….
  3. 公式列會顯示在結果上方: fx、一個名稱欄位、 =,以及公式欄位。SQLly 會填入一個名稱(Computed),並用你點的欄位作為公式的開頭,例如 =!Price.
  4. 為欄位命名並輸入公式 - 例如 小計 和 =!Qty * !Price.
  5. 按 Enter 鍵 或點擊 新增欄位. Esc鍵 或 取消 會關閉輸入列,不做任何變更。
SQLly
結果上方的公式列:公式中的每個參照都有自己的顏色,而它讀取的儲存格在網格中以相同的顏色標示。結果上方的公式列:公式中的每個參照都有自己的顏色,而它讀取的儲存格在網格中以相同的顏色標示。
結果上方的公式列:公式中的每個參照都有自己的顏色,而它讀取的儲存格在網格中以相同的顏色標示。

從 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)橫跨一月到三月的欄位。
=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 上)會從訊息分頁回到結果,而且

SQLly
一個帶有兩個計算欄位(Line Total 和 Running Total)的結果,緊鄰它們讀取的欄位,而 sqlly 欄位註解行則位於查詢頂端。一個帶有兩個計算欄位(Line Total 和 Running Total)的結果,緊鄰它們讀取的欄位,而 sqlly 欄位註解行則位於查詢頂端。
一個帶有兩個計算欄位(Line Total 和 Running Total)的結果,緊鄰它們讀取的欄位,而 sqlly 欄位註解行則位於查詢頂端。

一旦建好,計算欄位就跟其他欄位沒兩樣:可以排序、篩選、給它數字格式,也可以替它上色,作法是透過 條件格式,或將它用於一個 樞軸.

讀取與編輯公式

  • 點擊計算欄中的任意儲存格 ,資料編輯列就會以唯讀方式顯示它的公式,顏色與外框都相同。這是查一個數字怎麼算出來的最快方法。
  • 點擊編輯公式 在該列上,或者以右鍵點擊欄位標題並選擇 編輯公式…,即可更改。按鈕上現在寫的是 更新欄位.
  • 套用編輯會取代該欄位在查詢中的那一行 在查詢中原地修改,而不是新增第二條,無論它在哪裡,包括鎖定區塊內也一樣。在「名稱」欄位重新命名欄位,那一行也會跟著改名。
SQLly
計算欄位中選取的儲存格,公式以唯讀顯示在公式欄中,參照以顏色標示,並有「編輯公式」按鈕。計算欄位中選取的儲存格,公式以唯讀顯示在公式欄中,參照以顏色標示,並有「編輯公式」按鈕。
計算欄位中選取的儲存格,公式以唯讀顯示在公式欄中,參照以顏色標示,並有「編輯公式」按鈕。

值得借用的公式

欄位公式
利潤率 %=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]
滾動三個月總計=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: 行。如果你也點擊 lock 在結果名稱前面時,SQLly 會把計算欄位與格線的其餘設定一起寫入鎖定區塊:欄位順序、寬度、格式與顏色。

匯出到 Excel

將結果匯出到 Excel,或使用 在試算表中開啟,每個計算欄位都會作為真正的 Excel 公式傳入。 =!Qty * !Price 工作表第 7 列上的會變成 =B7*C7, !Qty[1] 會變成上一列, !Qty[$1] 變成 B$2,而且 @rate 會變成活頁簿中的命名值。在 Excel 中更改一個數字,該欄會重新計算。詳細資訊在 Excel 匯出.