选择按午夜时间分组的数据 [英] Select data grouped by time over midnight
问题描述
我有一张表格：
ID TIMEVALUE
----- ------ -------
1 06.07.15 06：43：01,000000000
2 06.07.15 12：17：01,000000000
3 06.07.15 18:21:01 ，000000000
4 06.07.15 23：56：01,000000000
5 07.07.15 04：11：01,000000000
6 07.07.15 10：47：01,000000000
7 07.07.15 12：32：01,000000000
8 07.07.15 14：47：01,000000000
，我想按特殊时间分组这些数据。
我目前的查询看起来像这样：
SELECT TO_CHAR（TIMEVALUE，'YYYY \MM \DD'），COUNT（ID），
SUM（CASE WHEN TO_CHAR（TIMEVALUE，'HH24MI'）<= 700 THEN 1 ELSE 0 END）as as day，
SUM（CASE WHEN TO_CHAR（TIMEVALUE，'HH24MI'）> 700 AND TO_CHAR（TIMEVALUE，'HH24MI'）< 1400 THEN 1 ELSE 0 END）as daytime，
SUM （例如当TO_CHAR（TIMEVALUE，'HH24MI'）> = 1400 THEN 1 ELSE 0 END）作为晚上从表
WHER E TIMEVALUE> = to_timestamp（'05 .07.2015'，'DD.MM.YYYY'）
GROUP BY TO_CHAR（TIMEVALUE，'YYYY \MM \DD'）
我得到了这个输出
白天总体早晨白天晚上
----- ---------
2015\07\05 454 0 0 454
2015\07\06 599 113 250 236
2015 \07\07 404 139 265 0
在同一天（0-7点，7-14点和14-24点）精细分组
现在我的问题是：
怎么能我在午夜时分组？
例如，第二天6-14,14-23和23-6点计数。
我希望你明白我的问题。如果有更好的解决方案，欢迎您提高我的上层查询。 解决方案
编辑：它现在被测试： SQL小提琴
关键是简单地通过来调整组，以便6am之前的任何内容都与前一天分组。
SELECT TO_CHAR（CASE WHEN EXTRACT（HOUR FROM timevalue））< 6
THEN timevalue - 1
ELSE timevalue
END，'YYYY\MM\DD'）AS day，
COUNT（*）总体来说，
SUM（CASE作为早晨，
SUM（CASE WHEN EXTRACT（HOUR FROM timevalue）>>>当提取物（时间值的小时）> = 6和提取物（小时的时间值）< 14
那么1 ELSE 0结束时） = 14 AND EXTRACT（HOUR FROM timevalue）< 23
THEN 1 ELSE 0 END）as daytime，
SUM（CASE WHEN EXTRACT（HOUR FROM timevalue）< 6 OR EXTRACT（HOUR FROM timevalue）> ; = 23
THEN 1 ELSE 0 END）晚上
FROM my_table
WHERE timevalue> = TO_TIMESTAMP（'05 .07.2015'，'DD.MM.YYYY'）
GROUP BY TO_CHAR（CASE WHEN EXTRACT（HOUR FROM timevalue））< 6
THEN VALUE - 1
ELSE timevalue
END，'YYYY\MM\DD'）;
I have a table like:
ID TIMEVALUE
----- -------------
1 06.07.15 06:43:01,000000000
2 06.07.15 12:17:01,000000000
3 06.07.15 18:21:01,000000000
4 06.07.15 23:56:01,000000000
5 07.07.15 04:11:01,000000000
6 07.07.15 10:47:01,000000000
7 07.07.15 12:32:01,000000000
8 07.07.15 14:47:01,000000000
and I want to group this data by special times.
My current query looks like this:
SELECT TO_CHAR(TIMEVALUE, 'YYYY\MM\DD'), COUNT(ID),
SUM(CASE WHEN TO_CHAR(TIMEVALUE, 'HH24MI') <=700 THEN 1 ELSE 0 END) as morning,
SUM(CASE WHEN TO_CHAR(TIMEVALUE, 'HH24MI') >700 AND TO_CHAR(TIMEVALUE, 'HH24MI') <1400 THEN 1 ELSE 0 END) as daytime,
SUM(CASE WHEN TO_CHAR(TIMEVALUE, 'HH24MI') >=1400 THEN 1 ELSE 0 END) as evening FROM Table
WHERE TIMEVALUE >= to_timestamp('05.07.2015','DD.MM.YYYY')
GROUP BY TO_CHAR(TIMEVALUE, 'YYYY\MM\DD')
and I am getting this output
day overall morning daytime evening
----- ---------
2015\07\05 454 0 0 454
2015\07\06 599 113 250 236
2015\07\07 404 139 265 0
so that is fine grouping on the same day (0-7 o'clock, 7-14 o'clock and 14-24 o'clock)
But my question now is:
How can I group over midnight?
For example count from 6-14 , 14-23 and 23-6 o'clock on next day.
I hope you understand my question. You are welcome to even improve my upper query if there is a better solution.
EDIT: It is tested now: SQL Fiddle
The key is simply to adjust the group by so that anything before 6am gets grouped with the previous day. After that, the counts are pretty straight-forward.
SELECT TO_CHAR(CASE WHEN EXTRACT(HOUR FROM timevalue) < 6
THEN timevalue - 1
ELSE timevalue
END, 'YYYY\MM\DD') AS day,
COUNT(*) AS overall,
SUM(CASE WHEN EXTRACT(HOUR FROM timevalue) >= 6 AND EXTRACT(HOUR FROM timevalue) < 14
THEN 1 ELSE 0 END) AS morning,
SUM(CASE WHEN EXTRACT(HOUR FROM timevalue) >= 14 AND EXTRACT(HOUR FROM timevalue) < 23
THEN 1 ELSE 0 END) AS daytime,
SUM(CASE WHEN EXTRACT(HOUR FROM timevalue) < 6 OR EXTRACT(HOUR FROM timevalue) >= 23
THEN 1 ELSE 0 END) AS evening
FROM my_table
WHERE timevalue >= TO_TIMESTAMP('05.07.2015','DD.MM.YYYY')
GROUP BY TO_CHAR(CASE WHEN EXTRACT(HOUR FROM timevalue) < 6
THEN timevalue - 1
ELSE timevalue
END, 'YYYY\MM\DD');
这篇关于选择按午夜时间分组的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
