基于多列条件的SQL表改造与数据聚合需求

现有customer_items表，包含ID、Category、Type、Date、Shop列，需要创建新表并满足以下要求：

• 移除Type列
• 若ID、Category、Date、Shop完全相同，合并重复行并新增购买数量统计列Count
• 若ID、Date、Shop相同但Category不同，将Category设为Fruit/Veg并统计总购买数量

你已经写了初始SQL，但仅完成了基础字段选择，需要补充逻辑实现上述要求：

CREATE TABLE SINGLE_ROW AS
SELECT ID, Category, Date, Shop
FROM customer_items

当前表结构及数据 #

目标表结构及数据 #

这里提供两种可实现需求的SQL写法：

写法一：两次分组直接处理 #

CREATE TABLE SINGLE_ROW AS
SELECT
    ID,
    -- 判断同一ID/Date/Shop下是否有多种分类，是则统一设为Fruit/Veg，否则保留原分类
    CASE
        WHEN COUNT(DISTINCT Category) > 1 THEN 'Fruit/Veg'
        ELSE MAX(Category)
    END AS Category,
    Date,
    Shop,
    COUNT(*) AS Count
FROM customer_items
-- 先按ID/Date/Shop分组，识别跨分类的情况
GROUP BY ID, Date, Shop
-- 再按修正后的分类+ID/Date/Shop分组，统计最终数量
GROUP BY ID, Category, Date, Shop;

写法二：用CTE分步处理（逻辑更清晰） #

CREATE TABLE SINGLE_ROW AS
WITH base_group AS (
    -- 先统计每个ID/分类/日期/店铺组合的基础数量
    SELECT
        ID,
        Category,
        Date,
        Shop,
        COUNT(*) AS item_count
    FROM customer_items
    GROUP BY ID, Category, Date, Shop
),
category_check AS (
    -- 统计每个ID/日期/店铺下的分类种类数
    SELECT
        ID,
        Date,
        Shop,
        COUNT(DISTINCT Category) AS category_num
    FROM customer_items
    GROUP BY ID, Date, Shop
)
SELECT
    bg.ID,
    -- 根据分类种类数决定最终显示的分类名称
    CASE
        WHEN cc.category_num > 1 THEN 'Fruit/Veg'
        ELSE bg.Category
    END AS Category,
    bg.Date,
    bg.Shop,
    SUM(bg.item_count) AS Count
FROM base_group bg
JOIN category_check cc 
    ON bg.ID = cc.ID 
    AND bg.Date = cc.Date 
    AND bg.Shop = cc.Shop
GROUP BY bg.ID, CASE WHEN cc.category_num >1 THEN 'Fruit/Veg' ELSE bg.Category END, bg.Date, bg.Shop;

逻辑说明 #

两种写法核心思路一致：

1. 先识别同一ID/Date/Shop组合下的分类数量，判断是否需要统一为Fruit/Veg
2. 再根据修正后的分类，统计对应组合的总购买数量

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