IT评测·应用市场-qidao123.com技术社区

标题: 【SQL】晨光咖啡馆，过滤聚合的玄妙碰撞 [打印本页]

---

作者: 飞不高    时间: 2024-7-15 12:33
标题: 【SQL】晨光咖啡馆，过滤聚合的玄妙碰撞
 这天，小悦懒洋洋田地入办公楼下的咖啡馆，意外地与一位男子不期而遇。他显然因前一晚的辛勤工作而略显疲惫，却仍选择早到此地，寻找一丝宁静与放松。他叫逸尘，身姿挺拔，衣着简约而不失格调，晨光下更显英俊不凡，吸引了周遭的目光。两人仿佛心有灵犀，不约而同地走向各自的位置。
小悦手中轻握着新出炉的拿铁，眼睛紧紧盯着手机上的工作邮件，心思全然沉浸在工作的海洋中，对前方即将发生的“小插曲”浑然未觉。而逸尘，正欲伸手取桌上的文件，两人的手在不经意间悄然相遇，伴随着一阵稍微的碰撞，小悦手中的拿铁微微倾斜，几滴热烫的咖啡瞬间在逸尘洁白的衬衫上绽放，如同从树上不经意间洒落的晨露，虽美却略显突兀。
“哎呀，真的非常抱歉！”小悦连忙道歉，脸颊上泛起了红晕，手忙脚乱地在包中搜寻纸巾，盼望能为这突如其来的尴尬局面做些什么。逸尘则以他特有的绅士风度，轻轻接过纸巾，自行处理惩罚起那片不速之客。
“没关系，下次小心些便是。”逸尘的话语中虽带有一丝不易察觉的责备，但更多的是温柔与宽容。他皱眉的瞬间，非但没有减少魅力，反而增添了几分成熟与稳重。
小悦心中五味杂陈，既有对自己疏忽的懊恼，也有对逸尘那不经意间流露出的严厉与温柔交错的复杂情感。她低声细语：“我真的不是故意的。”这句话虽轻如蚊蚋，却清晰地传入了逸尘的耳中，两人的心间仿佛被一股莫名的力量轻轻触碰，营造出一种难以言喻的玄妙氛围。
其时，小悦手机上的邮件内容深深吸引了她的留意，邮件中详细列出了一项sql任务：要求根据公司名称和月份进行分组，统计出2024年全年的订单表总数量，并进一步细分出已下单数量（状态1）、送货中数量（状态2）以及已收货数量（状态3）。这一挑战性的任务让小悦不禁陷入了沉思，她敏捷地在脑海中构想出了初步的方案1：

1. SELECT
2.     o.company,
3.     EXTRACT(MONTH FROM o.order_date) AS month,
4.     (SELECT COUNT(*) FROM orders o2 WHERE o2.company = o.company AND EXTRACT(MONTH FROM o2.order_date) = EXTRACT(MONTH FROM o.order_date) AND EXTRACT(YEAR FROM o2.order_date) = 2024) AS total_orders,
5.     (SELECT COUNT(*) FROM orders o2 WHERE o2.company = o.company AND EXTRACT(MONTH FROM o2.order_date) = EXTRACT(MONTH FROM o.order_date) AND o2.order_status = 1 AND EXTRACT(YEAR FROM o2.order_date) = 2024) AS ordered_count,
6.     (SELECT COUNT(*) FROM orders o2 WHERE o2.company = o.company AND EXTRACT(MONTH FROM o2.order_date) = EXTRACT(MONTH FROM o.order_date) AND o2.order_status = 2 AND EXTRACT(YEAR FROM o2.order_date) = 2024) AS delivering_count,
7.     (SELECT COUNT(*) FROM orders o2 WHERE o2.company = o.company AND EXTRACT(MONTH FROM o2.order_date) = EXTRACT(MONTH FROM o.order_date) AND o2.order_status = 3 AND EXTRACT(YEAR FROM o2.order_date) = 2024) AS received_count
8. FROM
9.     orders o
10. WHERE
11.     EXTRACT(YEAR FROM o.order_date) = 2024
12. GROUP BY
13.     o.company,
14.     EXTRACT(MONTH FROM o.order_date)
15. ORDER BY
16.     o.company,
17.     month;

