如何在Impala-Shell中获取日期区间数据，无数据日期返回0计数

要让无数据的日期也显示计数为0，核心是先生成目标区间内的所有连续日期，再和原表的统计结果做左连接，空值补0。以下是主流数据库的实现方案：

MySQL（8.0+ 支持递归CTE） #

WITH RECURSIVE date_range AS (
    SELECT '2023-07-12' AS cdate  -- 区间起始日期
    UNION ALL
    SELECT DATE_ADD(cdate, INTERVAL 1 DAY)
    FROM date_range
    WHERE cdate < '2023-07-18'  -- 区间结束日期
)
SELECT 
    COALESCE(t.count_num, 0) AS `count(*)`,
    dr.cdate
FROM date_range dr
LEFT JOIN (
    SELECT COUNT(*) AS count_num, TO_DATE(date_column) AS cdate
    FROM `table`
    WHERE TO_DATE(date_column) BETWEEN '2023-07-12' AND '2023-07-18'
    GROUP BY TO_DATE(date_column)
) t ON dr.cdate = t.cdate
ORDER BY dr.cdate;

PostgreSQL #

用generate_series快速生成连续日期：

WITH date_range AS (
    SELECT generate_series(
        '2023-07-12'::DATE,
        '2023-07-18'::DATE,
        '1 day'::INTERVAL
    ) AS cdate
)
SELECT 
    COALESCE(t.count_num, 0) AS "count(*)",
    dr.cdate::DATE
FROM date_range dr
LEFT JOIN (
    SELECT COUNT(*) AS count_num, date_column::DATE AS cdate
    FROM "table"
    WHERE date_column::DATE BETWEEN '2023-07-12' AND '2023-07-18'
    GROUP BY date_column::DATE
) t ON dr.cdate::DATE = t.cdate
ORDER BY dr.cdate;

Oracle #

用CONNECT BY生成连续日期序列：

WITH date_range AS (
    SELECT TO_DATE('2023-07-12', 'YYYY-MM-DD') + LEVEL - 1 AS cdate
    FROM dual
    CONNECT BY TO_DATE('2023-07-12', 'YYYY-MM-DD') + LEVEL - 1 <= TO_DATE('2023-07-18', 'YYYY-MM-DD')
)
SELECT 
    NVL(t.count_num, 0) AS "count(*)",
    dr.cdate
FROM date_range dr
LEFT JOIN (
    SELECT COUNT(*) AS count_num, TRUNC(date_column) AS cdate
    FROM "table"
    WHERE TRUNC(date_column) BETWEEN TO_DATE('2023-07-12', 'YYYY-MM-DD') AND TO_DATE('2023-07-18', 'YYYY-MM-DD')
    GROUP BY TRUNC(date_column)
) t ON dr.cdate = t.cdate
ORDER BY dr.cdate;

关键说明 #

• 替换SQL中的起始、结束日期为你需要的区间
• COALESCE（MySQL/PostgreSQL）或NVL（Oracle）用来将左连接产生的空计数替换为0
• 原表子查询仅筛选区间内的数据，减少不必要的计算量

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