聚合函数内统计唯一值：高效统计门店订单履约情况

嘿，这个需求其实可以用分组聚合+条件判断的思路高效解决，完全不用堆砌一堆昂贵的子查询，性能友好还容易理解！

核心思路 #

我们可以先在订单维度判断每个订单的履约状态（是完全履约还是存在缺货），再向上汇总到门店维度，全程只需要两次分组操作，避免了复杂的子查询或表关联。

通用SQL实现（适用于大多数关系型数据库） #

-- 先按门店+订单分组，判断每个订单的履约状态
WITH order_status AS (
    SELECT
        Store,
        OrderID,
        -- 标记该订单是否完全履约：只要有一个商品发货量<订购量，就不是完全履约
        CASE 
            WHEN MAX(CASE WHEN Shipped < Ordered THEN 1 ELSE 0 END) = 0 
            THEN 1  -- 完全履约
            ELSE 0  -- 存在缺货
        END AS is_good_order
    FROM your_business_table
    GROUP BY Store, OrderID
)
-- 再按门店汇总统计
SELECT
    Store,
    COUNT(OrderID) AS Orders,  -- 订单总数
    SUM(is_good_order) AS Good,  -- 完全履约订单数
    COUNT(OrderID) - SUM(is_good_order) AS Shorted  -- 存在缺货的订单数
FROM order_status
GROUP BY Store
ORDER BY Store;

代码解释 #

1. 订单状态判断层：
   • 按Store和OrderID分组，对每个订单下的所有商品做检查：用MAX(CASE...)判断是否存在Shipped < Ordered的商品。如果存在，MAX值为1，标记为非完全履约；否则为0，标记为完全履约。
2. 门店汇总层：
   • 直接统计每个门店的订单总数（COUNT(OrderID)），完全履约订单数是is_good_order的求和（因为完全履约的订单标记为1，求和就是数量），缺货订单数用总数减去完全履约数即可。

验证示例数据 #

用你提供的示例数据测试，这个SQL会输出：

完全符合期望结果。

简化写法（针对支持BOOL聚合函数的数据库） #

如果你的数据库支持BOOL_AND/BOOL_OR（比如PostgreSQL、BigQuery），可以写出更简洁的代码：

SELECT
    Store,
    COUNT(DISTINCT OrderID) AS Orders,
    SUM(CASE WHEN BOOL_AND(Shipped = Ordered) THEN 1 ELSE 0 END) AS Good,
    SUM(CASE WHEN BOOL_OR(Shipped < Ordered) THEN 1 ELSE 0 END) AS Shorted
FROM your_business_table
GROUP BY Store, OrderID
GROUP BY Store;

• BOOL_AND(Shipped = Ordered)：判断订单内所有商品都满足发货量=订购量（完全履约）
• BOOL_OR(Shipped < Ordered)：判断订单内至少有一个商品发货量<订购量（存在缺货）

性能优势 #

这个方案只做了两次分组操作，没有嵌套复杂的子查询或表关联，数据库可以高效利用索引（如果Store和OrderID有索引的话），相比多次子查询或EXISTS关联的写法，在大数据量场景下性能提升非常明显。

内容的提问来源于stack exchange，提问作者Wally Meier