Oracle查询各组最大值的前几位、最小值的前几位和出现次数

Oracle查询各组最大值的前几位、最小值的前几位和出现次数

查询各编码前3个最大值、前3个最小值，以及各数据出现的次数

SELECT b.* 
FROM (
	SELECT
		a.CODE,
		a.VALUE,
		COUNT( a.VALUE ) count,
		ROW_NUMBER() OVER( PARTITION BY a.CODE ORDER BY a.VALUE DESC) maxrn,
		ROW_NUMBER() OVER( PARTITION BY a.CODE ORDER BY a.VALUE) minrn 
	FROM tableName a 
	GROUP BY
		a.CODE,	a.VALUE 
	) b 
WHERE
	maxrn <= 3 
	OR minrn <= 3;

1.通过ROW_NUMBER() OVER( PARTITION BY a.CODE ORDER BY a.VALUE DESC) 将编码分组。每组中根据数值倒叙排序并生成行号；取每组的前3条数据即为前3个最大值

2.同理，通过ROW_NUMBER() OVER( PARTITION BY a.CODE ORDER BY a.VALUE将编码分组。每组中根据数值正叙排序并生成行号；取每组的前3条数据即为前3个最小值

	SELECT
		a.CODE,
		a.VALUE,
		COUNT( a.VALUE ) count,
		ROW_NUMBER() OVER( PARTITION BY a.CODE ORDER BY a.VALUE DESC) maxrn,
		ROW_NUMBER() OVER( PARTITION BY a.CODE ORDER BY a.VALUE) minrn 
	FROM tableName a 
	GROUP BY
		a.CODE,	a.VALUE 
	) b 

查询结果：