挑战100天不停更hive sql第33天 -游戏玩家-首日连续登陆占比

本文链接：https://blog.csdn.net/weixin_38221481/article/details/124719747

🌿挑战100天不停更，刷爆 hive sql🧲

详情请点击🔗我的专栏🖲,共同学习,一起进步~

文章目录

NUM: 第33天 -游戏玩家-首日连续登陆占比

我们之前写过关于访问的统计,活跃用户的统计,以及连续出现的值等
先把链接贴出来
挑战100天不停更第30天 -求连续出现的值 (lead()求的偏移量,再join即可)
挑战100天不停更hive sql第27天 -活跃用户统计(row_number()打标求差值,然后分组)
挑战100天不停更，刷爆 hive sql第26天 - 场景题-访问统计(累计访问使用 sum()开窗)
今天要写的sql并没有用到开窗函数, 因为涉及到首日的问题, 我们只需要先求得首日的日期,再和原表进行join,通过_datediff()_求得日期范围即可求得首日连续登陆的_playid_count_,再和总玩家求占比即可
这里突然想到还有一种关于_**连续**_的问题,视频是B站上的,_求得最大连续获胜次数_
传送门
这个问题相对于挑战100天不停更hive sql第27天 -活跃用户统计这里只需要进行两次分区即可将 获胜和失败区分开,然后操作一样是求差值,分组即可,这里供大家参考
大家如果有类似的问题也欢迎留言哈~ 一起进步! 感谢~🍔

🧨那就不废话了, 开始今天的刷题~~🧨

🎈表结构

🎉建表并插入数据

CREATE TABLE t34
(
    player_id    bigint,
    device_id    bigint,
    event_date   string,
    games_played bigint
);
INSERT INTO t34 (player_id, device_id, event_date, games_played)
VALUES (1, 2, '2016-03-01', 5),
       (1, 2, '2016-03-02', 6),
       (2, 3, '2017-06-25', 1),
       (3, 1, '2016-03-02', 0),
       (3, 4, '2018-07-03', 5);

👓问题:求首次登录的第二天再次登录的玩家的占比

换句话说，您需要计算从首次登录日期开始至少连续两天登录的玩家的数量，然后除以玩家总数。

查询结果

🧨SQL

WITH login_first AS
         (
             SELECT player_id
                  , min(event_date) login_time
             FROM t34
             GROUP BY player_id
         ),
     cnt AS
         (
             SELECT count(DISTINCT player_id) play_cnt
             FROM t34
         ),
     login_2d_cnt AS (
         SELECT count(1) login_2d_cnt
         FROM login_first
                  LEFT JOIN t34 ON t34.player_id = login_first.player_id
         WHERE datediff(event_date, login_time) = 1
     )
SELECT
    round(login_2d_cnt / cnt.play_cnt,2) play_2d_rate
FROM  login_2d_cnt, cnt
;

关于sql的规范问题

尽量把逗号放在前面,为什么?
方便排查,不会遗漏逗号
方便注释,可以单行直接注释,不用再改逗号
排版看起来更紧密,我用datagrip快捷键ctrl + alt+ L可以快速缩进
特别用datagrip进行快速复制一行的时候,只需要Ctrl + D不需要关注逗号，写起来很快

使用关键字大写,养成好习惯
参考 hive sql 官方conf地址:https://cwiki.apache.org/confluence/display/Hive/LanguageManual