從同一行的不同列中選擇n個最高值

考慮我有一個這樣的表（類別列可以更多）

Name    CATEGORY_1  CATEGORY_2  CATEGORY_3
John        8           65          3
Sally       0            5         12
Sarah      15           17          0

我怎樣才能找到這樣的最高值

Name    CATEGORY_1  CATEGORY_2  CATEGORY_3  first_highest   second_highest  third_highest
John        8          65          3         CATEGORY_2       CATEGORY_1     CATEGORY_3
Sally       0           5         12         CATEGORY_3       CATEGORY_2     CATEGORY_1
Sarah      15          17          0         CATEGORY_2       CATEGORY_1     CATEGORY_3

uj5u.com熱心網友回復：

這是一種選擇；閱讀代碼中的注釋：

SQL> with
  2  test (name, cat1, cat2, cat3) as
  3    -- sample data
  4    (select 'John' ,  8, 65,  3 from dual union all
  5     select 'Sally',  0,  5, 12 from dual union all
  6     select 'Sarah', 15, 17,  0 from dual
  7    ),

  8  temp as
  9    -- make rows out of columns
 10    (select name, 1 cat_rn, cat1 cat from test union all
 11     select name, 2,        cat2 from test union all
 12     select name, 3,        cat3 from test
 13    ),
 14  srt as
 15    -- sort rows by CAT values; use ROW_NUMBER instead of RANK in case two (or more) values
 16    -- are equal
 17    (select name, cat_rn, cat,
 18       row_number() over (partition by name order by cat desc) rn
 19     from temp
 20    )
 21  -- finally:
 22  select a.name, a.cat1, a.cat2, a.cat3,
 23    max('cat' || case when s.rn = 1 then cat_rn end) first_highest,
 24    max('cat' || case when s.rn = 2 then cat_rn end) second_highest,
 25    max('cat' || case when s.rn = 3 then cat_rn end) third_highest
 26  from test a join srt s on a.name = s.name
 27  group by a.name, a.cat1, a.cat2, a.cat3;

NAME        CAT1       CAT2       CAT3 FIRST_HIGHEST   SECOND_HIGHEST  THIRD_HIGHEST
----- ---------- ---------- ---------- --------------- --------------- ---------------
John           8         65          3 cat2            cat1            cat3
Sally          0          5         12 cat3            cat2            cat1
Sarah         15         17          0 cat2            cat1            cat3

SQL>

uj5u.com熱心網友回復：

您也可以為此使用以下解決方案。它使用unpivot子句對源資料進行反透視，并分別創建包含第 n 個最高值的三列（通過nth_value分析函式）。然后，它將結果與源資料重新連接以取回三個類別列。

with SampleData ( Name, CATEGORY_1, CATEGORY_2, CATEGORY_3 ) as (
    select 'John' ,  8, 65,  3 from dual union all
    select 'Sally',  0,  5, 12 from dual union all
    select 'Sarah', 15, 17,  0 from dual
)

, SampleData_NTH as (
  select NAME, COL, VAL
    , NTH_VALUE( COL, 1) OVER ( PARTITION BY NAME ORDER BY VAL desc 
                rows between UNBOUNDED preceding and UNBOUNDED following ) first_highest
    , NTH_VALUE( COL, 2) OVER ( PARTITION BY NAME ORDER BY VAL desc 
                rows between UNBOUNDED preceding and UNBOUNDED following ) second_highest
    , NTH_VALUE( COL, 3) OVER ( PARTITION BY NAME ORDER BY VAL desc 
                rows between UNBOUNDED preceding and UNBOUNDED following ) third_highest
  from SampleData
  unpivot (
    val for col in (
        CATEGORY_1 as 'CATEGORY_1'
      , CATEGORY_2 as 'CATEGORY_2'
      , CATEGORY_3 as 'CATEGORY_3'
      )
    )
)
select t1.NAME
    , t1.CATEGORY_1, t1.CATEGORY_2, t1.CATEGORY_3
    , t2.FIRST_HIGHEST, t2.SECOND_HIGHEST, t2.THIRD_HIGHEST
from SampleData t1
join SampleData_NTH t2 
    on t1.name = t2.name
group by t1.NAME
    , t1.CATEGORY_1, t1.CATEGORY_2, t1.CATEGORY_3
    , t2.FIRST_HIGHEST, t2.SECOND_HIGHEST, t2.THIRD_HIGHEST
order by 1
;

演示

標籤：甲骨文

上一篇：使用:new和:old更新和插入之前的Oracle觸發器

下一篇：為什么cx_Oracle的性能比PHPOCI8好？