【SQL】晨光咖啡馆，过滤聚合的微妙碰撞

2024-07-15 08:02 由 lanedm 发表于 #数据库

这天，小悦懒洋洋地步入办公楼下的咖啡馆，意外地与一位男子不期而遇。他显然因前一晚的辛勤工作而略显疲惫，却仍选择早到此地，寻找一丝宁静与放松。他叫逸尘，身姿挺拔，衣着简约而不失格调，晨光下更显英俊不凡，吸引了周遭的目光。两人仿佛心有灵犀，不约而同地走向各自的位置。

小悦手中轻握着新出炉的拿铁，眼睛紧紧盯着手机上的工作邮件，心思全然沉浸在工作的海洋中，对前方即将发生的“小插曲”浑然未觉。而逸尘，正欲伸手取桌上的文件，两人的手在不经意间悄然相遇，伴随着一阵轻微的碰撞，小悦手中的拿铁微微倾斜，几滴热烫的咖啡瞬间在逸尘洁白的衬衫上绽放，如同从树上不经意间洒落的晨露，虽美却略显突兀。

“哎呀，真的非常抱歉！”小悦连忙道歉，脸颊上泛起了红晕，手忙脚乱地在包中搜寻纸巾，希望能为这突如其来的尴尬场面做些什么。逸尘则以他特有的绅士风度，轻轻接过纸巾，自行处理起那片不速之客。

“没关系，下次小心些便是。”逸尘的话语中虽带有一丝不易察觉的责备，但更多的是温柔与宽容。他皱眉的瞬间，非但没有减少魅力，反而增添了几分成熟与稳重。

小悦心中五味杂陈，既有对自己疏忽的懊恼，也有对逸尘那不经意间流露出的严厉与温柔交织的复杂情感。她低声细语：“我真的不是故意的。”这句话虽轻如蚊蚋，却清晰地传入了逸尘的耳中，两人的心间仿佛被一股莫名的力量轻轻触碰，营造出一种难以言喻的微妙氛围。

当时，小悦手机上的邮件内容深深吸引了她的注意，邮件中详细列出了一项sql任务：要求根据公司名称和月份进行分组，统计出2024年全年的订单表总数量，并进一步细分出已下单数量（状态1）、送货中数量（状态2）以及已收货数量（状态3）。这一挑战性的任务让小悦不禁陷入了沉思，她迅速地在脑海中构想出了初步的方案1：

SELECT
    o.company,
    EXTRACT(MONTH FROM o.order_date) AS month,
    (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,
    (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,
    (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,
    (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
FROM
    orders o
WHERE
    EXTRACT(YEAR FROM o.order_date) = 2024
GROUP BY
    o.company,
    EXTRACT(MONTH FROM o.order_date)
ORDER BY
    o.company,
    month;

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

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

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

SELECT
    company,
    EXTRACT(MONTH FROM order_date) AS month,
    COUNT(*) AS total_orders,
    COUNT(CASE WHEN status = 1 THEN 1 END) AS ordered_count,
    COUNT(CASE WHEN status = 2 THEN 1 END) AS delivering_count,
    COUNT(CASE WHEN status = 3 THEN 1 END) AS received_count
FROM
    orders
WHERE
    EXTRACT(YEAR FROM order_date) = 2024
GROUP BY
    company,
    EXTRACT(MONTH FROM order_date)
ORDER BY
    company,
    month;

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

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

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

SELECT
    company,
    EXTRACT(MONTH FROM order_date) AS month,
    COUNT(*) AS total_orders,
    COUNT(*) FILTER (WHERE status = 1) AS ordered_count,
    COUNT(*) FILTER (WHERE status = 2) AS delivering_count,
    COUNT(*) FILTER (WHERE status = 3) AS received_count
FROM
    orders
WHERE
    EXTRACT(YEAR FROM order_date) = 2024
GROUP BY
    company,
    EXTRACT(MONTH FROM order_date)
ORDER BY
    company,
    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中使用过滤聚合语法：

--case语法示例
SELECT
    company,
    EXTRACT(MONTH FROM order_date) AS month,
    COUNT(*) AS total_orders,
    COUNT(CASE WHEN status = 1 THEN 1 END) AS ordered_count,
    COUNT(CASE WHEN status = 2 THEN 1 END) AS delivering_count,
    COUNT(CASE WHEN status = 3 THEN 1 END) AS received_count
FROM
    orders
WHERE
    EXTRACT(YEAR FROM order_date) = 2024
GROUP BY
    company,
    EXTRACT(MONTH FROM order_date)
Having
    COUNT(CASE WHEN status = 1 THEN 1 END)>0
ORDER BY
    company,
    month;

--filter语法示例
SELECT
    company,
    EXTRACT(MONTH FROM order_date) AS month,
    COUNT(*) AS total_orders,
    COUNT(*) FILTER (WHERE status = 1) AS ordered_count,
    COUNT(*) FILTER (WHERE status = 2) AS delivering_count,
    COUNT(*) FILTER (WHERE status = 3) AS received_count
FROM
    orders
WHERE
    EXTRACT(YEAR FROM order_date) = 2024
GROUP BY
    company,
    EXTRACT(MONTH FROM order_date)
Having
    COUNT(*) FILTER (WHERE status = 1)>0
ORDER BY
    company,
    month;