oracle 如何在 DATETIME 范围内“分组”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4286216/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to “group by” over a DATETIME range?
提问by Andrea
I'm trying to bulid up a datetime range based transactions report, for a business that can be open across two days, depending on the shift management.
我正在尝试为可以在两天内开放的企业建立基于日期时间范围的交易报告，具体取决于轮班管理。
The user can select a datetime range (monthly, daily, weekly, freely...), the query I implemented get the startDateTime and the EndDateTime, and will return all the transactions total grouped by day.
用户可以选择一个日期时间范围（每月、每天、每周、自由...），我实现的查询获取 startDateTime 和 EndDateTime，并将返回按天分组的所有事务总数。
I.E.
IE
DateTime Total Sales
---------------------------
10/15/2010 ,300.38
10/16/2010 ,780.00
10/17/2010 ,200.22
10/20/2010 0.66
My problem is that if the shift of the business is setted, for example, from 05.00 AM to 02.00 AM of the next day, all the transactions done from midnight to 02.00 AM will be grouped in the next day... and so on... the totals are corrupted. When a business has a shift like this, it wants a report based on that shift, but without code patching (I'm using Java calling Oracle native queries), I'm unable to get the requested report.
我的问题是，如果设置了业务的班次，例如从早上 05 点到第二天凌晨 02 点，那么从午夜到凌晨 2 点进行的所有交易都会在第二天分组......等等。 .. 总数已损坏。当企业有这样的转变时，它需要基于该转变的报告，但没有代码修补（我使用 Java 调用 Oracle 本机查询），我无法获得请求的报告。
I'm wondering if there is some smart manner to group by a datetime range these sets of transactions using nothing more than Oracle.
我想知道是否有一些聪明的方法可以仅使用 Oracle 来按日期时间范围对这些事务集进行分组。
Here goes the query, for the the month of July:
这是 7 月份的查询：
SELECT Q1.dateFormat, NVL(Q1.sales, 0)
FROM (
SELECT to_date(to_char(tx.datetimeGMT +1/24 , 'mm-dd-yyyy'), 'mm-dd-yyyy') AS dateFormat
, NVL(SUM(tx.amount),0) AS sales
FROM Transaction tx
WHERE tx.datetimeGMT > to_date('20100801 08:59:59', 'yyyymmdd hh24:mi:ss') +1/24
AND tx.datetimeGMT < to_date('20100901 09:00:00', 'yyyymmdd hh24:mi:ss') + 1/24
GROUP BY to_date(to_char(tx.datetimeGMT +1/24 , 'mm-dd-yyyy'), 'mm-dd-yyyy')
) Q1
ORDER BY 1 DESC
采纳答案by Andrea
Thank you all for your answers, by taking a look to them I could write down the query I was searching for:
感谢大家的回答，通过查看他们，我可以写下我正在搜索的查询：
SELECT CASE
WHEN EXTRACT(HOUR FROM TX.DATETIME) >= 5 THEN TO_CHAR(TX.DATETIME,'DD-MM-YYYY')
WHEN EXTRACT(HOUR FROM TX.DATETIME) BETWEEN 0 AND 2 THEN TO_CHAR(TX.DATETIME-1,'DD-MM-YYYY')
WHEN EXTRACT(hour from tx.datetime) between 2 and 5 THEN to_char(TX.DATETIME-1,'DD-MM-YYYY')
END AS age,
NVL(SUM(tx.amount),0) AS sales
FROM TRANSACTION TX
WHERE tx.datetime > to_date('20100801 08:59:59', 'yyyymmdd hh24:mi:ss')
AND TX.DATETIME < TO_DATE('20100901 09:00:00', 'yyyymmdd hh24:mi:ss')
GROUP BY CASE
WHEN EXTRACT(HOUR FROM TX.DATETIME) >= 5 THEN TO_CHAR(TX.DATETIME,'DD-MM-YYYY')
WHEN EXTRACT(HOUR FROM TX.DATETIME) BETWEEN 0 AND 2 THEN TO_CHAR(TX.DATETIME-1,'DD-MM-YYYY')
WHEN EXTRACT(hour from tx.datetime) between 2 and 5 THEN to_char(TX.DATETIME-1,'DD-MM-YYYY')
END
ORDER BY 1
回答by Will Marcouiller
To group by a date range, you'll have to have this range into a column value into a subquery, and group by it in your query. Obviously, this date range within this column value will be of VARCHAR type.
要按日期范围分组，您必须将此范围放入子查询中的列值中，并在查询中按它分组。显然，此列值中的此日期范围将是 VARCHAR 类型。
回答by Ronnis
If the first shift of the day starts at 08:00, and the last shift of that same day ends 07:59 the next day, you can use something like this to group the transactions by the shift date.
如果当天的第一个班次从 08:00 开始，而同一天的最后一个班次在第二天 07:59 结束，您可以使用类似的方法按班次日期对交易进行分组。
select trunc(trans_date - interval '8' hour) as shift_date
,sum(amount)
from transactions
group
by trunc(trans_date - interval '8' hour)
order
by shift_date desc;
回答by gpeche
You can try this approach (just out of my head, not even sure if it runs):
您可以尝试这种方法（只是在我的脑海中，甚至不确定它是否运行）：
select
trans_date,
trans_shift,
aggregates(whatever)
from (
select
-- we want to group by normalized transaction date,
-- not by real transaction date
normalized_trans_date,
-- get the shift to group by
case
when trans_date between trunc(normalized_trans_date) + shift_1_start_offset and
trunc(normalized_trans_date) + shift_1_end_offset then
1
when trans_date between trunc(normalized_trans_date) + shift_2_start_offset and
trunc(normalized_trans_date) + shift_2_end_offset then
2
...
when trans_date between trunc(normalized_trans_date) + shift_N_start_offset and
trunc(normalized_trans_date) + shift_N_end_offset then
N
end trans_shift,
whatever
from (
select
-- get a normalized transaction date: if date is before 1st shift
-- it belongs to the day before
case
when trans_date - trunc(trans_date) < shift_1_start_offset then
trans_date - 1
else
trans_date
end normalized_trans_date,
t.*
from
transactions t
)
)
group by trans_date, trans_shift
