데이터베이스 질문과 답변

국가별 유통권리 표시하는 쿼리 작성중 도움을 요청드립니다. 0 9 7,666

by 신이만든지기 [SQL Query] oracle 11g [2019.07.31 16:23:20]

안녕하세요. 오랜만에 방문하네요.

이번에 아래와 같은 요구사항이 발생하여 쿼리를 짜고 있는데, 결과테이블의 EXCLUDE_RIGHT 부분을 헤매고 있습니다. 게다가 하단의 작성중인 쿼리는 처리속도도 늦어 여러 고수님들의 도움을 구하고자 합니다.
더 나은 방법이 있다면 주저마시고 조언 부탁드립니다. (아래 코드 테이블과 유통권리 테이블은 샘플링한 테이블로 앨범은 약 5만개, 국가전체코드는 약 260개입니다.)

오라클 11G를 사용합니다.

ㅇ 설명
- 앨범별로 유통가능한 국가를 표기하는 것이 목표.
- 미주지역코드 : AMERICA_CODES, 아시아지역코드 : ASIA_CODES, 전체국가코드 : ALL_CODES 로 구분된 코드테이블이 존재하며, 
- 중국(CN)은 아시아지역코드에서 따로 빼냈으며, 전체국가코드에는 포함되어 있다. 전체국가코드에는 전세계를 의미하는 'WW'가 존재한다.
- 유통권한 테이블의 METHOD는 유통국가를 포함(INCLUDE)할 것인지 제외(EXCLUDE)할 것인지를 의미
- 1개의 앨범에는 1개 이상의 다수의 국가가 존재할 수 있으나, METHOD는 INCLUDE 또는 EXCLUDE 한 종류만 존재함(예컨데 41 앨범에는 INCLUDE만 존재하고, 50 앨범에는 EXCLUDE 만 존재할 수 있음)
- 39 앨범은 WW (전세계)에 앨범을 유통할 수 있다 =>모든 국가에서 유통가능, 미주권리 'O', 아시아 'O', 중국 'O'
- 40 앨범은 WW (전세계)에 앨범을 유통할 수 없다.=>모든국가에서 유통불가, 미주권리 'X', 아시아 'X', 중국 'X'
- 41 앨범은 WW (전세계), AR 국가에 앨범을 유통할 수 있다. =>모든국가에서 유통가능,  미주권리 'O', 아시아 'O', 중국 'O'
- 50 앨범은 WW (전세계), KR 국가에 앨범을 유통할 수 없다. =>모든국가에서 유통불가능, 미주권리 'X', 아시아 'X', 중국 'X'
- 61 앨범은 AR, HT, KR에서만 앨범을 유통할 수 있다. => 미주권리 'AR,HT', 아시아 'KR', 중국 'X'
- 70 앨범은 AR 에서만 앨범을 유통할 수 없다. '-'  표시는 해당 국가는 빼고 유통 할 수 있다는 의미다. => 미주권리 '-AR', 아시아 'O', 중국 'O'
- EXCLUDE_RIGHT는 유통 할 수 없는 모든 국가코드를 표시한다. 모든 국가에 유통할 수 없는 경우에도 국가코드를 모두 표시한다. WW 로 축약해도 무방, 국가코드의 정렬순서는 알파벳순서 또는 미정렬도 무방함


ㅇ 앨범테이블
 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE TABLE ALBUM
