最新消息:XAMPP默认安装之后是很不安全的,我们只需要点击左方菜单的 "安全"选项,按照向导操作即可完成安全设置。

SQL中的窗口函数:聚合值

XAMPP相关 admin 80浏览 0评论

通常在SQL中处理表时,人们可能希望聚合值,或计算表中的值之间的运行总和。

在本文中,我们将研究如何使用所谓的窗口函数来实现这一点。

此外,我们将看到case语句也可以嵌套在窗口函数中,以进一步自定义可以基于某些条件在数组上执行的计算。

 

使用窗口函数的 SUM 和 AVERAGE

以下某个商店中列出的服装项目的假设表 (值由作者编撰)。

date            | item               | price   | size

—————-+——————–+———+——–

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

 

现在,假设所有者想要在累积的基础上对每个值求和和求平均值,即创建一个新数组,显示前两个值的总和,然后是前三个值,依此类推。这同样适用于计算平均值。

窗口函数可用于对值求和,如下所示 (前五行所示)

 

>>> SELECT date,price,

>>>   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)

 

同样,也可以计算平均累计价格。

>>> SELECT date,price,

>>>   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语句类似。如果满足条件,则返回特定值; 否则,如果不满足条件,则返回另一个值。

让我们考虑这个例子。假设对于这家特定的服装店,商家必须对某些商品提供退款。这怎么能反映在运行总量中?

考虑这个扩展的表格。

 

date            | item               | price   | size   | refund

—————-+——————–+———+——–+———

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语句嵌套在窗口函数中-如果退款变量包含一个是值。

 

>>> SELECT date,price,refund,SUM(CASE WHEN refund = ‘yes’ THEN -1*price ELSE price END) OVER (ORDER BY date) AS total_price FROM table;

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中的窗口函数:聚合值