手撕SQL

Veryperi
64 阅读34分钟

手撕SQL题

去重操作💗💗💗

💎1.找出每个用户购买前三的商品类目

分组排序，熟悉row_number开窗函数即可

得到每个用户消费金额最高的前 3 个类别。

👑最里层计算个目录的消费金额，中层分区排序，外层控制前三

💎2.订单转化归因分析

页面跳转题目

末次归因分析：看最后进入C页面进行下单的前一个页面是哪个页面

按照时间戳进行排序，构建对应的用户行为序列，最后找到包含 A->C 或者 B->C序列

concat_ws 是按指定分隔符（这里是 ->）拼接字符串的函数

instr("A->C", page_seq) 判断 page_seq 中是否包含子串 “A->C”

count(distinct uid) as cnt：统计每个 page_id 对应的不同用户数量，distinct uid 确保同一个用户多次符合条件时只计数一次。

distinct去重

👑最里层行转列分区排序，中层拼接，外层转换成A、B分组查询，使用case

💎3.反作弊检测

dtime timestamp

略微复杂一点的需求基本上都会涉及到开窗，我们仅需思考按照什么字段分组以及按照什么字段排序

本题的分组字段就需要进行二次加工，可以将两次访问记录小于1分钟的放进同一个组中，即打上同一个分组id

本题的排序字段很容易看出，就是dtime

弄清楚分组字段以及排序字段，本题就迎刃而解了！！！

lag(dtime, 1) over(partition by user_id order by dtime) last_dtime
  • timestampdiff(second, last_dtime, dtime)：计算 last_dtime 和 dtime 之间的秒数差。
 sum(flag) over(partition by user_id order by dtime) as gid

👑第一层获取上一个的时间，第二层flag判断是否时间超过60秒，第三层按会话分组，利用sum递增改变，构建gid列，来分组，第四层group用于uid和gid，取gid分组中小的

💎4.如何统计接口性能测试结果？

已知一张某页面所有后端接口的访问日志信息表，统计所有接口被持续访问成功的次数

ps：持续访问成功 = 连续两次访问成功

求解不同接口连续访问两次成功的次数，难点就在于如何判断连续两次访问均成功？

第一步，取当前行所在上一行的访问状态，如果均为1，则计数连续访问成功状态为1

第二步，按照不同接口分组，对连续访问成功状态进行累加

👑第一层获取上一个的状态，第二层用flag判断都为1，成功状态也为1，第三层，累加成功状态就是访问成功次数

💎5.优化7日留存率

问题：使用HiveSQL如何计算最近7日用户留存率（不允许使用JOIN）

留存率问题常见的解题思路是通过🍟自关联🍟计算的，但是本题不允许使用JOIN

首先对需求进行理解，用户7日留存就是指用户在第1日浏览后，第7日也进行浏览

然后我们将每个用户的浏览日期存到对应数组中

最后判断第1日和第7日是否同时出现在浏览日期数组中，出现即为7日留存

WITH tmp AS 这种写法被称为公共表表达式（Common Table Expression，CTE），tmp 是自定义的 CTE 名称。它允许你定义一个临时的结果集，这个结果集可以在后续的 SELECT、INSERT、UPDATE 或 DELETE 语句中被引用。

ROUND( ，2)：将留存率保留 2 位小数

date_add(reg_date, 6)日期增加

SELECT
  reg_date,  -- 按注册日分组，统计每日的留存率
  -- 计算 7 日留存率并保留 2 位小数
  ROUND(
    SUM(IF(array_contains(active_dates, date_add(reg_date, 6)), 1, 0)) 
    / COUNT(user_id), 
    2
  ) AS retention_rate
FROM tmp
GROUP BY reg_date

🍟1、获取首次登录日期，行转列获取日期数组

2、获取第7日的日期，判断这个日期是否在日期数组中，在的话设为1，不在设为0，使用sum获得在的个数，去除以总用户数，结果保存两位小数

💎6.新用户次日留存率

cast(login_dt as date) 中的 cast 是一个类型转换函数

问题：计算每日新用户登录的次日留存率

使用join操作

新用户可以通过求用户的最早登录时间

次日留存率需要判断一个用户第二天是否登录，因此涉及到自关联，如果同一个用户两次登录日期相差一天即为次日登录

注意：用户可能登录多次，需要对用户进行去重

