如何按X与Y组合筛选连续10天值未变化的最新记录

给定如下数据：

Date        X    Y  Value
2024-01-10  X1  123 1
2024-01-11  X1  123 3
2024-01-12  X1  123 2
2024-01-13  X1  123 5
2024-01-14  X1  123 6
2024-01-15  X1  123 2
2024-01-16  X1  123 2
2024-01-17  X1  123 2
2024-01-18  X1  123 3
2024-01-19  X1  123 2
2024-01-20  X1  123 2
2024-01-10  X2  456 4
2024-01-11  X2  456 4
2024-01-12  X2  456 4
2024-01-13  X2  456 4
2024-01-14  X2  456 4
2024-01-15  X2  456 4
2024-01-16  X2  456 4
2024-01-17  X2  456 4
2024-01-18  X2  456 4
2024-01-19  X2  456 4
2024-01-20  X2  456 4

需求：按X与Y的组合分组，筛选出Value连续10天未发生变化的组，仅输出每组中日期最新的一条记录。本例期望输出：

Date        X    Y    Value
2024-01-20  X2  456    4

方法一：SQL实现 #

假设数据存储在名为data_table的表中，使用窗口函数识别连续相同Value的时间段：

WITH grouped_data AS (
    SELECT 
        Date,
        X,
        Y,
        Value,
        -- 标记连续相同Value的分组
        ROW_NUMBER() OVER (PARTITION BY X, Y ORDER BY Date) 
        - ROW_NUMBER() OVER (PARTITION BY X, Y, Value ORDER BY Date) AS grp
    FROM data_table
),
continuous_periods AS (
    SELECT 
        X,
        Y,
        Value,
        MIN(Date) AS start_date,
        MAX(Date) AS end_date,
        COUNT(*) AS consecutive_days
    FROM grouped_data
    GROUP BY X, Y, Value, grp
    HAVING COUNT(*) >= 10
)
SELECT 
    cp.end_date AS Date,
    cp.X,
    cp.Y,
    cp.Value
FROM continuous_periods cp
-- 取每个X-Y组合中最新的记录
WHERE cp.end_date = (SELECT MAX(end_date) FROM continuous_periods WHERE X = cp.X AND Y = cp.Y);

方法二：Python Pandas实现 #

通过分组和标记连续值的方式筛选目标记录：

import pandas as pd

# 加载数据
data = pd.DataFrame([
    ["2024-01-10", "X1", 123, 1],
    ["2024-01-11", "X1", 123, 3],
    ["2024-01-12", "X1", 123, 2],
    ["2024-01-13", "X1", 123, 5],
    ["2024-01-14", "X1", 123, 6],
    ["2024-01-15", "X1", 123, 2],
    ["2024-01-16", "X1", 123, 2],
    ["2024-01-17", "X1", 123, 2],
    ["2024-01-18", "X1", 123, 3],
    ["2024-01-19", "X1", 123, 2],
    ["2024-01-20", "X1", 123, 2],
    ["2024-01-10", "X2", 456, 4],
    ["2024-01-11", "X2", 456, 4],
    ["2024-01-12", "X2", 456, 4],
    ["2024-01-13", "X2", 456, 4],
    ["2024-01-14", "X2", 456, 4],
    ["2024-01-15", "X2", 456, 4],
    ["2024-01-16", "X2", 456, 4],
    ["2024-01-17", "X2", 456, 4],
    ["2024-01-18", "X2", 456, 4],
    ["2024-01-19", "X2", 456, 4],
    ["2024-01-20", "X2", 456, 4]
], columns=["Date", "X", "Y", "Value"])

# 转换日期格式
data["Date"] = pd.to_datetime(data["Date"])

# 按X、Y分组，标记连续相同Value的组
data["grp"] = data.groupby(["X", "Y"])["Value"].apply(lambda x: x.ne(x.shift()).cumsum())

# 计算每个连续组的天数
continuous_groups = data.groupby(["X", "Y", "Value", "grp"]).agg(
    start_date=("Date", "min"),
    end_date=("Date", "max"),
    consecutive_days=("Date", "count")
).reset_index()

# 筛选连续天数≥10的组
filtered = continuous_groups[continuous_groups["consecutive_days"] >= 10]

# 取每个X-Y组合中最新的记录
result = filtered.loc[filtered.groupby(["X", "Y"])["end_date"].idxmax()]

# 整理输出格式
result = result[["end_date", "X", "Y", "Value"]].rename(columns={"end_date": "Date"})
print(result.to_string(index=False))

运行后输出：

Date   X    Y  Value
2024-01-20  X2  456      4

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