如何识别客户购买的各类型产品最高等级并生成汇总表？

需求说明 #

需识别每位客户购买的C、M、S各类型产品的最高等级，生成指定格式的汇总表，在对应最高等级位置标记“Yes”；同时根据客户性别标记M/F列。

样本数据 #

-- 创建样本数据表
CREATE TABLE Sales (
    Day Date,
    Customer VARCHAR(50),
    Gender  VARCHAR(50),
    Store#1 VARCHAR(50),
    Store#2 VARCHAR(50),
    Store#3 VARCHAR(50)
);

INSERT INTO Sales VALUES 
('2/1/2021',    'Tom',  'M',    'C1','','S3'),
('4/2/2021',    'Jerry','M',    'M2','C1','S3'),
('4/12/2021',   'Tom',  'M',    'S1','M2','C2'),
('6/21/2021',   'Ben',  'M',    'C1','M2','S3'),
('7/21/2021',   'Ben',  'M',    'C2','M1',''),  
('7/21/2021',   'Jerry','M',    'S2','C3','M1'),
('8/1/2021',    'Alisa','F',    'C3','M2','S2'),
('9/30/2021',   'Ben',  'M',    'M2','',''),    
('10/11/2021',  'Jerry','M', '' ,'M3','S1'),
('12/12/2021',  'Alisa','F','M2','C1','S2'),
('2/12/2022',   'Rachel','F','M1','C1','S1');

产品等级规则 #

• C类产品：等级从高到低为 C3 > C2 > C1
• M类产品：等级从高到低为 M3 > M2 > M1
• S类产品：等级从高到低为 S3 > S2 > S1
• 性别标记：客户性别为M则结果表M列标记“Yes”，为F则F列标记“Yes”

预期结果 #

CREATE TABLE expected_outcomes (
    Customer VARCHAR(50),
    M VARCHAR(50),
    F VARCHAR(50),
    C1 VARCHAR(50),
    C2 VARCHAR(50),
    C3 VARCHAR(50),
    M1 VARCHAR(50),
    M2 VARCHAR(50),
    M3 VARCHAR(50),
    S1 VARCHAR(50),
    S2 VARCHAR(50),
    S3 VARCHAR(50)
);

INSERT INTO expected_outcomes VALUES 
('Tom', 'Yes','','','Yes','','','Yes','','','','Yes'),
('Jerry','Yes','','','','Yes','','','Yes','','','Yes'),
('Ben', 'Yes','','','Yes','','','Yes','','','','Yes'),
('Alisa','','Yes','','','Yes','','Yes','','','Yes',''), 
('Rachel','','Yes','Yes','','','Yes','','','Yes','','');    

解决方案（SQL实现） #

通过CTE拆解数据、提取最高等级，最终生成透视表：

WITH customer_products AS (
    -- 转换多列产品为单行记录，拆分类型与等级
    SELECT 
        Customer,
        Gender,
        LEFT(product, 1) AS product_type,
        RIGHT(product, 1) AS product_level
    FROM Sales
    UNPIVOT (
        product FOR store IN (Store#1, Store#2, Store#3)
    ) AS unpvt
    WHERE product <> ''
),
max_product_level AS (
    -- 按客户+产品类型分组，取最高等级（数字越大等级越高）
    SELECT 
        Customer,
        product_type,
        MAX(product_level) AS max_level
    FROM customer_products
    GROUP BY Customer, product_type
)
-- 生成最终汇总表
SELECT 
    cp.Customer,
    CASE WHEN cp.Gender = 'M' THEN 'Yes' ELSE '' END AS M,
    CASE WHEN cp.Gender = 'F' THEN 'Yes' ELSE '' END AS F,
    -- 匹配各等级标记
    CASE WHEN EXISTS(SELECT 1 FROM max_product_level mpl WHERE mpl.Customer = cp.Customer AND mpl.product_type = 'C' AND mpl.max_level = '1') THEN 'Yes' ELSE '' END AS C1,
    CASE WHEN EXISTS(SELECT 1 FROM max_product_level mpl WHERE mpl.Customer = cp.Customer AND mpl.product_type = 'C' AND mpl.max_level = '2') THEN 'Yes' ELSE '' END AS C2,
    CASE WHEN EXISTS(SELECT 1 FROM max_product_level mpl WHERE mpl.Customer = cp.Customer AND mpl.product_type = 'C' AND mpl.max_level = '3') THEN 'Yes' ELSE '' END AS C3,
    CASE WHEN EXISTS(SELECT 1 FROM max_product_level mpl WHERE mpl.Customer = cp.Customer AND mpl.product_type = 'M' AND mpl.max_level = '1') THEN 'Yes' ELSE '' END AS M1,
    CASE WHEN EXISTS(SELECT 1 FROM max_product_level mpl WHERE mpl.Customer = cp.Customer AND mpl.product_type = 'M' AND mpl.max_level = '2') THEN 'Yes' ELSE '' END AS M2,
    CASE WHEN EXISTS(SELECT 1 FROM max_product_level mpl WHERE mpl.Customer = cp.Customer AND mpl.product_type = 'M' AND mpl.max_level = '3') THEN 'Yes' ELSE '' END AS M3,
    CASE WHEN EXISTS(SELECT 1 FROM max_product_level mpl WHERE mpl.Customer = cp.Customer AND mpl.product_type = 'S' AND mpl.max_level = '1') THEN 'Yes' ELSE '' END AS S1,
    CASE WHEN EXISTS(SELECT 1 FROM max_product_level mpl WHERE mpl.Customer = cp.Customer AND mpl.product_type = 'S' AND mpl.max_level = '2') THEN 'Yes' ELSE '' END AS S2,
    CASE WHEN EXISTS(SELECT 1 FROM max_product_level mpl WHERE mpl.Customer = cp.Customer AND mpl.product_type = 'S' AND mpl.max_level = '3') THEN 'Yes' ELSE '' END AS S3
FROM (SELECT DISTINCT Customer, Gender FROM Sales) cp
ORDER BY Customer;

逻辑说明 #

1. customer_products：使用UNPIVOT将三个Store列的产品数据转为单行记录，同时拆分出产品类型（C/M/S）和等级数字，过滤空值。
2. max_product_level：按客户和产品类型分组，取每组中最大的等级数字（对应最高等级）。
3. 最终查询：基于客户的唯一性别记录，通过EXISTS判断每个客户的各类型产品最高等级，标记“Yes”或空字符串，生成符合要求的汇总表。

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