[Oracle] 유용한 함수

-- 연속된 데이터를 가져옴.

-- level CONNECT BY

SELECT level AS lvl 

FROM dual 

CONNECT BY level <= 50;

-- 조건절

-- WHERE NOT EXISTS

SELECT x,

  y,

  '0'

FROM

  (SELECT level AS x FROM dual CONNECT BY level < 9

  ) x,

  (SELECT level AS y FROM dual CONNECT BY level < 5

  ) y

WHERE NOT EXISTS

  (SELECT t.t_x,

    t.t_y

  FROM

    (SELECT 4 AS t_x, 4 AS t_y FROM dual

    ) t

  WHERE t.t_x = x

  AND t.t_y   = y

  );

-- 같은 데이터를 갖는 컬럼에서 다른 데이터 값은 컬럼을 이용해 순서를 정함.

-- row_number() OVER (PARTITION BY column1 ORDER BY column2)

WITH T AS

  (SELECT '1' cls, 'm' sub, 100 point FROM dual

  UNION ALL

  SELECT '2' cls, 'm' sub, 70 point FROM dual

  UNION ALL

  SELECT '2' cls, '1' sub, 50 point FROM dual

  UNION ALL

  SELECT '1' cls, 'l' sub, 90 point FROM dual

  UNION ALL

  SELECT '1' cls, 's' sub, 80 point FROM dual

  UNION ALL

  SELECT '2' cls, 's' sub, 60 point FROM dual

  )

SELECT cls,

  sub,

  point,

  row_number() OVER (PARTITION BY cls ORDER BY point DESC) as rn

FROM T;

-- 데이터를 ,로 연결 함.

-- wm_concat()

WITH T AS

  ( SELECT 1 NUM, 1000 WON FROM dual

  UNION ALL

  SELECT 1 NUM, 2000 WON FROM dual

  UNION ALL

  SELECT 1 NUM, 3000 WON FROM dual

  UNION ALL

  SELECT 2 NUM, 6000 WON FROM dual

  UNION ALL

  SELECT 2 NUM, 5000 WON FROM dual

  UNION ALL

  SELECT 2 NUM, 4000 WON FROM dual

  )

SELECT wm_concat(WON), NUM

FROM T 

GROUP BY NUM ;

-- 그룹핑을 할때 표현할 데이터의 우선 순위를 정 할 수 있음.

-- keep(dense_rank column1 FIRST[LAST] ORDER BY column1)

WITH T AS

  (SELECT 1 AS num, 'AAA' AS tdata FROM dual

  UNION ALL

  SELECT 2 AS num, 'BBB' AS tdata FROM dual

  UNION ALL

  SELECT 3 AS num, 'CCC' AS tdata FROM dual

  UNION ALL

  SELECT 4 AS num, 'BBB' AS tdata FROM dual

  UNION ALL

  SELECT 5 AS num, 'AAA' AS tdata FROM dual

  )

--SELECT num, tdata

SELECT MAX(num) keep(dense_rank FIRST ORDER BY num), tdata

--SELECT MAX(num) keep(dense_rank LAST ORDER BY num), tdata

FROM T

GROUP BY tdata;