需求: 1:id有5组, 现在要求出各组内薪资最高得那个人
2:如果该组有多个薪水一样得人,那么取出年龄最大那个
数据源: mysql、hive
id | name | age | salary |
---|---|---|---|
1 | a1 | 10 | 80 |
1 | a2 | 11 | 65 |
1 | a3 | 5 | 90 |
2 | b1 | 12 | 130 |
2 | b2 | 13 | 45 |
2 | b3 | 14 | 80 |
3 | c1 | 14 | 300 |
3 | c2 | 15 | 900 |
3 | c3 | 16 | 900 |
4 | d1 | 16 | 500 |
4 | d2 | 16 | 600 |
4 | d3 | 17 | 300 |
5 | e1 | 20 | 200 |
5 | e2 | 20 | 200 |
5 | e3 | 19 | 100 |
一、数据生成
1:创建表
CREATE TABLE `test` ( `id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `age` int(255) NULL DEFAULT NULL, `salary` int(10) NULL DEFAULT NULL ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
2: 插入数据
insert into test(id,name,age,salary) values(1,'a1',10,80);
insert into test(id,name,age,salary) values(1,'a2',11,65);
insert into test(id,name,age,salary) values(1,'a3',05,90);
insert into test(id,name,age,salary) values(2,'b1',12,130);
insert into test(id,name,age,salary) values(2,'b2',13,45);
insert into test(id,name,age,salary) values(2,'b3',14,80);
insert into test(id,name,age,salary) values(3,'c1',14,300);
insert into test(id,name,age,salary) values(3,'c2',15,900);
insert into test(id,name,age,salary) values(3,'c3',16,900);
insert into test(id,name,age,salary) values(4,'d1',16,500);
insert into test(id,name,age,salary) values(4,'d2',16,600);
insert into test(id,name,age,salary) values(4,'d3',17,300);
insert into test(id,name,age,salary) values(5,'e1',20,200);
insert into test(id,name,age,salary) values(5,'e2',20,200);
insert into test(id,name,age,salary) values(5,'e3',19,100);
二: 需求实现(Mysql)
Mysql实现求出组内最大
SELECT * FROM test WHERE (id,salary) IN (
SELECT id,MAX(salary) FROM test GROUP BY id
)
id | name | age | salary |
1 | a3 | 5 | 90 |
2 | b1 | 12 | 130 |
3 | c2 | 15 | 900 |
3 | c3 | 16 | 900 |
4 | d2 | 16 | 600 |
5 | e1 | 20 | 200 |
5 | e2 | 20 | 200 |
可以看到结果现在是正确得,但是3组和5组当中, 3组求出来了组内薪资最大但是有两条数据薪资一样,他们的age不一样, 5组求出来了组内薪资最大但是有两条数据薪资一样,他们age也一样。
所以现在实现第二个需求,如果薪资一样求出age最大那一条数据, 如果薪资一样年龄也一样的话取出组内任意一条。那么现在3组应该取出name为c3的, 5组取出组内任意一条
SELECT
*
FROM
test
WHERE
( ID, AGE, salary ) IN (
SELECT
T.ID,
MAX( AGE ),
salary
FROM
test T
WHERE
( T.ID, T.SALARY ) IN ( SELECT ID, MAX( SALARY ) FROM test T1 GROUP BY T1.ID )
GROUP BY
T.ID
)
GROUP BY
id
id | name | age | salary |
1 | a3 | 5 | 90 |
2 | b1 | 12 | 130 |
3 | c3 | 16 | 900 |
4 | d2 | 16 | 600 |
5 | e1 | 20 | 200 |
现在这样的结果,就符合了需求
三: 需求实现(Hive)
在使用开窗函数之前可以先了解一下什么是开窗函数
在了解完开窗函数之后可以结合需求, 这次我们需要的是,row_number() over(partition by) 或者 first_value over(partition by) 这两个函数, 具体实现方法如下
row_number() 使用
SELECT id,name,age,salary,row_number() over (PARTITION BY id ORDER BY salary desc) rn from salarytest
id | name | age | salary | rn |
1 | a3 | 5 | 90 | 1 |
1 | a1 | 10 | 80 | 2 |
1 | a2 | 11 | 65 | 3 |
2 | b1 | 12 | 130 | 1 |
2 | b3 | 14 | 80 | 2 |
2 | b2 | 13 | 45 | 3 |
3 | c3 | 16 | 900 | 1 |
3 | c2 | 15 | 900 | 2 |
3 | c1 | 14 | 300 | 3 |
4 | d2 | 16 | 600 | 1 |
4 | d1 | 16 | 500 | 2 |
4 | d3 | 17 | 300 | 3 |
5 | e1 | 20 | 200 | 1 |
5 | e2 | 20 | 200 | 2 |
5 | e3 | 19 | 100 | 3 |
从上结果可以看出, row_number函数将按照分区条件分区后按照指定字段排序的结果,可以根据需求取数据, 比如我想要前三位, 那么可以再阔一层加个判断 rn <= 3。
如果再这个SQL的基础上再去实现第二个需求, 如果薪资一样求出age最大那一条数据, 如果薪资一样年龄也一样的话取出组内任意一条。那么现在3组应该取出name为c3的, 5组取出组内任意一条
SELECT id,name,age,salary,row_number() over (PARTITION BY id ORDER BY salary desc ,age desc) rn from salarytest
id | name | age | salary | rn |
1 | a3 | 5 | 90 | 1 |
1 | a1 | 10 | 80 | 2 |
1 | a2 | 11 | 65 | 3 |
2 | b1 | 12 | 130 | 1 |
2 | b3 | 14 | 80 | 2 |
2 | b2 | 13 | 45 | 3 |
3 | c3 | 16 | 900 | 1 |
3 | c2 | 15 | 900 | 2 |
3 | c1 | 14 | 300 | 3 |
4 | d2 | 16 | 600 | 1 |
4 | d1 | 16 | 500 | 2 |
4 | d3 | 17 | 300 | 3 |
5 | e1 | 20 | 200 | 1 |
5 | e2 | 20 | 200 | 2 |
5 | e3 | 19 | 100 | 3 |
就是排序的时候 薪资倒序,年龄也倒序就可以了。如果要求出来薪资相同年龄最下的那一条, age 升序就可以了
first_value() 使用
SELECT id,name,age,first_value(salary) OVER (PARTITION BY id ORDER BY salary desc) rn FROM salarytest
id | name | age | rn |
1 | a3 | 5 | 90 |
1 | a1 | 10 | 90 |
1 | a2 | 11 | 90 |
2 | b1 | 12 | 130 |
2 | b3 | 14 | 130 |
2 | b2 | 13 | 130 |
3 | c3 | 16 | 900 |
3 | c2 | 15 | 900 |
3 | c1 | 14 | 900 |
4 | d2 | 16 | 600 |
4 | d1 | 16 | 600 |
4 | d3 | 17 | 600 |
5 | e1 | 20 | 200 |
5 | e2 | 20 | 200 |
5 | e3 | 19 | 200 |
frist_value 的结果很直观,就是分区排序后 取第一位