AS
    WITH
        ALBUM AS
            (SELECT '39' ALBUM_ID, '앨범명39' ALBUM_NAME FROM DUAL
             UNION ALL
             SELECT '40' ALBUM_ID, '앨범명40' ALBUM_NAME FROM DUAL
             UNION ALL
             SELECT '41' ALBUM_ID, '앨범명41' ALBUM_NAME FROM DUAL
             UNION ALL
             SELECT '50' ALBUM_ID, '앨범명50' ALBUM_NAME FROM DUAL
             UNION ALL
             SELECT '61' ALBUM_ID, '앨범명61' ALBUM_NAME FROM DUAL
             UNION ALL
             SELECT '70' ALBUM_ID, '앨범명70' ALBUM_NAME FROM DUAL
             UNION ALL
             SELECT '71' ALBUM_ID, '앨범명71' ALBUM_NAME FROM DUAL
             UNION ALL
             SELECT '80' ALBUM_ID, '앨범명80' ALBUM_NAME FROM DUAL
             UNION ALL
             SELECT '90' ALBUM_ID, '앨범명90' ALBUM_NAME FROM DUAL
             UNION ALL
             SELECT '91' ALBUM_ID, '앨범명91' ALBUM_NAME FROM DUAL)
    SELECT *
      FROM ALBUM;


ㅇ 코드 테이블
 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
CREATE TABLE CODE AS
WITH
    CODE AS
        (SELECT 'AMERICA_CODES' CODE_ID, 'AR' CODE_VAL FROM DUAL
         UNION ALL
         SELECT 'AMERICA_CODES' CODE_ID, 'HT' CODE_VAL FROM DUAL
         UNION ALL
         SELECT 'AMERICA_CODES' CODE_ID, 'US' CODE_VAL FROM DUAL
         UNION ALL
         SELECT 'AMERICA_CODES' CODE_ID, 'AI' CODE_VAL FROM DUAL
         UNION ALL
         SELECT 'ASIA_CODES' CODE_ID, 'KR' CODE_VAL FROM DUAL
         UNION ALL
         SELECT 'ASIA_CODES' CODE_ID, 'VN' CODE_VAL FROM DUAL
         UNION ALL
         SELECT 'ASIA_CODES' CODE_ID, 'PH' CODE_VAL FROM DUAL
         UNION ALL
         SELECT 'ASIA_CODES' CODE_ID, 'HK' CODE_VAL FROM DUAL
     UNION ALL
     SELECT 'ALL_CODES' CODE_ID, 'AR' CODE_VAL FROM DUAL
         UNION ALL
         SELECT 'ALL_CODES' CODE_ID, 'HT' CODE_VAL FROM DUAL
         UNION ALL
         SELECT 'ALL_CODES' CODE_ID, 'US' CODE_VAL FROM DUAL
         UNION ALL
         SELECT 'ALL_CODES' CODE_ID, 'AI' CODE_VAL FROM DUAL
         UNION ALL
         SELECT 'ALL_CODES' CODE_ID, 'KR' CODE_VAL FROM DUAL
         UNION ALL
         SELECT 'ALL_CODES' CODE_ID, 'VN' CODE_VAL FROM DUAL
         UNION ALL
         SELECT 'ALL_CODES' CODE_ID, 'PH' CODE_VAL FROM DUAL
         UNION ALL
         SELECT 'ALL_CODES' CODE_ID, 'HK' CODE_VAL FROM DUAL
         UNION ALL
         SELECT 'ALL_CODES' CODE_ID, 'CN' CODE_VAL FROM DUAL
         UNION ALL
         SELECT 'ALL_CODES' CODE_ID, 'WW' CODE_VAL FROM DUAL)
SELECT *
  FROM CODE;


ㅇ 유통권리 테이블
 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
