이야봉 블로그

[쿼리]

SELECT SUBSTRING(A.PRODUCT FROM '\[(.*?)\]') AS PRODUCT_CODE,       
                 A.PRODUCT
FROM
      ( 
      	SELECT '[SM-G981N]갤럭시 S20' AS PRODUCT
        UNION ALL
      	SELECT '[SM-G991N]갤럭시 S21'
        UNION ALL
        SELECT '[SM-S901N]갤럭시 S22'
      ) A

[수행결과]

[참고]

https://popsql.com/learn-sql/postgresql/how-to-use-substring-with-regular-expressions-in-postgresql

http://daplus.net/regex-%EB%8C%80%EA%B4%84%ED%98%B8-%EC%82%AC%EC%9D%B4%EC%97%90%EC%84%9C-%ED%85%8D%EC%8A%A4%ED%8A%B8%EB%A5%BC-%EC%B6%94%EC%B6%9C%ED%95%98%EB%8A%94-%EC%A0%95%EA%B7%9C%EC%8B%9D/

https://namu.wiki/w/%EC%82%BC%EC%84%B1%20%EA%B0%A4%EB%9F%AD%EC%8B%9C/%EC%A0%9C%ED%92%88%20%EB%AA%A9%EB%A1%9D#s-1.1

WITH EMPP AS 
(
	SELECT 10 AS DEPTNO, 'CLERK'     AS JOB, 7934 AS EMPNO, 'MILLER' AS ENAME, 1300 AS SAL, 200  AS BONUS, 3 AS CHLDN FROM DUAL UNION ALL
	SELECT 10 AS DEPTNO, 'MANAGER'   AS JOB, 7782 AS EMPNO, 'CLARK'  AS ENAME, 2450 AS SAL, 100  AS BONUS, 0 AS CHLDN FROM DUAL UNION ALL
	SELECT 10 AS DEPTNO, 'PRESIDENT' AS JOB, 7839 AS EMPNO, 'KING'   AS ENAME, 5000 AS SAL, 0    AS BONUS, 2 AS CHLDN FROM DUAL UNION ALL
	SELECT 20 AS DEPTNO, 'ANALYST'   AS JOB, 7902 AS EMPNO, 'FORD'   AS ENAME, 3000 AS SAL, 300  AS BONUS, 1 AS CHLDN FROM DUAL UNION ALL
	SELECT 20 AS DEPTNO, 'ANALYST'   AS JOB, 7788 AS EMPNO, 'SCOTT'  AS ENAME, 3000 AS SAL, 600  AS BONUS, 1 AS CHLDN FROM DUAL UNION ALL
	SELECT 20 AS DEPTNO, 'CLERK'     AS JOB, 7876 AS EMPNO, 'ADAMS'  AS ENAME, 1100 AS SAL, 200  AS BONUS, 1 AS CHLDN FROM DUAL UNION ALL
	SELECT 20 AS DEPTNO, 'CLERK'     AS JOB, 7369 AS EMPNO, 'SMITH'  AS ENAME, 800  AS SAL, 200  AS BONUS, 4 AS CHLDN FROM DUAL UNION ALL
	SELECT 20 AS DEPTNO, 'MANAGER'   AS JOB, 7566 AS EMPNO, 'JONES'  AS ENAME, 2975 AS SAL, 300  AS BONUS, 2 AS CHLDN FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'CLERK'     AS JOB, 7900 AS EMPNO, 'JAMES'  AS ENAME, 950  AS SAL, 300  AS BONUS, 0 AS CHLDN FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'MANAGER'   AS JOB, 7698 AS EMPNO, 'BLAKE'  AS ENAME, 2850 AS SAL, 400  AS BONUS, 1 AS CHLDN FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'SALESMAN'  AS JOB, 7521 AS EMPNO, 'WARD'   AS ENAME, 1250 AS SAL, 500  AS BONUS, 0 AS CHLDN FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'SALESMAN'  AS JOB, 7844 AS EMPNO, 'TURNER' AS ENAME, 1500 AS SAL, 0    AS BONUS, 2 AS CHLDN FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'SALESMAN'  AS JOB, 7499 AS EMPNO, 'ALLEN'  AS ENAME, 1600 AS SAL, 300  AS BONUS, 2 AS CHLDN FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'SALESMAN'  AS JOB, 7654 AS EMPNO, 'MARTIN' AS ENAME, 1250 AS SAL, 1400 AS BONUS, 3 AS CHLDN FROM DUAL
),
/* NON-PIVOT 결과 */
A AS (
   SELECT DEPTNO,
          JOB,
          SUM(CHLDN) AS CHLDN
   FROM EMPP
   GROUP BY DEPTNO, JOB
),
/* PIVOT 결과 */
B AS 
(
	SELECT *
	FROM 
	(
		SELECT 
		       DEPTNO, -- GROUP BY 
		       JOB,    -- GROUP BY (JOIN 불가)
		       JOB J,  -- JOIN용
		       SAL,    -- SUM(SAL)
		       BONUS   -- SUM(BONUS)
		   FROM EMPP
	)
	PIVOT
	(
		SUM(SAL) AS SAL, 
		SUM(BONUS) AS BNS
		FOR JOB IN ( 'CLERK' AS C, 
                      'ANALYST' AS A,
                       'PRESIDENT' AS P,
                       'SALESMAN' AS S,
                       'MANAGER' AS M
                       )
	) 
) /* End of B */
SELECT A.DEPTNO AS A_DEPTNO,
       A.JOB AS A_JOB,
       A.CHLDN,
       B.*
