使用SUM OVER窗口函数统计超销售阈值代理人数的SQL逻辑问题

实现思路 #

• 基于你已经完成的「单代理单月销售额聚合CTE」，仅需新增两个状态标记字段即可实现规则切换：
  1. 用SUM OVER窗口函数计算每个代理到当前月的累计销售额
  2. 用LAG函数获取该代理上月的累计销售额，判断当前月之前是否从未突破过30的阈值
  3. 根据阈值突破状态，选择对应判断规则统计达标代理

可直接复用的SQL示例 #

WITH monthly_agent_sales AS (
    -- 此处替换为你已经实现的按年月、代理ID聚合每月销售额的逻辑
    SELECT 
        date_trunc('month', sale_date)::date AS year_month,
        agent_id,
        SUM(sale_amount) AS monthly_sales
    FROM agentsales
    GROUP BY 1,2
),
agent_sales_with_state AS (
    SELECT
        year_month,
        agent_id,
        monthly_sales,
        -- 计算到当前月的累计销售额
        SUM(monthly_sales) OVER (PARTITION BY agent_id ORDER BY year_month) AS cumulative_sales,
        -- 判断当前月之前是否从未突破30阈值：上月累计小于30/首月的场景为true
        COALESCE(
            LAG(SUM(monthly_sales)) OVER (PARTITION BY agent_id ORDER BY year_month),
            0
        ) < 30 AS is_before_threshold
    FROM monthly_agent_sales
)
-- 按年月统计达标人数
SELECT
    year_month,
    COUNT(DISTINCT agent_id) AS qualified_agent_count
FROM agent_sales_with_state
WHERE
    CASE
        -- 未突破过阈值：判断累计销售额是否达标
        WHEN is_before_threshold THEN cumulative_sales >= 30
        -- 已突破过阈值：仅判断当月销售额是否达标
        ELSE monthly_sales >=30
    END
GROUP BY year_month
ORDER BY year_month;

LAG方案可行性说明 #

你提到的用LAG函数处理SUM OVER计算结果的思路完全可行，上述方案就是基于该逻辑实现的。核心是通过LAG取上月累计值做状态标记，避免了全量累计始终参与判断的问题，完全匹配你需要的规则切换需求。

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