10个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等子句中选择的数据集中，因此非常快。