sjd_

分类

/ 注册

全部文章（共16篇）

连续签到领金币（技巧型）

with coin_table as ( select uid ,date_format(dt,'%Y%m') as month ,case row_number()over(partition by uid, rk_tag order by ...

2025-05-23

1 34

2021年11月每天新用户的次日留存率

一、窗口函数 select dt ,round(sum(if(rk=1 and datediff(lead_dt,dt)=1,1,0))/sum(if(rk=1,1,0)),2) uv_left_rate from ( select dt ,r...

2025-05-23

1 31

每篇文章同一时刻最大在看人数（同时在线/阅读人数）

select artical_id ,max(max_uv) max_uv from ( select artical_id ,sum(num)over(partition by artical_id order by dt asc, num ...

2025-05-23

1 40

计算每个id的加减分后最终成绩（需排名）

select id, name, grade_num from ( select id, name, grade_num, rank()over(order by grade_num desc) as rk from ( select id, name, ...

2024-11-10

1 51

当某数的正逆序累计均大于等于整个序列数字个数的一半即为中位数

select grade from ( select grade, (select sum(number) from class_grade) as total, sum(number)over(order by grade) a, sum(number)over(o...

2024-11-10

2 55

题解 | #牛客的课程订单分析(三)#count窗口

方法一：内表找出user_id，外表找出该user_id符合的记录 with moreThan1 as( select user_id from order_info where product_name in ('C++','Java','Python') and ...

2024-11-09

1 55

每组job中位数位置上的信息（含score在每组的排名）

1、首先在原表上增添两列（num和rk）构成新表rankgrade，其中num为每组job中score从大到小的序号，rk为每组job中score的名次（num用于确定中位数的位置，rk用于生成t_rank列）；2、在表rankgrade中联合筛选（job，num）为每组job的score处于中位数...

2024-11-09

1 64

统计每个日期新用户的次日留存率

select date, ifnull( round(sum(case when (user_id, date) in ( select user_id, date_sub(date, interval 1 day) from login gr...

2024-11-09

1 43

统计每个日期新用户登录个数

1、row_number窗口函数 + sum条件判断 select date, sum(case when rk = 1 then 1 else 0 end) as new from ( select user_id, date, row_number()over(partition...

2024-11-09

1 34

计算次日留存率***

1、where in判断用户是否次日登录+ date_add函数 select round(count(distinct user_id)/(select count(distinct user_id) from login),3) as p from login where (user_id, d...

2024-11-09

1 91