复制代码

方案1查询语句使用了多个子查询来计算每个公司和月份的订单数量，虽然可以实现所需的功能，但也存在一些缺点：

• 性能问题：
  
  
  
  • 每个子查询都需要对orders表进行独立的扫描，这会导致多次重复的数据库查询，增加了数据库的负担。
    
  • 对于大型数据集，这种多次扫描和查询的方式会导致性能明显降落。
    
  
  
• 可读性和维护性：
  
  
  
  • 使用多个子查询使得SQL语句变得复杂，难以阅读和明白。
    
  • 如果需要修改或调试，需要逐个检查每个子查询，增加了维护的难度。
    
  
  
• 重复代码：
  
  
  
  • 相同的条件（如公司、月份、年份）在每个子查询中重复出现，导致代码冗余。
    
  • 如果需要修改这些条件，必须在每个子查询中逐一修改，容易遗漏或出错。
    
  
  
• 索引利用：
  
  
  
  • 子查询可能无法有效利用索引，尤其是在没有合适的索引情况下，查询性能会进一步降落。
    
  
  

 随后，小悦没有放弃，反而更加专注地投入到方案一的优化中。她仔细分析了初步方案的可行性，并考虑到了性能优化和数据处理惩罚服从的问题。于是，她提出了优化后的方案2(Oracle/MySql/Mssql)：

1. SELECT
2.     company,
3.     EXTRACT(MONTH FROM order_date) AS month,
4.     COUNT(*) AS total_orders,
5.     COUNT(CASE WHEN status = 1 THEN 1 END) AS ordered_count,
6.     COUNT(CASE WHEN status = 2 THEN 1 END) AS delivering_count,
7.     COUNT(CASE WHEN status = 3 THEN 1 END) AS received_count
8. FROM
9.     orders
10. WHERE
11.     EXTRACT(YEAR FROM order_date) = 2024
12. GROUP BY
13.     company,
14.     EXTRACT(MONTH FROM order_date)
15. ORDER BY
16.     company,
17.     month;

复制代码

方案2查询语句使用了COUNT(CASE WHEN ...)语法，具有以下长处：

• 性能优化：
  
  
  
  • 通过在一个查询中完成所有计算，避免了多次扫描和查询数据库，从而提高了查询性能。
    
  • 数据库引擎可以更好地优化查询计划，利用索引和缓存来加速查询。
    
  
  
• 轻巧性和可读性：
  
  
  
  • 使用COUNT(CASE WHEN ...)语法使得SQL语句更加轻巧，减少了冗余代码。
    
  • 查询逻辑清晰，易于阅读和明白，便于维护和调试。
    
  
  
• 减少重复代码：
  
  
  
  • 相同的条件（如公司、月份、年份）只需要在WHERE子句中写一次，避免了在多个子查询中重复书写相同的条件。
    
  • 如果需要修改查询条件，只需在一个地方进行修改，减少了出错的可能性。
    
  
  
• 机动性：
  
  
  
  • COUNT(CASE WHEN ...)语法非常机动，可以轻松地添加或修改条件，以顺应不同的查询需求。
    
  • 可以很容易地扩展到其他状态或条件，而不需要重构整个查询。
    
  
  
• 索引利用：
  
  
  
  • 这种查询方式可以更好地利用索引，尤其是在有合适的索引情况下，查询性能会得到进一步提拔。
    
  
  

小悦意识到虽然方案2的CASE语法可以实现需求，但使用COUNT FILTER语法在PostgreSQL中更为轻巧高效，而且由于国产数据库大多兼容PostgreSQL，这种选择不但提拔了查询性能，还确保了代码在国产数据库环境中的广泛实用性。方案3(PostgreSQL语法):，

