hiveSql 百度面试题-连续签到领金币

最新推荐文章于 2024-07-04 18:05:03 发布
最新推荐文章于 2024-07-04 18:05:03 发布
阅读量698 收藏 4
点赞数 1
分类专栏： hive 文章标签： hive sql 大数据 数据仓库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/i_mycode/article/details/128568389
版权
文章详细分析了一道HiveSQL面试题，涉及用户每日签到获取金币的场景。用户签到数据按日期连续性分组，并使用窗口函数计算每个用户每日获取的金币数，包括基础金币和连续签到奖励。具体步骤包括构造标志字段、计算分组、分配行号以及应用签到规则计算金币。
摘要由CSDN通过智能技术生成

hiveSql 百度面试题-连续签到领金币

需求

用户在文章页可以每天签到，签到按照某种规则可获取金币，需统计每个用户每月获取金币数。

有用户签到明细表：

CREATE TABLE tmp_tb_user_log (
    uid INT COMMENT '用户ID',
    artical_id INT COMMENT '视频ID',
    in_time string COMMENT '进入时间',
    out_time string COMMENT '离开时间',
    sign_in int COMMENT '是否签到1是0否'
)

记录用户进入签到页，点击签到按钮后退出log数据。
部分表数据明细如下：

uidartical_idin_timeout_timesign_in
102022-07-07 10:00:002022-07-07 10:00:091
102022-07-08 10:00:002022-07-08 10:00:091
102022-07-09 10:00:002022-07-09 10:00:421
102022-07-10 10:00:002022-07-10 10:00:091
102022-07-11 23:59:552022-07-11 23:59:591
102022-07-12 10:00:282022-07-12 10:00:501
102022-07-13 10:00:282022-07-13 10:00:501
102022-07-14 11:00:282022-07-14 11:00:501
102022-07-15 11:59:282022-07-16 00:01:201

领取金币规则：

（1）只要用户签到就能获取一枚金币
（2）连续签到第三天和第七天分别可以在每天一枚的基础上额外再获取2枚和6金币
（3）连续签到7天后进行重置，按照规则（1）和（2）进行新一轮领币
（4）用户进入时间和离开时间如果跨天 按照进入时间计算打卡时间

上述举例明细中，用户id：1在2022-07-07到2022-07-15号连续签到，按照上述金币规则，每天的获取金币数据如下：

uidartical_idin_timeout_timesign_incoin
102022-07-07 10:00:002022-07-07 10:00:0911
102022-07-08 10:00:002022-07-08 10:00:0911
102022-07-09 10:00:002022-07-09 10:00:4213
102022-07-10 10:00:002022-07-10 10:00:0911
102022-07-11 23:59:552022-07-11 23:59:5911
102022-07-12 10:00:282022-07-12 10:00:5011
102022-07-13 10:00:282022-07-13 10:00:5017
102022-07-14 11:00:282022-07-14 11:00:5011
102022-07-15 11:59:282022-07-16 00:01:2011

用户id:1在2022-07-09是连续签到3天获得3金币，在2022-07-13连续签到7天，获得7金币。7月份共获得17金币。

分析

逆反思想思考，想要获取以上结果，得知道每个用户的有效签到连续日期分组，同一个用户可能有多个连续签到数据段，在每个数据段内需要row_number() 标行号，按照需求的金币规则计算每个连续签到数据段内的获取金币数。

  • 1.如果我们已经有如下数据：
uidartical_idin_timeout_timesign_inrank
102022-07-07 10:00:002022-07-07 10:00:0911
102022-07-08 10:00:002022-07-08 10:00:0912
102022-07-09 10:00:002022-07-09 10:00:4213
102022-07-10 10:00:002022-07-10 10:00:0914
102022-07-11 23:59:552022-07-11 23:59:5915
102022-07-12 10:00:282022-07-12 10:00:5016
102022-07-13 10:00:282022-07-13 10:00:5017
102022-07-14 11:00:282022-07-14 11:00:5018
102022-07-15 11:59:282022-07-16 00:01:2019

