是否有一种方法可以通过窗口函数(SQL)计算保留率

Question

我有一个名为'orders'的表从2019年1月4日到2019年1月25日，我们修改了界面，并希望看到对保留率的影响。为了计算保留率，我想找到所有在2019年1月4日和01/25/2019之间订购的客户，并在第一阶后的30天内找到其中有多少客户。

。

我尝试创建一个查询以使用窗口函数(Amazon Redshift(获取此数据，但我无法设法找到解决方案。

SELECT customer_id, FIRST_VALUE(order_date IGNORE NULLS) 
over (partition by customer_id order by order_date asc rows between unbounded preceding and unbounded following) AS first_order,
NTH_VALUE(order_date, 2)
over(partition by customer_id order by order_date desc
rows between unbounded preceding and unbounded following) AS second_order
FROM il_country_sg.v_fct_orders
WHERE order_date between '20190104' AND '20190226'
GROUP BY customer_id, order_date

这是我尝试做的。我的问题是，当我使用first_value和nth_value(2(函数时，我会得到两个最新日期。

我需要使用last_value和nth_value(n-1(功能，但我不知道该怎么做。也许您有更好的解决方案可以帮助我：(

谢谢你，祝你有美好的一天！

Answer 1

我想找到所有在01/04/2019和01/25/2019之间订购的客户，并在其第一订单后的30天内查找其中有多少位。

。

您可以使用lead()。这样的逻辑应该为您提供满足问题条件的客户的一阶：

SELECT fo.*
FROM (SELECT fo.*,
             LEAD(fo.order_date) OVER (PARTITION BY fo.customer_id ORDER BY fo.order_date) as next_order_date,
             ROW_NUMBER() OVER (PARTITION BY fo.customer_id ORDER BY fo.order_date) as seqnum
      FROM il_country_sg.v_fct_orders fo
      WHERE fo.order_date >= '20190104'
     ) fo
WHERE order_date < '20190226' AND
      seqnum = 1 AND
      DATEDIFF(day, orderdate, next_order_date) <= 30;

您可以使用子查询或CTE来获取此类客户的计数。