🍟1、建立两个临时结果集，t1存首次登录时间，注意转换时间，且group by会自动进行去重操作，t2存所有登录时间，去重后的

2、算留存率，进行表关联，左连接保证所有首次登录时间都在，算首次登录时间和其他登录时间的差值，如果为1，那么保留用户id去加入count计算，注意去重，然后除以首次登录时间的所有的用户数目，计算留存率，group byt1.dt

💎7.计算微信运动步数好友排名

想要求出自己的运动步数在所有好友中的排名

第一：需要找出自己的所有好友【已知】，算排名要加上自己，第二：需要求出所有好友以及自己的运动步数

在拿到所有运动步数之后，可以通过对排名函数进行开窗即可拿到好友排名，获取自己对应的排名即可

👑第一层将自己也加入好友排名中，用union all，第二层左连接步数表，进行roe_number排序，第三层输出uid等于fid的那一行

“开窗” 的本质是在不改变数据行数的前提下，实现 “组内精细化分析”

row_number() 函数本身不需要需要传入任何参数，它的作用是单纯地为 “窗口内的行” 生成唯一序号

💎8.计算部门平均工资

题目需求部门平均工资，先删除每个部门的最高工资和最低工资，然后求工资的平均即可

每个部门的最高/最低工资如何计算呢？

  • 碰到局部求和/最大/最小/排名等问题，按照局部汇总key进行开窗求解

🍟第一层，加两个行rk1升序，rk2降序，第二层，求平均，在where rk1 !=1 和rk2!=1的情况下

💎9.分钟级直播在线人数

image-20250903163922668

本题的核心在于如何生成从最早的进入时间到最晚的离开时间之间的连续分钟数

如果在Hive环境中，我们可以用posexplode来生成连续序列，但是在MySQL环境中，是不支持该语法的

这时候有一个非常牛逼的语法就出现了——递归CTE（公共表表达式） ，可能你没有听说过，但你一定用过普通CTE，即with xxx as()

with recursive nums as (...)

on t1.time >= t2.in_time and t1.time < t2.out_time：匹配条件 —— 判断 “时间点 t1.time” 是否落在 “用户操作的时间区间（t2.in_time 到 t2.out_time）” 内。

count(1) 是用于统计记录行数的聚合函数，它的作用是计算查询结果集中满足条件的记录有多少行。

🍟1、递归CTE，生成连续分钟数

2、把时间表和人员表左连接，连接字段是，time落在区间内的行，按time分组，用count得到数量

相关题目：直播间最大在线人数

if(op_type = 1, 1, -1)

思路分析

  • 需求1：求直播间最大在线人数
    • 我们将用户出直播间的操作记为-1，进直播间的操作记为1，这样进进出出只要按照时间进行累加就可以求出每一个时刻直播间对应的在线人数，最后取最大值即可
  • 需求2：求直播间出现峰值后持续的时间
    • 仅需要找出在线人数等于峰值的时刻的后一个时刻即为峰值结束时间

💎10.口述 JOIN

image-20250903173919419

image-20250903174156381

image-20250903174220953

其实本题想要考察的就是LEFT JOIN的原理是什么？

  • 通俗来讲，将左表和右表的公共key连接起来输出（inner join），同时保留左表的所有无法匹配的key

本题另外一个迷惑点：表A有两个1，表B有两个1，join之后会输出几行？

  • 输出4行，🍟JOIN可以理解为将两个表进行笛卡尔积之后，取出key相同的记录🍟

笛卡尔积：组合所有可能

💎11.曝点转问题分析

仅需要求出曝光的次数、点击的次数以及支付的次数，即可求出曝光->点击的转化率，以及 点击->支付的妆化率

思考：这个思路是不是存在问题？会不会有一些异常情况呢？（评论区留言一起讨论）

🍟第一层，筛选20250101，第二层用count和case when 计数，分组，第三层除法，concat(数，%)

💎12.连续售出的商品

连续购买三次及以上的商品：即此次购买商品后，下一次购买仍然是该商品，下下次购买仍然是该商品，下下下次购买可以是该商品也可以不是（不考虑下下下次）

🍟第一层，加两行，就是下行和下下行，用开窗实现，第二层，查询条件是该列和下列下下列值相同

💎13.GMV同环比

总销售额是gmv

本题主要考察的是同比和环比的定义是什么？

同比：跟去年同一时间的GMV进行比较

环比：跟上一个相邻时间段的GMV进行比较（通常为上一个月、上一个季度）

