Hive中有一个技巧可以生成一系列的数字——然后这些数字就可以变成日期。这是一个痛苦，但有一个技巧使用 space() 以及 posexplode() --从这里改编。
我不确定语法是否100%正确，但类似这样：

with hh as (
      select unix_timestamp(min(start_time)) + (n - 1) * 60*60 as hh_start,
             unix_timestamp(min(start_time)) + n * 60*60 as hh_end
      from (select unix_timestamp(min(start_time)) as min_st,
                   floor((unix_timestamp(max(end_time)) - unix_timestamp(min(start_time))) / (60 * 60)) as num_hours
            from t
           ) x lateral view
           posexplode(split(space(num_hours + 1), ' ')) pe as n, x
     )
select hh.hh_start, count(t.id)
from hh left join
     t
     on t.start_time < hh.hh_end and
        t.end_time >= hh.hh_start
group by hh.hh_start
order by h.hh_start;

用维数做一个不等式连接给了我期望的o/p

Dimension
+-------------------+
|start_time         |
+-------------------+
|2020-03-31 12:00:00|
|2020-03-31 13:00:00|
|2020-03-31 14:00:00|
|2020-03-31 15:00:00|
|2020-03-31 16:00:00|
|2020-03-31 17:00:00|
|2020-03-31 18:00:00|
|2020-03-31 19:00:00|
|2020-03-31 20:00:00|
|2020-03-31 21:00:00|
|2020-03-31 22:00:00|
|2020-03-31 23:00:00|
|2020-04-01 00:00:00|
|2020-04-01 01:00:00|
|2020-04-01 02:00:00|
|2020-04-01 03:00:00|
+-------------------+

联接条件

tab.start_time <= dim.start_time and tab.end_time > dim.start_time