조언 부탁합니다. (내용추가) 0 9 554

by 김선우 [SQL Query] [2018.10.02 17:59:58]



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

 

by 우리집아찌 [2018.10.02 18:09:54]

번개가야해서 내일.. 답을드리지요..


by 봄빛 [2018.10.03 00:11:57]

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

 

 

 


by 김선우 [2018.10.03 06:28:32]

답변 감사합니다.

그런데

(추가) 이때 gubun 값은 중복 될 수 있으며 gubun 값별 value의 max 값이 name과 value 가 됩니다.

위 내용이 추가 되었습니다.
실제 이내용이 들어가서 더 헷갈리는데 빠져있었네요.
 


by 봄빛 [2018.10.03 16:02:32]
설명이 잘못된듯 싶은데요

 데이타만 보면 gubun기준으로 max(name) 과 max(value)를 구해서 
name과 value가 널인경우 각각의 max값으로 채우는 데이타입니다.


설명은 "널인경우 max(value)로 name과 value를 채운다" 인데 
결과 데이타와 설명이 모순적입니다.

 


by 봄빛 [2018.10.03 16:40:22]
--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 

 


by 김선우 [2018.10.04 00:41:35]

답변 감사합니다.
아래 댓글로 다시 정리해보았습니다.


by 김선우 [2018.10.04 00:40:23]

우선 우문현답 감사합니다.
아래 다시 최종(?) 정리해서 말씀 드려봅니다.
다시한번 애매한 질문으로 죄송합니다. (__ 

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 값" 이 내용을 당췌 처리 하기가 힘드네요.

염치 없지만 도움 부탁 드려봅니다.

 


by 마농 [2018.10.04 10:15:06]
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
;

 


by 봄빛 [2018.10.04 10:40:19]
--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

 

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