就是差值/上一个的gmv*100+%

🍟第一层，用lag1和12求上一个的，第二层concat拼接%，差值除以上一个计算，where month = "202412"

💎14.计算裁员名单

注意： 就是看时间的格式是否能做加减法，不能做要转换形式

如果涉及分区里很多条数据需要分组，可以考虑使用sum来分组

time_to_sec(...)：把时间差（如 01:30:00）转成秒数（如 5400 秒）

image-20250903202636973

可以用 CAST：datetime或timestamp

date() 是一个日期提取函数，输入 '2024-12-13 08:30:00' → 输出 '2024-12-13'

思路分析

如果一个员工每天只有一次进入和出去的记录，那么员工时长就很容易计算了；但是员工可以进进出出很多次，如何计算总时长呢？

  • 我们先试想一下如何计算总的员工时长，是不是就累计每次员工进出的出去时间-进入时间就可以了？
  • 所以关键点在于把员工的每次进入和出去建立mapping关系
  • 只需要将两条记录放入同一个分组即可，🍟如何构建分组🍟是多数SQL题的难点
  • 我们可以通过sum(in_flag) over(partition by emp_id order by time)即可完成分组

🍟第一层，日期格式的转换完成，用sum分组完成，where date(time) = "2024-12-13"，第二层，时间减法，用time_to_sec转换成秒单位，用用户id和gid分组，第三层条件查询，一天时间和<8h，用用户id分组

💎15.司机好评率

不用分组但是注意乘客id的去重distinct

只要求出 每位司机的评价乘客总数 以及 每位司机的评价超过80分的乘客总数

case when,count,round,concat

💎16.商品最大降价幅度

注意： 时间格式如果排序得改cast

str_to_date(time, '%Y-%m-%d %H:%i')第一个大写

加最高价格的一列，加降价幅度一列

🍟第一层，加一列截止目前行的最高价格，用窗口函数实现，第二层用最高价格-当前价格，得到降价幅度，第三层从降价幅度里max得到最大降价幅度

💎17.最大流失天数

里用 date_format(login_dt, '%Y-%m-%d') as login_dt更合适（只需年月日），DATETIME/TIMESTAMP 会多存时分秒（默认 00:00:00）

首先理解需求，题目要求用户最大流失天数，很显然先计算用户流失天数再求最大值即可，那么流失天数是什么？

举个例子，一个用户11月1号第一次登录，11月10日第二次登录，那么就表示这个用户流失了9天

如何计算流失天数？

其实就是计算用户连续两次登录间隔的天数，即：按照用户id分组、登录日期排序，取上一次登录日期，然后和当前登录日期相减得到流失天数

🍟第一层，转换日期格式，第二层，和前一天求差值，注意前一天没有默认为该值，第三层，取最大差值

💎18.股票最大连续上涨天数

date_format(ds, '%Y-%m-%d') as dt

注意时间格式的转换

问题：计算每只股票收盘价持续上涨的最大天数

备注：当日最晚的交易时间即为收盘时间

时间格式转换

interval 是一个关键字，用于表示 “时间间隔”（如几天、几个月、几小时等），常与日期函数（如 date_add、date_sub）配合使用，实现对日期的增减操作。 基本语法 interval 的使用格式通常是： sql interval 数值 单位 数值：可以是整数或表达式（如 1、row_number() 的结果等）。 单位：表示时间单位，常见的有 day（天）、month（月）、year（年）、hour（小时）、minute（分钟）、second（秒）等。 具体作用 interval 的核心作用是定义 “要增减的时间量”，让日期函数知道 “要给日期加 / 减多少时间”。

用日期减去行号，“连续的日期” 标记为同一个值（diff）

CTE：确定每日收盘价

主查询通过四层嵌套，逐步完成： 标记 “当日是否上涨”； 构造 “连续上涨周期的唯一标识”； 统计每个周期的天数； 提取最大天数。

🍟1、用CTE确定每日收盘价

2、第一层，标记该天是否上涨，第二层，条件是上涨的天数，标记哪些天是连续上涨的，通过该天日期减去行数，第三层，求连续上涨天数，第四层，求最大上涨天数

row_number() over(partition by s_code, date_format(ds, '%Y-%m-%d') order by STR_TO_DATE(ds, '%Y-%m-%d %H:%i') desc) as rk

💎19.用户分层画像

