计数Windows功能
计数Windows功能
提问于 2021-12-16 21:09:41
我希望得到一个滚动的客户数量的基础上，他们的忠诚等级。每个层都基于LTV (0-124.99,125-198.99,199-749.99和750+).这就是我现在所拥有的，每一次约会都要返回0，几个零散的几年。有人能帮上忙吗？
SELECT TRUNC(CD.date) AS "day", tier,
COUNT(customer_email) OVER(PARTITION BY TRUNC(CD.date), tier ORDER BY
TRUNC(CD.date), tier
ASC ROWS UNBOUNDED PRECEDING) AS "customers"
FROM {{ @public_fact_criquet_loyalty_master AS LM}}
RIGHT JOIN public.dim_calendar_dates CD ON TRUNC(CD.date) = TRUNC(LM.timestamp)
WHERE TRUNC(CD.date) BETWEEN '2011-11-09' AND CURRENT_DATE
GROUP BY TRUNC(CD.date), tier, customer_email
ORDER BY TRUNC(CD.date), tier ASC回答 2
Stack Overflow用户
回答已采纳
发布于 2021-12-27 18:06:16
问题已经变成了一个如何准备数据进行分析的问题，所以我将添加这段代码来创建允许进一步查询的基本数据。
drop table if exists test;
create table test (dt date, customer_email varchar(64), tier varchar(32));
insert into test values
('2/3/2021', 'xxxxxxxx@yyyyyy.com', '2almost_vip'),
('3/12/2021', 'xxxxxxxx@yyyyyy.com', '4the_players_club'),
('4/27/2021', 'xxxxxxxx@yyyyyy.com', '5the_players_club'),
('8/6/2021', 'xxxxxxxx@yyyyyy.com', '6the_players_club'),
('11/22/2021', 'xxxxxxxx@yyyyyy.com', '7the_players_club'),
('12/16/2021', 'xxxxxxxx@yyyyyy.com', '8the_players_club'),
('1/3/2021', 'abc@qrs.com', '2almost_vip'),
('2/12/2021', 'abc@qrs.com', '4the_players_club'),
('3/27/2021', 'abc@qrs.com', '5the_players_club'),
('7/6/2021', 'abc@qrs.com', '6the_players_club'),
('10/22/2021', 'abc@qrs.com', '7the_players_club'),
('11/16/2021', 'abc@qrs.com', '8the_players_club');
with recursive dates(d) as (
select '2021-01-01'::date as d
union all
select d + 1 as d
from dates
where d < '2021-12-31') -- This CTE creates dates from 2021-01-01 to 2021-12-31)
select dt, customer_email,
LAG(min(tier), 1) IGNORE NULLS OVER (PARTITION BY customer_email ORDER BY dt ASC) AS tier -- Find the previous non-NULL tier - remember that group by runs before window
from (
select dt, customer_email, tier as tier -- combine your input data with ...
from test
union all
select d as dt, customer_email, null as tier -- a list of all dates per email
from dates
cross join (select DISTINCT customer_email from test) -- combine all dates with all emails
)
group by customer_email, dt
order by customer_email, dt;此查询的输出(基于我输入的虚拟数据)是所有电子邮件的所有日期列表，并填写了它们的层。你下一步打算做什么对我来说还不清楚，所以我就把它留在这里。由于这个过程产生的数据比它开始的要多得多，所以它不会是超级高效的，而且很有可能不需要所有的数据来获得最终的结果。如果能消除这种数据扩展，处理速度就会提高。
Stack Overflow用户
发布于 2021-12-17 00:49:00
您已经通过TRUNC(CD.date)进行了分区，它以一种我认为您不想要的方式分解计数组。你在找这个吗？
COUNT(customer_email) OVER(PARTITION BY tier ORDER BY day ASC
ROWS UNBOUNDED PRECEDING) AS "customers"PS。您可以在窗口函数中使用"day“，因为它在第一个结果列中被定义为"TRUNC(CD.date)”。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/70385673
复制相关文章
相似问题
腾讯云开发者