FROM A,  /* NON-PIVOT 결과 */ 
     B   /* PIVOT 결과 */
WHERE A.DEPTNO = B.DEPTNO
  AND A.JOB = B.J
ORDER BY A_DEPTNO, A_JOB;

참조
http://www.yes24.com/Product/Goods/46218018
http://blog.naver.com/PostView.nhn?blogId=silentis&logNo=220943488844

WITH EMP AS
(
	SELECT 10 AS DEPTNO, 'CLERK'     AS JOB, 7934 AS EMPNO, 'MILLER' AS ENAME, 1300 AS SAL, 200  AS BONUS FROM DUAL UNION ALL
	SELECT 10 AS DEPTNO, 'MANAGER'   AS JOB, 7782 AS EMPNO, 'CLARK'  AS ENAME, 2450 AS SAL, 100  AS BONUS FROM DUAL UNION ALL
	SELECT 10 AS DEPTNO, 'PRESIDENT' AS JOB, 7839 AS EMPNO, 'KING'   AS ENAME, 5000 AS SAL, 0    AS BONUS FROM DUAL UNION ALL
	SELECT 20 AS DEPTNO, 'ANALYST'   AS JOB, 7902 AS EMPNO, 'FORD'   AS ENAME, 3000 AS SAL, 300  AS BONUS FROM DUAL UNION ALL
	SELECT 20 AS DEPTNO, 'ANALYST'   AS JOB, 7788 AS EMPNO, 'SCOTT'  AS ENAME, 3000 AS SAL, 600  AS BONUS FROM DUAL UNION ALL
	SELECT 20 AS DEPTNO, 'CLERK'     AS JOB, 7876 AS EMPNO, 'ADAMS'  AS ENAME, 1100 AS SAL, 200  AS BONUS FROM DUAL UNION ALL
	SELECT 20 AS DEPTNO, 'CLERK'     AS JOB, 7369 AS EMPNO, 'SMITH'  AS ENAME, 800  AS SAL, 200  AS BONUS FROM DUAL UNION ALL
	SELECT 20 AS DEPTNO, 'MANAGER'   AS JOB, 7566 AS EMPNO, 'JONES'  AS ENAME, 2975 AS SAL, 300  AS BONUS FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'CLERK'     AS JOB, 7900 AS EMPNO, 'JAMES'  AS ENAME, 950  AS SAL, 300  AS BONUS FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'MANAGER'   AS JOB, 7698 AS EMPNO, 'BLAKE'  AS ENAME, 2850 AS SAL, 400  AS BONUS FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'SALESMAN'  AS JOB, 7521 AS EMPNO, 'WARD'   AS ENAME, 1250 AS SAL, 500  AS BONUS FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'SALESMAN'  AS JOB, 7844 AS EMPNO, 'TURNER' AS ENAME, 1500 AS SAL, 0    AS BONUS FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'SALESMAN'  AS JOB, 7499 AS EMPNO, 'ALLEN'  AS ENAME, 1600 AS SAL, 300  AS BONUS FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'SALESMAN'  AS JOB, 7654 AS EMPNO, 'MARTIN' AS ENAME, 1250 AS SAL, 1400 AS BONUS FROM DUAL
)
SELECT DEPTNO, 
       JOB,
       SUM(SAL) AS SUM_SAL,              /* (부서 & 직함별) 급여 합 */
       SUM(CASE WHEN JOB = 'ANALYST'     /* (부서 & 직함별) 급여 합 : 행 => 열 변환 */
            THEN SAL
       END) AS SUM_SAL_ANALYST,
       SUM(CASE WHEN JOB = 'CLERK'        /* (부서 & 직함별) 급여 합 : 행 => 열 변환 */
                THEN SAL
       END) AS SUM_SAL_CLERK,
       SUM(CASE WHEN JOB = 'MANAGER'      /* (부서 & 직함별) 급여 합 : 행 => 열 변환 */
                THEN SAL
       END) AS SUM_SAL_MANAGER,
       SUM(CASE WHEN JOB = 'SALESMAN'     /* (부서 & 직함별) 급여 합 : 행 => 열 변환 */
       	        THEN SAL
       END) AS SUM_SAL_SALESMAN, 
       SUM(CASE WHEN JOB = 'PRESIDENT'    /* (부서 & 직함별) 급여 합 : 행 => 열 변환 */
                THEN SAL
       END) AS SUM_SAL_PRESIDENT,
       SUM(BONUS) AS SUM_BONUS,          /* (부서 & 직함별) 보너스 합 */
       COUNT(*) AS CNT                   /* (부서 & 직함별) 인원 수 */
FROM EMP 
GROUP BY DEPTNO, JOB
ORDER BY DEPTNO, JOB