注意：计算比值用round

问题：统计活跃间隔对用户分级后，各活跃等级用户占比

用户分层标准：忠实用户(近7天活跃过且非新晋用户)、新晋用户(近7天新增)、沉睡用户(近7天未活跃但更早前活跃过)、流失用户(近30天未活跃但更早前活跃过)。假设今天就是数据中所有日期的最大值。

思路分析

  • 问题求解相对复杂，先对问题进行拆解，求各活跃等级用户占比，即求 用户数 以及 各活跃等级用户数
    • 用户数：对所有用户id进行去重计数即可得到
    • 各活跃等级用户数：根据用户首次进入时间和用户最晚进入时间以及当天时间进行判断，具体逻辑可看代码部分
      • 当天时间：对所有用户进入时间取最大值即可
      • 用户首次进入时间和用户最晚进入时间：按照用户对进入时间取最小和最大值即可

🍟1、建立公共表*2，临时存关键信息，当前日期，总用户数，每个用户最早登录和最迟登录的日期

2、第一层，算最早和最迟日期与当前日期的差值，第二层再去casewhen分层，第三层，计算比值

💎20.CTE使用场景

查询逻辑复杂，需要拆分多层嵌套，eg：计算股票最大连续上涨天数

同一临时结果集需要被多次引用，eg：分析每日登录用户数的多维度指标

需要实现递归查询（Recursive CTE），当查询涉及 “层级数据”（如组织架构、菜单树）或 “序列生成”（如生成连续日期）时，eg：查询组织架构的所有子部门，生成连续日期序列

需要提升代码的可维护性和调试效率，eg：调试 “用户留存率” 计算

💎21.骑手跑单中位数

中位数条件：where asc_rk >= cnt / 2 and desc_rk >= cnt / 2

正序排一次，倒序排一次

所谓中位数，即无论正序还是逆序排名都是正中间的数字

对于本题而言，按照每个骑手的跑单量进行正序和逆序排列以及求总数，最后过滤出正序 排名以及逆序排名都在正中间的数字

🍟第一层，正序倒序，排序两次，第二层，用where判断，where asc_rk >= cnt / 2 and desc_rk >= cnt / 2

💎22.Hbase为什么有列族

第一就是 列族的定义是什么，第二就是 列族的作用是什么；

列族是HBase中数据模型的一部分，它是一组相关列的集合

第一、列族可以帮助我们更好地组织和管理数据；第二、我们在查询某列的时候，可以查询对应的列族下的某列，而不需要读取所有列，提高查询效率

💎23.音乐TOP推荐

转年份：优先用 year(date_format(sdate, '%Y%m%d'))

转日：优先用 day(date_format(sdate, '%Y%m%d'))

month(date_format(sdate, '%Y%m%d')) as month,

问题如果看起来比较复杂，那么可以将问题进行如下拆解：

  • 维度：月份、歌曲
  • 事实：播放次数
  • 过滤条件：播放时间为2024年、歌手为陈奕迅

首先计算每个月每首歌播放的次数，然后按照月份进行分组，播放次数进行排名，求出每个月前三名的播放歌曲即可

🍟第一层，筛选年和陈奕迅，把每首歌的播放次数算出来，按照月份和歌曲分组，第二层，排序，第三层选出前三

💎24.股票买卖最佳时机

想要获取最大利润，那么我们就一定在最低点买入，最高点卖出

如果知道每一个时刻卖出前最小的买入价格，是不是就可以得到每一个时刻卖出时的最大利润，最终取max即可

需求转化为：如何求当前最小买入价格？

按照股票代码进行分组，交易时间进行排序，获取直到当前时刻的最小交易价格

🍟第一层，求到当前时刻为止历史最小价格，第二层，求当前时间的最大利润，第三层，求当天的最大利润

💎25.花呗逾期分析

看到多张表，先进行JOIN，但是一眼看去好像只能用逾期天数进行关联，可以又无法直接关联，那么就笛卡尔积（考虑到配置表很小）

这时候我们就需要判断每个用户的逾期天数是否小于所有配置的逾期天数，如果是则记为1，这时候会出现一个用户对应多个1，我们要取对应配置逾期天数最小的那一条，怎么办？

按照uid进行分组，配置逾期天数进行排序，对标志位进行求和， 最后取开窗结果为1的行记录即可

