通常在SQL中处理表时，人们可能希望聚合值，或计算表中的值之间的运行总和。
在本文中，我们将研究如何使用所谓的窗口函数来实现这一点。
此外，我们将看到case语句也可以嵌套在窗口函数中，以进一步自定义可以基于某些条件在数组上执行的计算。
使用窗口函数的 SUM 和 AVERAGE
以下某个商店中列出的服装项目的假设表 (值由作者编撰)。
—————-+——————–+———+——–
2021-01-01 | Sky Blue Jeans | 79.99 | 31
2021-01-02 | Fire Red Jeans | 89.99 | 36
2021-01-03 | Sky Blue Shirt | 59.99 | 38
2021-01-04 | Grass Green Shirt | 69.99 | 34
2021-01-05 | Peach Purple Hat | 79.99 | 40
2021-01-06 | Sun Yellow Jeans | 109.99 | 42
2021-01-07 | Olive Green Hat | 89.99 | 37
现在，假设所有者想要在累积的基础上对每个值求和和求平均值，即创建一个新数组，显示前两个值的总和，然后是前三个值，依此类推。这同样适用于计算平均值。
窗口函数可用于对值求和，如下所示 (前五行所示)
>>> SUM(price) OVER (ORDER BY date)
>>> AS total_price
>>> FROM table;
date | price | total_price
———————+———+————
2021-01-01 | 79.99 | 79.99
2021-01-02 | 89.99 | 169.98
2021-01-03 | 59.99 | 229.97
2021-01-04 | 69.99 | 299.96
2020-01-05 | 79.99 | 379.95
(5 rows)
同样，也可以计算平均累计价格。
>>> AVG(price) OVER (ORDER BY date)
>>> AS mean_price
>>> FROM table;
date | price | mean_price
———————+———+————
2021-01-01 | 79.99 | 79.99
2021-01-02 | 89.99 | 84.99
2021-01-03 | 59.99 | 76.66
2021-01-04 | 69.99 | 74.99
2020-01-05 | 79.99 | 75.99
(5 rows)
将CASE语句与窗口函数结合起来
CASE语句的功能与if-then语句类似。如果满足条件，则返回特定值; 否则，如果不满足条件，则返回另一个值。
让我们考虑这个例子。假设对于这家特定的服装店，商家必须对某些商品提供退款。这怎么能反映在运行总量中？
考虑这个扩展的表格。
—————-+——————–+———+——–+———
2021-01-01 | Sky Blue Jeans | 79.99 | 31 | no
2021-01-02 | Fire Red Jeans | 89.99 | 36 | no
2021-01-03 | Sky Blue Shirt | 59.99 | 38 | no
2021-01-04 | Grass Green Shirt | 69.99 | 34 | yes
2021-01-05 | Peach Purple Hat | 79.99 | 40 | yes
2021-01-06 | Sun Yellow Jeans | 109.99 | 42 | no
2021-01-07 | Olive Green Hat | 89.99 | 37 | no
从上面可以看出，商家在1月4日和5日提供退款。要计算新的累计总和，这些值需要减去-而不是添加到总数中。
在这方面，CASE语句嵌套在窗口函数中-如果退款变量包含一个是值。
date | price | total_price | refund
———————+———+————–+————
2021-01-01 | 79.99 | 79.99 | no
2021-01-02 | 89.99 | 169.98 | no
2021-01-03 | 59.99 | 229.97 | no
2021-01-04 | 69.99 | 159.98 | yes
2020-01-05 | 79.99 | 79.99 | yes
2020-01-06 | 79.99 | 189.98 | no
2020-01-07 | 79.99 | 279.97 | no
(5 rows)
如我们所见，279.97 的总价是在 1 月 4 日和 5 日减去 69.99 和 79.99 的价格后计算得出的——这是由 CASE 语句执行的，因为退款变量的 yes 条目会导致负价格由 CASE 语句赋值。
结论
在本文中，您已经看到:
- 使用窗口函数的目的
- 如何使用窗口函数获取累积值
- 将窗口函数与CASE语句结合起来，使窗口函数更加灵活
转载请注明：XAMPP中文组官网 » SQL中的窗口函数：聚合值