Azure Databricks SQL补全日期间隙并填充代理机构值的实现问询

在Azure Databricks SQL环境中处理含日期间隙的数据集，based_date列记录数据存入系统的日期。需求如下：

• 将所有日期转换为每月第一天
• 补全缺失的月份日期
• 为缺失日期填充对应账户的最后代理机构值

CREATE TEMPORARY VIEW xTableA
AS
SELECT 111 AS ACCOUNTID, '2022-02-05' AS BASED_DATE, 'XYZ' AS AGENCY
UNION ALL
SELECT 111, '2022-02-05', 'ABC'
UNION ALL
SELECT 111, '2022-05-25', 'BGG'
UNION ALL
SELECT 111, '2022-07-13', 'DXA'
UNION ALL
SELECT 111, '2023-02-22', 'VGQ'
UNION ALL
SELECT 114, '2022-08-09', 'QYD'
UNION ALL
SELECT 114, '2022-12-26', 'OMG'
UNION ALL
SELECT 114, '2023-03-12', 'TNK';

WITH xBased_Date AS (
SELECT 
    ACCOUNTID,
    AGENCY,
    CAST(date_trunc('MONTH', BASED_DATE) AS DATE) AS StartDate,
    LEAD(CAST(date_trunc('MONTH', BASED_DATE) AS DATE),1) OVER (ORDER BY CAST(date_trunc('MONTH', BASED_DATE) AS DATE)) AS EndDate
FROM xTableA
),
xRecursive AS (
    SELECT
    ACCOUNTID,
    add_months(StartDate, 1) AS xDate
    FROM xBased_Date
    WHERE add_months(StartDate, 1) <= EndDate
)

SELECT
a.ACCOUNTID,
ADD_MONTHS(a.StartDate, 1) AS BASED_DATE,
a.AGENCY
FROM xBased_Date a
CROSS JOIN xRecursive b
WHERE ADD_MONTHS(a.StartDate, 1) <= a.EndDate
ORDER BY 1, 2

核心思路 #

1. 预处理去重：对同一账户同一月份的记录排序，保留最后一条作为该月及后续缺失月份的填充基准
2. 生成日期序列：为每个账户生成从最早到最晚记录月份的完整月份第一天序列，补全缺失月份
3. 关联填充：用窗口函数向前填充缺失月份的代理机构值，最后合并原始记录与填充记录

示例代码（Databricks SQL兼容） #

CREATE TEMPORARY VIEW xTableA
AS
SELECT 111 AS ACCOUNTID, '2022-02-05' AS BASED_DATE, 'XYZ' AS AGENCY
UNION ALL
SELECT 111, '2022-02-05', 'ABC'
UNION ALL
SELECT 111, '2022-05-25', 'BGG'
UNION ALL
SELECT 111, '2022-07-13', 'DXA'
UNION ALL
SELECT 111, '2023-02-22', 'VGQ'
UNION ALL
SELECT 114, '2022-08-09', 'QYD'
UNION ALL
SELECT 114, '2022-12-26', 'OMG'
UNION ALL
SELECT 114, '2023-03-12', 'TNK';

WITH ranked_data AS (
    -- 标记同一账户同一月份的最后一条记录
    SELECT 
        ACCOUNTID,
        CAST(date_trunc('MONTH', BASED_DATE) AS DATE) AS month_start,
        AGENCY,
        ROW_NUMBER() OVER (PARTITION BY ACCOUNTID, CAST(date_trunc('MONTH', BASED_DATE) AS DATE) ORDER BY BASED_DATE DESC) AS rn
    FROM xTableA
),
unique_monthly_data AS (
    -- 提取每个账户每个月的最终代理机构，以及下一个代理机构的生效月份
    SELECT 
        ACCOUNTID,
        month_start,
        AGENCY,
        LEAD(month_start, 1, DATE_ADD(date_trunc('MONTH', CURRENT_DATE()), 1)) OVER (PARTITION BY ACCOUNTID ORDER BY month_start) AS next_month_start
    FROM ranked_data
    WHERE rn = 1
),
account_date_ranges AS (
    -- 获取每个账户的时间范围
    SELECT 
        ACCOUNTID,
        MIN(month_start) AS min_month,
        MAX(month_start) AS max_month
    FROM unique_monthly_data
    GROUP BY ACCOUNTID
),
date_series AS (
    -- 生成每个账户的完整月份序列
    SELECT 
        adr.ACCOUNTID,
        sequence.month_start
    FROM account_date_ranges adr
    LATERAL VIEW explode(sequence(adr.min_month, adr.max_month, INTERVAL 1 MONTH)) sequence AS month_start
),
filled_data AS (
    -- 向前填充代理机构值
    SELECT 
        ds.ACCOUNTID,
        ds.month_start AS BASED_DATE,
        LAST_VALUE(umd.AGENCY) OVER (PARTITION BY ds.ACCOUNTID ORDER BY ds.month_start ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS AGENCY
    FROM date_series ds
    LEFT JOIN unique_monthly_data umd 
        ON ds.ACCOUNTID = umd.ACCOUNTID 
        AND ds.month_start >= umd.month_start 
        AND ds.month_start < umd.next_month_start
),
original_monthly_records AS (
    -- 保留原始所有记录的月份转换结果
    SELECT 
        ACCOUNTID,
        CAST(date_trunc('MONTH', BASED_DATE) AS DATE) AS BASED_DATE,
        AGENCY
    FROM xTableA
)
-- 合并原始记录与填充记录，去重后排序
SELECT * FROM original_monthly_records
UNION ALL
SELECT * FROM filled_data
WHERE NOT EXISTS (
    SELECT 1 FROM original_monthly_records omr
    WHERE omr.ACCOUNTID = filled_data.ACCOUNTID 
    AND omr.BASED_DATE = filled_data.BASED_DATE
)
ORDER BY ACCOUNTID, BASED_DATE;

内容的提问来源于stack exchange，提问作者Let Soo Gas