标题 sql:求用户的最大连续活跃天数 题目描述 表名:dwd_user_active_di 字段: user_id string, ds string (也是分区字段) 表样例: i234 ‘2023-05-01’ i234 ‘2023-05-02’ i234 ‘2023-05-03’ i234 ‘2023-05-05’ i234 ‘2023-05-07’ i234 ‘2023-05-08’ i222 ‘2023-05-05’ i222 ‘2023-05-07’ i222 ‘2023-05-08’ i222 ‘2023-05-09’ 结果返回: i234 3 i222 3
时间: 2023-09-04 14:16:07 浏览: 51
可以使用SQL语句进行求解,具体如下:
SELECT user_id, MAX(streak) AS max_streak FROM (
SELECT user_id, ds,
ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY ds)
- ROW_NUMBER() OVER(PARTITION BY user_id, active ORDER BY ds) AS streak
FROM (
SELECT user_id, ds,
CASE
WHEN
LAG(ds, 1, '1970-01-01') OVER(PARTITION BY user_id ORDER BY ds) = DATEADD(day, -1, ds)
THEN 1
ELSE 0
END AS active
FROM dwd_user_active_di
) t
) t2
GROUP BY user_id;
其中,内层的SELECT语句生成了一个包含user_id、ds和active字段的表,其中active表示该用户当天是否有活跃,如果有则为1,否则为0。接着,在外层的SELECT语句中,使用了窗口函数ROW_NUMBER()来生成一个streak序列,表示每个用户的连续活跃天数。最后,对每个用户的streak取最大值,即可得到用户的最大连续活跃天数。
相关问题
Cypher查询语句中,p=(:dwd_hive_blood_di_upper{id:'${id}'}) 和 (p:dwd_hive_blood_di_upper{id:'${id}'}) 写法的区别
在Cypher查询语句中,p=(:dwd_hive_blood_di_upper{id:'${id}'})和(p:dwd_hive_blood_di_upper{id:'${id}'})的写法有所不同:
- p=(:dwd_hive_blood_di_upper{id:'${id}'})是一个模式匹配,表示将匹配到的节点/关系赋值给变量p。这里的模式是一个简单的节点,它不需要有标签,但必须有一个id属性等于${id}的节点才能匹配成功。如果匹配成功,变量p将成为一个节点,节点的属性可以通过p.id、p.name等方式访问。
- (p:dwd_hive_blood_di_upper{id:'${id}'})是一个节点变量声明,表示声明一个节点变量p,并将其绑定到一个具有标签dwd_hive_blood_di_upper和id属性等于${id}的节点。这里的p是一个节点变量,它可以在查询中用于引用这个节点,例如p.name、p.age等。
因此,两种写法的主要区别在于是否需要将匹配到的节点赋值给一个变量,并且在使用变量时的方式略有不同。
抽取 ods 库中表 table1 最新分区数据,并结合 dim_table1 最新分区现有的 数据,根据 id 合并数据到 dwd 库中 dim_table1 的分区表,分区字段为 etl_date 且值与 ods 库的相对应表该值相等,并添加 dwd_insert_user、 dwd_insert_time 、 dwd_modify_user 、 dwd_modify_time 四 列 , 其 中 dwd_insert_user、dwd_modify_user 均填写“user1”。
假设 ods 库中的表 table1 有以下字段:id、name、age、etl_date;dim_table1 有以下字段:id、name、age、etl_date、dwd_insert_user、dwd_insert_time、dwd_modify_user、dwd_modify_time。dwd 库中的 dim_table1 分区表结构与 dim_table1 相同,但仅包含最新分区的数据。
以下为 SQL 语句实现该需求:
```sql
-- 创建临时表 tmp_table1 用于存储 ods 库中表 table1 最新分区数据
CREATE TEMPORARY TABLE tmp_table1 AS
SELECT *
FROM ods.table1
WHERE etl_date = (SELECT MAX(etl_date) FROM ods.table1);
-- 更新 dwd 库中 dim_table1 的分区表
INSERT INTO dwd.dim_table1 PARTITION(etl_date)
SELECT t1.*, 'user1', NOW(), 'user1', NOW(), etl_date
FROM tmp_table1 t1
JOIN (
SELECT id, MAX(etl_date) as etl_date
FROM dwd.dim_table1
GROUP BY id
) t2 ON t1.id = t2.id AND t1.etl_date = t2.etl_date;
```
解释:
首先创建临时表 tmp_table1,存储 ods 库中表 table1 最新分区的数据;然后通过 JOIN 操作将 tmp_table1 和 dwd 库中 dim_table1 的分区表合并,仅保留最新分区的数据(即 etl_date 最大的数据),并添加 dwd_insert_user、dwd_insert_time、dwd_modify_user、dwd_modify_time 四列,其中 dwd_insert_user、dwd_modify_user 均填写“user1”。最后将结果插入到 dwd 库中 dim_table1 的分区表中。
相关推荐
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)