a table idx gubun name value xx221111 gid020 aaaaaa 200 ee312211 gid020 bbbbbb 300 ww322211 gid120 cccccc 300 ww322211 gid120 dddddd 900 xx711111 gid320 aaaaaa 700 ew122227 gid720 ffffff 100 b table idx gubun name value xx711111 gid320 aaaaaa 700 ee712221 gid320 bbbbbb 800 ew714441 gid320 null null ww736661 gid420 cccccc 2200 ww328881 gid420 dddddd 2300 ew722121 gid420 null null ew122227 gid720 null null ew122226 gid720 eeeee 300 ew122223 gid720 null null ew122222 gid720 xxxxx 100 ew122223 gid720 null null a 테이블과 b 테이블 전체에서 gubun 값이 같은 것중 value 값이 가장 큰 값이 같은 gubun row중 null 인 값이 되어야 하는데요. (추가) 이때 gubun 값은 중복 될 수 있으며 gubun 값별 value의 max 값이 name과 value 가 됩니다. 즉 아래와 같이 되야 합니다. b테이블 내용 xx711111 gid320 aaaaaa 700 ee712221 gid320 bbbbbb 800 ew714441 gid320 bbbbbb 800 ww736661 gid420 cccccc 2200 ww328881 gid420 dddddd 2300 ew722121 gid420 dddddd 2300 ew122227 gid720 ffffff 100 ew122226 gid720 eeeee 300 ew122223 gid720 eeeee 300 ew122222 gid720 xxxxx 100 ew122223 gid720 xxxxx 100
DB는 postgreSQL에서 실행했습니다.
WITH TEST1 (IDX, GUBUN ,NAME,VALUE) AS ( SELECT 'xx221111', 'gid020', 'aaaaaa', '200' UNION ALL SELECT 'ee312211', 'gid020', 'bbbbbb', '300' UNION ALL SELECT 'ww322211', 'gid120', 'cccccc', '300' UNION ALL SELECT 'ww322211', 'gid120', 'dddddd', '900' UNION ALL SELECT 'xx711111', 'gid320', 'aaaaaa', '700' UNION ALL SELECT 'ew122227', 'gid720', 'ffffff', '100' ) ,TEST2 (IDX, GUBUN ,NAME,VALUE) AS ( SELECT 'xx711111' ,'gid320' ,'aaaaaa' ,'700' UNION ALL SELECT 'ee712221' ,'gid320' ,'bbbbbb' ,'800' UNION ALL SELECT 'ew714441' ,'gid320' ,null , null UNION ALL SELECT 'ww736661' ,'gid420' ,'cccccc' ,'2200' UNION ALL SELECT 'ww328881' ,'gid420' ,'dddddd' ,'2300' UNION ALL SELECT 'ew722121' ,'gid420' ,null , null UNION ALL SELECT 'ew122227' ,'gid720' ,null , null ) ,TEST3 (GUBUN ,NAME,VALUE) AS ( SELECT GUBUN,MAX(NAME),MAX(VALUE) FROM ( SELECT GUBUN ,NAME,VALUE FROM TEST1 A UNION ALL SELECT GUBUN ,NAME,VALUE FROM TEST2 B )AA GROUP BY GUBUN ) SELECT B.IDX, B.GUBUN ,CASE WHEN B.NAME IS NULL THEN (SELECT C.NAME FROM TEST3 C WHERE B.GUBUN=C.GUBUN ) ELSE B.NAME END AS NAME ,CASE WHEN B.VALUE IS NULL THEN (SELECT C.VALUE FROM TEST3 C WHERE B.GUBUN=C.GUBUN ) ELSE B.VALUE END AS VALUE FROM TEST2 B
--DB는 postgreSQL에서 실행했습니다. WITH TEST1 (IDX, GUBUN ,NAME,VALUE) AS ( SELECT 'xx221111', 'gid020', 'aaaaaa', '200' UNION ALL SELECT 'ee312211', 'gid020', 'bbbbbb', '300' UNION ALL SELECT 'ww322211', 'gid120', 'cccccc', '300' UNION ALL SELECT 'ww322211', 'gid120', 'dddddd', '900' UNION ALL SELECT 'xx711111', 'gid320', 'aaaaaa', '700' UNION ALL SELECT 'ew122227', 'gid720', 'ffffff', '100' ) ,TEST2 (IDX, GUBUN ,NAME,VALUE) AS ( SELECT 'xx711111' ,'gid320' ,'aaaaaa' ,'700' UNION ALL SELECT 'ee712221' ,'gid320' ,'bbbbbb' ,'800' UNION ALL SELECT 'ew714441' ,'gid320' ,null , null UNION ALL SELECT 'ww736661' ,'gid420' ,'cccccc' ,'2200' UNION ALL SELECT 'ww328881' ,'gid420' ,'dddddd' ,'2300' UNION ALL SELECT 'ew722121' ,'gid420' ,null , null UNION ALL SELECT 'ew122227' ,'gid720' ,null , null UNION ALL SELECT 'ew122226' ,'gid720' ,'eeeee' , '300' UNION ALL SELECT 'ew122223' ,'gid720' ,null , null UNION ALL SELECT 'ew122222' ,'gid720' ,'xxxxx' , '100' UNION ALL SELECT 'ew122223' ,'gid720' ,null , null ) ,TEST3 (GUBUN ,NAME,VALUE) AS ( SELECT GUBUN,MAX(NAME),MAX(VALUE) FROM ( SELECT GUBUN ,NAME,VALUE FROM TEST1 A UNION ALL SELECT GUBUN ,NAME,VALUE FROM TEST2 B )AA GROUP BY GUBUN ) SELECT B.IDX, B.GUBUN ,CASE WHEN B.NAME IS NULL THEN (SELECT C.NAME FROM TEST3 C WHERE B.GUBUN=C.GUBUN ) ELSE B.NAME END AS NAME ,CASE WHEN B.VALUE IS NULL THEN (SELECT C.VALUE FROM TEST3 C WHERE B.GUBUN=C.GUBUN ) ELSE B.VALUE END AS VALUE FROM TEST2 B GROUP BY IDX, GUBUN ,NAME,VALUE ORDER BY GUBUN ,NAME,VALUE --결과 : IDX | GUBUN | NAME | VALUE xx711111 gid320 aaaaaa 700 ew714441 gid320 bbbbbb 800 ee712221 gid320 bbbbbb 800 ww736661 gid420 cccccc 2200 ew722121 gid420 dddddd 2300 ww328881 gid420 dddddd 2300 ew122226 gid720 eeeee 300 ew122222 gid720 xxxxx 100 ew122227 gid720 xxxxx 300 ew122223 gid720 xxxxx 300
우선 우문현답 감사합니다. 아래 다시 최종(?) 정리해서 말씀 드려봅니다. 다시한번 애매한 질문으로 죄송합니다. (__ a table idx id no gid name value gvalue VSBXK_20180927123956492 idfoo 17 gid001023 namefa6c020b1 1537197279 1537197279 9VQYX_20180927123957117 idfoo 17 gid001023 null null 1537197282 LX8UX_20180927123956197 idfoo 17 gid001023 namefb630d32f 1537197282 1537197282 b table idx id no gid name value gvalue 7NNVP_20180927123956656 idfoo 17 gid001023 null null 1537197283 L9SXV_20180927123956686 idfoo 17 gid001023 name39e181783 1537197283 1537197283 ETL9E_20180927123956495 idfoo 17 gid001023 name27cf51fcc 1537197285 1537197285 T9BCS_20180927123957573 idfoo 17 gid001396 name040c4fc75 1537197519 1537197519 LHZKG_20180927123956455 idfoo 17 gid001396 name8ad4505da 1537197531 1537197531 GNKWB_20180927123956197 idfoo 17 gid001396 null null 1537197532 WTVWN_20180927123956198 idfoo 17 gid001396 named4932534f 1537197533 1537197533 MFQTT_20180927123957339 idfoo 17 gid001396 null null 1537197534 BE2RM_20180927123956200 idfoo 17 gid001396 name752ee98ce 1537197535 1537197535 알아보기 쉽게 a, b 테이블을 정렬을 한것인데요. 핵심은 id, no, gid 그룹기준으로 null 값을 a, b 테이블에서 찾아서 넣어주는 것이고 넣어주는 기준은 id, no, gid가 같은 그룹중 value 가 null이 아닌 row 중 "gvalue 값보다 작은 row 중에서 가장 큰 값의 row 가 해당 null 값"이 됩니다. 즉 아래와 같이 되면 되는데요. 앞서 질문에서는 gvalue 내용이 빠져서 알맹이 없는 질문을 드렸네요. (사실 이 부분이 가장 헷갈리는 내용이라서요) 원하는 결과 result VSBXK_20180927123956492 idfoo 17 gid001023 namefa6c020b1 1537197279 1537197279 9VQYX_20180927123957117 idfoo 17 gid001023 namefa6c020b1 1537197279 1537197282 LX8UX_20180927123956197 idfoo 17 gid001023 namefb630d32f 1537197282 1537197282 7NNVP_20180927123956656 idfoo 17 gid001023 namefb630d32f 1537197282 1537197283 L9SXV_20180927123956686 idfoo 17 gid001023 name39e181783 1537197283 1537197283 ETL9E_20180927123956495 idfoo 17 gid001023 name27cf51fcc 1537197285 1537197285 T9BCS_20180927123957573 idfoo 17 gid001396 name040c4fc75 1537197519 1537197519 LHZKG_20180927123956455 idfoo 17 gid001396 name8ad4505da 1537197531 1537197531 GNKWB_20180927123956197 idfoo 17 gid001396 name8ad4505da 1537197531 1537197532 WTVWN_20180927123956198 idfoo 17 gid001396 named4932534f 1537197533 1537197533 MFQTT_20180927123957339 idfoo 17 gid001396 named4932534f 1537197533 1537197534 BE2RM_20180927123956200 idfoo 17 gid001396 name752ee98ce 1537197535 1537197535 제가 해본 것으로는 우선 value 가 is not null 인 row 를 모두 추출해서 조인을 해서 어케 하려고 해보니 gvalue의 "gvalue 값보다 작은 row 중에서 가장 큰 값의 row 가 해당 null 값" 이 내용을 당췌 처리 하기가 힘드네요. 염치 없지만 도움 부탁 드려봅니다.
WITH t_a (idx, id, no, gid, name, value, gvalue) AS ( SELECT 'VSBXK_20180927123956492', 'idfoo', 17, 'gid001023', 'namefa6c020b1', '1537197279', '1537197279' FROM dual UNION ALL SELECT '9VQYX_20180927123957117', 'idfoo', 17, 'gid001023', null , null , '1537197282' FROM dual UNION ALL SELECT 'LX8UX_20180927123956197', 'idfoo', 17, 'gid001023', 'namefb630d32f', '1537197282', '1537197282' FROM dual ) , t_b (idx, id, no, gid, name, value, gvalue) AS ( SELECT '7NNVP_20180927123956656', 'idfoo', 17, 'gid001023', null , null , '1537197283' FROM dual UNION ALL SELECT 'L9SXV_20180927123956686', 'idfoo', 17, 'gid001023', 'name39e181783', '1537197283', '1537197283' FROM dual UNION ALL SELECT 'ETL9E_20180927123956495', 'idfoo', 17, 'gid001023', 'name27cf51fcc', '1537197285', '1537197285' FROM dual UNION ALL SELECT 'T9BCS_20180927123957573', 'idfoo', 17, 'gid001396', 'name040c4fc75', '1537197519', '1537197519' FROM dual UNION ALL SELECT 'LHZKG_20180927123956455', 'idfoo', 17, 'gid001396', 'name8ad4505da', '1537197531', '1537197531' FROM dual UNION ALL SELECT 'GNKWB_20180927123956197', 'idfoo', 17, 'gid001396', null , null , '1537197532' FROM dual UNION ALL SELECT 'WTVWN_20180927123956198', 'idfoo', 17, 'gid001396', 'named4932534f', '1537197533', '1537197533' FROM dual UNION ALL SELECT 'MFQTT_20180927123957339', 'idfoo', 17, 'gid001396', null , null , '1537197534' FROM dual UNION ALL SELECT 'BE2RM_20180927123956200', 'idfoo', 17, 'gid001396', 'name752ee98ce', '1537197535', '1537197535' FROM dual ) , t_c AS ( SELECT * FROM t_a UNION ALL SELECT * FROM t_b ) SELECT idx , id , no , gid , LAST_VALUE(name ) IGNORE NULLS OVER(PARTITION BY id, no, gid ORDER BY gvalue, value NULLS FIRST) name , LAST_VALUE(value) IGNORE NULLS OVER(PARTITION BY id, no, gid ORDER BY gvalue, value NULLS FIRST) value , gvalue FROM t_c ;
--DB는 postgreSQL에서 실행했습니다. WITH DATA_T1 (IDX, GUBUN ,NAME,VALUE,GVALUE) AS ( SELECT 'VSBXK_20180927123956492', 'gid001023', 'namefa6c020b1', '1537197279','1537197279' UNION ALL SELECT '9VQYX_20180927123957117', 'gid001023', null , null ,'1537197282' UNION ALL SELECT 'LX8UX_20180927123956197', 'gid001023', 'namefb630d32f', '1537197282','1537197282' ) ,DATA_T2 (IDX, GUBUN ,NAME,VALUE,GVALUE) AS ( SELECT '7NNVP_20180927123956656' ,'gid001023' ,null ,null ,'1537197283' UNION ALL SELECT 'L9SXV_20180927123956686' ,'gid001023' ,'name39e181783' ,'1537197283' ,'1537197283' UNION ALL SELECT 'ETL9E_20180927123956495' ,'gid001023' ,'name27cf51fcc' ,'1537197285' ,'1537197285' UNION ALL SELECT 'T9BCS_20180927123957573' ,'gid001396' ,'name040c4fc75' ,'1537197519' ,'1537197519' UNION ALL SELECT 'LHZKG_20180927123956455' ,'gid001396' ,'name8ad4505da' ,'1537197531' ,'1537197531' UNION ALL SELECT 'GNKWB_20180927123956197' ,'gid001396' ,null ,null ,'1537197532' UNION ALL SELECT 'WTVWN_20180927123956198' ,'gid001396' ,'named4932534f' ,'1537197533' ,'1537197533' UNION ALL SELECT 'MFQTT_20180927123957339' ,'gid001396' ,null ,null ,'1537197534' UNION ALL SELECT 'BE2RM_20180927123956200' ,'gid001396' ,'name752ee98ce' ,'1537197535' ,'1537197535' ) ,VAL_NN_T (IDX, GUBUN ,NAME,VALUE) AS ( SELECT IDX, GUBUN ,NAME,VALUE,GVALUE FROM ( SELECT IDX, GUBUN ,NAME,VALUE,GVALUE FROM DATA_T1 A WHERE VALUE IS NOT NULL UNION ALL SELECT IDX, GUBUN ,NAME,VALUE,GVALUE FROM DATA_T2 B WHERE VALUE IS NOT NULL )AA GROUP BY IDX, GUBUN ,NAME,VALUE,GVALUE ) ,VAL_N_T (IDX, GUBUN ,NAME,VALUE,GVALUE) AS ( SELECT IDX, GUBUN ,NAME,VALUE,GVALUE FROM ( SELECT IDX, GUBUN ,NAME,VALUE,GVALUE FROM DATA_T1 A WHERE VALUE IS NULL UNION ALL SELECT IDX, GUBUN ,NAME,VALUE,GVALUE FROM DATA_T2 B WHERE VALUE IS NULL )AA GROUP BY IDX, GUBUN ,NAME,VALUE,GVALUE ) ,RESULT_T(IDX, GUBUN ,NAME,VALUE,GVALUE) AS ( SELECT A.IDX ,A.GUBUN ,(SELECT MAX(NAME) FROM VAL_NN_T B WHERE A.GUBUN = B.GUBUN AND A.GVALUE > B.GVALUE ) AS NAME ,(SELECT MAX(VALUE) FROM VAL_NN_T B WHERE A.GUBUN = B.GUBUN AND A.GVALUE > B.GVALUE) AS VALUE ,GVALUE FROM VAL_N_T A UNION ALL SELECT IDX,GUBUN,NAME,VALUE,GVALUE FROM VAL_NN_T B ) SELECT * FROM RESULT_T ORDER BY GUBUN