DAX如何填充日期范围内的缺失日期（Report Builder场景）

核心逻辑为先生成目标区间的完整连续日期序列，再和现有业务表左连接，用空值替换函数把缺失的score值设为0即可，以下为不同主流数据库的单条查询实现： #

前置说明 #

假设你的业务表名为daily_score，包含date、score两列，查询结果按日期正序排列后，输出的fill_score列就是你需要的连续数值序列。 #

MySQL 8.0+（支持递归CTE） #

WITH RECURSIVE date_range AS (
    -- 取表中最小日期作为序列起点，也可自行指定固定起始日期如'2010-08-01'
    SELECT MIN(STR_TO_DATE(date, '%d.%m.%Y')) AS dt FROM daily_score
    UNION ALL
    SELECT DATE_ADD(dt, INTERVAL 1 DAY) 
    FROM date_range 
    -- 最多生成30天日期，符合你的表存储规则
    WHERE dt < DATE_ADD((SELECT MIN(STR_TO_DATE(date, '%d.%m.%Y')) FROM daily_score), INTERVAL 29 DAY)
)
SELECT COALESCE(ds.score, 0) AS fill_score
FROM date_range dr
LEFT JOIN daily_score ds ON dr.dt = STR_TO_DATE(ds.date, '%d.%m.%Y')
ORDER BY dr.dt;

MySQL 5.x（无CTE支持） #

SELECT COALESCE(ds.score, 0) AS fill_score
FROM (
    -- 手动生成0-29的数字序列，构造最近30天连续日期
    SELECT DATE_SUB(CURDATE(), INTERVAL t.n DAY) AS dt
    FROM (
        SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
        UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
        UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14
        UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19
        UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24
        UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29
    ) t
) dr
LEFT JOIN daily_score ds ON dr.dt = STR_TO_DATE(ds.date, '%d.%m.%Y')
ORDER BY dr.dt;

PostgreSQL #

SELECT COALESCE(ds.score, 0) AS fill_score
FROM generate_series(
    (SELECT MIN(TO_DATE(date, 'DD.MM.YYYY')) FROM daily_score),
    (SELECT MIN(TO_DATE(date, 'DD.MM.YYYY')) + INTERVAL '29 days' FROM daily_score),
    '1 day'::interval
) dr(dt)
LEFT JOIN daily_score ds ON dr.dt::date = TO_DATE(ds.date, 'DD.MM.YYYY')
ORDER BY dr.dt;

SQL Server #

WITH date_range AS (
    SELECT CAST((SELECT MIN(CONVERT(DATE, date, 103)) FROM daily_score) AS DATE) dt
    UNION ALL
    SELECT DATEADD(DAY, 1, dt) FROM date_range
    WHERE dt < DATEADD(DAY, 29, (SELECT MIN(CONVERT(DATE, date, 103)) FROM daily_score))
)
SELECT ISNULL(ds.score, 0) AS fill_score
FROM date_range dr
LEFT JOIN daily_score ds ON dr.dt = CONVERT(DATE, ds.date, 103)
ORDER BY dr.dt
OPTION (MAXRECURSION 30);

注意事项 #

• 上述代码已经适配你样例中的日.月.年字符串日期格式，如果你实际date字段为日期类型，删掉对应的日期转换函数即可
• 如果需要固定取当前时间往前30天的序列，把生成日期序列的起始日期替换为CURDATE()（MySQL）、CURRENT_DATE（PostgreSQL）、GETDATE()（SQL Server）对应调整即可

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