如何为数据表添加基于过去6个月连续达标的判断列？

步骤1：数据预处理（去重+处理NULL值） #

先处理原表中的重复行与NULL值：

• 用DISTINCT去除重复行
• 用COALESCE将PercentageCategory的NULL值转为0（因为NULL视为不达标，0<0.8会被判定为不满足条件）

步骤2：按月度聚合达标状态 #

按客户（PersonalNumber）和月份聚合，判断每个客户的每个月份是否达标：

• 若该月所有记录的PercentageCategory均>0.8，则标记该月为达标（1）
• 只要有一条记录不满足（含原NULL值），则标记该月为不达标（0）

步骤3：窗口函数计算过去6个月达标情况 #

通过窗口函数统计每个客户当前月份之前6个月内的不达标月份数，若数量为0则标记NEWCOLUMN为1，否则为0。

完整SQL示例（以PostgreSQL为例） #

WITH deduplicated_data AS (
    -- 去除重复行
    SELECT DISTINCT
        PersonalNumber,
        Dim_Date,
        PercentageCategory
    FROM your_table
),
processed_data AS (
    -- 处理NULL值，转为0（不达标）
    SELECT
        PersonalNumber,
        Dim_Date,
        COALESCE(PercentageCategory, 0) AS PercentageCategory,
        DATE_TRUNC('month', Dim_Date) AS month_start
    FROM deduplicated_data
),
monthly_summary AS (
    -- 按月度聚合，判断每个月是否达标
    SELECT
        PersonalNumber,
        month_start,
        CASE WHEN MIN(PercentageCategory) > 0.8 THEN 1 ELSE 0 END AS month_qualified
    FROM processed_data
    GROUP BY PersonalNumber, month_start
),
windowed_check AS (
    -- 统计过去6个月内的不达标月份数
    SELECT
        *,
        SUM(1 - month_qualified) OVER (
            PARTITION BY PersonalNumber
            ORDER BY month_start
            RANGE BETWEEN INTERVAL '6 months' PRECEDING AND INTERVAL '1 month' PRECEDING
        ) AS unqualified_month_count
    FROM monthly_summary
)
-- 关联回原表，生成NEWCOLUMN
SELECT
    t.PersonalNumber,
    t.Dim_Date,
    t.PercentageCategory,
    CASE WHEN wc.unqualified_month_count = 0 THEN 1 ELSE 0 END AS NEWCOLUMN
FROM your_table t
JOIN windowed_check wc
    ON t.PersonalNumber = wc.PersonalNumber
    AND DATE_TRUNC('month', t.Dim_Date) = wc.month_start;

适配其他数据库的调整说明 #

• MySQL：替换DATE_TRUNC('month', Dim_Date)为DATE_FORMAT(Dim_Date, '%Y-%m-01')，INTERVAL写法保持一致
• SQL Server：替换DATE_TRUNC('month', Dim_Date)为DATEADD(month, DATEDIFF(month, 0, Dim_Date), 0)，窗口范围改为用ROWS结合日期计算：

  SUM(1 - month_qualified) OVER (
      PARTITION BY PersonalNumber
      ORDER BY month_start
      ROWS BETWEEN 6 PRECEDING AND 1 PRECEDING
  )
  

逻辑说明 #

针对你提到的示例：客户1的2019年7月记录，过去6个月（2019-01至2019-06）的月度达标状态均为1，因此NEWCOLUMN标记为1；2019年8月记录的过去6个月包含2019-02至2019-07，其中2019-07月度不达标，因此NEWCOLUMN标记为0。

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