CREATE TABLE ALBUM_RIGHT AS
WITH
    ALBUM_RIGHT AS
        (SELECT '39' ALBUM_ID, 'INCLUDE' METHOD, 'WW' CODE_VAL FROM DUAL
         UNION ALL
         SELECT '40' ALBUM_ID, 'EXCLUDE' METHOD, 'WW' CODE_VAL FROM DUAL
         UNION ALL
         SELECT '41' ALBUM_ID, 'INCLUDE' METHOD, 'WW' CODE_VAL FROM DUAL
         UNION ALL
         SELECT '41' ALBUM_ID, 'INCLUDE' METHOD, 'AR' CODE_VAL FROM DUAL
         UNION ALL
         SELECT '50' ALBUM_ID, 'EXCLUDE' METHOD, 'WW' CODE_VAL FROM DUAL
         UNION ALL
         SELECT '50' ALBUM_ID, 'EXCLUDE' METHOD, 'KR' CODE_VAL FROM DUAL
         UNION ALL
         SELECT '61' ALBUM_ID, 'INCLUDE' METHOD, 'AR' CODE_VAL FROM DUAL
         UNION ALL
         SELECT '61' ALBUM_ID, 'INCLUDE' METHOD, 'HT' CODE_VAL FROM DUAL
         UNION ALL
         SELECT '61' ALBUM_ID, 'INCLUDE' METHOD, 'KR' CODE_VAL FROM DUAL
         UNION ALL
         SELECT '70' ALBUM_ID, 'EXCLUDE' METHOD, 'AR' CODE_VAL FROM DUAL
         UNION ALL
         SELECT '71' ALBUM_ID, 'EXCLUDE' METHOD, 'KR' CODE_VAL FROM DUAL
         UNION ALL
         SELECT '80' ALBUM_ID, 'EXCLUDE' METHOD, 'US' CODE_VAL FROM DUAL
         UNION ALL
         SELECT '80' ALBUM_ID, 'EXCLUDE' METHOD, 'AI' CODE_VAL FROM DUAL
         UNION ALL
         SELECT '90' ALBUM_ID, 'EXCLUDE' METHOD, 'AR' CODE_VAL FROM DUAL
         UNION ALL
         SELECT '90' ALBUM_ID, 'EXCLUDE' METHOD, 'HT' CODE_VAL FROM DUAL
         UNION ALL
         SELECT '90' ALBUM_ID, 'EXCLUDE' METHOD, 'US' CODE_VAL FROM DUAL
         UNION ALL
         SELECT '91' ALBUM_ID, 'EXCLUDE' METHOD, 'CN' CODE_VAL FROM DUAL
)
SELECT *
  FROM ALBUM_RIGHT;


ㅇ 희망하는 결과 테이블

ALBUM_ID ALBUM_NAME AMERICA_RIGHT ASIA_RIGHT CHINA_RIGHT EXCLUDE_RIGHT
39 앨범명39       O O O 　
40 앨범명40       X X X AR,HT,US,AI,KR,VN,PH,HK,CN
41 앨범명41       O O O 　
50 앨범명50       X X X AR,HT,US,AI,KR,VN,PH,HK,CN
61 앨범명61       AR,HT KR X US,AI,VN,PH,HK,CN
70 앨범명70       -AR O O AR
71 앨범명71       O -KR O KR
80 앨범명80       -AI,-US O O AI,US
90 앨범명90       -AR,-HT,-US O O AR,HT,US
91 앨범명91       O O X CN

 