on 1 = 1 会让两张表产生笛卡尔积关联（全量关联），即左表的每一行都会和右表的每一行匹配，生成的结果行数 = 左表行数 × 右表行数。

🍟第一层，两个表全量关联，sum，当逾期日期<阈值返回1，当0变1，说明此时就是他所属的等级，第二层，因为是sum递增，所以当sum值为1就是所属阶级

GROUP BY uid 会破坏原本的筛选逻辑 原代码的核心逻辑是通过 total_cnt = 1 筛选每个用户 “首次匹配的等级”（唯一的一行），此时结果中每个 uid 应该只有一行数据。 再加上 GROUP BY uid 是多余的，反而可能因为聚合导致错误（比如多个等级被合并，丢失正确的 user_level）。

就是说group by应该保证得到的结果中一对多，而现在我们的结果是一对一，一行

💎26.小时累计登录用户数

需求转化为 求截止当前小时登录过的用户数

考虑到同一个用户一个小时内可能多次登录，因此需要进行去重

最后就是最常见的开窗逻辑，按照小时进行分组排序，然后对用户id进行count distinct

提取时间单位（年 / 月 / 日 / 时 / 分 / 秒）

这类函数从时间字符串中提取具体的时间单位，返回整数或字符串。

image-20250904201712074

问题：计算20240921当天从零点累计到当前小时进入过直播间的用户数量

输出是hour，所以要转化出hour

🍟第一层，转换出hour，筛选时间，第二层，计数，over开窗

注意：里层查询group by uid,hour(ds)，因为同一个用户一个小时内可能多次登录

💎27.每日新登录用户数

从示例输出可以看到，需要展示所有涉及的日期（如 20240902、20240904），即使这些日期新增用户数为 0。

所以我们之前删掉一部分，表内容不全，需要用leftjoin关联完整表

on t1.user_id = t2.user_id
and t1.dt = t2.dt

image-20250905150538087

1.计算每个用户首次登录日期* （实现方式：row_number / groupby + min）*

2.判断用户登录是否为首次登录* （实现方式：将登录表和结果1进行关联，如果结果1的user_id不为空即为当天新登录用户）*

3.累计每个日期首次登录的用户* （实现方式：count distinct）*

🍟第一层，找首次登录日期，排序后rk=1，第二层rk=1，第三层，按时间分组，计算删掉后的表2,的uid，需要一个左连接

💎28.计算GMV中位数

题目要求大于中位数的店铺，其实就是判断一下中位数对应的逆序排名，小于该名次的即为题目所求

逆序排名如何选择？

  • dense_rank()
  • rank()
  • row_number()

本场景仅有rank适用，至于为什么，大家一起在评论区讨论一下

一种思路，找出中位数，立一个flag，用gmy比大小来判断是不是超过中位数，可以用row_number

另一种思路，给gmv排序，排名高于中位数排名的就是，但是需要用rank排名来避免重复值排名的情况

🍟第一层，rank排序，计算总记录数，第二层，排序高于中位数排序的

where rk < (total_cnt + 1) / 2记得+1

💎29.直播间最大在线人数

因为op_type的存在，所以峰值只能有一条，持续时间就是当前记录时间和下一条记录的差值

需求1：求直播间最大在线人数

  • 我们将用户出直播间的操作记为-1，进直播间的操作记为1，这样进进出出只要按照时间进行累加就可以求出每一个时刻直播间对应的在线人数，最后取最大值即可

需求2：求直播间出现峰值后持续的时间

  • 仅需要找出在线人数等于峰值的时刻的后一个时刻即为峰值结束时间

🍟第一层，用sum求当前直播间在线人数，第二层，求最大在线人数和下一条记录的时间，第三层，下一条记录减去当前记录，得到持续时间

💎30.间隔连续N天登录

本题不同于常见的《用户最大连续登录天数》

回顾一下如何求用户最大连续登录天数

  • 首先计算出连续分组id：登录日期 - 按照用户id分组登录日期进行排序的排名
  • 最后按照用户id和分组id进行分组计数

同上述题目一样，本题的解题关键是 如何计算出连续分组id

  • 我们反向分析以下，如何将间隔一天的登录放到同一个分组中
  • 只要相邻两个登录日期相差不超过2即为同一个分组，如果超过2就会被分到新组中

此时思路就很明显了，先获取当前行上一次登录日期并相减得到差值，然后判断差值是否大于2，如果大于2就开一个新的组，否则被分为同一个组中， 最后根据用户id和分组id进行分组，计算最大登录日期-最小登录日期+1即可得到连续登录天数，再取最大值即可

