PostgreSQL按日期合并员工IN/OUT打卡记录为单行的优化方案

需求回顾 #

将employee表中每位员工每日的IN、OUT时间戳合并至同一行，当日无OUT记录时对应字段显示null。

表结构：

CREATE TABLE employee (
id bigint PRIMARY KEY,
date_time timestamp,
type varchar,
name varchar);

原方案的不足 #

你提供的查询存在两个明显问题：

1. 表名笔误：子查询中误用了customers，实际应为employee；
2. 性能开销：两次子查询重复扫描全表，再通过日期转换后的字段做连接，不仅冗余扫描数据，还可能导致索引失效，数据量较大时效率极低。

优化方案：条件聚合（单次扫描表） #

通过条件聚合实现仅扫描一次表即可得到结果，是性能最优的方案（适用于每位员工每日最多一次IN/OUT的场景）：

SELECT
    MAX(CASE WHEN type = 'IN' THEN date_time END) AS in_time,
    MAX(CASE WHEN type = 'OUT' THEN date_time END) AS out_time,
    name
FROM employee
GROUP BY name, DATE_TRUNC('day', date_time)
ORDER BY DATE_TRUNC('day', date_time), name;

逻辑说明 #

• 按员工姓名name和打卡日期DATE_TRUNC('day', date_time)分组，确保同一员工同一天的记录归为一组；
• 用CASE语句筛选出IN/OUT类型的时间戳，通过MAX（单日单条记录时MIN结果一致）提取对应时间；
• 无OUT记录时，MAX(CASE...)会返回null，完全符合需求。

进阶场景：同一员工单日多次IN/OUT #

如果存在员工单日多次打卡（如外出返回后再次打卡），需要将每条IN记录匹配到后续最近的OUT记录，可使用窗口函数+关联查询：

WITH in_records AS (
    SELECT
        id,
        date_time AS in_time,
        name,
        DATE_TRUNC('day', date_time) AS record_date
    FROM employee
    WHERE type = 'IN'
)
SELECT
    i.in_time,
    o.date_time AS out_time,
    i.name
FROM in_records i
LEFT JOIN employee o
    ON o.name = i.name
    AND o.type = 'OUT'
    AND o.date_time >= i.in_time
    AND DATE_TRUNC('day', o.date_time) = i.record_date
    AND o.id = (
        SELECT MIN(id)
        FROM employee
        WHERE name = i.name
          AND type = 'OUT'
          AND date_time >= i.in_time
          AND DATE_TRUNC('day', date_time) = i.record_date
    )
ORDER BY i.record_date, i.name, i.in_time;

索引优化建议 #

为进一步提升查询速度，创建复合索引覆盖分组、过滤和排序字段：

CREATE INDEX idx_employee_name_date_type ON employee(name, DATE_TRUNC('day', date_time), type, date_time);

该索引可以让数据库直接通过索引获取所需数据，避免全表扫描。

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