按ID聚合数据行：筛选最优测试分数的技术实现求助

嘿，这个需求我之前碰过好多次，其实用数据库窗口函数或者Python Pandas都能轻松解决，给你分两种常用场景详细说：

一、用SQL处理（适用于数据库中的数据） #

核心思路是给每个ID下的记录按规则排序，然后只保留排名第一的那条。我们可以用窗口函数ROW_NUMBER()来实现：

假设你的表名为test_scores，字段包括id（用户ID）、test_score（测试分数）、test_date（测试日期）、standard_error（标准误差），具体SQL代码如下：

WITH ranked_scores AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY id 
            ORDER BY 
                ABS(DATEDIFF(CURRENT_DATE, test_date)) ASC,  -- 日期与当前日期差值越小越靠前
                standard_error ASC  -- 日期相同时，标准误差越小越靠前
        ) AS rn
    FROM test_scores
)
SELECT id, test_score, test_date, standard_error
FROM ranked_scores
WHERE rn = 1;

小提示：不同数据库的日期差值函数可能略有不同，比如PostgreSQL用AGE(CURRENT_DATE, test_date)，SQL Server用DATEDIFF(day, test_date, CURRENT_DATE)，但核心逻辑都是先按日期接近程度排序，再按标准误差排序。

二、用Python Pandas处理（适用于本地数据集） #

如果你的数据是CSV/Excel这类本地文件，用Pandas处理更方便，步骤很清晰：

1. 先读取数据并转换日期格式
2. 计算每条记录与当前日期的间隔天数
3. 按ID分组后，先按间隔天数升序、再按标准误差升序排序
4. 保留每个ID的第一条记录

具体代码示例：

import pandas as pd

# 读取数据（假设是CSV文件）
df = pd.read_csv('your_data.csv')

# 转换test_date为日期格式
df['test_date'] = pd.to_datetime(df['test_date'])

# 计算与当前日期的间隔天数（绝对值，确保是正数）
df['days_from_current'] = abs((pd.to_datetime('today') - df['test_date']).dt.days)

# 按规则排序：先按ID，再按间隔天数，最后按标准误差
df_sorted = df.sort_values(
    by=['id', 'days_from_current', 'standard_error'],
    ascending=[True, True, True]
)

# 保留每个ID的第一条记录
result = df_sorted.drop_duplicates(subset='id', keep='first')

# 可选：删除临时列days_from_current
result = result.drop(columns='days_from_current')

# 查看结果
print(result)

这样处理后，每个ID就只会保留最符合你要求的那一条记录啦～

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