注意：datediff(max(login_dt), min(login_dt)) + 1 as cnt，天数差 ≠ 天数数量，登录天数数量是间隔差+1

🍟第一层，计算该天和上一天的天数差，第二层，计算天数差>2的返回1，第三层，按组算持续天数，就是最大日期减最小日期，第四层，求最大天数

💎31.缺失余额填充

用sum实现和上一个数相同，且分组思想也用sum实现

如果可以把NULL值和最近一个有值的放到一个组，是不是就可以很容易的解决这个问题了？

所以本题核心就是如何分组？

  • 当余额有值时，组号加一，否则不加一
  • 这样就可以把NULL值和最近一个余额放到一起

最后按照用户id和组号进行分组，对余额进行累计计算即可

🍟第一层,用sum分组，有值为1，没值为0，第二层，用sum实现和上一个值相同的值填充

💎32.连续3次得分的球员

此题无法套用连续N天登录用户数模板

为什么无法使用？

  • 以前都是求连续N天，这里是连续N次
  • 本次的连续并不要求严格等差

怎么办呢？

  • 如何定义连续三次及以上得分？
  • 即同一支球队同一个球员至少连续三次进球
  • 即当一个球员得分后，下一次进球仍然是该球员，下下次进球仍然是该球员，下下次进球可以是该球员也可以不是（不考虑下下次）

因此分析思路转化为代码，就可以按照球队分组，得分时间进行排序，取当前进球后一次进球的球员id，以及当前进球后两次进球的球员id，如果都是同一个球员，那么该球员即为连续三次及以上得分

🍟第一层，加两列，一列是下一行的uid，一列是下下一列，第二层，

where uid = uid_1 and uid = uid_2

💎33.最后一次登录停留时长

根据"经验之谈"可知，先按照用户id和日期进行分组，登录时间进行逆序排列，计算对应的排名，然后获取排名为1的数据，即为题目所求

🍟第一层，

排序，按照uid和转化过的天来排序，降序排序，第二层，取rk = 1

💎34.根据4节气推算季节

关键：用表的left join来筛选，选出节气表1，符合条件的进行join

where dt_term in ('立春','立夏','立秋','立冬')in的用法

思路分析

经验之谈： 对于大多数题目而言，一定会开窗，不管三七二十一，先按照主键进行分组，日期进行排序，然后再思考具体解法

  • 首先一定要理解清楚题意，立春和立夏之间是春天，立夏和立秋之间是夏天，立秋和立冬之间是秋天，立冬和立春之间是冬天（容易忽略掉立春之前的部分）
    • 进一步转化：立春之前的日期打上标签'冬'；立夏之前立春之后的日期打上标签'春'；立秋之前立夏之后的日期打上标签'夏'；立冬之前立秋之后的日期打上标签'秋'；立冬之后的日期打上标签'冬'
  • 对上述分析进行代码拆解：
    • 第一步：找到对应节气的开始日期和结束日期（先过滤出这四个节气，按照用户分、日期进行排序，取下一行对应的日期）
    • 第二步：给所有日期打上季节标签（当日期位于对应节气的起始区间，打上对应季节标签）

🍟1、建立公共表，筛选四条记录，加一列下一行即这个季节的结束日期

2、当是立春返回reason为春

💎35.补全缺失日期且累计金额

coalesce(expression1, expression2, ..., expression_n)

coalesce(pay_amt_1d, 0) 会先检查 pay_amt_1d 的值：

如果 pay_amt_1d 不为 NULL，则返回 pay_amt_1d 本身的值，即用户在该日期的实际交易金额。 如果 pay_amt_1d 为 NULL，则返回 0，表示该日期用户没有交易金额。

可以使用recursive构建连续日期，然后合并的时候使用coalesce

🍟1、建立临时表t1存最大最小日期

2、使用recursive建立连续日期，left join连接表，coalesce将不是null的设为0

with recursive t1 as(
    select
    uid,
    min(dt) as start_dt,
    max(dt) as end_dt
    from dws_table
    group by uid
),
t2 as(
    select
    uid,
    start_dt as current_dt,
    end_dt
    from t1
    union all
    select 
    uid,
    date_add(current_dt,1) as current_dt
    end_dt
    from t2
    where current_dt < end_dt
)
​
select
rd.uid,
rd.current_dt
sum(coalesce(t.pay,0)) over(partition by rd.uid order by rd.current_dt) as total_pay
from t2 rd
left join dws_table t
on rd.uid = t.uid
and rd.current_dt = t.dt;

