首页
学习
活动
专区
圈层
工具
发布

利用Python统计连续登录N天或以上用户

在有些时候,我们需要统计连续登录N天或以上用户,这里采用python通过分组排序、分组计数等步骤实现该功能,具体如下: 导入需要的库 import pandas as pd import numpy as...pd.to_datetime(df["@timestamp"]) #将日期列转化为 时间格式 第三步,分组排序 分组排序是指将每个用户登录日期进行组内排序 采用groupby方法结合rank方法进行处理...='d') #计算登录日期与组内排序的差值(是一个日期) ?...第六步,计算每个用户连续登录最大天数 这里用到的是sort_values和first方法,对每个用户连续登录天数做组内排序(降序),再取第一个值即为该用户连续登录最大天数 data = data.sort_values...补充 当我们计算出每个用户在周期内的每个连续登录天数后,想计算连续登录N天或以上玩家清单就非常方便了,条件筛选即可。 同时,也可以自由计算连续登录最大天数 各玩家数等等。

4.5K30

大数据学习之数据仓库代码题总结上

6个 窗口函数 函数功能说明 ROW_NUMBER() 根据具体的分组和排序,为每行数据生成一个起始值等于1的唯一序列数 RANK() 对组中的数据进行排名,如果名次相同,则排名也相同,但是下一个名次的排名序号会出现不连续...FIRST_VALUE 取分组内排序后,截止到当前行,第一个值 LAST_VALUE 取分组内排序后,截止到当前行,最后一个值 二、笔试题考查 2.1、复购/留存率统计问题 新增用户留存率=新增用户中登录用户数.../新增用户数×100%(统计周期为天)。...留存率的计算方式是在注册后的特定天数内继续学习的用户数除以当天注册的用户总数。结果应包含日期、留存天数和留存率。...现在的任务是计算每个品牌的总的打折销售天数。然而,由于数据中存在时间交叠的情况,我们需要合理处理这种情况,避免重复计算销售天数。因此,你需要设计一条 SQL 查询来解决这个问题。