rank是每个用户每个连续签到数据段内按照in_time升序序号，如果有了这个序号，用这个序号对7取余后，处理余数为3和0（7天的整数，即每一个连续7天），其余都是1金币。就可以算出每个用户每天的获取金币数。

  • 2.如果想要得到步骤1的数据，前提是先将每个用户每次的联系签到日期分到同一组中，这里很容易想到利用重分组思想，在用户每次签到相对于前一天变化的日期数据处标记数据为1，连续签到数据标记为0，再sum() over() 按照签到时间累加，即将每个用户每次联系签到数据分到同一组。可见举例数据：

假设有数据如下：

uidartical_idin_timeout_timesign_in
102022-07-07 10:00:002022-07-07 10:00:091
102022-07-08 10:00:002022-07-08 10:00:091
102022-07-09 10:00:002022-07-09 10:00:421
102022-07-10 10:00:002022-07-10 10:00:090
102022-07-11 23:59:552022-07-11 23:59:591
102022-07-13 10:00:282022-07-13 10:00:501
102022-07-14 11:00:282022-07-14 11:00:501

用户在2022-07-10号进入签到页了 ，但是没有签到（sign_in为0），在2022-07-13直接没有来，即该用户应该有3段连续签到数据段。
分别是7号到9号，11号 和 13号到14号。分组应该是这样：

uidartical_idin_timeout_timesign_ingroup_name
102022-07-07 10:00:002022-07-07 10:00:0910
102022-07-08 10:00:002022-07-08 10:00:0910
102022-07-09 10:00:002022-07-09 10:00:4210
102022-07-10 10:00:002022-07-10 10:00:0901
102022-07-11 23:59:552022-07-11 23:59:5912
102022-07-13 10:00:282022-07-13 10:00:5013
102022-07-14 11:00:282022-07-14 11:00:5013

可以看到group_name字段一共有0到3 四个分组，但是组号1是没有签到的。如何实现这种分组？

  • 3.实现重分组，将每个用户的每段连续签到日期分到同一组。
    构造每次签到相对于前一天变化的日期数据标记为1，连续日期标记为0，再sum() over()开窗累加即可。可见数据：
uidartical_idin_timeout_timesign_insum_over
102022-07-07 10:00:002022-07-07 10:00:0910
102022-07-08 10:00:002022-07-08 10:00:0910
102022-07-09 10:00:002022-07-09 10:00:4210
102022-07-10 10:00:002022-07-10 10:00:0901
102022-07-11 23:59:552022-07-11 23:59:5911
102022-07-13 10:00:282022-07-13 10:00:5011
102022-07-14 11:00:282022-07-14 11:00:5010

sum_over字段：

  • 2022-07-10号相对于2022-07-09号是变化的（sign_in变为0），标记为1；

  • 2022-07-11号相对于2022-07-10号是变化的（sign_in变为1），标记为1；

  • 2022-07-13号相对于2022-07-11号是变化的（日期不连续），标记为1；
    有sum_over字段，sum(sum_over) over(partition by uid order by in_time) 即得到步骤1中的group_name。
    那构造sum_over需要注意什么？再往上一步想。

  • 4.这一步就是具体的分析什么是相对于前一条数据是否变化
    首先要明确，断签的条件有两个：
    1、没来，数据表现为没有当天数据
    2、来了没签到，数据表现为有当天数据，但是sign_in是0
    所以要标注这两个条件以明确步骤3中的sum_over到底应该是1还是0

相对于前一条数据，很容易想到开窗函数lead() over() 或者 lag() over()。不清楚这两个开窗函数用法的同学可以查看俺写的另一篇介绍开窗函数的文章开窗函数，很nice的。

if(datediff(cast(in_time as datetime), cast(lag(in_time,1,in_time) over(partition by uid order by in_time) as datetime),'dd') > 1, 1, 0) as flag_days,
if(lag(sign_in,1,sign_in) over(partition by uid order by in_time) != sign_in, 1, 0) as sign_flag

