—> 中位数
—> 查找每个公司薪水中位数的员工
数据&建表
create table employee
(
id int,
company varchar(11),
salary int
);
insert into employee (id, company, salary)
values (1, 'A', 2341),
(2, 'A', 341),
(3, 'A', 15),
(4, 'A', 15314),
(5, 'A', 451),
(6, 'A', 513),
(7, 'B', 15),
(8, 'B', 13),
(9, 'B', 1154),
(10, 'B', 1345),
(11, 'B', 1221),
(12, 'B', 234),
(13, 'C', 2345),
(14, 'C', 2645),
(15, 'C', 2645),
(16, 'C', 2652),
(17, 'C', 65);
原表 employee
id | company | salary |
---|---|---|
1 | A | 2341 |
2 | A | 341 |
3 | A | 15 |
4 | A | 15314 |
5 | A | 451 |
6 | A | 513 |
7 | B | 15 |
8 | B | 13 |
9 | B | 1154 |
10 | B | 1345 |
11 | B | 1221 |
12 | B | 234 |
13 | C | 2345 |
14 | C | 2645 |
15 | C | 2645 |
16 | C | 2652 |
17 | C | 65 |
需求:查找每个公司薪水中位数的员工
代码编写
select *
from (
select id,
salary,
row_number() over (partition by company order by salary) as rn,
count(1) over (partition by company) as cnt
from employee
) t1
where rn in (floor((cnt + 1) / 2), floor((cnt + 2) / 2))
id | company | salary |
---|---|---|
5 | A | 451 |
6 | A | 513 |
12 | B | 234 |
9 | B | 1154 |
14 | C | 2645 |
—> 牛客直播课分析
—> 牛客直播各科目出勤率
with a as (
select course_id,
course_name
from course_tb
),
b as (
select course_id,
count(distinct if(timestampdiff(minute, in_datetime, out_datetime) >= 10, user_id, null)) as flag
from attend_tb
group by course_id
),
c as (
select course_id,
sum(if(if_sign = 1, 1, 0)) as cnt
from behavior_tb
group by course_id
)
select a.course_id,
a.course_name,
round(b.flag * 100 / c.cnt, 2) as rate
from a
join b on a.course_id = b.course_id
join c on a.course_id = c.course_id
order by a.course_id