sql - 显示每个月之前销售日期 > 3 个月的客户数量以及在给定月份具有“销售日期”的客户数量
问题描述
基本上,我的要求是 - 对于给定月份,有多少客户在给定月份前 3 个月拥有“上一个销售日期”,而这些客户中有多少人在给定月份拥有“销售日期”。
我尝试使用 Lag 函数,但我的列“Reactivated_Guests”总是给我空值。
SELECT datepart(month,["sale date"]) `"Sale_Month",count(distinct
["user id"]) "Lost_Guests",
lag("Guests",4) OVER (ORDER BY "Sale_Month")+
lag("Guests",5) OVER (ORDER BY "Sale_Month")+
lag("Guests",6) OVER (ORDER BY "Sale_Month")+
lag("Guests",7) OVER (ORDER BY "Sale_Month")+
lag("Guests",8) OVER (ORDER BY "Sale_Month")+
lag("Guests",9) OVER (ORDER BY "Sale_Month")+
lag("Guests",10) OVER (ORDER BY "Sale_Month")+
lag("Guests",11) OVER (ORDER BY "Sale_Month")+
lag("Guests",12) OVER (ORDER BY "Sale_Month") "Reactivated_Guests"
group by "Sale_Month"
order by "Sale_Month"
我的预期输出是按月计算,在给定月份之前的“销售日期”大于 3 个月的客人数量(Lost_Guests)以及这些客户中有多少人在给定月份有“销售日期”(Reactivated_Guests)
预期结果 :
Sale_Month Lost_Guests Reactivated_Guests
(prev Sale date > 3 months) (Prev Sale date > 3 months and
have a Sale date in given month)
June 1,200 110
July 1,800 130
Aug 1,900 140
实际结果 :
Sale_Month Lost_Guests Reactivated_Guests
June 1,200 null
July 1,800 null
Aug 1,900 null
样本数据 :
Customer Sale Date
AAAAA 11/15/2018
BBBBB 11/16/2018
CCCCC 9/23/2018
CCCCC 1/25/2019
AAAAA 3/16/2019 ----> so for given month of March, AAAAA to be
CCCCC 3/18/2019 considered in "Lost_Guests" because
AAAAA's previous sale date (11/15/2018) is
more than 3 months from the given month
(March - 2019) and AAAAA to be considered in
"Reactivated_guests" because AAAAA has a
Sale date in the given month (March-2019)
----> for given month of March, CCCCC shall not
be considered in "Lost guests" and
"Reactivated Guests" because
previous sale date (1/25/2019) is less
than 3 months from given month (March-2019)
and hence does not appear in
"Reactivated_Guests" as well
解决方案
这解决了问题的原始版本。
你似乎想要这样的东西:
select sale_month, count(distinct user_id) as guests,
count(distinct case when min_sale_date < sale_date - interval '3 month' then user_id end) as old_guests
from (select t.*,
min(sale_date) over (partition by user_id) as min_sale_date
from t
) t
group by sale_month
order by sale_month;
请注意,日期函数非常依赖于数据库,因此确切的语法可能会因您的数据库而异。
推荐阅读
- grafana - Grafana 添加了我不想要的其他仪表板
- css - 有没有办法使用资产管道拆分 CSS?
- azure - Azure 媒体服务使用帐户筛选器返回 0 持续时间
- angular - 可以将“客户列表”添加到“客户”模块
- python - 从列python获取元素
- c++ - 指向单例和内存泄漏的原始指针:Xcode 没有显示任何内存泄漏
- c# - 将一个表达式“注入”另一个表达式
- groovy - Groovy groupBy 可变字符序列
- node.js - 使用猫鼬(NodeJS)连接到 MongoDB Atlas 集群时出现问题
- servicestack - 如何配置客户端以使用 authsecret 进行访问?