如何从仅追加订单表中统计有效订单总数？

表结构 #

CREATE TABLE orders (
    order_id INT, 
    line_type VARCHAR(10),
    record_timestamp TIMESTAMPTZ
);

现有数据 #

INSERT INTO orders (order_id, line_type, record_timestamp) VALUES (1, 'ordered', CURRENT_TIMESTAMP);
INSERT INTO orders (order_id, line_type, record_timestamp) VALUES (1, 'shipped', CURRENT_TIMESTAMP);
INSERT INTO orders (order_id, line_type, record_timestamp) VALUES (2, 'ordered', CURRENT_TIMESTAMP);
INSERT INTO orders (order_id, line_type, record_timestamp) VALUES (2, 'ordered', CURRENT_TIMESTAMP);

需求与问题 #

需要统计每个order_id的有效记录：

• 只要该订单存在shipped类型的记录，就只统计所有shipped记录
• 没有shipped记录时，统计所有ordered记录

当前使用的SQL会把所有记录（包括已发货订单的ordered记录）都计入统计，结果不符合需求：

SELECT COUNT(*), order_id FROM orders
GROUP BY order_id

解决方案 #

方法1：先标记订单状态再筛选统计 #

通过CTE先判断每个订单是否存在已发货记录，再筛选出对应类型的记录进行统计：

WITH order_status AS (
    SELECT 
        order_id,
        BOOL_OR(line_type = 'shipped') AS has_shipped
    FROM orders
    GROUP BY order_id
)
SELECT 
    COUNT(o.line_type) AS valid_count,
    o.order_id
FROM orders o
JOIN order_status os ON o.order_id = os.order_id
WHERE 
    (os.has_shipped AND o.line_type = 'shipped')
    OR (NOT os.has_shipped AND o.line_type = 'ordered')
GROUP BY o.order_id;

方法2：用条件计数简化逻辑 #

直接在聚合查询中通过CASE和EXISTS判断订单状态，完成统计：

SELECT
    order_id,
    CASE
        WHEN EXISTS (SELECT 1 FROM orders o2 WHERE o2.order_id = o1.order_id AND o2.line_type = 'shipped')
        THEN COUNT(CASE WHEN line_type = 'shipped' THEN 1 END)
        ELSE COUNT(CASE WHEN line_type = 'ordered' THEN 1 END)
    END AS valid_count
FROM orders o1
GROUP BY order_id;

结果验证 #

执行上述任意SQL后，会得到符合需求的结果：

• order_id 1 的统计数为1（仅统计1条shipped记录）
• order_id 2 的统计数为2（统计2条ordered记录）

若某订单存在多条shipped记录，所有shipped记录都会被计入统计，且不会包含该订单的ordered记录。

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