ㅇ 본인이 작성중인 쿼리(아직 EXCLUDE_RIGHT 미완성 ㅠ_ㅠ)

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
SELECT ALBUM_ID
     , METHOD
     , CASE
           WHEN WORLD = 'WW' THEN 'O'
           WHEN WORLD = '-WW' THEN 'X'
           ELSE NVL(AMERICA, DECODE(METHOD, 'INCLUDE', 'X', 'O'))
       END
           AMERICA
     , CASE
           WHEN WORLD = 'WW' THEN 'O'
           WHEN WORLD = '-WW' THEN 'X'
           ELSE NVL(ASIA, DECODE(METHOD, 'INCLUDE', 'X', 'O'))
       END
           ASIA
     , CASE
           WHEN WORLD = 'WW' THEN 'O'
           WHEN WORLD = '-WW' THEN 'X'
           WHEN CHINA = 'CN' THEN 'O'
           WHEN CHINA = '-CN' THEN 'X'
           ELSE DECODE(METHOD, 'INCLUDE', 'X', 'O')
       END
           CHINA
  FROM (  SELECT ALBUM_ID
               , METHOD
               , LISTAGG(AMERICA, ',') WITHIN GROUP (ORDER BY CODE_VAL) AMERICA
               , LISTAGG(ASIA, ',') WITHIN GROUP (ORDER BY CODE_VAL) ASIA
               , MAX(WORLD) WORLD
               , MAX(CHINA) CHINA
            FROM (
                     SELECT ALBUM_ID
                          , CODE_VAL
                          , METHOD
                          , CASE
                                WHEN CODE_VAL = 'WW' THEN DECODE(METHOD, 'EXCLUDE', '-') || CODE_VAL
                            END
                                WORLD
                          , CASE
                                WHEN CODE_VAL IN (SELECT CODE_VAL
                                                    FROM CODE
                                                   WHERE CODE_ID = 'AMERICA_CODES') THEN
                                    DECODE(METHOD, 'EXCLUDE', '-') || CODE_VAL
                            END
                                AMERICA
                          , CASE
                                WHEN CODE_VAL IN (SELECT CODE_VAL
                                                    FROM CODE
                                                   WHERE CODE_ID = 'ASIA_CODES') THEN
                                    DECODE(METHOD, 'EXCLUDE', '-') || CODE_VAL
                            END
                                ASIA
                          , CASE
                                WHEN CODE_VAL = 'CN' THEN DECODE(METHOD, 'EXCLUDE', '-') || CODE_VAL
                            END
                                CHINA
                       FROM ALBUM_RIGHT
                 )
        GROUP BY ALBUM_ID
               , METHOD)

 

by 신이만든지기 [2019.07.31 16:33:16]

최대한 자세하게 설명하려고 노력하였는데, 이해가 안가는 부분이 있으면 댓글 달아주세요.

by 생각 [2019.08.01 15:29:54]

뻘글이지만 PK를 코드명으로 하고, 기존 코드를 코드명으로 하면 좋았을것 같다는 생각이 들어요.

WW 는 공용으로 쓰이는데, AMERIAC에는 없고 ALLCODE에만 있는게 좀...

by 신이만든지기 [2019.08.05 09:22:26]

조회시점에 실시간으로 계산하지 않고, 데이터를 넣는 시점에 권리를 계산해서 저장하는 것으로 방향을 선회하였습니다.

by jkson [2019.08.05 10:38:32]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
WITH
    ALBUM AS
        (SELECT '39' ALBUM_ID, '앨범명39' ALBUM_NAME FROM DUAL
         UNION ALL
         SELECT '40' ALBUM_ID, '앨범명40' ALBUM_NAME FROM DUAL
         UNION ALL
         SELECT '41' ALBUM_ID, '앨범명41' ALBUM_NAME FROM DUAL
         UNION ALL
         SELECT '50' ALBUM_ID, '앨범명50' ALBUM_NAME FROM DUAL
         UNION ALL
         SELECT '61' ALBUM_ID, '앨범명61' ALBUM_NAME FROM DUAL
         UNION ALL
         SELECT '70' ALBUM_ID, '앨범명70' ALBUM_NAME FROM DUAL
         UNION ALL
         SELECT '71' ALBUM_ID, '앨범명71' ALBUM_NAME FROM DUAL
         UNION ALL
         SELECT '80' ALBUM_ID, '앨범명80' ALBUM_NAME FROM DUAL
         UNION ALL
         SELECT '90' ALBUM_ID, '앨범명90' ALBUM_NAME FROM DUAL
         UNION ALL
         SELECT '91' ALBUM_ID, '앨범명91' ALBUM_NAME FROM DUAL),
    CODE AS
        (SELECT 'AMERICA_CODES' CODE_ID, 'AR' CODE_VAL FROM DUAL
         UNION ALL
         SELECT 'AMERICA_CODES' CODE_ID, 'HT' CODE_VAL FROM DUAL
         UNION ALL
         SELECT 'AMERICA_CODES' CODE_ID, 'US' CODE_VAL FROM DUAL
         UNION ALL
         SELECT 'AMERICA_CODES' CODE_ID, 'AI' CODE_VAL FROM DUAL
         UNION ALL
         SELECT 'ASIA_CODES' CODE_ID, 'KR' CODE_VAL FROM DUAL
         UNION ALL
         SELECT 'ASIA_CODES' CODE_ID, 'VN' CODE_VAL FROM DUAL
         UNION ALL
         SELECT 'ASIA_CODES' CODE_ID, 'PH' CODE_VAL FROM DUAL
         UNION ALL
         SELECT 'ASIA_CODES' CODE_ID, 'HK' CODE_VAL FROM DUAL
         UNION ALL
         SELECT 'ALL_CODES' CODE_ID, 'AR' CODE_VAL FROM DUAL
         UNION ALL
         SELECT 'ALL_CODES' CODE_ID, 'HT' CODE_VAL FROM DUAL
         UNION ALL
         SELECT 'ALL_CODES' CODE_ID, 'US' CODE_VAL FROM DUAL
         UNION ALL
         SELECT 'ALL_CODES' CODE_ID, 'AI' CODE_VAL FROM DUAL
         UNION ALL
         SELECT 'ALL_CODES' CODE_ID, 'KR' CODE_VAL FROM DUAL
         UNION ALL
         SELECT 'ALL_CODES' CODE_ID, 'VN' CODE_VAL FROM DUAL
         UNION ALL
         SELECT 'ALL_CODES' CODE_ID, 'PH' CODE_VAL FROM DUAL
         UNION ALL
         SELECT 'ALL_CODES' CODE_ID, 'HK' CODE_VAL FROM DUAL
         UNION ALL
         SELECT 'ALL_CODES' CODE_ID, 'CN' CODE_VAL FROM DUAL
         UNION ALL
         SELECT 'ALL_CODES' CODE_ID, 'WW' CODE_VAL FROM DUAL),
