如何在Impala中查找受试者未服药的缺失日期

核心思路和Oracle的日历表左连逻辑一致，不需要提前创建物理日历表，直接用Impala内置函数动态生成连续日期序列即可，性能更好也更灵活。

实现步骤 #

• 第一步：统一日期格式，先将源表中字符串格式的服药日期转换为标准DATE类型，避免关联时格式不匹配
• 第二步：按受试者维度，取每个受试者的最早服药日期、最晚服药日期作为连续日期序列的生成边界，避免生成超出实际观测周期的无效日期
• 第三步：用sequence+posexplode函数生成每个受试者观测周期内的所有连续日历日期
• 第四步：将连续日期序列与原服药记录表做左连接，筛选出原表服药日期为空的记录，即为对应受试者未服药的缺失日期

可直接运行的SQL代码 #

假设源表名为medicine_record，如果只查询受试者Charlie的缺失日期，写法如下：

WITH subject_date_bound AS (
    -- 取目标受试者的服药起止日期
    SELECT
        Subject,
        MIN(to_date(Medicine_Date, 'MM-dd-yyyy')) AS start_dt,
        MAX(to_date(Medicine_Date, 'MM-dd-yyyy')) AS end_dt
    FROM medicine_record
    WHERE Subject = 'Charlie' -- 如果要查所有受试者的缺失日期，删掉这行过滤条件即可
    GROUP BY Subject
),
full_calendar AS (
    -- 生成每个受试者观测周期内的所有连续日期
    SELECT
        s.Subject,
        calendar_dt
    FROM subject_date_bound s
    LATERAL VIEW posexplode(sequence(s.start_dt, s.end_dt, interval 1 days)) t AS pos, calendar_dt
)
-- 左连原表筛选缺失日期
SELECT
    c.Subject,
    -- 按需求转回MM-dd-yyyy格式输出
    date_format(c.calendar_dt, 'MM-dd-yyyy') AS missing_medicine_date
FROM full_calendar c
LEFT JOIN (
    SELECT DISTINCT 
        Subject,
        to_date(Medicine_Date, 'MM-dd-yyyy') AS medicine_dt
    FROM medicine_record
) m
ON c.Subject = m.Subject AND c.calendar_dt = m.medicine_dt
WHERE m.medicine_dt IS NULL;

结果验证 #

针对Charlie的样例数据，最早服药日为2018-09-09，最晚服药日为2018-09-15，生成的连续日期共7天，左连匹配后会过滤掉有服药记录的09-09、09-10、09-13、09-15，最终返回的缺失日期正好是09-11-2018、09-12-2018、09-14-2018，符合预期。

注意事项 #

• 如果源表Medicine_Date字段已经是DATE类型，可以去掉所有to_date转换逻辑，直接使用字段即可
• 如果需要支持跨月、跨年的日期缺失查询，该写法同样适用，sequence函数会自动处理月末、年末的日期进位
• 若需要排除周末、法定节假日等不需要服药的日期，可以在full_calendar层增加对应的日期过滤规则

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