计算每位员工的Trailing 12 Months累计销售额

计算每位员工的过去12个月（Trailing 12 Months）累计销售额，已知并非每位员工在所有日期都有销售记录。最终需支持业务查询，例如：找出2024年1月活跃的所有员工的过去12个月销售额。

原始Sales表数据 #

已尝试的思路及问题 #

曾尝试将Sales表与日历维度表执行CROSS JOIN，试图生成所有日历日期与有销售记录的员工ID组合（无销售则记为0），再按empID分区做行前累计，但未得到预期结果。尝试的SQL片段如下：

select CAL.*, S.emp_id, S.ACTIVITY_Date, S.Sales
from sales s
cross join CAL

解决方案 #

方案1：基于全量日期维度的累计计算 #

适用于需要每天的累计值，或数据库支持区间窗口函数的场景：

-- 生成所有员工-日期的完整组合
WITH all_emp_dates AS (
    SELECT DISTINCT Emp_ID FROM Sales
    CROSS JOIN (SELECT calendar_date FROM Calendar) cal_dates
),
-- 关联销售数据，无销售则填充0
emp_daily_sales AS (
    SELECT 
        a.Emp_ID,
        a.calendar_date,
        COALESCE(s.Sales, 0) AS daily_sales
    FROM all_emp_dates a
    LEFT JOIN Sales s 
        ON a.Emp_ID = s.Emp_ID 
        AND a.calendar_date = s.Activity_Date
)
-- 计算过去12个月累计销售额
SELECT 
    Emp_ID,
    calendar_date,
    SUM(daily_sales) OVER (
        PARTITION BY Emp_ID 
        ORDER BY calendar_date 
        RANGE BETWEEN INTERVAL '12' MONTH PRECEDING AND CURRENT ROW
    ) AS trailing_12m_sales
FROM emp_daily_sales

方案2：直接基于销售记录的关联计算 #

无需生成全量日期组合，仅针对已有销售记录计算对应日期的过去12个月累计：

SELECT 
    s1.Emp_ID,
    s1.Activity_Date,
    SUM(s2.Sales) AS trailing_12m_sales
FROM Sales s1
LEFT JOIN Sales s2 
    ON s1.Emp_ID = s2.Emp_ID
    AND s2.Activity_Date >= s1.Activity_Date - INTERVAL '12' MONTH
    AND s2.Activity_Date <= s1.Activity_Date
GROUP BY s1.Emp_ID, s1.Activity_Date

业务查询示例（2024年1月活跃员工的T1M销售额） #

基于方案1的结果，筛选并聚合2024年1月的数据：

WITH all_emp_dates AS (
    SELECT DISTINCT Emp_ID FROM Sales
    CROSS JOIN (SELECT calendar_date FROM Calendar) cal_dates
),
emp_daily_sales AS (
    SELECT 
        a.Emp_ID,
        a.calendar_date,
        COALESCE(s.Sales, 0) AS daily_sales
    FROM all_emp_dates a
    LEFT JOIN Sales s 
        ON a.Emp_ID = s.Emp_ID 
        AND a.calendar_date = s.Activity_Date
),
trailing_sales AS (
    SELECT 
        Emp_ID,
        calendar_date,
        SUM(daily_sales) OVER (
            PARTITION BY Emp_ID 
            ORDER BY calendar_date 
            RANGE BETWEEN INTERVAL '12' MONTH PRECEDING AND CURRENT ROW
        ) AS trailing_12m_sales
    FROM emp_daily_sales
)
SELECT 
    Emp_ID,
    MAX(trailing_12m_sales) AS trailing_12m_sales -- 取1月最后一天的累计值，可根据需求调整
FROM trailing_sales
WHERE calendar_date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY Emp_ID

关键说明 #

• 方案1的RANGE BETWEEN INTERVAL '12' MONTH PRECEDING AND CURRENT ROW语法适用于PostgreSQL、BigQuery等数据库；若数据库不支持，可改用DATEDIFF函数判断日期范围。
• 方案2性能更优，无需生成全量日期，但仅能得到有销售记录日期的累计值。

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