row_number() over(partition by) 和 first_value over(partition by) 实用例子和二者区别。Mysql和Hive分别实现取组内最大条数据

需求:  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)

在使用开窗函数之前可以先了解一下什么是开窗函数

https://blog.csdn.net/kuodannie1668/article/details/79757186?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-1.control&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-1.control

在了解完开窗函数之后可以结合需求, 这次我们需要的是,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 的结果很直观,就是分区排序后 取第一位