MySQL按2个字段分组 实现同天flow列前值相减及首尾差值计算

原始数据集示例 #

预期结果示例 #

注意：请将后续代码中你的表名、日期字段名、你的数据集路径等占位符替换为你实际的表/字段/文件路径名称。

1. SQL 实现（直接操作数据库表） #

依托自增id的顺序特性，同日期范围内id越大对应数据生成时间越晚，可直接基于id定位首尾数据。

1.1 按日期聚合输出（每日对应一条差值结果） #

适配PostgreSQL写法：

SELECT 
    DATE(日期字段名) AS 统计日期,
    MAX(flow) FILTER (WHERE id = max_id) - MIN(flow) FILTER (WHERE id = min_id) AS flow日差值
FROM (
    SELECT 
        *,
        MAX(id) OVER (PARTITION BY DATE(日期字段名)) AS max_id,
        MIN(id) OVER (PARTITION BY DATE(日期字段名)) AS min_id
    FROM 你的表名
) t
GROUP BY DATE(日期字段名);

适配MySQL写法：

SELECT 
    DATE(日期字段名) AS 统计日期,
    (SELECT flow FROM 你的表名 t2 WHERE t2.id = MAX(t1.id)) - (SELECT flow FROM 你的表名 t2 WHERE t2.id = MIN(t1.id)) AS flow日差值
FROM 你的表名 t1
GROUP BY DATE(日期字段名);

1.2 保留原表所有行输出（每行计算同日期内与上一行的差值） #

SELECT 
    *,
    flow - LAG(flow, 1, 0) OVER (PARTITION BY DATE(日期字段名) ORDER BY id) AS flow环比差值
FROM 你的表名;

2. Python Pandas 实现（处理本地数据集文件） #

首先读取数据集（以csv格式为例）：

import pandas as pd

# 读取数据，自动解析日期字段
df = pd.read_csv('你的数据集路径.csv', parse_dates=['日期字段名'])

2.1 按日期聚合输出（每日对应一条差值结果） #

daily_flow_diff = df.groupby(df['日期字段名'].dt.date).apply(
    lambda group: group.loc[group['id'].idxmax(), 'flow'] - group.loc[group['id'].idxmin(), 'flow']
).reset_index(name='flow日差值')

2.2 保留原表所有行输出（每行计算同日期内与上一行的差值） #

# 先按id排序，再按日期分组计算逐行差值
df = df.sort_values('id')
df['flow环比差值'] = df.groupby(df['日期字段名'].dt.date)['flow'].diff(1).fillna(0)

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