【DB】ORACLE の GROUP BY とか PARTITION BY

これも備忘録として。

想定データ

SELECT
  ID,
  NAME,
  KBN,
  PRICE
FROM 
  TRANSACTION1
;

~~区分がKBNってダサいよね・・・ｗ~~

区分（KBN）ごとにまとめる

SELECT
  -- ID,
  -- NAME,
  KBN,
  SUM(PRICE),
  MAX(PRICE),
  MIN(PRICE)
FROM 
  TRANSACTION1
GROUP BY KBN
ORDER BY KBN
;

区分（KBN）ごとにROW_NUMBERをつける

いつも忘れる「PARTITION BY」。

SELECT
  ID,
  NAME,
  KBN,
  PRICE,
  ROW_NUMBER() OVER(PARTITION BY KBN ORDER BY PRICE ASC) ROW_NUM
FROM 
  TRANSACTION1
ORDER BY ID
;

先頭２件だけ取得

SELECT *
FROM(
    SELECT
      ID,
      NAME,
      KBN,
      PRICE,
      ROW_NUMBER() OVER(PARTITION BY KBN ORDER BY PRICE ASC) ROW_NUM
    FROM 
      TRANSACTION1
    )
WHERE ROW_NUM <= 2
ORDER BY ID
;

横で取得

SELECT 
  T1.KBN,
  MAX(CASE WHEN T1.ROW_NUM = 1 THEN T1.PRICE END) AS ROW1,
  MAX(CASE WHEN T1.ROW_NUM = 2 THEN T1.PRICE END) AS ROW2,
  MAX(CASE WHEN T1.ROW_NUM = 3 THEN T1.PRICE END) AS ROW3
FROM (
  SELECT 
    KBN,
    PRICE,
    ROW_NUMBER() OVER(PARTITION BY KBN ORDER BY PRICE ASC) ROW_NUM
  FROM 
    TRANSACTION1
)T1
GROUP BY T1.KBN
;