使用MSSQL查询各门店单交易内最常购买的前N个商品组合

以下是可直接落地的查询代码，可通过修改@TopN参数自定义返回每个门店的前N个高频组合：

-- 自定义返回前N个高频组合，默认取前2
DECLARE @TopN INT = 2;

WITH ProdPair AS (
    -- 生成单交易内不重复的两两商品组合，避免同组合正反两次计数
    SELECT 
        t1.store_id,
        t1.transaction_id,
        -- 拼接商品ID组合，小ID在前保证同一组合唯一标识
        CONCAT(CAST(t1.product_id AS VARCHAR), ',', CAST(t2.product_id AS VARCHAR)) AS freq_prod_ids
    FROM 你的实际表名 t1
    INNER JOIN 你的实际表名 t2
        ON t1.transaction_id = t2.transaction_id
        AND t1.store_id = t2.store_id
        AND t1.product_id < t2.product_id -- 核心去重逻辑，避免A&B和B&A重复统计
),
PairCount AS (
    -- 统计每个门店下每个组合的关联交易次数
    SELECT
        store_id AS store,
        freq_prod_ids,
        COUNT(DISTINCT transaction_id) AS count_of_transactions
    FROM ProdPair
    GROUP BY store_id, freq_prod_ids
),
PairRank AS (
    -- 按门店分组，对组合的交易频次排序
    SELECT
        *,
        RANK() OVER (PARTITION BY store ORDER BY count_of_transactions DESC) AS rk
    FROM PairCount
)
-- 过滤得到每个门店前N个高频组合
SELECT store, freq_prod_ids, count_of_transactions
FROM PairRank
WHERE rk <= @TopN
ORDER BY store, rk;

注意事项 #

• 请将代码中的你的实际表名替换为你自己的交易商品数据表名
• 如果需要返回商品名称组合而非ID组合，可将freq_prod_ids的拼接逻辑替换为CONCAT(t1.product_name, ',', t2.product_name)
• 若存在频次并列的情况，RANK()会返回所有并列结果，若需要严格仅返回指定N条结果，可将RANK()替换为ROW_NUMBER()

内容的提问来源于stack exchange，提问作者Nidhin Bose J.