ALBUM_RIGHT AS
        (SELECT '39' ALBUM_ID, 'INCLUDE' METHOD, 'WW' CODE_VAL FROM DUAL
         UNION ALL
         SELECT '40' ALBUM_ID, 'EXCLUDE' METHOD, 'WW' CODE_VAL FROM DUAL
         UNION ALL
         SELECT '41' ALBUM_ID, 'INCLUDE' METHOD, 'WW' CODE_VAL FROM DUAL
         UNION ALL
         SELECT '41' ALBUM_ID, 'INCLUDE' METHOD, 'AR' CODE_VAL FROM DUAL
         UNION ALL
         SELECT '50' ALBUM_ID, 'EXCLUDE' METHOD, 'WW' CODE_VAL FROM DUAL
         UNION ALL
         SELECT '50' ALBUM_ID, 'EXCLUDE' METHOD, 'KR' CODE_VAL FROM DUAL
         UNION ALL
         SELECT '61' ALBUM_ID, 'INCLUDE' METHOD, 'AR' CODE_VAL FROM DUAL
         UNION ALL
         SELECT '61' ALBUM_ID, 'INCLUDE' METHOD, 'HT' CODE_VAL FROM DUAL
         UNION ALL
         SELECT '61' ALBUM_ID, 'INCLUDE' METHOD, 'KR' CODE_VAL FROM DUAL
         UNION ALL
         SELECT '70' ALBUM_ID, 'EXCLUDE' METHOD, 'AR' CODE_VAL FROM DUAL
         UNION ALL
         SELECT '71' ALBUM_ID, 'EXCLUDE' METHOD, 'KR' CODE_VAL FROM DUAL
         UNION ALL
         SELECT '80' ALBUM_ID, 'EXCLUDE' METHOD, 'US' CODE_VAL FROM DUAL
         UNION ALL
         SELECT '80' ALBUM_ID, 'EXCLUDE' METHOD, 'AI' CODE_VAL FROM DUAL
         UNION ALL
         SELECT '90' ALBUM_ID, 'EXCLUDE' METHOD, 'AR' CODE_VAL FROM DUAL
         UNION ALL
         SELECT '90' ALBUM_ID, 'EXCLUDE' METHOD, 'HT' CODE_VAL FROM DUAL
         UNION ALL
         SELECT '90' ALBUM_ID, 'EXCLUDE' METHOD, 'US' CODE_VAL FROM DUAL
         UNION ALL
         SELECT '91' ALBUM_ID, 'EXCLUDE' METHOD, 'CN' CODE_VAL FROM DUAL
),
CODES AS -- ALL_CODES 관련 수정해야하지 않을지요.
        (SELECT CODE_ID, CODE_VAL
           FROM CODE
          WHERE CODE_ID != 'ALL_CODES'
         UNION ALL
         SELECT 'ALL_CODES', 'WW'
           FROM DUAL
         UNION ALL
         SELECT 'CHINA', 'CN' FROM DUAL)
 
