PostgreSQL中统计items_next_order数组在items_current_order中的匹配数

你需要统计items_next_order数组中，能被items_current_order数组满足出现次数需求的元素总数——即对于每个元素，取当前订单中该元素的出现次数与下订单中该元素出现次数的较小值，最后将所有元素的这个值相加。

原SQL的问题在于使用cross join unnest会产生笛卡尔积，导致重复计数。比如第一个订单中，3个1和2个1交叉连接会得到6次匹配，完全不符合实际需求。

正确的思路是先分别统计两个数组中每个元素的出现次数，再对每个元素取两者的最小值求和：

SELECT
    t.order_id,
    t.items_current_order,
    t.items_next_order,
    COALESCE(SUM(LLEAST(current_counts.count, next_counts.count)), 0) AS count
FROM T t
LEFT JOIN (
    -- 统计当前订单各元素的出现次数
    SELECT
        order_id,
        item,
        COUNT(*) AS count
    FROM T, unnest(items_current_order) AS item
    GROUP BY order_id, item
) current_counts ON t.order_id = current_counts.order_id
LEFT JOIN (
    -- 统计下订单各元素的出现次数
    SELECT
        order_id,
        item,
        COUNT(*) AS count
    FROM T, unnest(items_next_order) AS item
    GROUP BY order_id, item
) next_counts 
    ON t.order_id = next_counts.order_id 
    AND current_counts.item = next_counts.item
GROUP BY t.order_id, t.items_current_order, t.items_next_order;

执行上述SQL后，会得到符合预期的结果：

注：原数据表T中没有client_id字段，若需要该字段需补充表结构或关联其他表。

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