-- 2023年产品销售情况统计查询 - 详细注释版
-- WITH关键字：定义公用表表达式(CTE)，创建临时命名结果集
-- product_basics：CTE的名称，用于计算每个产品的基础统计数据
WITH
    product_basics AS (
        -- SELECT：选择要返回的列
        SELECT
            p.product_id, -- p是products表的别名，product_id是产品唯一标识符字段
            p.unit_price, -- unit_price是products表中的单价字段(DECIMAL类型)
            SUM(o.quantity) as total_quantity, -- SUM()聚合函数：对quantity字段求和; o是orders表别名; AS定义列别名
            SUM(o.quantity * p.unit_price) as total_sales, -- 计算总销售额：每笔订单(数量×单价)的总和
            ROUND(SUM(o.quantity * p.unit_price) / 12, 2) as avg_monthly_sales -- ROUND()函数：四舍五入; 参数1是表达式; 参数2是小数位数(2位)
            -- FROM子句：指定主表，orders表别名为o
        FROM
            orders o
            -- JOIN：内连接关键字，连接两表的公共字段
            -- products表别名为p，ON指定连接条件
            JOIN products p ON p.product_id = o.product_id -- 连接条件：产品表的product_id = 订单表的product_id
            -- WHERE子句：过滤条件
            -- YEAR()函数：提取日期的年份部分，参数是order_date字段(DATE类型)
        WHERE
            YEAR(o.order_date) = 2023 -- 只统计2023年的订单数据
            -- GROUP BY：分组子句，按指定字段分组，使聚合函数生效
        GROUP BY
            p.product_id,
            p.unit_price -- 按产品ID和单价分组，确保每个产品一行结果
    ),
    -- 第二个CTE：计算单月最高销量
    monthly_max AS (
        -- 外层查询：从月度汇总中找最大值
        SELECT
            product_id, -- 产品ID字段
            MAX(monthly_qty) as max_monthly_quantity -- MAX()聚合函数：找出最大值; monthly_qty来自子查询
            -- FROM子句：使用子查询作为数据源
        FROM
            (
                -- 内层子查询：计算每个产品每个月的销量
                SELECT
                    product_id, -- 产品ID字段
                    YEAR(order_date) as year_num, -- 提取年份，别名为year_num
                    MONTH(order_date) as month_num, -- MONTH()函数：提取月份(1-12)，别名为month_num
                    SUM(quantity) as monthly_qty -- 计算每个月的销量总和，别名为monthly_qty
                FROM
                    orders -- 从orders表查询
                WHERE
                    YEAR(order_date) = 2023 -- 筛选2023年数据
                    -- 三重分组：按产品、年份、月份分组
                GROUP BY
                    product_id,
                    YEAR(order_date),
                    MONTH(order_date) -- 确保每个产品每个月一行数据
            ) monthly_totals -- 子查询别名为monthly_totals
        GROUP BY
            product_id -- 按产品ID分组，配合MAX函数找出每个产品的最大月销量
    ),
    -- 第三个CTE：计算每个客户对每个产品的总购买量
    customer_product_totals AS (
        SELECT
            o.product_id, -- 订单表中的产品ID字段
            o.customer_id, -- 订单表中的客户ID字段(外键，关联customers表)
            SUM(o.quantity) as customer_total_qty -- 计算该客户购买该产品的总数量
        FROM
            orders o -- 从订单表查询，别名为o
        WHERE
            YEAR(o.order_date) = 2023 -- 只统计2023年的购买数据
        GROUP BY
            o.product_id,
            o.customer_id -- 双重分组：按产品ID和客户ID分组，得到客户-产品购买矩阵
    ),
    -- 第四个CTE：找出每个产品购买量最多的客户年龄段
    top_customers AS (
        SELECT
            cpt.product_id, -- cpt是customer_product_totals的别名
            MIN(c.customer_age) as min_age -- MIN()聚合函数：找最小值; c.customer_age是客户年龄字段(INT类型)
            -- 连接客户-产品购买表和客户表
        FROM
            customer_product_totals cpt -- 主表：客户产品购买汇总
            JOIN customers c ON cpt.customer_id = c.customer_id -- 连接客户表获取年龄信息，连接条件是customer_id
            -- WHERE子句：复杂的筛选条件
            -- (字段1, 字段2) IN (子查询)：多字段IN条件，同时匹配产品ID和购买量
        WHERE
            (cpt.product_id, cpt.customer_total_qty) IN (
                -- 子查询：找出每个产品的最大购买量
                SELECT
                    product_id,
                    MAX(customer_total_qty) -- 每个产品的最大客户购买量
                FROM
                    customer_product_totals -- 从客户产品购买表查询
                GROUP BY
                    product_id -- 按产品分组
            )
        GROUP BY
            cpt.product_id -- 按产品分组，配合MIN函数选择年龄最小的客户
    )
    -- 主查询：整合所有CTE的结果