96310
  • 您找到你想要的搜索结果了吗?
    是的
    没有找到

    SQL 游戏道具连续购买天数:ROW_NUMBER差值法分组求连续(腾讯面试题)

    解题步骤: 按 (user_id, item_id) 分组,按 purchase_date 排序,计算行号 rn; 使用 date_sub(purchase_date, rn) 计算分组标识——连续的日期会得到相同的基准日期...; 按 (user_id, item_id, 基准日期) 分组,统计每段连续天数、开始日期、结束日期; 按 (user_id, item_id) 取最大连续天数; 维度 评分 题目难度 ⭐️⭐️⭐️⭐️...按用户+道具分组,计算行号和基准日期 执行SQL select user_id, item_id, purchase_date, amount,...按用户+道具+基准日期分组,统计每段连续天数 执行SQL select user_id, item_id, group_base, min(purchase_date...五、常见坑点 坑1:多维度PARTITION BY的正确写法 PARTITION BY user_id, item_id 同时按用户和道具分组,每个用户每个道具独立排序。

    26210

    【学习】用R和集算器计算连续上涨5天的股票

    思路:导入数据;过滤出上个月的数据;按照股票代码分组;将数据按日期排序;计算出每天比上一天的收盘价的增长额;计算出连续正增长的天数;过滤出正增长天数大于等于5的那些股票。   ...A3:=A2.group(Code),按股票代码分组。这和R语言中的split函数功能类似。点击该单元格可以在右边看到计算结果: ?   每行是一个分组,点击其中一行,可以看到分组内的数据: ?...要注意的是R对Excel的支持第三方的包l,需要先行下载安装,R对第三方包比较挑剔,找到一个兼容可能的不大容易   02:导入Excel的数据。...06-22:由于R不能像集算器那样方便的用~来操作每个分组,因此这里需要一个大循环,每次循环针对一个股票进行计算。   07:按日期排序。...分组前就排序代码会更简单,但我考虑到最直观的思路是:分组前数据是混在一起的,看不出是否排序;分组后经过观察才发现顺序混乱,不排序则无法进行下一步的行间运算。

    2.1K90

    SQL 每个用户最长连续活跃天数:连续问题经典解法(腾讯面试题)

    核心思路: 按用户分组,按活跃日期排序,生成行号 row_num 使用 date_sub(active_date, row_num) 计算差值 —— 连续的日期减去连续递增的行号,得到的日期是相同的 按用户和差值分组...按用户分组,按活跃日期排序生成行号 执行SQL select user_id, active_date, row_number() over (partition by user_id...按用户和group_date分组,统计每段连续天数,取最大值 执行SQL select user_id, max(consecutive_days) as max_consecutive_days..."断签"天数——相邻两段之间隔了多少天 按周/月维度:用 date_trunc('week', active_date) 聚合后统计"连续活跃的周数",而非连续天数 七、知识点总结 考点 说明 ROW_NUMBER...+ date_sub 差值法 连续日期的分组标识:差值相同=同一连续段 GROUP BY user_id, group_date 按用户+分组标识统计每段连续天数 MAX + GROUP BY user_id

    34110

    电商项目分析用户购买行为案例一

    这里是基于hive on spark来对数据进行分析的,所以使用sql进行讲解 使用sql求用户连续购买天数以及与上次购买间隔天数,按照下面步骤进行处理 对数据进行聚合/去重 对用户进行分组排序 日期与序号进行减法运算...获取开始连续的日期以及连续天数 使用Hive中lead函数 根据业务需求不一样,可能名称也不一样,主要是理解思想。...对数据进行聚合/去重 第一步是对数据按天进行初步聚合(因为一个用户可能在某天有多次购买行为) 因为只是作为演示,所以只用单个用户进行 select member_id,order_date from...TABLE_NAME where member_id='1690' group by member_id,order_date 结果如下: 对用户进行分组排序 我们把上一个结果表称为: TABLE...思路: 如果我的日期是连续的.这时日期减去序号天数,那么连续的日期会得到同一个日期,我们暂且称之为起始日期。

    2K41

    一文搞懂连续问题

    计算连续分组赋值相同的分组ID判断连续条件,有两种处理思路:1.双排序差值法;2.累积求和法;1.双排序差值法双排序差值法是指对一列连续数据得到排序1,对符合条件数据进行排序的到排序2,两列排序差值作为分组...(具体是什么日期无所谓,只是以一个固定日期为锚点)的差得到排序值1(date_diff),然后使用row_number()函数根据用户分组,按照登陆日期进行排序得到排序值2(row_num),然后用两个排序值做差...腾讯大数据面试SQL-连续登陆超过N天的用户该题目是在得到连续分组ID 之后,增加了聚合逻辑的考察,count()统计连续登陆天数,然后对统计结果进行筛选;3....然后是在得到连续分组ID 之后,count()统计连续天数,并对统计结果进行筛选。2....在得到连续分组ID之后 需要计算出连续登陆的最早和最晚日期,然后差值计算,还需要考虑到差值与登陆天数差天的细节。总结通过以上面试题目可以看出,只要找到连续分组ID,所以的题目都可以迎刃而解。

    2K00

    字节跳动大数据面试SQL-最大连续登录天数

    三、思路分析 核心是日期 - ROW_NUMBER 差值法: 按 user_id 分区,按 login_date 排序,生成 ROW_NUMBER 计算 login_date - ROW_NUMBER,...连续的日期会得到相同的差值 按差值分组,COUNT 求每组连续天数,MAX 取最大值 原理图解: 用户1 的日期: 01-01 → ROW_NUMBER=1 → 差值=01-01 - 1 = 12-31...ROW_NUMBER 这次咱们直接使用减完的日期作为分组字段,之前都是使用差值作为分组字段,其实差值用什么都可以,序号、差值、连续的开始日期等等,只要保证用户内不重复就可以。...---------+-------------+-------------+ 12 rows selected (0.409 seconds)(https://www.dwsql.com) 步骤3:按差值分组...),再套用差值法 当前连续天数:不只是历史最大,还要知道"截止今天正在连续的最近一段有多少天" 七、知识点总结 考点 说明 ROW_NUMBER + PARTITION BY 按用户分区编号 日期 -

    39510

    基于时间维度水平拆分的多 TiDB 集群统一数据路由联邦查询技术的实践

    、月度/年度收支统计等,需根据查询时间范围确定涉及的集群范围,并将多个集群的查询结果在组件的结果集归并模块中按分组条件进行汇总归并;轮询路由-追加/汇总归并:对应单笔/多笔查询、修改场景,由于输入参数能够定位数据的只有非交易时间类字段...、日期类型(相对 or 绝对)、基于日期排序的集群顺序(为了按日期正序或倒序遍历)、版本信息等。...表结构如代码 1 所示;路由配置:主要配置项包括每个集群的上下界的名义日期、日期类型(相对 or 绝对)、基于日期排序的集群顺序(为了按日期正序或倒序遍历)、版本信息等。...最后会按集群维度将结果集追加合并;结果集归并:分页查询场景:对于按交易日期排序的简单场景,只需将多个集群的结果集按顺序追加归并即可。...对于非交易日期排序的复杂场景,则需要根据排序字段、各字段的正序/倒序规则,对集群内局部有序的所有结果集进行整体重排序,算法模型采用的是稳定性较好的插入排序;聚合查询:对于可以汇总归并的算子,如 sum、

    1.5K10

    5 步搞定 4 层嵌套 SQL 才能算清的股票连涨天数

    要算某支股票历史上收盘价连续上涨的最长天数。逻辑很简单:按日期排序,依次判断每一天是否比前一天上涨,把连续上涨的区间标出来,数每个区间有多长,取最大值。...第 2 层:按这个累加值分组,count(*) 数每个区间有多少天。第 1 层:取最大值。费了好大劲算是读懂了,似乎原理也明白了。但下次需求变了呢?...再比如:“不仅要算最长连涨天数,还要知道最长连涨发生在什么时候”,最外层只有一个 max,拿不到起止日期。你得从最内层开始把日期字段一层层往外传,整个查询重写一遍。典型的一看就会,一做就废。...filter:筛选目标股票sort:按日期排序segment:按条件分段——CL down 的意思是“如果收盘价下跌,就新开一组”。...把 partition CODE 加在需要按股票分组的操作上即可:仍然是顺序读下来的 5 步:sort:按股票代码和日期排序segment partition CODE:对每支股票分别按下跌分段summarize

    29320

    『数据分析』pandas计算连续行为天数的几种思路

    这里我们用北京空气质量数据作为案例进行演示,需求是找出北京空气质量连续污染最长持续多久并确定其周期。 ?...思路1:按时间排序求差值再分组计数 才哥上次的解法就是这种思路,回看当初的代码显得比较稚嫩,今天我们看看小明哥的解法,非常精彩。...图4:筛选空气质量污染的数据 步骤2:新增辅助列(辅助列可以不用加到原数据t上) 这里的逻辑大概如下: 辅助排名列(按照时间顺序排序)为间隔天数 然后用时间字段(time)与间隔天数求差值得到一个日期...图5:辅助列 步骤3:分组计数获得连续天数,分组求最小最大值获得连续 污染起止日期 t.groupby(groupids).agg({ 'time': lambda x:f'{x.min()}~...图7:辅助列值预览 我们可以发现,按照辅助列分组进行计数即可获得连续污染天数,如上红色标记区域。

    8.9K11

    AI 编写可信 SQL,审计成本直降 90%

    DT 升序分段 CL 变小 命名 不涨标记汇总 DT 计数 命名 连涨天数; 分组 不涨标记汇总 连涨天数 最大 命名 最长上涨天数每一步只做一件事:筛选、排序、分段、聚合…… 逻辑线性展开,像读业务操作清单一样清晰...点击任意步骤,就能看到对应输出的完整数据,过滤条件对不对、分组计算准不准,当场就能验证。对审计而言,这意味着不再是 “结果对不对全靠跑”,而是可以逐步骤追溯逻辑、逐环节校验数据。...三、一套逻辑,多库编译:跨库适配不再重复审计AI 训练数据对主流数据库覆盖多,对小众数据库、国产数据库支持弱,生成的代码经常要手工改;同一份业务逻辑要适配多个数据库,就得写 N 个版本、做 N 次审计,...订单金额 命名 月销售额 分组 ymonth(订单日期) 命名 年月分段 月销售额 未变大 命名 段号汇总 计数 段号 命名 连续上涨月数 分组 段号汇总 最大 连续上涨月数 命名 最长连续上涨月数最后编译成...多供应商订单:匹配 关联表 唯一延迟供应商订单延迟明细:匹配 关联表 候选订单 筛选 (国家名称 = "SAUDI ARABIA")汇总 计数 订单ID 命名 等待次数 分组 供应商名称排序 等待次数

    12920

    腾讯面试题:你今天上班打卡了吗?

    image.png 问题:计算截至当前每个用户已经连续签到的天数(输出表仅包含当天签到的所有用户,计算其连续签到天数) 【解题步骤】 1.分组排序 先看问题,要求输出两个字段:当天签到的用户id和连续签到的天数...翻译成大白话就是,找出当天签到的用户,和每个用户最近一次未签到的日期,计算从上一次未签到日期到当天的间隔天数(这段时间内,该用户每天签到)。...该问题是分组排序问题,这类问题要想到《猴子 从零学会SQL》里讲过的窗口函数。 先按用户id分组,找出每个用户id当天未签到的日期,再按日期降序排序。...我们可以查询用户签到表的开始日期,将那天作为该用户开始签到的日期,计算该日期和当天的间隔,然后加1,即为该用户的连续签到天数。...对应SQL如下: image.png  查询结果: image.png 【本题考点】 遇到要取出每个分组(用户/部门/月份)中,某个字段的值最高/最低/处于第n个的记录,也就是分组排序问题,要想到用窗口函数

    1.1K00

    MySQL--查询和常用函数(知识点)

    1.查询 1.1 查询语法: select 显示的字段列表 from 表名 where 条件 GROUP BY 分组 having 条件 limit 开始记录,条数 order by 排序字段...10.1.7 常量列 select '常量值' as 列名 from 表名; select * ,'重庆市渝北区人和' as 详细地址 from student 1.2 查询中的各种子句 1) 排序...语法: select * from 表名 order by 排序字段 asc|desc 案例: select * from student order by bormDate desc 2) 分页...group by, 如果要和其他字段一起显示需要进行分组,表示对不同的数据进行分组后的一个统计操作 按科目进行分组统计 select subjectNo,sum(studentResult) from...1,日期2) 计算两个日期之间的天数 adddate(日期,天数) 为指定的日期添加天数 select datediff(now(),'1989-09-07') select adddate(now

    92520

    MySQL之数据库基本查询语句

    (order by) #Article表按aid从低到高查询作者姓名和文章类别 select aid,author,type from Article order by aid; 对多个查询列进行排序(...order by a,b:a排序的基础上,b再排序): #Article表按aid和粉丝数从低到高查询作者姓名和文章类别 select aid,author,type,fans from Article...; 对多个列降序排列 #按fans降序排列Article信息,再对articles排序 select * from Article order by fans desc,articles; #以上仅对fans...select * from Article order by convert(type using gbk); SELECT grop by子句 对条件进行分组排序 #分别统计coco和vivi的文章数...()); #获取给定日期的天数——获取当前系统时间的天数 select day(CURDATE()); #获取当前时间的前一天 select date_add(CURDATE(),INTERVAL

    6.9K40

    3小时吃掉HiveSQL:从基础语法到性能优化

    • 8,ORDER BY: 对记录进行排序。 • 9,LIMIT: 限制返回的记录数。...• ORDER BY:指定排序列,对每个分组内的数据进行排序。 • ROWS/RANGE BETWEEN:指定窗口范围,定义窗口的大小。 其中ROWS BETWEEN是根据物理行来确定窗口范围。...日期加上天数 • DATE_SUB(date, days) - 日期减去天数 • DATEDIFF(end_date, start_date) - 日期之间的天数 下面是是范例代码 SELECT...MapJoin的 实现方式是将小表广播到所有Map任务的内存中,大表的数据按分片分配到各个Map 任务,逐行读取并与内存中的小表哈希表进行匹配实现join。...在reducer内部排序,局部排序, 不保证全局有序,较快。 • distributed by: 控制数据按哪些key分发。本身无排序。 • clustered by: 按同一列分发并排序。

    2.3K12

    用 WorkBuddy 搭建捻股整卷率每日自动统计 + 定时邮件推送,彻底告别手工拉表

    一个传统制造业质量岗位的真实案例:每天上午 11:00,一份按设备×供应商分组、带红色预警和格式化样式的整卷率日报自动生成并发进邮箱——全程零人工。...二、最终效果每天上午 11:00 自动完成:读取产量表源文件的最新明细数据按「设备品牌归一 → 供应商 → 工单号」分组汇总,计算各组合的整卷率按既定格式生成日报 Excel(小计行绿色、≤75% 红色预警...我没有说"帮我统计一下整卷率",而是把口径一次讲死:帮我做一个每日统计:从源文件「产量表」sheet 读取当天数据,按设备×供应商分组计算整卷率。...开头的机台统一叫 HAMANA,S 开头的统一叫 S300,归一后相同组合必须合并重算;只保留 CS / IS / OS 三个工段;整卷率 = (∑O 实际卷数 − ∑Z 料) / ∑O 实际卷数;排序...经验:需求一次说清楚,比来回追问省 3 倍积分(WorkBuddy 按任务消耗积分,每次"重新生成"都是钱)。

    24510

    hive sql(六)—— 每个用户连续登录最大天数

    by '\t' ; 数据 #这里将数据分开,便于直观看到连续登录天数 insert into table login values (1,"2019-07-26"), (1,"2019-07-27")...Spent: 7 seconds 320 msec OK t3.id _c1 1 3 2 3 Time taken: 38.097 seconds, Fetched: 2 row(s) 分析 1、通过对需求理解发现...,首先需要对用户id开窗 2、连续登录,所以时间信息,并按照升序,需要在窗口里面添加order by 3、核心逻辑——连续登录的判断是,通过排序添加序号,再用当前日期和当前序号做差, 如果得到日期相同,...则表示是连续日期,所以使用row_number, 4、整体的逻辑顺序是先排序添加序号字段、计算差值日期、统计差值日期相同数量、最后得出每个用户差值日期数最多即需求 扩展 1、这里t1,t2可以合并为一步...,减少一次子查询 2、第一次分组是每个用户每天只有一条数据,第二次分组是统计差值日期相同数量,第三次分组是统计每个用户最大连续登录天数 知识点 1、row_number添加序号,无论字段值是否相同 2、

    3.9K40

    抖音面试题:遇到连续问题怎么办?

    可以用分组汇总,也就是分组(group by 月,用户id),统计(对分组后每个组计数就是连续登陆的天数 count) 2....子查询 1)获取登陆日期的天,需要用到day()函数; 2)获取登录日期的月,需要用到month()函数; 3)获取每个月登陆顺序,这类问题属于“每个+排序”,要想到用《猴子 从零学会SQL》里讲过的知识...汇总分析 1)分组汇总:查询每个月,每个用户,每一次连续登陆的天数。...也就是分组(group by 月,用户id,标记),统计(对分组后每个组计数就是连续登陆的天数 count) 1 select 月,用户id,标记, 2        count(*) as 连续登陆天数...与原题的区别在于: 1)“连续2天”变成了“连续5天”:对最后的where条件进行修改; 2)查询“用户名单”变成了“用户数”:用group by按月分组,然后统计用户数和count(distinct

    1.7K00
    领券