[Oracle] 계층형 쿼리 (feat. ASL 시즌 9 경기결과)

1. 사용자 생성 및 권한 부여

-- 1.  사용자 생성
CREATE USER asl IDENTIFIED BY [비밀번호]
DEFAULT tablespace USERS
TEMPORARY tablespace TEMP;

-- 2. 권한부여
GRANT RESOURCE, CONNECT TO asl

 

2.1. 경기결과 테이블 생성

-- ASL."RESULT" definition

CREATE TABLE "RESULT" 
   ("SEASON" NUMBER NOT NULL ENABLE, 
"ROUND" NUMBER NOT NULL ENABLE, 
"GAME" NUMBER(1,0) NOT NULL ENABLE, 
"PLAYER_1_ID" VARCHAR2(100) NOT NULL ENABLE, 
"PLAYER_1_NAME" VARCHAR2(100), 
"PLAYER_2_ID" VARCHAR2(100) NOT NULL ENABLE, 
"PLAYER_2_NAME" VARCHAR2(100), 
"WINNER_ID" VARCHAR2(100), 
"DATE" DATE, 
"UPPER_SRG" VARCHAR2(100), 
 CONSTRAINT "RESULT_PK" PRIMARY KEY ("SEASON", "ROUND", "GAME")
   );

   COMMENT ON COLUMN "RESULT"."SEASON" IS '시즌';
   COMMENT ON COLUMN "RESULT"."ROUND" IS '강';
   COMMENT ON COLUMN "RESULT"."GAME" IS '경기';
   COMMENT ON COLUMN "RESULT"."PLAYER_1_ID" IS '선수1 아이디';
   COMMENT ON COLUMN "RESULT"."PLAYER_1_NAME" IS '선수1 이름';
   COMMENT ON COLUMN "RESULT"."PLAYER_2_ID" IS '선수2 아이디';
   COMMENT ON COLUMN "RESULT"."PLAYER_2_NAME" IS '선수2 이름';
   COMMENT ON COLUMN "RESULT"."WINNER_ID" IS '승자 아이디';
   COMMENT ON COLUMN "RESULT"."DATE" IS '경기 날짜';
   COMMENT ON COLUMN "RESULT"."UPPER_SRG" IS '상위_게임유일값(SEASON_ROURND_GAME)';
   COMMENT ON TABLE "RESULT"  IS '경기결과';

 

2.2. 경기결과 데이터 INSERT(8강, 4강, 결승)

INSERT INTO "RESULT" (SEASON, ROUND, GAME, PLAYER_1_ID, PLAYER_1_NAME, PLAYER_2_ID, PLAYER_2_NAME, WINNER_ID, "DATE", UPPER_SRG) VALUES(9, 8, 1, 'FlaSh', '이영호', 'Action', '김성대', 'FlaSh', TIMESTAMP '2020-03-29 00:00:00.000000', '941');
INSERT INTO "RESULT" (SEASON, ROUND, GAME, PLAYER_1_ID, PLAYER_1_NAME, PLAYER_2_ID, PLAYER_2_NAME, WINNER_ID, "DATE", UPPER_SRG) VALUES(9, 8, 2, 'Queen', '김명운', 'Larva', '임홍규', 'Queen', TIMESTAMP '2020-03-31 00:00:00.000000', '941');
INSERT INTO "RESULT" (SEASON, ROUND, GAME, PLAYER_1_ID, PLAYER_1_NAME, PLAYER_2_ID, PLAYER_2_NAME, WINNER_ID, "DATE", UPPER_SRG) VALUES(9, 8, 3, 'Stork', '송병구', 'Light', '이재호', 'Light', TIMESTAMP '2020-04-05 00:00:00.000000', '942');
INSERT INTO "RESULT" (SEASON, ROUND, GAME, PLAYER_1_ID, PLAYER_1_NAME, PLAYER_2_ID, PLAYER_2_NAME, WINNER_ID, "DATE", UPPER_SRG) VALUES(9, 8, 4, 'ChosimZerg', '박상현', 'Bisu', '김택용', 'ChosimZerg', TIMESTAMP '2020-04-07 00:00:00.000000', '942');
INSERT INTO "RESULT" (SEASON, ROUND, GAME, PLAYER_1_ID, PLAYER_1_NAME, PLAYER_2_ID, PLAYER_2_NAME, WINNER_ID, "DATE", UPPER_SRG) VALUES(9, 4, 1, 'FlaSh', '이영호', 'Queen', '김명운', 'Queen', TIMESTAMP '2020-04-12 00:00:00.000000', '921');
INSERT INTO "RESULT" (SEASON, ROUND, GAME, PLAYER_1_ID, PLAYER_1_NAME, PLAYER_2_ID, PLAYER_2_NAME, WINNER_ID, "DATE", UPPER_SRG) VALUES(9, 4, 2, 'Light', '이재호', 'ChosimZerg', '박상현', 'Light', TIMESTAMP '2020-04-14 00:00:00.000000', '921');
INSERT INTO "RESULT" (SEASON, ROUND, GAME, PLAYER_1_ID, PLAYER_1_NAME, PLAYER_2_ID, PLAYER_2_NAME, WINNER_ID, "DATE", UPPER_SRG) VALUES(9, 2, 1, 'Queen', '김명운', 'Light', '이재호', 'Queen', TIMESTAMP '2020-04-26 00:00:00.000000', NULL);

 

2.3. 경기 결과 테이블 데이터 입력 사항 조회

SELECT *
FROM RESULT
ORDER BY SEASON, ROUND, GAME

 

3. 계층형 쿼리 사용하여 조회

/*
 * 1. WITH절에서 경기별 고유값을 지정 
 */
WITH BASE AS (
SELECT SEASON,							-- 시즌(9)
       ROUND,                           -- 라운드(8강, 4강, 결승)
       SEASON || ROUND || GAME AS SRG,  -- 경기별 고유값(시즌 + 라운드 + 경기 )
       UPPER_SRG,                       -- 상위 경기 고유값
       GAME,
       NVL2(UPPER_SRG,
        ROUND || '강 ' || GAME || '경기 - ' || PLAYER_1_NAME || ' VS ' || PLAYER_2_NAME,
        '결승 - ' || PLAYER_1_NAME || ' VS ' || PLAYER_2_NAME 
       ) AS TITLE,
       PLAYER_1_NAME,
       PLAYER_2_NAME
FROM RESULT
)
/*
 * 2.경기별 고유값(SRG)와 상위 경기고유값(UPPER_SRG)으로 게층 구현
 */
SELECT SEASON,
       ROUND,
       SRG,
       UPPER_SRG,
       GAME,
       LPAD(' ', 2*(LEVEL-1)) || TITLE AS LPAD_TITLE, --레벨별 들여쓰기
       LEVEL
FROM BASE
START WITH UPPER_SRG IS NULL		-- 결승은 상위 경기고유값이 NULL
CONNECT BY PRIOR SRG = UPPER_SRG    -- 경기별 고유값과 상위 경기고유값으로 연결
ORDER BY GAME

참조
namu.wiki/w/%EC%95%84%ED%94%84%EB%A6%AC%EC%B9%B4TV%20%EC%8A%A4%ED%83%80%EB%A6%AC%EA%B7%B8%20%EC%8B%9C%EC%A6%8C9?from=ASL%20%EC%8B%9C%EC%A6%8C9#s-6
https://dreamstorage.tistory.com/169
https://coding-factory.tistory.com/461