SELECT
    pb.product_id, -- pb是product_basics的别名，获取产品ID
    pb.total_sales, -- 从第一个CTE获取总销售额
    pb.unit_price, -- 从第一个CTE获取单价
    pb.total_quantity, -- 从第一个CTE获取总销量
    pb.avg_monthly_sales, -- 从第一个CTE获取月平均销售额
    mm.max_monthly_quantity, -- mm是monthly_max的别名，获取单月最高销量
    -- CASE表达式：条件判断，类似if-else语句
    CASE
        -- WHEN条件 THEN结果：如果年龄在1-10之间，返回'1-10'
        WHEN tc.min_age BETWEEN 1 AND 10  THEN '1-10' -- BETWEEN...AND：范围条件，包含边界值
        WHEN tc.min_age BETWEEN 11 AND 20  THEN '11-20' -- tc是top_customers的别名
        WHEN tc.min_age BETWEEN 21 AND 30  THEN '21-30'
        WHEN tc.min_age BETWEEN 31 AND 40  THEN '31-40'
        WHEN tc.min_age BETWEEN 41 AND 50  THEN '41-50'
        WHEN tc.min_age BETWEEN 51 AND 60  THEN '51-60'
        ELSE '61+' -- ELSE：默认情况，年龄大于60的归为'61+'
    END as customer_age_group -- END：结束CASE表达式，AS定义别名
    -- FROM子句：主表是第一个CTE
FROM
    product_basics pb
    -- LEFT JOIN：左外连接，保留左表(pb)的所有记录，即使右表没有匹配
    LEFT JOIN monthly_max mm ON pb.product_id = mm.product_id -- 连接条件：产品ID相等
    LEFT JOIN top_customers tc ON pb.product_id = tc.product_id -- 连接客户年龄段信息
    -- ORDER BY：排序子句
ORDER BY
    pb.total_sales DESC,
    pb.product_id ASC;

 -- DESC降序(大到小)，ASC升序(小到大)
-- 先按总销售额降序，再按产品ID升序

SQL代码逐行详解

整体结构说明

这个查询使用了**CTE（公用表表达式）**的方式，将复杂查询分解为4个逻辑步骤，最后在主查询中整合结果。

第一部分：product_basics CTE

WITH product_basics AS (

• 作用：定义第一个CTE，用于计算每个产品的基础统计数据
• CTE：Common Table Expression，类似于临时表，可以在后续查询中引用

    SELECT 
        p.product_id,
        p.unit_price,
        SUM(o.quantity) as total_quantity,
        SUM(o.quantity * p.unit_price) as total_sales,
        ROUND(SUM(o.quantity * p.unit_price) / 12, 2) as avg_monthly_sales

• p.product_id：获取产品ID
• p.unit_price：获取产品单价
• SUM(o.quantity) as total_quantity：计算该产品2023年总销量
• SUM(o.quantity * p.unit_price) as total_sales：计算总销售额（数量×单价的总和）
• ROUND(.../ 12, 2) as avg_monthly_sales：计算月平均销售额（总销售额÷12个月，保留2位小数）

    FROM orders o
    JOIN products p ON p.product_id = o.product_id

• 连接订单表和产品表，通过product_id关联
• 这样可以获取到订单的数量信息和产品的单价信息

    WHERE YEAR(o.order_date) = 2023

• 关键筛选条件：只统计2023年的订单数据
• YEAR()函数提取订单日期的年份

    GROUP BY p.product_id, p.unit_price
),

• 按产品ID和单价分组，确保每个产品得到一行汇总数据
• 分组后SUM函数才能计算每个产品的总和

第二部分：monthly_max CTE

