SQL查找表中的五个最大值而不是一个最大值

Oracle有一个 NTH_VALUE() 作用不幸的是,它只是一个分析函数,而不是一个窗口函数。这导致了 SELECT DISTINCT

SELECT DISTINCT OSI_KEY,
       MAX(VALUE) OVER (PARTITION BY OSI_KEY),
       NTH_VALUE(VALUE, 2) OVER (PARTITION BY OSI_KEY ORDER BY VALUE DESC) as MAX_2,
       NTH_VALUE(VALUE, 3) OVER (PARTITION BY OSI_KEY ORDER BY VALUE DESC) as MAX_3,
       NTH_VALUE(VALUE, 4) OVER (PARTITION BY OSI_KEY ORDER BY VALUE DESC) as MAX_4,
       NTH_VALUE(VALUE, 5) OVER (PARTITION BY OSI_KEY ORDER BY VALUE DESC) as MAX_5,
       AVG(VALUE) OVER (PARTITION BY OSI_KEY),
       STDDEV(VALUE)  OVER (PARTITION BY OSI_KEY),
       variance(VALUE)  OVER (PARTITION BY OSI_KEY)
FROM DATA_VALUES_5MIN_6_2013
ORDER BY OSI_KEY;

您还可以使用条件聚合来实现这一点 row_number() dense_rank()

SELECT OSI_KEY, MaxValue FROM (
    SELECT OSI_KEY, MAX(value) AS MaxValue FROM table GROUP BY OSI_KEY
)
ORDER BY MaxValue DESC
FETCH FIRST 5 ROWS ONLY;