1. SELECT
2.     company,
3.     EXTRACT(MONTH FROM order_date) AS month,
4.     COUNT(*) AS total_orders,
5.     COUNT(*) FILTER (WHERE status = 1) AS ordered_count,
6.     COUNT(*) FILTER (WHERE status = 2) AS delivering_count,
7.     COUNT(*) FILTER (WHERE status = 3) AS received_count
8. FROM
9.     orders
10. WHERE
11.     EXTRACT(YEAR FROM order_date) = 2024
12. GROUP BY
13.     company,
14.     EXTRACT(MONTH FROM order_date)
15. ORDER BY
16.     company,
17.     month;

复制代码

方案3中的COUNT(*) FILTER (WHERE status = 1)` 这种语法是 SQL:2003 标准引入的一个新特性,称为"过滤聚合"(Filtered Aggregation)。
过滤聚合的出现是为了解决一些常见的 SQL 分析需求,例如:
1. 在统计订单总数的同时,也统计已完成订单的数量。
2. 在统计贩卖总额的同时,也统计已付款订单的贩卖额。
3. 在统计某个商品的总销量中,也统计该商品的正常销量和退货销量。
在传统的 SQL 中,解决这类需求通常需要使用多个子查询或者分组之后进行过滤,代码会比较复杂。
过滤聚合的出现,让这类需求的实现变得更加简单和优雅。开辟者可以在聚合函数中直接加上 `FILTER (WHERE ...)` 子句,对聚合的数据进行过滤,从而得到所需的统计结果。
比如上面的例子中,`COUNT(*) FILTER (WHERE status = 1)` 就可以直接统计状态为 1 的订单数量,无需再额外添加子查询。
这种语法在 SQL:2003 标准中引入，PostgreSQL起首实现了这个语法。它极大地简化了 SQL 的编写,提高了代码的可读性和可维护性。
Oracle /MySql/MsSql,对于这个 SQL 标准的新特性,并没有直接支持，只能通过case when的形式实现。
示例，在Having中使用过滤聚合语法：

1. --case语法示例
2. SELECT
3.     company,
4.     EXTRACT(MONTH FROM order_date) AS month,
5.     COUNT(*) AS total_orders,
6.     COUNT(CASE WHEN status = 1 THEN 1 END) AS ordered_count,
7.     COUNT(CASE WHEN status = 2 THEN 1 END) AS delivering_count,
8.     COUNT(CASE WHEN status = 3 THEN 1 END) AS received_count
9. FROM
10.     orders
11. WHERE
12.     EXTRACT(YEAR FROM order_date) = 2024
13. GROUP BY
14.     company,
15.     EXTRACT(MONTH FROM order_date)
16. Having
17.     COUNT(CASE WHEN status = 1 THEN 1 END)>0
18. ORDER BY
19.     company,
20.     month;
22. --filter语法示例
23. SELECT
24.     company,
25.     EXTRACT(MONTH FROM order_date) AS month,
26.     COUNT(*) AS total_orders,
27.     COUNT(*) FILTER (WHERE status = 1) AS ordered_count,
28.     COUNT(*) FILTER (WHERE status = 2) AS delivering_count,
29.     COUNT(*) FILTER (WHERE status = 3) AS received_count
30. FROM
31.     orders
32. WHERE
33.     EXTRACT(YEAR FROM order_date) = 2024
34. GROUP BY
35.     company,
36.     EXTRACT(MONTH FROM order_date)
37. Having
38.     COUNT(*) FILTER (WHERE status = 1)>0
39. ORDER BY
40.     company,
41.     month;

复制代码

 

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

---

欢迎光临 IT评测·应用市场-qidao123.com技术社区 (https://dis.qidao123.com/)

Powered by Discuz! X3.4