时间戳

首页 > 解决方案 > 按小时分组，如果没有数据则为 0

sql - 按小时分组，如果没有数据则为 0

问题描述

我有以下格式查询：

select status as status,
    count(*) as count,
    EXTRACT(
        hour
        from creationtime
    ) AS hour,
    creationtime::date as datee
from user_logging_table_detail
where creationtime::date = current_date
    and status = 'SUCCESS'
group by hour,
    creationtime::date,
    status
order by hour asc


status  count   hour    datee
SUCCESS  1  1   8/6/2020
SUCCESS  2  2   8/6/2020
SUCCESS 5   3   8/6/2020
SUCCESS 2   4   8/6/2020
SUCCESS 3   5   8/6/2020
SUCCESS 2   8   8/6/2020
所需的输出应该是所有时间

    status  count   hour    datee 
    SUCCESS  1  1   8/6/2020
    SUCCESS  2  2   8/6/2020
    SUCCESS 5   3   8/6/2020
    SUCCESS 2   4   8/6/2020
    SUCCESS 3   5   8/6/2020
    SUCCESS 0   6   8/6/2020
    SUCCESS 0   7   8/6/2020
    SUCCESS 2   8   8/6/2020

我的意思是小时（6,7）应该带有（0）值。

标签： sql

解决方案

你的语法看起来像 Postgres，它有方便的generate_series()：

select gs.hh, ultd.status, count(ultd.status) as count
from generate_series(current_date, now(), interval '1 hour') gs(hh) left join
     zainksa_mobileapp.user_logging_table_detail ultd
     on ultd.creationtime >= gs.hh and
        ultd.creationtime < gs.hh + interval '1 hour' and
        ultd.status = 'SUCCESS' 
group by gs.hh, ultd.status
order by hour asc

推荐阅读