滚动每日不同计数 [英] Rolling Daily Distinct Counts
问题描述
我们有一个包含以下列的表格:
We have a table with the following columns:
SESSION_ID USER_ID CONNECT_TS
-------------- --------------- ---------------
1 99 2013-01-01 2:23:33
2 101 2013-01-01 2:23:55
3 104 2013-01-01 2:24:41
4 101 2013-01-01 2:24:43
5 233 2013-01-01 2:25:01
我们需要获取每天的不同用户数量 和活跃用户"数量,活跃用户"的定义是最近45天内使用过该应用程序的用户.这是我们想出的,但是我觉得必须有更好的方法:
We need to get a distinct count of users for each day and a count of "active users" which are defined as users that have used the application in the last 45 days. Here is what we have come up with, but I feel like there has to be a better way:
select trunc(a.connect_ts)
, count(distinct a.user_id) daily_users
, count(distinct b.user_id) active_users
from sessions a
join sessions b
on (b.connect_ts between trunc(a.connect_ts) - 45 and trunc(a.connect_ts))
where a.connect_ts between '01-jan-13' and '12-jun-13'
and b.connect_ts between '01-nov-12' and '12-jun-13'
group by trunc(a.connect_ts);
我们研究了窗口函数,但看起来不支持不同的计数.我们还考虑过首先将聚合加载到临时表中,但是再次,不同的计数将其排除在外.有没有更好的方法可以做到这一点?
We looked at window functions, but it doesn't look like distinct counts are supported. We also considered loading aggregates into a temp table first but, again, the distinct counts ruled it out. Is there a better way to be doing this?
推荐答案
如果您的Oracle版本支持WITH语句,这可能对您有帮助:
If Your version of Oracle supports WITH-statements, this might help You:
with sel as (
select trunc(a.connect_ts) as logon_day
, count(distinct user_id) as logon_count
from sessions
group by trunc(connect_ts)
)
select s1.logon_day
, s1.logon_count as daily_users
, (select sum(logon_count) from sel where logon_day between s1.logon_day - 45 and s1.logon_day) as active_users
from sel s1
否则,您必须以这种方式编写(执行速度要慢得多...):
otherwise You'll have to write it this way (which executes much slower...):
select sel.logon_day
, sel.logon_count as daily_users
, (select count(distinct user_id) as logon_count
from t_ad_session
where trunc(connect_ts) between sel.logon_day - 45 and sel.logon_day) as active_users
from (select trunc(connect_ts) as logon_day, count(distinct user_id) as logon_count
from t_ad_session
group by trunc(connect_ts)) sel
这篇关于滚动每日不同计数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!