WITH EMP AS
(
	...
    ...
    ...
)
SELECT DEPTNO, 
       JOB,
       SUM(SAL) AS SUM_SAL,             						 /* (부서 & 직함별) 급여 합 - NON-PIVOT */       
       SUM(DECODE(JOB, 'ANALYST', SAL)) AS SUM_SAL_ANALYST,      /* (부서 & 직함별) 급여 합 - PIVOT(DECODE) */
       SUM(DECODE(JOB, 'CLERK', SAL)) AS SUM_SAL_CLERK,          /* (부서 & 직함별) 급여 합 - PIVOT(DECODE) */
       SUM(DECODE(JOB, 'MANAGER', SAL)) AS SUM_SAL_MANAGER,      /* (부서 & 직함별) 급여 합 - PIVOT(DECODE) */
       SUM(DECODE(JOB, 'SALESMAN', SAL)) AS SUM_SAL_SALESMAN,    /* (부서 & 직함별) 급여 합 - PIVOT(DECODE) */
       SUM(DECODE(JOB, 'PRESIDENT', SAL)) AS SUM_SAL_PRESIDENT,  /* (부서 & 직함별) 급여 합 - PIVOT(DECODE) */
       SUM(BONUS) AS SUM_BONUS,          						 /* (부서 & 직함별) 보너스 합 - NON-PIVOT */
       COUNT(*) AS CNT                  						 /* (부서 & 직함별) 인원 수  - NON-PIVOT */
FROM EMP 
GROUP BY DEPTNO, JOB
ORDER BY DEPTNO, JOB

WITH EMP AS
(
   ...
   ...
   ...
)
SELECT DEPTNO, 
     --  JOB,   /* JOB을 그룹핑 제외 */
       SUM(SAL) AS SUM_SAL,              
       SUM(CASE WHEN JOB = 'ANALYST'    
            THEN SAL
       END) AS SUM_SAL_ANALYST,
          ...
          ...
          ...
       SUM(BONUS) AS SUM_BONUS,         
       COUNT(*) AS CNT                  
FROM EMP 
GROUP BY DEPTNO --,  JOB    /* JOB을 그룹핑 제외 */
ORDER BY DEPTNO --,  JOB    /* JOB을 그룹핑 제외 */

쿼리

SELECT GRADE       AS "학년",
       DEPTNO1     AS "학과",
       COUNT(*)    AS /* (학년 or 학과별) */ "인원 수",
       SUM(HEIGHT) AS /* (학년 or 학과별) */ "키의 합계",
       SUM(WEIGHT) AS /* (학년 or 학과별) */ "몸무게의 합계"
FROM STUDENT 
GROUP BY GROUPING SETS(GRADE, DEPTNO1)
ORDER BY GRADE, DEPTNO1

책 링크
booksr.co.kr/www.yes24.com/Product/Goods/46218018

SQL 공부를 위해 구입한 책

「SQL의 컨셉 for ORACLE」의 실습용 모델 ERD

영문으로 테이블과 컬럼을 생성한 후,  책에 제시된 한글로 COMMENT를 작성.

책 링크
「SQL의 컨셉 for ORACLE」

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

1번 테이블

SELECT 'A' T1_COL1 FROM DUAL UNION ALL
SELECT 'B' T1_COL1 FROM DUAL UNION ALL
SELECT 'B' T1_COL1 FROM DUAL UNION ALL
SELECT 'C' T1_COL1 FROM DUAL

2번 테이블

SELECT 'A' T2_COL1 FROM DUAL UNION ALL
SELECT 'B' T2_COL1 FROM DUAL UNION ALL
SELECT 'B' T2_COL1 FROM DUAL UNION ALL
SELECT 'B' T2_COL1 FROM DUAL UNION ALL
SELECT 'D' T2_COL1 FROM DUAL

1번 테이블과 2번테이블 INNER-JOIN

SELECT T1.T1_COL1, T2.T2_COL1
FROM ( SELECT 'A' T1_COL1 FROM DUAL UNION ALL
       SELECT 'B' T1_COL1 FROM DUAL UNION ALL
       SELECT 'B' T1_COL1 FROM DUAL UNION ALL
       SELECT 'C' T1_COL1 FROM DUAL ) T1,
     ( SELECT 'A' T2_COL1 FROM DUAL UNION ALL
       SELECT 'B' T2_COL1 FROM DUAL UNION ALL
       SELECT 'B' T2_COL1 FROM DUAL UNION ALL
       SELECT 'B' T2_COL1 FROM DUAL UNION ALL
       SELECT 'D' T2_COL1 FROM DUAL ) T2
WHERE T1.T1_COL1 = T2.T2_COL1

INNER-JOIN 결과

참조
「SQL BOOSTER」

O 업무 프로세스 
 - 고객은 주문을 할 수 있다.

 - 하나의 주문에 여러 개의 아이템을 동시에 주문할 수 있다.

 - 고객은 아이템에 좋고 나쁨을 평가 할 수 있다.

 - 아이템 단가는 이력을 이용해 관리하고 있다.

책 링크
「SQL BOOSTER」