我有一个关于SQL的问题,希望能得到一些帮助,因为一直找不到合适的答案。
背景:
我正在使用供应商产品,它具有作为后端的Oracle数据库。我可以编写任何adhoc SQL来查询底层表,但不能对其基础结构(或数据模型本身)进行任何更改。我目前感兴趣的表大约有100万行,主要用于跟踪用户会话。它有4个感兴趣的列:
当前表示例:
表名 = web_session
背景:
我正在使用供应商产品,它具有作为后端的Oracle数据库。我可以编写任何adhoc SQL来查询底层表,但不能对其基础结构(或数据模型本身)进行任何更改。我目前感兴趣的表大约有100万行,主要用于跟踪用户会话。它有4个感兴趣的列:
session_id
(这是一个主键,每个会话唯一),user_name
,start_date
(跟踪会话开始的日期)和stop_date
(跟踪会话结束的日期)。我的目标是根据给定的开始日期和结束日期,在月、日和小时级别上执行活动会话的数据聚合。我需要创建一个视图(或3个单独的视图),它可以执行聚合本身,或者作为中间对象,然后我可以查询并执行聚合操作。我理解最终的SQL/视图可能实际上需要3个不同的视图(一个用于月份,一个用于天数,一个用于小时数),但似乎无论时间段如何,概念都应该是相同的。当前表示例:
表名 = web_session
| Session_id | user_name | start_date | stop_date
----------------------------------------------------------------------------
| 1 | joe | 4/20/2017 10:42:10 PM | 4/21/2017 2:42:10 AM |
| 2 | matt | 4/20/2017 5:43:10 PM | 4/20/2017 5:59:10 PM |
| 3 | matt | 4/20/2017 3:42:10 PM | 4/20/2017 5:42:10 PM |
| 4 | joe | 4/20/2017 11:20:10 AM | 4/20/2017 4:42:10 PM |
| 5 | john | 4/20/2017 8:42:10 AM | 4/20/2017 11:42:10 AM |
| 6 | matt | 4/20/2017 7:42:10 AM | 4/20/2017 11:42:10 PM |
| 7 | joe | 4/19/2017 11:20:10 PM | 4/20/2017 1:42:10 AM |
小时视图的理想输出
- 对于示例,12:00可以是0或24。
| Date | HR | active_sessions | distinct_users |
------------------------------------------------------------
| 4/21/2017 | 2 | 1 | 1 |
| 4/21/2017 | 1 | 1 | 1 |
| 4/20/2017 | 0 | 1 | 1 |
| 4/20/2017 | 23 | 1 | 1 |
| 4/20/2017 | 22 | 1 | 1 |
| 4/20/2017 | 17 | 2 | 1 |
| 4/20/2017 | 16 | 2 | 2 |
| 4/20/2017 | 15 | 2 | 2 |
| 4/20/2017 | 14 | 1 | 1 |
| 4/20/2017 | 13 | 1 | 1 |
| 4/20/2017 | 12 | 1 | 1 |
| 4/20/2017 | 11 | 3 | 3 |
| 4/20/2017 | 10 | 2 | 2 |
| 4/20/2017 | 9 | 2 | 2 |
| 4/20/2017 | 8 | 2 | 2 |
| 4/20/2017 | 7 | 1 | 1 |
| 4/20/2017 | 1 | 1 | 1 |
| 4/20/2017 | 0 | 1 | 1 |
| 4/19/2017 | 23 | 1 | 1 |
目标及其他选项
我想通过这个输出最终实现的目标是,在两个日期之间,以月、日或小时为单位显示活动会话数的折线图。在小时示例中,结合日期和小时使用X轴,活动会话使用Y轴。如果用户悬停在图表上的点上,则可以查看不同的用户数。请注意,活动会话是在时间间隔内任何时间打开的会话总数。不同的用户是时间间隔内的不同用户总数。如果我在同一小时内登录和注销了两次,则会有2个活动会话,但只有1个不同的用户。
备选方案
这似乎是一个经常出现的问题,但从我所有的谷歌搜索和栈溢出研究中,我似乎找不到正确的方法。如果我的查询或理想输出思路有误,我也接受替代建议,以使我能够在前端适当地填充所需的输出以生成图表。
我尝试过的一些SQL(真诚的努力)
我尝试过很多查询,但我将从这个查询开始,因为它是我最接近的,但速度非常慢(无法使用),而且它仍然没有产生我需要的结果。
Select * FROM (
SELECT
u.YearDt, u.MonthDt, u.DayDt, u.HourDt, u.MinDt,
COUNT(Distinct u.session_id) as unique_sessions,
COUNT(Distinct u.user_name) as unique_users,
LISTAGG(u.user_name, ', ') WITHIN GROUP (ORDER BY u.user_name ASC) as users
FROM
(SELECT EXTRACT(year FROM l.start_date) as YearDt,
EXTRACT(month FROM l.start_date) as MonthDt,
EXTRACT(day FROM l.start_date) as DayDt,
EXTRACT(HOUR FROM CAST(l.start_date AS TIMESTAMP)) as HourDt,
EXTRACT(MINUTE FROM CAST(l.start_date AS TIMESTAMP)) as MinDt,
l.session_id,
l.user_name,
l.start_date as act_date,
1 as is_start
FROM web_session l
UNION ALL
SELECT EXTRACT(year FROM l.stop_date) as YearDt,
EXTRACT(month FROM l.stop_date) as MonthDt,
EXTRACT(day FROM l.stop_date) as DayDt,
EXTRACT(HOUR FROM CAST(l.stop_date AS TIMESTAMP)) as HourDt,
EXTRACT(MINUTE FROM CAST(l.stop_date AS TIMESTAMP)) as MinDt,
l.session_id,
l.user_name,
l.stop_date as act_date,
0 as is_start
FROM web_session l
) u
GROUP BY CUBE ( u.YearDt, u.MonthDt, u.DayDt, u.HourDt, u.MinDt)
) c
| 4/20/2017 | 17 | 2 | 1 |
,你能解释一下它们为什么不同吗? - krokodilko