SQL需求：筛选特定idcrsp并新增最近true日期及天数差列

需求说明 #

原有查询逻辑：筛选同时包含boolean_col为true和false的idcrsp，从中选取每个idcrsp对应的最早日期的false记录。

需扩展查询，新增两个列：

• date_true：对应idcrsp的boolean_col为true的最近日期
• difference_in_days：date_true与date_false的天数差

示例数据库数据 #

现有SQL语句 #

WITH eligible_ids AS (
    SELECT idcrsp
    FROM your_table
    GROUP BY idcrsp
    HAVING COUNT(DISTINCT boolean_col) = 2
),
earliest_false AS (
    SELECT idcrsp, date AS date_false
    FROM your_table t
    JOIN eligible_ids e ON t.idcrsp = e.idcrsp
    WHERE boolean_col = false
    QUALIFY ROW_NUMBER() OVER (PARTITION BY idcrsp ORDER BY date) = 1
)
SELECT * FROM earliest_false;

当前查询结果 #

期望输出结果 #

扩展后的SQL语句 #

WITH eligible_ids AS (
    SELECT idcrsp
    FROM your_table
    GROUP BY idcrsp
    HAVING COUNT(DISTINCT boolean_col) = 2
),
earliest_false AS (
    SELECT 
        idcrsp, 
        date AS date_false
    FROM your_table t
    JOIN eligible_ids e ON t.idcrsp = e.idcrsp
    WHERE boolean_col = false
    QUALIFY ROW_NUMBER() OVER (PARTITION BY idcrsp ORDER BY date) = 1
),
latest_true AS (
    SELECT 
        idcrsp, 
        MAX(date) AS date_true
    FROM your_table t
    JOIN eligible_ids e ON t.idcrsp = e.idcrsp
    WHERE boolean_col = true
    GROUP BY idcrsp
)
SELECT 
    ef.idcrsp,
    ef.date_false,
    lt.date_true,
    DATEDIFF(day, ef.date_false, lt.date_true) AS difference_in_days
FROM earliest_false ef
JOIN latest_true lt ON ef.idcrsp = lt.idcrsp;

说明 #

• 新增latest_trueCTE，通过MAX(date)获取每个符合条件的idcrsp对应的最近true记录日期
• DATEDIFF函数的参数顺序需注意，不同数据库语法可能略有差异（比如MySQL中使用DATEDIFF(date_true, date_false)）

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