Oracle/SQL 中的时间分片
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/379556/
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
Time slicing in Oracle/SQL
提问by Ryan Bright
I have a large-ish Oracle table containing rows representing units of work, with columns for start time and end time in addition to other meta-data.
我有一个大型 Oracle 表，其中包含表示工作单元的行，除了其他元数据之外，还有用于开始时间和结束时间的列。
I need to generate usage graphs from this data, given some arbitrary filtering criteria and a reporting time period. E.g., show me a graph of all of Alice's jobs for the 24-hour period starting last Tuesday at 7:00am. Each DB row will stack vertically in the graph.
给定一些任意过滤标准和报告时间段，我需要根据这些数据生成使用图。例如，给我看一张图表，显示从上周二早上 7:00 开始的 24 小时内 Alice 的所有工作。每个 DB 行将在图中垂直堆叠。
I could do this in a high-level language by querying all potentially relevant rows, time slicing each one into 1-minute buckets, and graphing the result. But is there an efficient way to do this time slicing in SQL? Or is there an existing Oracle technology that does this?
我可以通过查询所有可能相关的行、将每个行按时间划分为 1 分钟的存储桶并绘制结果图表，从而以高级语言执行此操作。但是有没有一种有效的方法可以在 SQL 中进行时间切片？或者是否有现有的 Oracle 技术可以做到这一点？
Thanks!
谢谢！
采纳答案by hamishmcn
In terms of getting the data out, you can use 'group by' and 'truncate' to slice the data into 1 minute intervals. eg:
在获取数据方面，您可以使用“group by”和“ truncate”将数据分成 1 分钟的间隔。例如：
SELECT user_name, truncate(event_time, 'YYYYMMDD HH24MI'), count(*)
FROM job_table
WHERE event_time > TO_DATE( some start date time)
AND user_name IN ( list of users to query )
GROUP BY user_name, truncate(event_time, 'YYYYMMDD HH24MI')
This will give you results like below (assuming there are 20 rows for alice between 8.00 and 8.01 and 40 rows between 8.01 and 8.02):
这将为您提供如下结果（假设 alice 在 8.00 和 8.01 之间有 20 行，在 8.01 和 8.02 之间有 40 行）：
Alice 2008-12-16 08:00 20
Alice 2008-12-16 08:01 40
回答by hamishmcn
This should do it fairly well. It will breakdown to the minute (1/1440 of a day).
这应该做得相当好。它将细分到分钟（一天的 1/1440）。
SELECT
to_char(Times.time,'hh24:mi'),
count(*)
FROM
(SELECT
time
FROM
dual
WHERE
1=2
MODEL
dimension by ( 0 as key )
measures ( sysdate -1 as time )
rules upsert ( time[ for key from 0 to 1 increment (1/1440) ] = sysdate-1 + cv(key)) ) Times,
job_table
WHERE
begintime <= Times.time
AND
endtime > Times.time
AND
user_name = 'Alice'
GROUP BY
Times.time
ORDER BY
Times.time
I'm sure there is a faster way to do this, but this is the best I can come up with. Hope this helps.
我相信有一种更快的方法可以做到这一点，但这是我能想到的最好的方法。希望这可以帮助。
回答by Tom H
Your best bet is to have a table (a temporary one generated on the fly would be fine if the time-slice is dynamic) and then join against that.
你最好的办法是有一个表（如果时间片是动态的，一个临时生成的表就可以了）然后加入它。
