MySQL：当连续满足条件时统计记录值

看来你需要处理连续交易状态的统计问题（比如连续完成销售/放弃销售的次数、累计成本等），我来给你两种常用的解决方案，分别适用于数据库查询和本地数据处理场景：

核心思路是利用窗口函数，给每个用户的连续相同交易状态（Code字段）标记分组，再按分组统计指标。

步骤说明： #

• 按UserId分组，TimeStamp排序，用LAG()函数获取上一行的交易状态
• 对比当前行与上一行的状态，若不同则标记为新分组的起点
• 累计分组标记，得到每个连续序列的唯一ID
• 按用户、分组ID、交易状态聚合统计所需指标

示例SQL代码（以PostgreSQL/MySQL 8.0+为例）： #

WITH ranked_data AS (
    SELECT
        UserId,
        TimeStamp,
        Code,
        Cost,
        -- 标记当前行与上一行状态是否不同
        CASE WHEN Code = LAG(Code) OVER (PARTITION BY UserId ORDER BY TimeStamp)
             THEN 0 ELSE 1 END AS is_new_group
    FROM your_transaction_table
),
grouped_data AS (
    SELECT
        *,
        -- 累计分组标记，得到连续序列的ID
        SUM(is_new_group) OVER (PARTITION BY UserId ORDER BY TimeStamp) AS consecutive_group_id
    FROM ranked_data
)
SELECT
    UserId,
    consecutive_group_id,
    Code,
    COUNT(*) AS consecutive_transactions, -- 连续交易次数
    SUM(Cost) AS total_cost, -- 累计成本
    MIN(TimeStamp) AS start_time, -- 连续序列开始时间
    MAX(TimeStamp) AS end_time -- 连续序列结束时间
FROM grouped_data
GROUP BY UserId, consecutive_group_id, Code
ORDER BY UserId, start_time;

针对你提供的示例数据，运行后会得到用户1的两个连续组：

• 组1：SALE_COMPLETE，3次交易，总成本115+199+177=491，时间范围01/01-03/01
• 组2：SALE_ABANDONED，3次交易，总成本103+126+31=260，时间范围12/01-14/01

如果数据是本地CSV/Excel文件，用Pandas可以快速完成连续序列的统计。

步骤说明： #

• 读取数据并转换时间戳格式
• 按用户和时间排序，保证序列的连续性
• 用diff()和cumsum()标记连续相同状态的分组
• 按分组聚合统计指标

示例Python代码： #

import pandas as pd

# 读取数据（假设是CSV文件，根据实际格式调整）
df = pd.read_csv('transaction_records.csv')

# 转换时间戳为datetime类型（匹配你的数据格式：日/月/年）
df['TimeStamp'] = pd.to_datetime(df['TimeStamp'], format='%d/%m/%Y')

# 按用户ID和时间排序，确保交易顺序正确
df = df.sort_values(by=['UserId', 'TimeStamp'])

# 标记每个用户的连续相同交易状态分组
df['consecutive_group_id'] = df.groupby('UserId')['Code'].apply(
    lambda x: x.ne(x.shift()).cumsum()
)

# 统计连续序列的关键指标
result = df.groupby(['UserId', 'consecutive_group_id', 'Code']).agg(
    consecutive_count=('Code', 'count'),
    total_cost=('Cost', 'sum'),
    start_date=('TimeStamp', 'min'),
    end_date=('TimeStamp', 'max')
).reset_index()

# 打印结果
print(result)

运行这段代码后，输出结果和SQL方案一致，能清晰看到每个用户的连续交易状态分组及对应的统计值。

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