时间戳

首页 > 解决方案 > 查找过去 X 天内的活跃客户

sql - 查找过去 X 天内的活跃客户

问题描述

我正面临一些困难时期，需要快速帮助。如果有人可以帮助我，那就太好了。提前非常感谢:)

我有 2 张桌子。第一个表：daily_customer_snapshot：客户的每日快照，如下所示。

c_id 日期 状态 地点
b1 2020-12-01 积极的 OOW
b1 2020-12-02 积极的 OOW
b1 2020-12-03 积极的 OOW
b1 2020-12-04 积极的 OOW
b1 2020-12-05 积极的 OOW
b3 2020-12-06 积极的 OOW
b3 2020-12-07 积极的 OOW
b3 2020-12-08 积极的 OOW
b1 2020-12-09 衰变 信息战
b2 2020-12-15 积极的 OOW

第二个表：customer_date_series：包含从用户成为我们的客户之日起的日期系列。例如：参考图片 2：用户 b1 在“2020-12-01”成为我们的客户，用户 b3 在“2​​020-12-06”成为我们的客户，b2 在“2020-12-15”成为我们的客户。我已经生成了带有 customer_id 的日期系列，以计算在任何给定日期我们有多少客户。

c_id 日期
b1 2020-12-01
b1 2020-12-02
b1 2020-12-03
b1 2020-12-04
b1 2020-12-05
b1 2020-12-06
b1 2020-12-07
b1 2020-12-08
b1 2020-12-09
b1 2020-12-10
b1 2020-12-11
b1 2020-12-12
b1 2020-12-13
b1 2020-12-14
b1 2020-12-15
b1 2020-12-16
b3 2020-12-06
b3 2020-12-07
b3 2020-12-08
b3 2020-12-09
b3 2020-12-10
b3 2020-12-11
b3 2020-12-12
b3 2020-12-13
b3 2020-12-14
b3 2020-12-15
b3 2020-12-16
b2 2020-12-15
b2 2020-12-16

我离开了加入 table1 (customer_date_series) 和 table2 (daily_customer_snapshot) 以了解任何给定日期的客户行为概览。我得到了如图 3 所示的结果。

查询加入：

    select 
    bds.date,
    bds.c_id,
    b.state,
    b.location
    
FROM
    customer_date_series bds LEFT JOIN daily_customer_snapshot b ON bds.c_id = b.c_id and bds.date = b.date
ORDER BY
    1,2;
日期 c_id 状态 地点
2020-12-01 b1 积极的 OOW
2020-12-02 b1 积极的 OOW
2020-12-03 b1 积极的 OOW
2020-12-04 b1 积极的 OOW
2020-12-05 b1 积极的 OOW
2020-12-06 b1
2020-12-06 b3 积极的 OOW
2020-12-07 b1
2020-12-07 b3 积极的 OOW
2020-12-08 b1
2020-12-08 b3 积极的 OOW
2020-12-09 b1 衰变 信息战
2020-12-09 b3
2020-12-10 b1
2020-12-10 b3
2020-12-11 b1
2020-12-11 b3
2020-12-12 b1
2020-12-12 b3
2020-12-13 b1
2020-12-13 b3
2020-12-14 b1
2020-12-14 b3
2020-12-15 b1
2020-12-15 b2 积极的 OOW
2020-12-15 b3
2020-12-16 b1
2020-12-16 b2
2020-12-16 b3

这就是我挣扎的地方。我在这里面临挑战。我想创建名为“状态”的新列，如果daily_customer_snapshot 中的客户数据在current_date 的过去5 天内更新，我想将状态设置为“活动”，否则为“非活动”。前任： 在此处输入图像描述 在此处输入图像描述

标签： sqlamazon-redshift

解决方案

如果您想同时使用这两个表，那么横向连接可以满足您的需求：

select bds.date, bds.c_id, b.state, b.location
--CASE WHEN b.state = '%ActiveDecay%' between current_date- 10 and current_date THEN 'ActIve' ELSE 'DECAY' END as STATUS
FROM battery_date_series bds LEFT JOIN LATERAL
     (SELECT b.*
      FROM battery b 
      WHERE bds.c_id = b.c_id and b.date <= bds.date
      ORDER BY b.date DESC
      LIMIT 1
     ) b
     ON 1=1
ORDER BY 1,2;

推荐阅读