按Product+Version分组，基于价格与类别变化标记行的实现需求

SQL 实现方案 #

思路 #

1. 按product和version分组，以month升序排序，获取每行的上月category和price
2. 标记出价格不变但category与上月不同的行（第一类标记）
3. 追踪首次category变化时前一category的最后价格，标记后续所有价格等于该值的行（第二类标记）

WITH ranked_data AS (
    SELECT 
        *,
        LAG(category) OVER (PARTITION BY product, version ORDER BY month) AS prev_category,
        LAG(price) OVER (PARTITION BY product, version ORDER BY month) AS prev_price,
        -- 标记首次出现category变化且价格不变的位置
        CASE 
            WHEN LAG(category) OVER (PARTITION BY product, version ORDER BY month) != category
                 AND LAG(price) OVER (PARTITION BY product, version ORDER BY month) = price
            THEN 1
            ELSE 0
        END AS is_first_change,
        ROW_NUMBER() OVER (PARTITION BY product, version ORDER BY month) AS rn
    FROM your_table_name
),
-- 获取每个分组中首次变化的位置及对应的前一category最后价格
first_change_info AS (
    SELECT 
        product,
        version,
        MIN(CASE WHEN is_first_change = 1 THEN rn END) AS first_change_rn,
        MIN(CASE WHEN is_first_change = 1 THEN prev_price END) AS target_price
    FROM ranked_data
    GROUP BY product, version
)
SELECT 
    rd.*,
    CASE
        -- 第一类：价格不变且category与上月不同
        WHEN rd.is_first_change = 1 THEN 1
        -- 第二类：在首次变化之后，价格等于目标价格
        WHEN rd.rn > fci.first_change_rn AND rd.price = fci.target_price THEN 1
        ELSE 0
    END AS tag
FROM ranked_data rd
LEFT JOIN first_change_info fci
    ON rd.product = fci.product AND rd.version = fci.version
ORDER BY rd.product, rd.version, rd.month;

Python 实现方案 #

思路 #

使用Pandas处理，步骤如下：

1. 按product、version分组，对month排序
2. 计算每行的上月category和price
3. 识别第一类标记行，再追踪首次变化后的目标价格，标记符合条件的后续行

import pandas as pd

# 假设数据集已加载为df，包含month、product、version、price、category字段
df = df.sort_values(['product', 'version', 'month'])

# 计算上月的category和price
df['prev_category'] = df.groupby(['product', 'version'])['category'].shift(1)
df['prev_price'] = df.groupby(['product', 'version'])['price'].shift(1)

# 标记第一类行：价格不变且category变化
df['is_first_change'] = ((df['category'] != df['prev_category']) & (df['price'] == df['prev_price'])).astype(int)

# 对每个分组，获取首次变化的位置和对应的目标价格
def mark_subsequent_rows(group):
    first_change_idx = group[group['is_first_change'] == 1].index.min()
    if pd.notna(first_change_idx):
        target_price = group.loc[first_change_idx, 'prev_price']
        # 标记首次变化后的所有价格等于target_price的行
        group.loc[group.index > first_change_idx, 'tag'] = (group.loc[group.index > first_change_idx, 'price'] == target_price).astype(int)
    # 第一类行直接标记为1
    group.loc[group['is_first_change'] == 1, 'tag'] = 1
    group['tag'] = group['tag'].fillna(0).astype(int)
    return group

df = df.groupby(['product', 'version'], group_keys=False).apply(mark_subsequent_rows)

# 可选：删除中间辅助列
df = df.drop(['prev_category', 'prev_price', 'is_first_change'], axis=1)

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