时间戳

首页 > 解决方案 > SQL：根据条件选择不同的用户和分组

sql - SQL：根据条件选择不同的用户和分组

问题描述

我有一个数据库表：

User_id | User Name | Join_date  | Activity_date |  
1           abc       01/01/2021    02-01-2021
2           jay       01/01/2021    03-01-2021
2           jay       01/01/2021    04-01-2021
1           abc       01/01/2021    09-01-2021
1           abc       01/01/2021    16-01-2021 
2           jay       01/01/2021    05-01-2021
3           xyz       03/03/2021    12-03-2021
3           xyz       03/03/2021    30-03-2021
2           jay       01/01/2021    26-01-2021

我想根据用户对用户进行分桶Activity_date，例如每隔一天进行活动的用户进入表 1，每周进行活动的用户（间隔 6-7 天）进入表 2，所有其他用户进入表 3。

output像这样的东西：

temporary_table1: (隔天做一次活动的人继续一天)

User_id | User Name | Join_date  | Activity_date |  
2           jay       01/01/2021    03-01-2021
2           jay       01/01/2021    04-01-2021
2           jay       01/01/2021    05-01-2021

temporary_table_2: （从第一次活动日期起每 6-7 天做一次活动的人）

User_id | User Name | Join_date  | Activity_date |  
1           abc       01/01/2021    02-01-2021
1           abc       01/01/2021    09-01-2021
1           abc       01/01/2021    15-01-2021

temporary_table_3: (在这里添加 user_id =2 因为从这个用户完成的最后一个活动到最近完成的活动有超过 7 天的差距)

User_id | User Name | Join_date  | Activity_date |  
3           xyz       03/03/2021    12-03-2021
3           xyz       03/03/2021    30-03-2021
2           jay       01/01/2021    26-01-2021

如何在 SQL（Redshift）中实现这一点？

标签： sqlgroup-byamazon-redshift

解决方案

你应该能够做这样的事情，然后你可以在 date_diff 上进行分组：

SELECT
user_id,
user_name,
join_date
activity_date,

#window function
LAG(activity_date) OVER (PARTITION BY user_id ORDER BY activity_date ASC) as day_before,

#difference in days between activity_date and day_before
DATEDIFF(
    day, 
    LAG(activity_date) OVER (PARTITION BY user_id ORDER BY activity_date ASC),
    activity_date
    ) as date_diff
FROM your_dataset

推荐阅读