PostgreSQL：按客户与订单分组计算数组元素匹配占比

现有orders表，包含client_id、order_id、items_current_order（当前订单商品数组）、items_next_order（下一订单商品数组）字段，需针对每个client_id + order_id组合，计算items_next_order中存在于当前订单items_current_order的元素占items_next_order总元素的比例。

正确SQL实现 #

方法1：Unnest拆分数组统计（通用兼容） #

这种写法不依赖特定版本的数组函数，兼容性更强：

WITH order_next_items AS (
    SELECT
        client_id,
        order_id,
        items_current_order,
        items_next_order,
        unnest(items_next_order) AS next_item,
        -- 提前计算下一订单的总商品数量
        cardinality(items_next_order) AS total_next_count
    FROM orders
)
SELECT
    client_id,
    order_id,
    items_current_order,
    items_next_order,
    -- 处理空数组场景，避免除以0错误
    CASE
        WHEN total_next_count = 0 THEN 0.0
        ELSE COUNT(CASE WHEN next_item = ANY(items_current_order) THEN 1 END)::FLOAT / total_next_count::FLOAT
    END AS share
FROM order_next_items
GROUP BY client_id, order_id, items_current_order, items_next_order, total_next_count;

方法2：数组交集函数简化写法（PostgreSQL 9.3+） #

如果你的PostgreSQL版本支持array_intersect函数，可以用更简洁的方式实现：

SELECT
    client_id,
    order_id,
    items_current_order,
    items_next_order,
    CASE
        WHEN cardinality(items_next_order) = 0 THEN 0.0
        ELSE cardinality(array_intersect(items_next_order, items_current_order))::FLOAT / cardinality(items_next_order)::FLOAT
    END AS share
FROM orders;

原SQL的问题分析 #

你之前的SQL存在两个核心问题：

• 未关联当前订单的商品数组：用rj.client_id = a.client_id关联的是客户所有订单的商品集合，而非当前order_id对应的items_current_order，导致统计逻辑偏离需求。
• 丢失order_id维度：仅按client_id分组，无法输出每个订单单独的占比结果，不符合按client_id + order_id分组的要求。

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