如何基于零售交易数据统计关联购买的商品品类组合？

需求说明 #

基于零售门店交易数据，分析一同购买的商品品类组合，需遵循以下规则：

• 按唯一交易编号聚合数据
• 品类组合顺序不影响（如A+B与B+A视为同一组合）
• 同一交易内重复出现的品类仅计1次

原始数据格式 #

预期输出 #

方案一：SQL实现 #

核心逻辑：先对每个交易的品类去重，再将品类按固定顺序拼接成组合，最后统计组合出现次数。

-- 步骤1：去重每个交易下的重复品类
WITH unique_category_per_trans AS (
    SELECT DISTINCT transaction_no, category
    FROM your_table_name
),
-- 步骤2：将每个交易的品类排序后拼接成统一格式的组合
transaction_buckets AS (
    SELECT 
        transaction_no,
        STRING_AGG(category, ', ' ORDER BY category) AS bucket
    FROM unique_category_per_trans
    GROUP BY transaction_no
)
-- 步骤3：统计各组合的出现次数
SELECT bucket, COUNT(*) AS count
FROM transaction_buckets
GROUP BY bucket
ORDER BY count DESC;

不同数据库的字符串拼接函数替代：

• MySQL：用GROUP_CONCAT(category ORDER BY category SEPARATOR ', ')
• SQL Server：用STRING_AGG(category, ', ') WITHIN GROUP (ORDER BY category)

方案二：Python（Pandas）实现 #

通过Pandas完成去重、分组拼接、统计三个核心步骤：

import pandas as pd

# 加载数据（示例数据，实际可通过read_csv等方式读取）
df = pd.DataFrame({
    'transaction_no': [1,1,1,1,2,2,3,3,3,4,4],
    'product_id': [100012,121111,121127,121127,465222,121127,121127,121127,121127,465222,121127],
    'category': ['A','A','B','G','N','M','F','G','F','M','N']
})

# 1. 去重每个交易下的重复品类
unique_df = df.drop_duplicates(subset=['transaction_no', 'category'])

# 2. 分组后对品类排序并拼接成组合
buckets = unique_df.groupby('transaction_no')['category'].apply(
    lambda x: ', '.join(sorted(x))
).reset_index(name='bucket')

# 3. 统计各组合的出现次数
result = buckets.groupby('bucket').size().reset_index(name='count')

# 输出结果
print(result)

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