monthly_max AS (

• 定义第二个CTE，专门计算每个产品的单月最高销量

    SELECT 
        product_id,
        MAX(monthly_qty) as max_monthly_quantity

• 从月度汇总数据中找出每个产品的最大月销量

    FROM (
        SELECT 
            product_id,
            YEAR(order_date) as year_num,
            MONTH(order_date) as month_num,
            SUM(quantity) as monthly_qty
        FROM orders
        WHERE YEAR(order_date) = 2023
        GROUP BY product_id, YEAR(order_date), MONTH(order_date)
    ) monthly_totals

• 内层子查询： YEAR(order_date) 和 MONTH(order_date)：提取年份和月份SUM(quantity) as monthly_qty：计算每个产品每个月的销量总和GROUP BY product_id, YEAR(order_date), MONTH(order_date)：按产品、年、月分组结果：每个产品每个月一行数据，包含该月的销量总和

    GROUP BY product_id
),

• 外层按产品ID分组，使用MAX函数找出每个产品所有月份中销量最高的月份

第三部分：customer_product_totals CTE

customer_product_totals AS (

• 第三个CTE，计算每个客户对每个产品的总购买量

    SELECT 
        o.product_id,
        o.customer_id,
        SUM(o.quantity) as customer_total_qty

• 汇总每个客户购买每个产品的总数量
• 一个客户可能多次购买同一产品，这里把所有购买累加

    FROM orders o
    WHERE YEAR(o.order_date) = 2023
    GROUP BY o.product_id, o.customer_id
),

• 同样只统计2023年数据
• 按产品ID和客户ID分组，得到客户-产品的购买量矩阵

第四部分：top_customers CTE

top_customers AS (

• 第四个CTE，找出每个产品购买量最多的客户年龄段

    SELECT 
        cpt.product_id,
        MIN(c.customer_age) as min_age

• MIN(c.customer_age)：当有多个客户并列购买量最多时，选择年龄最小的

    FROM customer_product_totals cpt
    JOIN customers c ON cpt.customer_id = c.customer_id

• 连接客户表获取年龄信息

    WHERE (cpt.product_id, cpt.customer_total_qty) IN (
        SELECT product_id, MAX(customer_total_qty)
        FROM customer_product_totals
        GROUP BY product_id
    )

• 复杂筛选条件： 内层查询：SELECT product_id, MAX(customer_total_qty) 找出每个产品的最大购买量外层条件：只保留购买量等于最大值的记录这样筛选出每个产品购买量最多的客户（可能有多个并列）

    GROUP BY cpt.product_id
)

• 按产品分组，配合MIN函数，确保每个产品只返回一个年龄（最小的）

第五部分：主查询

SELECT 
    pb.product_id,
    pb.total_sales,
    pb.unit_price,
    pb.total_quantity,
    pb.avg_monthly_sales,
    mm.max_monthly_quantity,

• 从前面的CTE中获取基础数据

    CASE 
        WHEN tc.min_age BETWEEN 1 AND 10 THEN '1-10'
        WHEN tc.min_age BETWEEN 11 AND 20 THEN '11-20'
        WHEN tc.min_age BETWEEN 21 AND 30 THEN '21-30'
        WHEN tc.min_age BETWEEN 31 AND 40 THEN '31-40'
        WHEN tc.min_age BETWEEN 41 AND 50 THEN '41-50'
        WHEN tc.min_age BETWEEN 51 AND 60 THEN '51-60'
        ELSE '61+'
    END as customer_age_group

• CASE语句：将具体年龄转换为年龄段标签
• 按照题目要求的年龄段分类

FROM product_basics pb
LEFT JOIN monthly_max mm ON pb.product_id = mm.product_id
LEFT JOIN top_customers tc ON pb.product_id = tc.product_id

• 连接所有CTE的结果
• 使用LEFT JOIN确保即使某些数据缺失，产品信息也不会丢失

ORDER BY pb.total_sales DESC, pb.product_id ASC;

• 排序规则： 首先按总销售额降序（销售额高的在前）销售额相同时按产品ID升序（ID小的在前）

核心设计思想

1. 分步骤处理：将复杂逻辑拆分为4个独立的CTE，每个CTE负责一个特定计算
2. 避免重复计算：基础数据计算一次，后续步骤复用
3. 处理边界情况：使用LEFT JOIN和MIN函数处理并列情况
4. 数据完整性：确保每个产品都有完整的统计信息

这种写法的优点是逻辑清晰、易于调试和维护，缺点是代码较长，但对于复杂的业务需求来说是很好的解决方案。