SQL - 按自定义24小时周期对结果进行分组

I need to create an Oracle 11g SQL report showing daily productivity: how many units were shipped during a 24 hour period. Each period starts at 6am and finishes at 5:59am the next day.

我需要创建一个Oracle 11g SQL报告,显示每日生产力:24小时内装运的单位数量。每个时段从早上6点开始,到第二天上午5:59结束。

How could I group the results in such a way as to display this 24 hour period? I've tried grouping by day, but, a day is 00:00 - 23:59 and so the results are inaccurate.

我怎样才能将结果分组以显示这24小时的时间段?我白天尝试分组,但是,一天是00:00 - 23:59,因此结果不准确。

The results will cover the past 2 months.

结果将涵盖过去2个月。

Many thanks.

4 个解决方案

#1

group by trunc(your_date - 1/4)

#2

Days are whole numbers in oracle so 6 am will be 0.25 of a day so :

天数是oracle中的整数,所以早上6点将是0.25天,所以:

select 
trunc(date + 0.25) as period, count(*) as number
from table
group by trunc(date + 0.25 )

I havent got an oracle to try it on at the moment.

我现在没有一个神谕尝试它。

#3

Well, you could group by a calculated date. So, add 6 hours to the dates and group by that which would then technically group your dates correctly and produce the correct results.

好吧,你可以按计算日期分组。因此,将6小时添加到日期和组中,然后在技术上将您的日期正确分组并生成正确的结果。

#4

Assuming that you have a units column or similar on your table, perhaps something like this:

假设您的表上有单位列或类似的列,可能是这样的:

SQL Fiddle

SELECT 
  TRUNC(us.shipping_datetime - 0.25) + 0.25 period_start
, TRUNC(us.shipping_datetime - 0.25) + 1 + (1/24 * 5) + (1/24/60 * 59) period_end
, SUM(us.units) units
FROM units_shipped us
GROUP BY TRUNC(us.shipping_datetime - 0.25)
ORDER BY 1

This simply subtracts 6 hours (0.25 of a day) from each date. If the time is earlier than 6am, the subtraction will make it fall prior to midnight, and when the resultant value is truncated (time element is removed, the date at midnight is returned), it falls within the grouping for the previous day.

这只是从每个日期减去6小时(0.25天)。如果时间早于早上6点,则减法将使其在午夜之前下降,并且当结果值被截断(时间元素被移除,返回午夜的日期)时,它将落在前一天的分组中。

Results:

|                 PERIOD_START |                   PERIOD_END | UNITS |
-----------------------------------------------------------------------
| April, 22 2013 06:00:00+0000 | April, 23 2013 05:59:00+0000 |     1 |
| April, 23 2013 06:00:00+0000 | April, 24 2013 05:59:00+0000 |     3 |
| April, 24 2013 06:00:00+0000 | April, 25 2013 05:59:00+0000 |     1 |

The bit of dynamic maths in the SELECT is just to help readability of the results. If you don't have a units column to SUM() up, i.e. each row represents a single unit, then substitute COUNT(*) instead.

SELECT中的动态数学运算只是为了帮助读取结果的可读性。如果没有单位列到SUM(),即每行代表一个单位,则替换COUNT(*)。

#1

group by trunc(your_date - 1/4)

#2