💎36.留存问题

首先按照用户和登录日期进行去重，因为每个用户在当天可能登录多次

题干要求是有新用户登录，等价于求用户的首次登录日期

如果用户登录时间和用户首次登录时间相差N天即为N日留存用户

🍟1、找到最早登录的日期，建一个临时表；存所有日期，建一个临时表，需要转换日期

2、计算，用当前日期减去最早日期 如果等于1就返回uid，等于3也返回uid，用count，用round

count(case when datediff(t2.dt,t1.dt) = 1 then t2.uid else null end)/ count(dinstinct t1.uid) as retain_1d_rate

group by user_id, cast(login_dt as date)

是为了去重

💎37.股票的波峰和波谷

选择加列的方式求上一行数据和下一行数据

思路分析

难点： 如何获取上一个交易日和后一个交易日的股票价格

  1. 获取所有股票交易日期前一个日期对应的价格X以及后一个日期对应的价格Y
  2. 如果当前股票价格高于X和Y，那么此时即为波峰，反之，如果当前股票价格低于X和Y，那么此时即为波谷

🍟第一层，加两列，上一行和下一行，第二层，大于小于判断，且加上flag一列

💎38.窗口函数总结

以下通过具体示例来演示这些窗口函数的用法，假设我们有一张 sales 表，包含 product_id（产品 ID）、sale_date（销售日期）、amount（销售额）字段，数据如下：

product_idsale_dateamount
12023-01-01100
12023-01-02150
12023-01-03200
22023-01-0180
22023-01-02120
22023-01-03160

1. FIRST_VALUE

获取每个产品按销售日期排序后的第一笔销售额。

