10个SQL技巧之三:进行总计算

19-01-03 banq
         

至少有十几种方法可以实现运行总计。从概念上讲,运行总计很容易理解。在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等子句中选择的数据集中,因此非常快。