PostgreSQL：计算每个ID首次与末次分数记录的天数差

别担心，这个需求其实用PostgreSQL的基础聚合函数就能轻松实现，我给你拆解一下步骤，再附上具体的查询语句：

核心思路 #

我们需要对每个id做三件事：

• 找到该id对应的最早的记录日期（首次分数记录）
• 找到该id对应的最晚的记录日期（末次分数记录）
• 计算这两个日期之间的天数差

具体实现步骤 #

首先假设你的表名为scores，注意如果你的date字段是字符串类型（比如样本里的1/1/2017格式），需要先把它转换成PostgreSQL的date类型，用TO_DATE()函数指定格式掩码（这里假设是月/日/年，所以用'MM/DD/YYYY'；如果是日/月/年就换成'DD/MM/YYYY'）。

然后用GROUP BY id把相同id的记录分组，再用聚合函数MIN()和MAX()分别提取首尾日期，最后通过日期相减+提取天数得到差值。

完整查询语句 #

SELECT
    id,
    -- 转换日期格式并取首次记录日期
    MIN(TO_DATE(date, 'MM/DD/YYYY')) AS first_record_date,
    -- 转换日期格式并取末次记录日期
    MAX(TO_DATE(date, 'MM/DD/YYYY')) AS last_record_date,
    -- 计算首尾日期的天数差
    EXTRACT(DAY FROM MAX(TO_DATE(date, 'MM/DD/YYYY')) - MIN(TO_DATE(date, 'MM/DD/YYYY'))) AS days_between
FROM
    scores
GROUP BY
    id
ORDER BY
    id;

补充说明 #

• 如果你的date字段已经是PostgreSQL原生的date类型，直接去掉TO_DATE()函数即可，简化成MIN(date)和MAX(date)。
• 计算天数差也可以用AGE()函数，效果完全一致：DATE_PART('day', AGE(MAX(date), MIN(date))) AS days_between。

用你提供的样本数据测试的话，比如id=11的首次日期是2017-01-01，末次是2017-03-09，计算出来的天数差就是67天，完全符合预期。

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