sql - 根据过去 31 天每天的情况滚动唯一计数
问题描述
我有一个表订单,其中包含有关下订单的时间以及谁下订单的信息。
order_timestamp user_id
-------------------- ---------
1-JAN-20 02.56.12 123
3-JAN-20 12.01.01 533
23-JAN-20 08.42.18 123
12-JAN-20 02.53.59 238
19-JAN-20 02.33.72 34
使用此信息,我想每天计算在过去 31 天内仅下过一个订单的不同用户的数量,从而得到一个表格
date distinct_user_count
----------- ---------------------
1-JAN-20 8
2-JAN-20 10
3-JAN-20 11
(i.e in the 31 days before and including 1st jan 2020, 8 unique users ordered only once, etc...)
简单地说,对于每一天 - 31,计算该期间每个用户的订单数(表中的条目),如果该计数仅为 1,则计算该用户的初始开始日期。
我可以编写查询来计算那些只订购一次的人:
with temp as (
select
user_id,
count(*) as order_count
from
orders
where
trunc(order_timestamp) >= trunc(systimestamp - interval '31' day)
group by
user_id
)
select
user_id,
order_count
from
temp
where
login_count=1
但我不确定如何实现按日期计数。请您协助我完成/编写查询吗?感谢您提前支持。
解决方案
您可以使用两个级别的grouping
和self join
,如下所示:
Select dt, count(1) as cnt
from
(Select distinct trunc(t1.order_timestamp) as dt,
t1.user_id
From your_table t1
join your_table t2
On t1.user_id = t2.user_id
And trunc(t2.order_timestamp) between trunc(t1.order_timestamp - interval '31' day)
and trunc(t1.order_timestamp)
Group by t1.user_id, trunc(t1.order_timestamp)
Having count(1) = 1)
Group by dt;
或者你可以使用NOT EXISTS
如下:
Select trunc(t1.order_timestamp),
Count(1) as cnt
From your_table t1
Where not exists
(Select 1
From your_table t2
Where t1.rowid <> t2.row_id
And t1.user_id = t2.user_id
And trunc(t2.order_timestamp) between trunc(t1.order_timestamp - interval '31' day)
and trunc(t1.order_timestamp)
Group by trunc(t1.order_timestamp)
推荐阅读
- python-3.x - 如何在 Python3 中将按键发送到终端?
- spring - spring - 使用 @ActiveProfiles 覆盖 spring.profiles.active
- .net - 在 F# Interactive 中加载 dotnet 项目文件
- sql - Cross join InterBase 根据条件获取重复记录
- c# - c# excel 如何在已用范围内查找第一个单元格。
- javascript - Vapor 3 接收包含 BOOL 的字符串化 JSON
- html - 如何根据角度 6 中的单选按钮选择显示/隐藏 div?
- xamarin - Prism 模板包:Xamarin 表单 - Fody 异常
- r - 将手册添加到包含两个单独的 geom 对象的 ggplot
- javascript - 按 Enter 添加动态字段