Oracle – helps to make a query

I have source of data in the following format:

Event Type| Date
1         | 2011-07-14 09:00
1         | 2011-07-14 09:01
1         | 2011-07-14 09:02
2         | 2011-07-14 09:30
2         | 2011-07-14 09:31
1         | 2011-07-14 10:00
1         | 2011-07-14 10:01

Event types are sorted by date, as they occurred. I need to make a query which will show the date ranges when events were used, sorted by date. Like this:

Event Type | Date Range
1          | 2011-07-14 09:00 - 2011-07-14 09:02
2          | 2011-07-14 09:30 - 2011-07-14 09:31
1          | 2011-07-14 10:00 - 2011-07-14 10:01

Do you have any hints? I reckon this will probably need to be done with analytic functions, but I haven’t been able to come up with a decent solution yet.

You could also try the following approach:

WITH ranked AS (
  SELECT
    EventType,
    Date,
    ROW_NUMBER() OVER (ORDER BY Date) -
      ROW_NUMBER() OVER (PARTITION BY EventType ORDER BY Date) AS GroupID
  FROM Events
)
SELECT
  EventType,
  MIN(Date) AS StartDate,
  MAX(Date) AS EndDate
FROM ranked
GROUP BY
  GroupID,
  EventType
ORDER BY
  MIN(Date)
Hello, buddy!稿源:Hello, buddy! (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » Oracle – helps to make a query

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录