SELECT ALBUM_ID, AMERICA, ASIA, CHINA,
       REGEXP_REPLACE(
       CASE
         WHEN AMERICA || ASIA || CHINA = 'OOO' THEN NULL
         WHEN AMERICA || ASIA || CHINA = 'XXX' THEN D.CODES
         WHEN METHOD = 'EXCLUDE' THEN
          REPLACE(DECODE(AMERICA, 'O', NULL, 'X', (SELECT LISTAGG(CODE_VAL, ',') WITHIN GROUP(ORDER BY CODE_VAL) FROM CODES WHERE CODE_ID = 'AMERICA_CODES'), AMERICA) ||
                  DECODE(ASIA, 'O', NULL, 'X', (SELECT LISTAGG(CODE_VAL, ',') WITHIN GROUP(ORDER BY CODE_VAL) FROM CODES WHERE CODE_ID = 'ASIA_CODES'), ASIA) ||
                  DECODE(CHINA, 'O', NULL, 'X', (SELECT LISTAGG(CODE_VAL, ',') WITHIN GROUP(ORDER BY CODE_VAL) FROM CODES WHERE CODE_ID = 'CHINA'), CHINA), '-', '')
         ELSE
          REGEXP_REPLACE(D.CODES,
                         REPLACE(AMERICA || '|' || ASIA || '|' || CHINA,',','|'))
       END,',+',',') EXCLUDE_RIGHT
  FROM (SELECT ALBUM_ID,
               NVL(MAX(AMERICA), DECODE(MAX(METHOD), 'EXCLUDE', 'O', 'X')) AMERICA,
               NVL(MAX(ASIA), DECODE(MAX(METHOD), 'EXCLUDE', 'O', 'X')) ASIA,
               NVL(MAX(CHINA), DECODE(MAX(METHOD), 'EXCLUDE', 'O', 'X')) CHINA,
               MAX(METHOD) METHOD
          FROM (SELECT CASE
                         WHEN METHOD = 'INCLUDE' AND INSTR(CODES2, 'WW') > 0 THEN 'O'
                         WHEN METHOD = 'EXCLUDE' AND INSTR(CODES2, 'WW') > 0 THEN 'X'
                         WHEN METHOD = 'INCLUDE' AND A.CODE_ID = 'AMERICA_CODES' AND CNT1 = CNT2 THEN 'O'
                         WHEN METHOD = 'EXCLUDE' AND A.CODE_ID = 'AMERICA_CODES' AND CNT1 = CNT2 THEN 'X'
                         WHEN METHOD = 'INCLUDE' AND A.CODE_ID = 'AMERICA_CODES' THEN CODES2
                         WHEN METHOD = 'EXCLUDE' AND A.CODE_ID = 'AMERICA_CODES' THEN CODES2
                       END AMERICA,
                       CASE
                         WHEN METHOD = 'INCLUDE' AND INSTR(CODES2, 'WW') > 0 THEN 'O'
                         WHEN METHOD = 'EXCLUDE' AND INSTR(CODES2, 'WW') > 0 THEN 'X'
                         WHEN METHOD = 'INCLUDE' AND A.CODE_ID = 'ASIA_CODES' AND CNT1 = CNT2 THEN 'O'
                         WHEN METHOD = 'EXCLUDE' AND A.CODE_ID = 'ASIA_CODES' AND CNT1 = CNT2 THEN 'X'
                         WHEN METHOD = 'INCLUDE' AND A.CODE_ID = 'ASIA_CODES' THEN CODES2
                         WHEN METHOD = 'EXCLUDE' AND A.CODE_ID = 'ASIA_CODES' THEN CODES2
                       END ASIA,
                       CASE
                         WHEN METHOD = 'INCLUDE' AND INSTR(CODES2, 'WW') > 0 THEN 'O'
                         WHEN METHOD = 'EXCLUDE' AND INSTR(CODES2, 'WW') > 0 THEN 'X'
                         WHEN METHOD = 'INCLUDE' AND A.CODE_ID = 'CHINA' AND CNT1 = CNT2 THEN 'O'
                         WHEN METHOD = 'EXCLUDE' AND A.CODE_ID = 'CHINA' AND CNT1 = CNT2 THEN 'X'
                         WHEN METHOD = 'INCLUDE' AND A.CODE_ID = 'CHINA' THEN CODES2
                         WHEN METHOD = 'EXCLUDE' AND A.CODE_ID = 'CHINA' THEN CODES2
                       END CHINA,
                       A.*
                  FROM (SELECT ALBUM_ID, B.CODE_ID, A.METHOD,
                               LISTAGG(DECODE(A.METHOD, 'EXCLUDE', '-') ||
                                       A.CODE_VAL,
                                       ',') WITHIN GROUP(ORDER BY A.CODE_VAL) CODES2,
                               COUNT(DISTINCT A.CODE_VAL) CNT2
                          FROM ALBUM_RIGHT A, CODES B
                         WHERE A.CODE_VAL = B.CODE_VAL
                         GROUP BY ALBUM_ID, B.CODE_ID, A.METHOD) A,
                       (SELECT CODE_ID,
                               LISTAGG(CODE_VAL, ',') WITHIN GROUP(ORDER BY CODE_VAL) CODES1,
                               COUNT(DISTINCT CODE_VAL) CNT1
                          FROM CODES
                         GROUP BY CODE_ID) B
                 WHERE A.CODE_ID = B.CODE_ID)
         GROUP BY ALBUM_ID) C,
       (SELECT LISTAGG(CODE_VAL, ',') WITHIN GROUP(ORDER BY CODE_VAL) CODES
          FROM CODES
         WHERE CODE_ID != 'ALL_CODES') D
 ORDER BY ALBUM_ID

