【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
;