基于现有销售数据表构建5个分级销售记录SQL查询表的需求

数据表结构 #

当周销售表（每周更新，仅存储当周数据） #

历史全量销售记录表 #

查询需求 #

需构建5个SQL查询，分别筛选：

• 当周完成首次成交的销售代表
• 当周完成第2笔成交的销售代表，需显示上一笔成交日期
• 当周完成第3笔成交的销售代表，需显示上一笔成交日期
• 当周完成第4笔成交的销售代表，需显示上一笔成交日期
• 当周完成第5笔成交的销售代表，需显示上一笔成交日期

特殊规则：若销售代表当周首次成交后，当周内再次成交，该记录需纳入第2个查询，Last_sale字段显示当周首次成交日期。

解决方案 #

先通过CTE合并全量数据并排序，再基于此生成各查询结果：

基础CTE（复用逻辑） #

WITH all_sales AS (
    -- 合并历史数据与当周数据，避免重复（若当周数据不会提前同步到历史表可去掉LEFT JOIN过滤）
    SELECT 
        销售代表,
        销售日期,
        iD AS sale_id
    FROM 历史全量销售记录表
    UNION ALL
    SELECT 
        s.销售代表,
        s.销售日期,
        s.iD_sale AS sale_id
    FROM 当周销售表 s
    LEFT JOIN 历史全量销售记录表 h ON s.iD_sale = h.iD
    WHERE h.iD IS NULL
),
sales_ranked AS (
    -- 按销售代表分组，按成交日期排序，标记每笔成交的顺序（第1/2/3...笔）
    SELECT 
        销售代表,
        销售日期,
        sale_id,
        ROW_NUMBER() OVER (PARTITION BY 销售代表 ORDER BY 销售日期) AS sale_order
    FROM all_sales
),
weekly_sales_ranked AS (
    -- 筛选当周成交记录，同时获取上一笔成交日期
    SELECT 
        sr.销售代表,
        sr.销售日期 AS current_sale_date,
        sr.sale_order,
        LAG(sr.销售日期) OVER (PARTITION BY sr.销售代表 ORDER BY sr.sale_order) AS last_sale_date
    FROM sales_ranked sr
    -- 替换为实际当周日期范围，可改为动态参数适配每周更新
    WHERE sr.销售日期 BETWEEN '10月9日' AND '10月15日'
)

1. 当周首次成交的销售代表 #

SELECT 
    销售代表,
    current_sale_date AS first_sale_date
FROM weekly_sales_ranked
WHERE sale_order = 1;

2. 当周完成第2笔成交的销售代表 #

SELECT 
    销售代表,
    current_sale_date AS second_sale_date,
    last_sale_date
FROM weekly_sales_ranked
WHERE sale_order = 2;

3. 当周完成第3笔成交的销售代表 #

SELECT 
    销售代表,
    current_sale_date AS third_sale_date,
    last_sale_date
FROM weekly_sales_ranked
WHERE sale_order = 3;

4. 当周完成第4笔成交的销售代表 #

SELECT 
    销售代表,
    current_sale_date AS fourth_sale_date,
    last_sale_date
FROM weekly_sales_ranked
WHERE sale_order = 4;

5. 当周完成第5笔成交的销售代表 #

SELECT 
    销售代表,
    current_sale_date AS fifth_sale_date,
    last_sale_date
FROM weekly_sales_ranked
WHERE sale_order = 5;

逻辑说明 #

1. all_sales：合并历史与当周数据，避免重复记录干扰统计
2. sales_ranked：为每个销售代表的成交记录按时间排序，生成全局成交顺序编号
3. weekly_sales_ranked：筛选当周数据，通过LAG()函数自动获取上一笔成交日期，无论上一笔是在当周还是历史时期，自动适配特殊场景需求

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