sjd_

分类

/ 注册

全部文章（共13篇）

计算每个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 11

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

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 12

题解 | #牛客的课程订单分析(三)#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 11

每组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 19

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

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 14

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

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 10

计算次日留存率***

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 24

统计salary的累计和running_total

-- 法1：聚合窗口函数 select emp_no, salary, sum(salary)over(order by emp_no) as running_total from salaries where to_date = '9999-01-01' -- 法2：自连接+group by s...

2024-11-08

1 15

exists与in的区别

select e.* from employees e where not exists ( select emp_no from dept_emp d where e.emp_no = d.emp_no ) /*必须要有where这句，exists先执行外层，拿进来和内层的比，内...

2024-11-08

1 16

group_concat函数

select dept_no, group_concat(emp_no) as employees from dept_emp group by dept_no group_concat(多个列名or自定义字符串 [Separator '分隔符'（默认为, ）])聚合函数搭配group by使用e...

2024-11-08

1 8