补齐小时数据

Question

如图，用0补齐每天24小时的value1，valu2数据，我只写了4个小时的，不是只有a、b、c3个组，有可能有上千组，大体是这个意思，无关因要列可以为空，用查询语句查出图二的结果，不是新增数据create table TEST
(
  ID     VARCHAR2(15),
  HOUR   NUMBER,
  VALUE1 NUMBER,
  TIME   DATE,
  VALUE2 NUMBER,
  NAME   NVARCHAR2(15)
)insert into TEST (ID, HOUR, VALUE1, TIME, VALUE2, NAME)
values ('a', 1, 1, to_date('17-10-2018 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 1, 'aa');
insert into TEST (ID, HOUR, VALUE1, TIME, VALUE2, NAME)
values ('a', 2, 1, to_date('17-10-2018 02:00:00', 'dd-mm-yyyy hh24:mi:ss'), 1, 'aa');
insert into TEST (ID, HOUR, VALUE1, TIME, VALUE2, NAME)
values ('a', 4, 1, to_date('17-10-2018 04:00:00', 'dd-mm-yyyy hh24:mi:ss'), 1, 'aa');
insert into TEST (ID, HOUR, VALUE1, TIME, VALUE2, NAME)
values ('b', 1, 1, to_date('17-10-2018 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 1, 'bb');
insert into TEST (ID, HOUR, VALUE1, TIME, VALUE2, NAME)
values ('c', 2, 1, to_date('17-10-2018 02:00:00', 'dd-mm-yyyy hh24:mi:ss'), 1, 'cc');
commit;

Answer 1

楼主百度一下 “ORACLE 稠化报表“ 。

Answer 2

引用樓上 稠化报表with t1 as (select distinct hour from test)
select a,id,t1.hour,nvl(a.value1,0),a.time,nvl(a.value2,0),a.name from test a
PARTITION BY (a.id)
right join t1
on t1.hour=a.hour

Answer 3

select t1.id,
       nvl(t1.hour, t2.hour) hour,
       nvl(t1.value1, 0) value1,
       nvl(t1.time, t2.time) time,
       nvl(t1.value2, 0) value2,
       max(t1.name) over(partition by t1.id) name
  from test t1 partition by(t1.id)
 right join (select level hour,
                    to_date('20181017', 'yyyymmdd') + level / 24 time
               from dual
             connect by level <= 4) t2 on t1.hour = t2.hour;

Answer 4

稠化报表不好理解，使用常规SQL即可实现：select distinct a.id,a.hour,nvl(b.value1,0) as value1,a.time,nvl(b.value2,0) as value2,a.name
  from(select id,rn as hour,trunc(time)+rn/24 as time,name 
         from test,(select rownum as rn from dual connect by rownum <= 24)
      )a left join test b on a.id = b.id and a.time = b.time
order by a.id,a.hour