각 행마다 code 테이블에 접근하는 것 때문에 성능저하가 있지 않나해서

바꿔보았습니다.

code에 all_codes는  'ww'만 남기는 게 효율적이지 않나 생각합니다.

'cn'은 따로 code_id를 따구요.

 

by 신이만든지기 [2019.08.06 09:18:55]

데이터를 입력하는 단계에서 계산해 넣는 방향으로 접근방법을 변경하였습니다만,

어려운 문제인데도 시간을 내어 답변해주신 것에 진심으로 감사드립니다. 

by 야신 [2019.08.11 16:51:12]

질문과는 상관없는데 예전에 신이만든짝퉁님과는 관계가 어떻게 되시나요? ^^;;

by 신이만든지기 [2019.08.13 09:36:24]

그놈이 접니다. ^^;

by 마농 [2019.08.13 08:04:01]

1. 국가 코드테이블은
 - 국가가 유니크하게 조정되면 좋을 듯.
 - 국가코드를 PK 로 지역 구분항목 추가
2. album_right 테이블은
 - 모순된 자료나 중복된 자료는 아예 입력 불가하도록 제약을 걸어야 함.
 - 예시자료에 모순된 자료는 보이지 않지만 중복된 자료는 보이고 있음.
 - 중복자료 41, 50

by 신이만든지기 [2019.08.13 09:37:26]

네. 조언 감사합니다. ^^

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입