Databricks SQL生成含公历日期、儒略日及月末年末标识的Delta Lake表需求咨询

当然可以用Databricks SQL来实现你需要的日历Delta表！我来给你一步步拆解实现方法～

首先明确你需要的表格结构：

• Calendar_Date：公历日期（例如2024-08-31）
• Julian_Date：对应日期的儒略日（即该日期在当年的第几天）
• EndOfMonth：布尔标识，日期为当月最后一天时为1，否则为0
• EndOfYear：布尔标识，日期为当年最后一天时为1，否则为0

实现SQL脚本 #

Databricks SQL提供了一系列日期函数可以直接满足你的需求，以下是完整的生成并写入Delta表的代码：

-- 创建Delta表，生成未来5年的日历数据
CREATE OR REPLACE TABLE calendar_julian_dates
USING DELTA
AS
WITH date_range AS (
    -- 生成从当前日期开始，未来5年（60个月）的每日日期序列
    SELECT explode(sequence(current_date(), add_months(current_date(), 60), interval 1 day)) AS Calendar_Date
)
SELECT
    Calendar_Date,
    -- 直接获取日期对应的儒略日（当年第几天）
    dayofyear(Calendar_Date) AS Julian_Date,
    -- 判断是否为月末：当前日期等于当月最后一天则标记1
    CASE WHEN Calendar_Date = last_day(Calendar_Date) THEN 1 ELSE 0 END AS EndOfMonth,
    -- 判断是否为年末：既是月末又是12月则标记1
    CASE WHEN Calendar_Date = last_day(Calendar_Date) AND month(Calendar_Date) = 12 THEN 1 ELSE 0 END AS EndOfYear
FROM date_range;

代码说明 #

• sequence(current_date(), add_months(current_date(), 60), interval 1 day)：生成日期序列，从当前日期到未来5年的最后一天，步长为1天；explode函数将序列拆分为单行记录
• dayofyear()：Databricks SQL内置函数，直接返回日期在当年的儒略日（范围1-366）
• last_day()：返回当前日期所在月份的最后一天，用来判断是否为月末
• 年末判断通过同时检查月末和月份为12月来实现

验证结果 #

你可以执行以下查询验证目标数据：

SELECT * FROM calendar_julian_dates
WHERE Calendar_Date IN ('2024-08-31', '2024-12-31');

预期返回结果：

备注：内容来源于stack exchange，提问作者Gourav Joshi