如何从MySQL表中排除begintime相同但endtime更小的行？

我有一个包含datetime类型列begintime和endtime的MySQL表，数据示例如下：

+---------------------+---------------------+
| begintime           | endtime             |
+---------------------+---------------------+
| 2024-05-22 10:13:23 | 2024-05-31 13:37:34 |
| 2024-05-30 17:03:21 | 2024-05-31 16:01:25 |
| 2024-05-30 17:03:21 | 2024-05-31 16:01:25 |
| 2024-05-30 17:03:21 | 2024-05-31 16:01:25 |
| 2024-05-31 15:00:00 | 2024-05-31 15:00:03 |
| 2024-05-31 15:01:32 | 2024-05-31 16:01:26 |
+---------------------+---------------------+

表中存在部分行与其他行begintime相同，但endtime更早的情况，比如下面这行：

| 2024-05-22 10:13:23 | 2024-05-31 12:02:18 |

该行的begintime和第一行一致，但endtime更早。需要过滤掉这类行，只保留每个begintime对应的最大endtime的行。

方法一：使用MySQL语句过滤 #

方式1：关联子查询（兼容所有MySQL版本） #

先分组获取每个begintime对应的最大endtime，再关联原表筛选出符合条件的行：

SELECT t.*
FROM your_table t
INNER JOIN (
    SELECT begintime, MAX(endtime) AS max_endtime
    FROM your_table
    GROUP BY begintime
) t_max ON t.begintime = t_max.begintime AND t.endtime = t_max.max_endtime;

方式2：窗口函数（MySQL 8.0及以上版本支持） #

利用窗口函数按begintime分组，每组内按endtime倒序排序，取排序后第一行：

SELECT begintime, endtime
FROM (
    SELECT 
        begintime, 
        endtime,
        ROW_NUMBER() OVER (PARTITION BY begintime ORDER BY endtime DESC) AS rn
    FROM your_table
) t
WHERE rn = 1;

如果同一begintime下有多个相同的最大endtime行，想保留所有这些行，可以把ROW_NUMBER()换成RANK()。

方法二：使用Python Pandas过滤 #

假设数据已读取到DataFrame中，且begintime和endtime已转换为datetime类型：

方式1：排序后分组取首行 #

先按begintime升序、endtime降序排序，再分组取每组第一行：

import pandas as pd

# 读取数据（示例）
# df = pd.read_sql("SELECT * FROM your_table", db_connection)
df['begintime'] = pd.to_datetime(df['begintime'])
df['endtime'] = pd.to_datetime(df['endtime'])

# 排序后分组
df_sorted = df.sort_values(['begintime', 'endtime'], ascending=[True, False])
result = df_sorted.groupby('begintime').first().reset_index()

方式2：利用idxmax获取最大行索引 #

直接通过groupby的idxmax方法获取每组中endtime最大的行的索引，再提取对应行：

import pandas as pd

# 读取并转换时间列（示例）
# df = pd.read_sql("SELECT * FROM your_table", db_connection)
df['begintime'] = pd.to_datetime(df['begintime'])
df['endtime'] = pd.to_datetime(df['endtime'])

# 获取最大endtime的行
max_end_idx = df.groupby('begintime')['endtime'].idxmax()
result = df.loc[max_end_idx].reset_index(drop=True)

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