flag_days：当前行减去上一行日期取天数差，如果差值大于1 ，标记为1，否则标记为0。这里理解为当前数据相对于上一条数据是连续日期为0，否则为1
sign_flag：当前行sign_in与上一行sign_in不一样则标记为1，否则标记为0。这里理解为从签到到未签到 和 从未签到到签到都被标记为1，其他标记为0。

有了上述flag_days 和 sign_flag。步骤3中的sum_over，很容易理解是当flag_days 和sign_flag都是0则sum_over标记为0，否则是1，怎么理解？
flag_days = 0 ：当前行与上一行日期连续
sign_flag = 0 ：当前行与上一行sign_in值没有变化（这里先不管是不是签到了，等于1，因为连续的没签到也是单独分为一组，不会和签到的分到同一组）
所以有变化的数据行（日期断连 或者 sign_in变化）被标记为1，其他为0。得到sum_over。

实现

1、准备数据


DROP TABLE IF EXISTS tmp_tb_user_log;
CREATE TABLE tmp_tb_user_log (
    uid INT COMMENT '用户ID',
    artical_id INT COMMENT '视频ID',
    in_time string COMMENT '进入时间',
    out_time string COMMENT '离开时间',
    sign_in int COMMENT '是否签到'
) lifecycle 3;
 
INSERT INTO tmp_tb_user_log(uid, artical_id, in_time, out_time, sign_in) VALUES
  (1, 0, '2022-07-07 10:00:00', '2022-07-07 10:00:09', 1),
  (1, 0, '2022-07-08 10:00:00', '2022-07-08 10:00:09', 1),
  (1, 0, '2022-07-09 10:00:00', '2022-07-09 10:00:42', 1),
  (1, 0, '2022-07-10 10:00:00', '2022-07-10 10:00:09', 1),
  (1, 0, '2022-07-11 23:59:55', '2022-07-11 23:59:59', 1),
  (1, 0, '2022-07-12 10:00:28', '2022-07-12 10:00:50', 1),
  (1, 0, '2022-07-13 10:00:28', '2022-07-13 10:00:50', 1),
  (1, 0, '2022-07-14 11:00:28', '2022-07-14 11:00:50', 1),
  (1, 0, '2022-07-15 11:59:28', '2022-07-16 00:01:20', 1),
  (2, 0, '2022-10-01 10:00:28', '2022-10-01 10:00:50', 1),
  (2, 0, '2022-10-02 10:00:01', '2022-10-02 10:01:50', 1),
  (2, 0, '2022-10-03 11:00:55', '2022-10-03 11:00:59', 1),
  (2, 0, '2022-10-04 11:00:45', '2022-10-04 11:00:55', 0),
  (2, 0, '2022-10-05 11:00:53', '2022-10-05 11:00:59', 1),
  (2, 0, '2022-10-06 11:00:45', '2022-10-06 11:00:55', 1),
  (3, 0, '2022-07-07 10:00:00', '2022-07-07 10:00:09', 1),
  (3, 0, '2022-07-08 10:00:00', '2022-07-08 10:00:09', 1),
  (3, 0, '2022-07-09 10:00:00', '2022-07-09 10:00:42', 1),
  (3, 0, '2022-07-11 23:59:55', '2022-07-11 23:59:59', 1),
  (3, 0, '2022-07-12 10:00:28', '2022-07-12 10:00:50', 1),
  (3, 0, '2022-07-13 10:00:28', '2022-07-13 10:00:50', 1),
  (3, 0, '2022-07-14 11:00:28', '2022-07-14 11:00:50', 1),
  (3, 0, '2022-07-15 11:59:28', '2022-07-16 00:01:20', 1),
  (4, 0, '2022-07-07 10:00:00', '2022-07-07 10:00:09', 1),
  (4, 0, '2022-07-08 10:00:00', '2022-07-08 10:00:09', 1),
  (4, 0, '2022-07-09 10:00:00', '2022-07-09 10:00:42', 1),
  (4, 0, '2022-07-10 10:00:00', '2022-07-10 10:00:09', 1),
  (4, 0, '2022-07-11 23:59:55', '2022-07-11 23:59:59', 1),
  (4, 0, '2022-07-12 10:00:28', '2022-07-12 10:00:50', 0),
  (4, 0, '2022-07-13 10:00:28', '2022-07-13 10:00:50', 1),
  (4, 0, '2022-07-14 11:00:28', '2022-07-14 11:00:50', 1),
  (4, 0, '2022-07-15 11:59:28', '2022-07-16 00:01:20', 1);

