我有一个Oracle表,表中充满了与日期时间相关的数据行。
例如:
Value Datetime
----- ----------------
123 12/08/2018 00:00
456 12/08/2018 01:00
789 12/08/2018 02:00
... ...
312 12/08/2018 23:00
321 12/09/2018 00:00
... ...
我需要将这些数据按天分组,但不知何故,将每小时的数据转换为24小时内每小时一列的列。
例如:
Date Value(00:00) Value(01:00) Value(02:00) ... Value(23:00)
---- ------------ ------------ ------------ --- ------------
12/08/2018 123 456 789 ... 312
12/09/2018 321 654 987 ... 423
... ... ... ... ... ...
以这种格式从表中获取数据的最有效的查询方式是什么?如果这个问题已经得到回答,我深表歉意。我在网上搜索了很多,但老实说,我甚至不知道搜索这个答案的正确关键词。提前谢谢。
我已经尽力简化了堆栈溢出的答案。我能够使用类似于下面的查询来格式化信息。
SELECT date,
SUM(CASE WHEN EXTRACT(HOUR FROM datetime) >= 0 AND EXTRACT(HOUR FROM datetime) < 1 THEN value ELSE 0 END) as "value(00:00)",
SUM(CASE WHEN EXTRACT(HOUR FROM datetime) >= 1 AND EXTRACT(HOUR FROM datetime) < 2 THEN value ELSE 0 END) as "value(01:00)",
SUM(CASE WHEN EXTRACT(HOUR FROM datetime) >= 2 AND EXTRACT(HOUR FROM datetime) < 3 THEN value ELSE 0 END) as "value(02:00)",
...
FROM (
SELECT TO_CHAR(datetime,'YYYY-MM-DD') AS date,
value,
datetime
FROM table
)
GROUP BY date
获取行并将其转换为列的过程称为枢轴。截至11g(?(,Oracle有PIVOT
关键字可以在本机中执行此操作。请注意,Oracle中没有"时间"数据类型,所以在进行数据透视之前,我将它们转换为两位字符。数据透视中必须有一个聚合函数,因此请确保没有具有相同日期的重复行。
select * from (
select "Value", to_char( "Datetime", 'HH24' ) as dt from table1
)
pivot ( max( "Value" ) for ( dt ) in ( '00' AS "hour0",
'01' as "hour1", '02' as "hour2" ));
编辑:
要按天聚合,忽略时间组件,请在内联视图中添加一个TRUNCed
列。
select * from (
select "Value", trunc("Datetime") as the_day,
to_char( "Datetime", 'HH24' ) as dt from table1
)
pivot ( max( "Value" ) for ( dt ) in ( '00', '01', '02' ));
SQL Fiddle 2