如何在ID分组内按Datetime排序创建连续3条记录分组并取组内最大Datetime？

SQL 实现 #

利用窗口函数先给每个ID内的记录按时间排序编行号，再通过整数除法生成组内分组，最后计算组内最大时间并生成全局连续组号：

WITH ranked_data AS (
    SELECT 
        Datetime,
        ID,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Datetime) AS rn
    FROM your_table  -- 替换为你的表名
),
grouped_data AS (
    SELECT 
        Datetime,
        ID,
        (rn - 1) / 3 + 1 AS id_group,
        MAX(Datetime) OVER (PARTITION BY ID, (rn - 1) / 3) AS max_time_in_group
    FROM ranked_data
)
SELECT 
    Datetime,
    ID,
    DENSE_RANK() OVER (ORDER BY ID, id_group) AS "Group of 3",
    max_time_in_group AS "Max Time in group of 3"
FROM grouped_data
ORDER BY ID, Datetime;

逻辑说明： #

• ROW_NUMBER() 给每个ID内的记录按时间顺序分配唯一行号
• (rn - 1) / 3 将行号转换为0起始的组索引，每3条记录归为一组
• MAX() OVER (PARTITION BY ID, (rn - 1) / 3) 计算每个组内的最大时间
• DENSE_RANK() 生成全局连续的组编号，和示例中的Group of 3一致

Pandas 实现 #

通过分组排序、累计计数生成组内分组，再计算组内最大时间并生成全局组号：

import pandas as pd

# 加载数据并转换时间格式（替换为你的数据加载方式）
df = pd.read_csv('your_data.csv')
df['Datetime'] = pd.to_datetime(df['Datetime'])

# 按ID和时间排序
df = df.sort_values(['ID', 'Datetime']).reset_index(drop=True)

# 生成每个ID内的行号
df['rn'] = df.groupby('ID').cumcount() + 1

# 生成ID内的分组编号
df['id_group'] = (df['rn'] - 1) // 3 + 1

# 计算每个分组的最大时间
df['Max Time in group of 3'] = df.groupby(['ID', 'id_group'])['Datetime'].transform('max')

# 生成全局连续的组编号
df['Group of 3'] = df.groupby(['ID', 'id_group']).ngroup() + 1

# 调整列顺序匹配示例
df = df[['Datetime', 'ID', 'Group of 3', 'Max Time in group of 3']]

# 输出结果
print(df)

逻辑说明： #

• sort_values 确保每个ID内的记录按时间顺序排列
• groupby('ID').cumcount() 生成每个ID内的连续序号
• (rn - 1) // 3 实现每3条记录一组的分组逻辑
• transform('max') 为每个组内的所有记录填充组内最大时间
• ngroup() 给每个(ID+组)组合分配全局唯一编号，加1后得到连续的Group of 3值

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