10個SQL技巧之三：進行總計算

banq發表於2019-01-03
SQL

至少有十幾種方法可以實現執行總計。從概念上講，執行總計很容易理解。在Microsoft Excel中，您只需計算兩個先前（或後續）值的總和（或差異），然後使用有用的十字游標在整個電子表格中提取該公式。您透過電子表格“執行”該總計。一個“執行總數”。
在SQL中，最好的方法是使用視窗[url=https://blog.jooq.org/tag/window-functions]函式[/url]。

Window視窗函式是一個強大的概念 - 起初不是那麼容易理解，但實際上，它們真的很容易：

視窗函式是相對於SELECT轉換的當前行的行子集的聚合/排名

它本質上意味著視窗函式可以對當前行“上方”或“下方”的行執行計算。與GROUP BY普通聚合不同，它們不會對行進行轉換，這使得它們非常有用。
語法可以總結如下，單個部分是可選的

function(...) OVER (
  PARTITION BY ...
  ORDER BY ...
  ROWS BETWEEN ... AND ...
)

所以，我們有任何型別的函式（我們稍後會看到這些函式的例子），然後是OVER()子句，它指定了視窗。即OVER()條款定義：
  • PARTITION：只有在相同分割槽的行作為當前行會被認為是視窗
  • ORDER：該視窗可以獨立排序的，排序取決於我們要Select的是什麼
  • ROWS（或RANGE）幀定義：視窗可以限制到“超前”和“後面”的固定數目的行數

這就是視窗功能。
現在，這有助於我們計算一個總計？請考慮以下資料：

| ID   | VALUE_DATE | AMOUNT |    BALANCE |
|------|------------|--------|------------|
| 9997 | 2014-03-18 |  99.17 |   19985.81 |
| 9981 | 2014-03-16 |  71.44 |   19886.64 |
| 9979 | 2014-03-16 | -94.60 |   19815.20 |
| 9977 | 2014-03-16 |  -6.96 |   19909.80 |
| 9971 | 2014-03-15 | -65.95 |   19916.76 |


我們假設BALANCE是我們想要從AMOUNT計算的，因此，用簡單的英語，任何餘額都可以用以下偽SQL表示：
TOP_BALANCE – SUM(AMOUNT) OVER (“all the rows on top of the current row”)

在真正的SQL中，那將寫成如下：

SUM(t.amount) OVER (
  PARTITION BY t.account_id 
  ORDER BY     t.value_date DESC,
               t.id         DESC
  ROWS BETWEEN UNBOUNDED PRECEDING
       AND     1         PRECEDING
)


說明：
  • 分割槽將計算每個銀行帳戶的總和，而不是整個資料集
  • 排序將保證在求和之前（在分割槽內）對事務進行排序
  • 在彙總之前，rows子句將僅考慮前面的行（在分割槽內，給定排序）

所有這些都將在記憶體中發生，在您已經在FROM .. WHERE等子句中選擇的資料集中，因此非常快。


 

相關文章