Swallow the hour data-CodePudding

24 hours a day, as shown in figure, with zero filling value1, valu2 data, I only wrote four hours, is not only a, b, c3 group, there might be thousands of group, is this meaning, has nothing to do because of columns can be null, use the query detected results, figure 2 is not a new data

 
Create table TEST 
(
ID VARCHAR2 (15), 
HOUR NUMBER, 
VALUE1 NUMBER, 
TIME DATE, 
VALUE2 NUMBER, 
The NAME NVARCHAR2 (15) 
)

 
Insert into TEST (ID, and VALUE1, TIME, VALUE2, NAME) 
Values (' a ', 1, 1, to_date (' 01:00:00 17-10-2018 ', 'the dd - mm yyyy hh24: mi: ss'), 1, "aa"); 
Insert into TEST (ID, and VALUE1, TIME, VALUE2, NAME) 
Values (' a ', 2, 1, to_date (' 02:00:00 17-10-2018 ', 'the dd - mm yyyy hh24: mi: ss'), 1, "aa"); 
Insert into TEST (ID, and VALUE1, TIME, VALUE2, NAME) 
Values (' a ', 4, 1, to_date (' 04:00:00 17-10-2018 ', 'the dd - mm yyyy hh24: mi: ss'), 1, "aa"); 
Insert into TEST (ID, and VALUE1, TIME, VALUE2, NAME) 
Values (' b ', 1, 1, to_date (' 01:00:00 17-10-2018 ', 'the dd - mm yyyy hh24: mi: ss'), 1, "bb"); 
Insert into TEST (ID, and VALUE1, TIME, VALUE2, NAME) 
Values (' c ', 2, 1, to_date (' 02:00:00 17-10-2018 ', 'the dd - mm yyyy hh24: mi: ss'), 1, "cc"); 
Commit;

CodePudding user response:

The building Lord baidu "ORACLE gelled statements",

CodePudding user response:

Reference upstairs gelled report

 
With t1 as (select distinct hour from test) 
Select a, id, t1, and NVL (Dr. Alue1, 0), a.t ime, NVL (Dr. Alue2, 0), a.n ame from the test a 
PARTITION BY Anderson (d) 
Right join t1 
On t1. Hour=a.h our

CodePudding user response:

 select t1. Id, 
NVL (t1 and t2. Hour) and 
NVL (t1. Value1, 0) value1, 
NVL (t1 time, t2 time) time, 
NVL (t1. Value2, 0) value2, 
Max (t1) name) over (partition by t1. The id name 
From the test t1 partition by (t1. Id) 
Right join (select level hour, 
To_date (' 20181017 ', 'yyyymmdd') + level/24 time 
The from dual 
Connect by level & lt; T2 on t1=4). The hour=t2. The hour;

CodePudding user response:

Gelled statements, using conventional SQL can be realized:

 
Select distinct Anderson d, a.h our NVL (b.v alue1, 0) as value1, a.t ime, NVL (b.v alue2, 0) as value2, a.n ame 
The from (select id, rn) as hour, trunc (time) + rn/24 as time, name 
From the test, select rownum as an rn from dual connect by rownum & lt;=24) 
B) a left join the test on Anderson, d=b.i d and a.t ime=b.t ime 
The order by Anderson, d, a.h our