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
查询结果: