如何用SQL分析账户首两年A_Count与B_Count的平均表现（需24条记录）

核心思路拆解 #

• 给每个账户的记录按ReportDate升序编号，筛选出前24条记录
• 将前24条记录拆分为前12条（首年）和后12条（次年）两个分组
• 分别计算两个分组的A_Count、B_Count平均值，同时提取账户的首次、末次记录日期
• 仅保留总记录数≥24的账户

SQL 查询代码 #

WITH ranked_records AS (
    SELECT
        Account,
        ReportDate,
        A_Count,
        B_Count,
        -- 按账户分组，给每条记录按日期升序编号
        ROW_NUMBER() OVER (PARTITION BY Account ORDER BY ReportDate) AS record_num,
        -- 提取每个账户的首次、末次记录日期
        MIN(ReportDate) OVER (PARTITION BY Account) AS FirstDate,
        MAX(ReportDate) OVER (PARTITION BY Account) AS LastDate
    FROM Volume
),
filtered_records AS (
    -- 保留每个账户的前24条记录，且仅保留总记录数≥24的账户
    SELECT *
    FROM ranked_records
    WHERE record_num <= 24
    AND (SELECT COUNT(*) FROM ranked_records rr WHERE rr.Account = ranked_records.Account) >= 24
),
year_groups AS (
    SELECT
        Account,
        FirstDate,
        LastDate,
        A_Count,
        B_Count,
        -- 划分首年、次组分组
        CASE WHEN record_num <= 12 THEN 'YR1' ELSE 'YR2' END AS year_group
    FROM filtered_records
)
SELECT
    Account,
    -- 计算首年A_Count平均值
    AVG(CASE WHEN year_group = 'YR1' THEN A_Count END) AS YR1_A_Avg,
    -- 计算首年B_Count平均值
    AVG(CASE WHEN year_group = 'YR1' THEN B_Count END) AS YR1_B_Avg,
    -- 计算次年A_Count平均值
    AVG(CASE WHEN year_group = 'YR2' THEN A_Count END) AS YR2_A_Avg,
    -- 计算次年B_Count平均值
    AVG(CASE WHEN year_group = 'YR2' THEN B_Count END) AS YR2_B_Avg,
    FirstDate,
    LastDate
FROM year_groups
GROUP BY Account, FirstDate, LastDate;

代码说明 #

• ranked_records：完成记录编号与首尾日期提取，为后续筛选和分组打基础
• filtered_records：过滤出符合条件的账户及对应前24条记录
• year_groups：将前24条记录按编号划分为首年、次年两个分组
• 最终查询：通过条件聚合计算各分组平均值，按账户维度输出结果

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