SELECT
    product_id,
    sale_date,
    amount,
    FIRST_VALUE(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS first_amount
FROM sales;

结果：

product_idsale_dateamountfirst_amount
12023-01-01100100
12023-01-02150100
12023-01-03200100
22023-01-018080
22023-01-0212080
22023-01-0316080

2. LAST_VALUE

获取每个产品按销售日期排序后的最后一笔销售额（注意：默认窗口范围是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，如需获取整个分区的最后一条，需指定窗口范围）。

SELECT
    product_id,
    sale_date,
    amount,
    LAST_VALUE(amount) OVER (PARTITION BY product_id ORDER BY sale_date 
                             RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_amount
FROM sales;

结果：

product_idsale_dateamountlast_amount
12023-01-01100200
12023-01-02150200
12023-01-03200200
22023-01-0180160
22023-01-02120160
22023-01-03160160

3. NTILE

将每个产品的销售额按销售日期排序后分成 2 等份，返回所在等份编号。

SELECT
    product_id,
    sale_date,
    amount,
    NTILE(2) OVER (PARTITION BY product_id ORDER BY sale_date) AS tile_num
FROM sales;

结果：

product_idsale_dateamounttile_num
12023-01-011001
12023-01-021501
12023-01-032002
22023-01-01801
22023-01-021201
22023-01-031602

4. PERCENT_RANK

计算每个产品的销售额按销售日期排序后的百分比排名。

SELECT
    product_id,
    sale_date,
    amount,
    PERCENT_RANK() OVER (PARTITION BY product_id ORDER BY sale_date) AS pct_rank
FROM sales;

结果（保留两位小数）：

product_idsale_dateamountpct_rank
12023-01-011000.00
12023-01-021500.50
12023-01-032001.00
22023-01-01800.00
22023-01-021200.50
22023-01-031601.00

5. MEDIAN（不同数据库语法略有差异，这里以支持的为例）

计算每个产品销售额的中位数。

SELECT
    product_id,
    MEDIAN(amount) OVER (PARTITION BY product_id) AS median_amount
FROM sales
GROUP BY product_id, amount;

结果：

product_idmedian_amount
1150
2120

6. STDDEV

计算每个产品销售额的标准差。

SELECT
    product_id,
    STDDEV(amount) OVER (PARTITION BY product_id) AS stddev_amount
FROM sales;

结果（保留两位小数）：

product_idstddev_amount
150.00
240.00

7. CLUSTER_SAMPLE（假设语法支持随机抽取 1 条数据）

从每个产品的销售额记录中随机抽取 1 条。

SELECT
    product_id,
    sale_date,
    amount
FROM (
    SELECT
        product_id,
        sale_date,
        amount,
        CLUSTER_SAMPLE(1) OVER (PARTITION BY product_id) AS sample_flag
    FROM sales
) t
WHERE sample_flag = 1;

假设随机结果：

product_idsale_dateamount
12023-01-02150
22023-01-0180

（实际结果因随机抽取而异）

💎39.用户登录区间合并

思路分析

难点：如何对交叉区间进行合并

  1. 按照登录时间和注销时间进行排序
  2. 如果登陆时间小于上一条记录的注销时间，就表示有交叉，打上标签flag=1
  3. 当flag由0变为1的时候，就表示多了一个分组，因此我们可以对flag进行累加，累加后的值即为分组id

注意：分组思想，sun加flag，全程和uid没啥关系

lag(end_time, 1, end_time) over (order by start_time asc, end_time asc) as lst_end_time

🍟第一层，对登录时间和注销时间升序排序，加一列就是上一行的注销时间，第二层，判断大小，创建flag，第三层，sum分组，第四层，在分组中，min得到起始时间，max得到终止时间

💎40.用户行为路径分析

  • 问题：统计每天进入A页面后，立刻进入B页面，又进入C页面的用户数* 【注意：进入C页面之前可能进入过其他页面】*

思路分析

  • 需求拆解如下：
    • 要想求按照 A->B->C 序列的用户，就需要知道每个用户的行为路径
    • 这个不难求得，只需要将每个用户进入的页面按照进入时间进行concat即可
    • 那么如何保证A到B是直接到达的，并且B到C是可能间接到达的呢？
    • 显然可以使用正则匹配 like %A,B%C%

🍟第一层，行转列collect_set，用,隔开，第二层，用like正则匹配包含A，B%C的选项，count计数符合条件的uid，按进入时间分组

💎41.连续签到领金币

根据题目"连续"二字可知，此SQL题在考察连续问题，可以套用本人总结的连续问题模板（见之前的文章）

需求拆解如下：

  • 题目要求每个月用户领金币的数量，假设知道用户每日可以领到的金币数量，问题就迎刃而解了
  • 要想求每日用户领到的金币数量，只需要关注三个特殊节点，连续3天签到+2个金币，连续7天签到+6个金币，7天后领取金币数量为1
  • 如何判断是否连续签到3天/7天？
  • 固定套路：首先对用户按照签到时间进行排序得到rn1，然后按照 用户和签到时间减去rn1进行分组，签到时间进行排序得到rn2
  • 得到rn2之后，就可以很容易得到每日用户签到领取的金币数量

🍟第一层，排序，筛选所有前提条件，第二层，用日期去减去排序，得到连续的分区，在分区内部，再进行排序，如果顺序对7取余，余数是3加3r，余数是0加7r，其他的加0r，第三层，对分区求和

💎42.相互关注数

求相互关注的用户对

🍟自关联，on t1.user_id = t2.follower_id and t1.follower_id = t2.user_id，

where t2.user_id is not null;

第二种：分组计数>2的

image-20250906153947636

💎43.连续日期

用with recursive的方法

WITH RECURSIVE date_series AS (
    -- 步骤1：获取每个记录的起始日期、结束日期和金额，并初始化递归的起始行
    SELECT
        start_date,
        end_date,
        amount,
        start_date AS current_date -- 初始的当前日期为start_date
    FROM tmp
    
    UNION ALL
    
    -- 步骤2：递归生成后续日期（当前日期 + 1 天，直到达到end_date）
    SELECT
        start_date,
        end_date,
        amount,
        date_add(current_date, 1) AS current_date
    FROM date_series
    WHERE current_date < end_date -- 递归终止条件：当前日期小于结束日期
)
-- 步骤3：按日期分组汇总金额
SELECT
    current_date AS date,
    SUM(amount) AS total_amount
FROM date_series
GROUP BY current_date;

用posexplode生成动态日期序列表

select     date,    sum(amount) as total_amountfrom (    select        date_add(start_date,id) as date, amount    from tmp    
   lateral view posexplode(split(space(datediff(end_date,start_date)),' ')) t as id,t_date) t group by date;

🍟第一层，生成连续日期四层，第二层count

avatar
文章
阅读
粉丝