select * from tmp_tb_user_log;

2、构造flag_days 和 sign_flag

select 
   *,
    if(datediff(cast(in_time as datetime), cast(lag(in_time,1,in_time) over(partition by uid order by in_time) as datetime),'dd') > 1, 1, 0) as flag_days,
    if(lag(sign_in,1,sign_in) over(partition by uid order by in_time) != sign_in, 1, 0) as sign_flag
from tmp_tb_user_log

3、构造sum_over 和 group_name

select 
   t.uid,
    t.artical_id,
    t.in_time,
    t.out_time,
    t.sign_in,
    sum(if(flag_days = 0 and sign_flag = 0,0,1)) over(partition by uid order by in_time) as group_name
from
    (select 
        *,
        if(datediff(cast(in_time as datetime), cast(lag(in_time,1,in_time) over(partition by uid order by in_time) as datetime),'dd') > 1, 1, 0) as flag_days,
        if(lag(sign_in,1,sign_in) over(partition by uid order by in_time) != sign_in, 1, 0) as sign_flag
    from tmp_tb_user_log
    ) t

4、构造irank

select 
    t1.uid,
    t1.artical_id,
    t1.in_time,
    t1.out_time,
    t1.sign_in,
    t1.group_name,
    row_number() over(partition by t1.uid, t1.group_name order by t1.in_time) as irank
from
    (select 
        t.uid,
        t.artical_id,
        t.in_time,
        t.out_time,
        t.sign_in,
        sum(if(flag_days = 0 and sign_flag = 0,0,1)) over(partition by uid order by in_time) as group_name
    from
        (select 
            *,
            if(datediff(cast(in_time as datetime), cast(lag(in_time,1,in_time) over(partition by uid order by in_time) as datetime),'dd') > 1, 1, 0) as flag_days,
            if(lag(sign_in,1,sign_in) over(partition by uid order by in_time) != sign_in, 1, 0) as sign_flag
        from tmp_tb_user_log
        ) t
    ) t1

5、处理金币获取规则

select 
    t2.uid,
    t2.artical_id,
    t2.in_time,
    t2.out_time,
    t2.sign_in,
    t2.group_name,
    t2.irank,
    case when t2.irank % 7 = 3 then 3 when t2.irank % 7 = 0 then 7 else 1 end as coin
from
    (select 
        t1.uid,
        t1.artical_id,
        t1.in_time,
        t1.out_time,
        t1.sign_in,
        t1.group_name,
        row_number() over(partition by t1.uid, t1.group_name order by t1.in_time) as irank
    from
        (select 
            t.uid,
            t.artical_id,
            t.in_time,
            t.out_time,
            t.sign_in,
            sum(if(flag_days = 0 and sign_flag = 0,0,1)) over(partition by uid order by in_time) as group_name
        from
            (select 
                *,
                if(datediff(cast(in_time as datetime), cast(lag(in_time,1,in_time) over(partition by uid order by in_time) as datetime),'dd') > 1, 1, 0) as flag_days,
                if(lag(sign_in,1,sign_in) over(partition by uid order by in_time) != sign_in, 1, 0) as sign_flag
            from tmp_tb_user_log
            ) t
        ) t1
    ) t2

6、分组计算每个用户每个月获取金币数

select 
    t3.uid
    ,substr(t3.in_time,1,7) as pmonth
    ,sum(if(t3.sign_in = 1,coin,0)) as coins
