如何使用datetime列按分钟统计记录数（无记录分钟需补0）

• 仅统计了存在记录的时段，没有覆盖查询日期的全量分钟，无法生成计数为0的行
• 时间截断精度为小时，不符合分钟维度统计的要求

核心是先生成查询日期的全量分钟基准序列，再和统计结果关联补0，步骤如下：

1. 生成查询单日的连续分钟序列，单日共1440个分钟（00:00到23:59）
2. 将分钟序列与指定查询的客户做笛卡尔关联，得到「客户+全量分钟」的基准全集
3. 原表数据先按分钟精度截断，分组统计每个分钟的记录数，再左关联到基准全集，未匹配到的计数用COALESCE函数补0

以下为Hive/Spark SQL版本适配代码：

-- 可直接修改以下两个参数调整查询客户和日期
set var.target_customer = 'A';
set var.target_date = '2020-01-20';

with 
-- 生成当日全量1440个分钟序列
all_minutes as (
    select 
        date_add(to_date('${hiveconf:var.target_date}'), (i/1440)) as minute_ts
    from (
        select posexplode(split(space(1439), ' ')) as (i, val)
    ) t
),
-- 生成查询基准：客户+当日所有分钟
query_base as (
    select 
        '${hiveconf:var.target_customer}' as Customer,
        date_format(minute_ts, 'MM/dd/yyyy HH:mm:00') as Time_Start
    from all_minutes
),
-- 原表按分钟统计有数据的记录数
data_stat as (
    select
        Customer,
        date_format(Time_Start, 'MM/dd/yyyy HH:mm:00') as stat_minute,
        count(*) as cnt
    from Table_1
    where 
        Customer = '${hiveconf:var.target_customer}'
        and to_date(Time_Start, 'MM/dd/yyyy HH:mm:ss') = to_date('${hiveconf:var.target_date}')
    group by Customer, stat_minute
)
-- 关联补0得到最终结果
select
    qb.Customer,
    qb.Time_Start,
    coalesce(ds.cnt, 0) as Count
from query_base qb
left join data_stat ds 
on qb.Customer = ds.Customer and qb.Time_Start = ds.stat_minute
order by qb.Time_Start;

如果使用MySQL 8.0及以上版本，可使用递归CTE生成时间序列，适配代码如下：

WITH RECURSIVE all_minutes AS (
    SELECT STR_TO_DATE('2020-01-20 00:00:00', '%m/%d/%Y %H:%i:%s') AS minute_ts
    UNION ALL
    SELECT DATE_ADD(minute_ts, INTERVAL 1 MINUTE)
    FROM all_minutes
    WHERE minute_ts < STR_TO_DATE('2020-01-20 23:59:00', '%m/%d/%Y %H:%i:%s')
),
query_base AS (
    SELECT 'A' AS Customer, DATE_FORMAT(minute_ts, '%m/%d/%Y %H:%i:00') AS Time_Start
    FROM all_minutes
),
data_stat AS (
    SELECT 
        Customer,
        DATE_FORMAT(Time_Start, '%m/%d/%Y %H:%i:00') AS stat_minute,
        COUNT(*) AS cnt
    FROM Table_1
    WHERE Customer = 'A' AND DATE(STR_TO_DATE(Time_Start, '%m/%d/%Y %H:%i:%s')) = '2020-01-20'
    GROUP BY Customer, stat_minute
)
SELECT qb.Customer, qb.Time_Start, COALESCE(ds.cnt, 0) AS Count
FROM query_base qb
LEFT JOIN data_stat ds ON qb.Customer = ds.Customer AND qb.Time_Start = ds.stat_minute
ORDER BY qb.Time_Start;

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