PostgreSQL查询：按id_crsp获取date_creation最小值与date_requalif最大值

需求：修改现有查询，针对每个id_crsp，仅返回其对应in_requalification=FALSE的最早创建时间（date_creation最小值）和in_requalification=TRUE的最晚重新认定时间（date_requalif最大值），以此修正时间间隔为正值。

原查询语句 #

SELECT DISTINCT true_table.id_crsp ,
true_table.type_orientation as type_orientation_apres_requalif,
CONCAT(to_char(true_table.ts_crea, 'DD-MM-YYYY - HH24hMI')) AS date_requalif,
CONCAT(to_char(false_table.ts_crea, 'DD-MM-YYYY - HH24hMI')) AS date_creation,
AGE (true_table.ts_crea::timestamp, false_table.ts_crea::timestamp) as intervalle_creation_requalif
FROM mydb as false_table 
INNER JOIN
(SELECT * FROM mydb WHERE in_requalification= TRUE) as true_table 
ON false_table.id_crsp = true_table.id_crsp
AND false_table.in_requalification = FALSE
order by intervalle_creation_requalif asc;

字段说明 #

• in_requalification：布尔型，标识是否为重新认定记录
• id_crsp：字符串型，唯一标识
• ts_crea：时间戳型，记录创建时间

修改后的查询语句 #

SELECT 
    base.id_crsp,
    max_true.type_orientation as type_orientation_apres_requalif,
    CONCAT(to_char(max_true.max_ts_crea, 'DD-MM-YYYY - HH24hMI')) AS date_requalif,
    CONCAT(to_char(min_false.min_ts_crea, 'DD-MM-YYYY - HH24hMI')) AS date_creation,
    AGE(max_true.max_ts_crea::timestamp, min_false.min_ts_crea::timestamp) as intervalle_creation_requalif
FROM (
    -- 筛选同时存在FALSE和TRUE状态的id_crsp
    SELECT id_crsp 
    FROM mydb 
    WHERE in_requalification IN (TRUE, FALSE)
    GROUP BY id_crsp 
    HAVING COUNT(DISTINCT in_requalification) = 2
) base
-- 关联取每个id_crsp的最早创建时间（FALSE状态）
INNER JOIN (
    SELECT 
        id_crsp,
        MIN(ts_crea) as min_ts_crea
    FROM mydb 
    WHERE in_requalification = FALSE
    GROUP BY id_crsp
) min_false ON base.id_crsp = min_false.id_crsp
-- 关联取每个id_crsp的最晚重新认定时间（TRUE状态）
INNER JOIN (
    SELECT 
        id_crsp,
        type_orientation,
        MAX(ts_crea) as max_ts_crea
    FROM mydb 
    WHERE in_requalification = TRUE
    GROUP BY id_crsp, type_orientation
) max_true ON base.id_crsp = max_true.id_crsp
ORDER BY intervalle_creation_requalif asc;

改动说明 #

1. 先通过子查询base筛选出同时存在两种状态的id_crsp，确保只处理符合条件的记录
2. 分别对两种状态的记录按id_crsp聚合，取ts_crea的最小/最大值，避免原查询多对多关联导致的重复结果和时间间隔异常
3. 计算的时间间隔为最晚重新认定时间 - 最早创建时间，确保结果为正值
4. 保留type_orientation_apres_requalif字段，若同一id_crsp的TRUE状态记录存在多个type_orientation，可根据需求改用STRING_AGG(type_orientation, ',')合并

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