from
    (select 
        t2.uid,
        t2.artical_id,
        t2.in_time,
        t2.out_time,
        t2.sign_in,
        t2.group_name,
        t2.irank,
        case when t2.irank % 7 = 3 then 3 when t2.irank % 7 = 0 then 7 else 1 end as coin
    from
        (select 
            t1.uid,
            t1.artical_id,
            t1.in_time,
            t1.out_time,
            t1.sign_in,
            t1.group_name,
            row_number() over(partition by t1.uid, t1.group_name order by t1.in_time) as irank
        from
            (select 
                t.uid,
                t.artical_id,
                t.in_time,
                t.out_time,
                t.sign_in,
                sum(if(flag_days = 0 and sign_flag = 0,0,1)) over(partition by uid order by in_time) as group_name
            from
                (select 
                    *,
                    if(datediff(cast(in_time as datetime), cast(lag(in_time,1,in_time) over(partition by uid order by in_time) as datetime),'dd') > 1, 1, 0) as flag_days,
                    if(lag(sign_in,1,sign_in) over(partition by uid order by in_time) != sign_in, 1, 0) as sign_flag
                from tmp_tb_user_log
                ) t
            ) t1
        ) t2
    ) t3
group by t3.uid,substr(t3.in_time,1,7);

最后

喜欢的点赞、关注、收藏吧~ 你的支持是最大的创作动力~~

