如何用SQL从Redshift提取多商品交易及含Apple&Pencil的交易？

原始交易数据表说明 #

假设交易数据存储在表transaction_items中，表结构及示例数据如下：

1. 提取包含两种及以上商品的交易 #

需求说明 #

筛选出所有包含2种或更多不同商品的交易，可选择输出交易明细或聚合后的交易汇总。

方案1：输出交易明细

通过子查询统计每个交易的商品数量，筛选出符合条件的交易ID后关联原表获取明细：

SELECT ti.*
FROM transaction_items ti
INNER JOIN (
    SELECT transaction_id
    FROM transaction_items
    GROUP BY transaction_id
    HAVING COUNT(DISTINCT product_name) >= 2
) qualified_transactions 
    ON ti.transaction_id = qualified_transactions.transaction_id
ORDER BY ti.transaction_id;

方案2：输出聚合后的交易汇总

如果只需要交易的商品数量和列表，可使用Redshift支持的STRING_AGG函数聚合商品名称：

SELECT 
    transaction_id,
    STRING_AGG(DISTINCT product_name, ', ') AS product_list,
    COUNT(DISTINCT product_name) AS product_count
FROM transaction_items
GROUP BY transaction_id
HAVING COUNT(DISTINCT product_name) >= 2
ORDER BY transaction_id;

目标结果示例 #

明细结果

汇总结果

2. 提取同时包含Apple和Pencil的交易 #

需求说明 #

筛选出同时包含Apple和Pencil两种商品的交易，无论是否包含其他商品。

方案1：基于IN和分组统计

先过滤出目标商品，再分组判断是否同时包含两种：

SELECT ti.*
FROM transaction_items ti
INNER JOIN (
    SELECT transaction_id
    FROM transaction_items
    WHERE product_name IN ('Apple', 'Pencil')
    GROUP BY transaction_id
    HAVING COUNT(DISTINCT product_name) = 2
) qualified_transactions 
    ON ti.transaction_id = qualified_transactions.transaction_id
ORDER BY ti.transaction_id;

方案2：基于条件聚合（更灵活）

使用条件聚合判断交易中是否同时存在两种商品，适合扩展到更多商品的场景：

SELECT ti.*
FROM transaction_items ti
INNER JOIN (
    SELECT transaction_id
    FROM transaction_items
    GROUP BY transaction_id
    HAVING SUM(CASE WHEN product_name = 'Apple' THEN 1 ELSE 0 END) >= 1
       AND SUM(CASE WHEN product_name = 'Pencil' THEN 1 ELSE 0 END) >= 1
) qualified_transactions 
    ON ti.transaction_id = qualified_transactions.transaction_id
ORDER BY ti.transaction_id;

目标结果示例 #

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