@nanami
关注
专栏目录
牛客-SQL篇-SQL大厂面试真题-02用户增长场景（某度信息流）-用户增长场景（某都信息流）-SQL167连续签到领金币
SunshineLu2019的博客
11-29 939
（uid-用户ID, artical_id-文章ID, in_time-进入时间, out_time-离开时间, sign_in-是否签到）：计算每个用户2021年7月以来每月获得的金币数（该活动到10月底结束，11月1日开始的签到不再获得金币）。：如果签到记录的in_time-进入时间和out_time-离开时间跨天了，也只记作in_time对应的日期签到了。artical_id INT NOT NULL COMMENT '视频ID',in_time datetime COMMENT '进入时间',
HiveSql面试题：连续签到领金币问题【百度-困难题-通用解法】
石榴姐yyds
05-04 2530
本文给出了一种连续签到领取金币的一种通用解法，通过窗口函数生成标签值进行辅助计算
SQL学习（11）：连续签到领金币问题
UoB1887858的博客
09-06 689
前言：本题目来自于海*金融二面前的小测试题目，也是非常经典的高难度题目【牛客应该能够找到】。 主要难点在于如何对于多种规则进行划分标签和计算。 本文提供一种较易理解的思路和方法，其余思路可以在CSDN找到
SQL面试题练习 —— 连续签到领金币
最新发布
hu_wei123的博客
07-04 436
题目来源：百度。
大数据SQL题38 连续签到领金币数
qq_35237689的博客
06-05 421
首先同样找出每个用户的所有连续登录区间，随后对区间内的每一天，求出当天可以获得的金币数量，最后求和。用户每天签到可以领1金币，并可以累计签到天数，连续签到的第3、7天分别可以额外领2和6金币。从用户登录明细表中求出每个用户金币总数，并按照金币总数倒序排序。每连续签到7天重新累积签到天数。
sql面试题 连续签到领金币（连续登录问题）
麻辣清汤的博客
03-26 1386
牛客面试题 问题：计算每个用户2021年7月以来每月获得的金币数 问题一：连续登录问题 select uid,date(in_time) as dt, row_number() over(partition by uid order by date(in_time)) 编号, date(in_time)-(row_number() over(partition by uid order by date(in_time))) dt2 from tb_user_log where
hive sql实战案例-最高连续签到天数
jianai858的博客
05-10 805
求每个用户的最高连续签到天数
牛客网 SQL大厂面试真题篇SQL12 连续签到领金币（困难类题目）
m0_46235332的博客
04-24 798
描述 用户行为日志表tb_user_log id uid artical_id in_time out_time sign_in 1 101 0 2021-07-07 10:00:00 2021-07-07 10:00:09 1 2 101 0 2021-07-08 10:00:00 2021-07-08 10:00:09 1 3 101 0 2021-07-09 10:00:00
HIVEsql-刷题40题
ABC15732186271的博客
01-10 857
hive刷题
SQL计算用户连续签到问题
roar_633的博客
08-05 4651
有一张用户签到表【t_user_attendence】，标记每天用户是否签到（说明：该表包含所有用户所有工作日的出勤记录） ，包含三个字段： 日期【fdate】 用户id【fuser_id】 用户当天是否签到【fis_sign_in：0否1是】 fdate fuser_id fis_sign_in 2021/6/1 1 1 2021/6/1 2 0 2021/6/1 3 0 2021/6/2 1 1 2021/6/2 2 1 2021/6/2 3 0 2021/6/3 1 1 2021/6/3 2 0 20
MYSQL实现连续签到功能断签一天从头开始(sql语句)
12-16
1，创建测试表 CREATE TABLE `testsign` ( `userid` int(5) DEFAULT NULL, `username` varchar(20) DEFAULT NULL, `signtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `type` int(1) DEFAULT '0' COMMENT '为0表示签到数据，1表示签到日期字典数据' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 2，插入测试数据，签到时间为
hive sql 怎么实现循环_Hive_sql如何计算连续签到天数？
weixin_39928017的博客
12-20 434
首先膜拜下二楼的大神，这个问题我困扰了很久，终于看到答案，我把 @世界第一辣鸡 的过程用HQL给写一遍。大概思路如下：1.根据uid进行分组，将所有签到记录依次按照时间排序并标记序号2.根据时间有序的特点，可以将所有时间减去序号，获取连续签到日期唯一的时间点，记为 data_rank。如 2017-02-09 序号12017-02-10 序号22017-02-11 序号 3减去序号可得唯一...
SQL面试题：连续登录获取奖励
话数Science
09-28 253
【代码】SQL面试题：连续登录获取奖励。
SQL-计算连续签到天数
weixin_47198715的博客
05-24 1712
计算连续签到天数，需要用到窗口函数，日期计算知识点
sql统计连续签到或连续矿工
kane245的专栏
12-31 1153
1
HiveSQL找出连续日期及连续的天数(方法二)
卡卡大叔
05-27 2939
经常遇到统计连续行为日期及连续天数的问题，之前的解决方案相对复杂，本次给出最简单的解决方案。 1、测试数据： use xxx; drop table test_serialdate; create table if not exists test_serialdate ( user_id string comment '用户ID', sign_date string comment '签到日期' ) stored as orcfile ; insert into table test
msql计算连续签到天数
zlfjavahome的专栏
11-16 528
MySQL
sql文连续签到领金币
03-31
抱歉，我是AI语言模型，没有具体的数据库可以访问，无法提供完整的SQL语句。以下是一些可能的思路： 1. 创建一个表来存储用户的签到记录，包括用户ID、日期和是否签到。例如： CREATE TABLE user_signin ( user_id INT, signin_date DATE, is_signed_in BOOLEAN ); 2. 当用户进行签到时，插入一条记录到表中。例如： INSERT INTO user_signin (user_id, signin_date, is_signed_in) VALUES (1, '2021-07-01', TRUE); 3. 使用SQL查询来检查用户的连续签到天数，并根据规则发放金币。例如： SELECT user_id, COUNT(*) as days_signed_in FROM user_signin WHERE is_signed_in = TRUE GROUP BY user_id HAVING days_signed_in >= 7; 4. 可以将上述查询与更新语句结合使用，将奖励金币直接添加到用户账户中。例如： UPDATE user SET coins = coins + 100 WHERE user_id IN ( SELECT user_id FROM user_signin WHERE is_signed_in = TRUE GROUP BY user_id HAVING COUNT(*) >= 7 );
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

@nanami

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值