데이터베이스 질문과 답변
by 날아라갑을 [SQL Query] sql postgresql [2024.04.16 11:08:27]
with CUR as (
select
r.*
,row_number() over (order by emd_cd) + 1 as sn
, COUNT(*) over () as total_cnt
from
(
select
ctprvn_nm ,
emd_cd,
max_count ,
emd_tot ,
ISD_00101,
ISD_00102,
ISD_001_TOTAL
from
(
select
(
select
ctprvn_nm
from
netn_sigungu_229 ns
where
substring(sigungu_cd, 1, 2) = emd_cd
limit 1) as ctprvn_nm ,
emd_cd ,
max(total) as max_count ,
sum(ISD_00101 + ISD_00102) as emd_tot ,
sum(ISD_00101) as ISD_00101,
sum(ISD_00102) as ISD_00102,
sum(ISD_00101 + ISD_00102) as ISD_001_TOTAL
from
(
select
(
select
count(*)
from
netn_dsstrwry_region
where
use_at = 'Y'
and sigungu_cd <> '99999') as total ,
case
when t1.dsstrwry_grp_code = 'ISD_001' then
case
when t1.dsstrwry_sys_code = 'ISD_00101'
and use_at = 'Y' then 1
else 0
end
else 0
end as ISD_00101 ,
case
when t1.dsstrwry_grp_code = 'ISD_001' then
case
when t1.dsstrwry_sys_code = 'ISD_00102'
and use_at = 'Y' then 1
else 0
end
else 0
end as ISD_00102,
substring(emd_cd, 1, 2)as emd_cd
from
netn_dsstrwry_region t1
left join netn_dsstrwry_code t2 on
t1.dsstrwry_grps_code = t2.dsstrwry_grps_code
and t1.dsstrwry_sys_code = t2.dsstrwry_sys_code) bb
group by
(emd_cd)
order by
emd_cd)t3
where
ctprvn_nm is not null
union all
select
ctprvn_nm ,
emd_cd,
max_count ,
emd_tot ,
ISD_00101,
ISD_00102,
ISD_001_TOTAL
from
(
select
(
select
CONCAT(ctprvn_nm, ' ', sigungu_nm)
from
netn_sigungu_229 ns
where
sigungu_cd = substring(bb.emd_cd, 1, 5)
limit 1) as ctprvn_nm ,
emd_cd,
max(total) as max_count ,
sum(ISD_00101 + ISD_00102) as emd_tot ,
/*모든 총합계값*/
sum(ISD_00101) as ISD_00101,
sum(ISD_00102) as ISD_00102,
sum(ISD_00101 + ISD_00102) as ISD_001_TOTAL
from
(
select
(
select
count(*)
from
netn_dsstrwry_region
where
use_at = 'Y'
and sigungu_cd <> '99999') as total ,
case
when t1.dsstrwry_grp_code = 'ISD_001' then
case
when t1.dsstrwry_sys_code = 'ISD_00101'
and use_at = 'Y' then 1
else 0
end
else 0
end as ISD_00101 ,
case
when t1.dsstrwry_grp_code = 'ISD_001' then
case
when t1.dsstrwry_sys_code = 'ISD_00102'
and use_at = 'Y' then 1
else 0
end
else 0
end as ISD_00102,
/*기타 끝*/
sigungu_cd as emd_cd
from
netn_dsstrwry_region t1
left join netn_dsstrwry_code t2 on
t1.dsstrwry_grps_code = t2.dsstrwry_grps_code
and t1.dsstrwry_sys_code = t2.dsstrwry_sys_code
and t1.use_at = 'Y'
and t1.sigungu_cd <> '99999' ) bb
group by
emd_cd
order by
emd_cd)t3
where
1 = 1
and ctprvn_nm is not null) r
)
select
COALESCE(ctprvn_nm, '계'),
emd_cd,
row_number() over (order by emd_cd) as sn,
max(total_cnt) as total_cnt,
sum(case when length(emd_cd) = 2 then emd_tot
when length(emd_cd) = 5 then emd_tot
else 0 end) as emd_tot,
sum(ISD_00101) as ISD_00101,
sum(ISD_00102) as ISD_00102,
sum(ISD_001_TOTAL) as ISD_001_TOTAL
-- sum(case when length(emd_cd) = 2 then ISD_00101
-- when length(emd_cd) = 5 then ISD_00101
-- else 0 end) as ISD_00101,
-- sum(case when length(emd_cd) = 2 then ISD_00102
-- when length(emd_cd) = 5 then ISD_00102
-- else 0 end) as ISD_00102,
-- sum(case when length(emd_cd) = 2 then ISD_001_TOTAL
-- when length(emd_cd) = 5 then ISD_001_TOTAL
-- else 0 end) as ISD_001_TOTAL
from CUR
group by rollup((ctprvn_nm, emd_cd))
order by emd_cd nulls first, emd_cd
=========================================================
기존에 시도별, 시군구별 데이터로 보여주고있었는데 하나로 합쳐서 전체 합계 붙이려다보니..
그룹으로 묶어서 합계해보니 최상위 '계' 부분이 2배가 되고있습니다
기존쿼리들 두개 가져와서 합치니까 계수가 같은데이터가 1+1 해서 2배가 되는건 알고있지만
어떻게 변경해 나가야할지 막막해서 여쭤봅니다
전체 데이터를 보지 않아서 화면으로만 보면 데이터의 정합성은 모르겠지만
시도 와 시군구 라면
서울특별시 등 광역지차체 기준 데이터와 그걸 세분화한 시군구 데이터가 있다면
서울을 예로 들면 서울이 100이라면 서울 하위 군구의 합은 100이 되야 할거 같은데요
그래서 시도와 시군구를 합쳐서 200이 되는거라면
전체의 계는 시도와 시군구중 1군데만 SUM을 해야하는거 아닐까요?
샘플데이터라도 정합성을 맞춰서 올려주시면 추가의견을 드릴수 있을것 같습니다.
맞습니다 ..
한군대만 sum을 해야하는데 무지성으로 잘나오는 데이터를 union all 시키니 한쪽을 빼면 시도별 데이터가 0 이되고
다른 한쪽을 빼면 시군구별 데이터가 0이 되는 상황이 발생했네요^^;
결국 union all을 한번씩 더붙여서 emd_cd = 2 일때 5일때 합계를 붙여서 처리했네요 ..
1. 애초에 유니온으로 두가지 집합(시도, 시군구)을 가져오는데
- 시군구 자료 전체 합계가 시도 합계와 같을 것입니다.
- 즉 두번 합계되도록 유니온된 집합입니다.
- 처음부터 유니온 하지 않고 가능 할 것 같습니다.
- 그러려면 원본 테이블들의 구조와 저장된 값에 대한 샘플이 필요합니다.
2. t2 는 조인이 되고 있는데? 사용이 안되고 있네요?
- 조인이 꼭 필요한건지? 빼도 될 것 같은데요?
3. 일단 만들어 뵜습니다.
- 테이블 값이나 연관관계 등을 보지 못해 감으로 만들어 봤습니다.
- 감으로 만든거라 오류가 나거나 값이 다를 수도 있습니다.
- 그점 감안하시고 이런 컨셉으로 작성하면 된다 하고 예를 들어 준 것입니다.
SELECT CASE WHEN nm1 IS NULL THEN '계'
WHEN nm2 IS NULL THEN nm1
ELSE CONCAT(nm1, ' ', nm2)
END ctprvn_nm
, COALESCE(cd2, cd1) emd_cd
, ROW_NUMBER() OVER(ORDER BY cd1, cd2) AS sn
, total_cnt
, SUM(isd_00101) isd_00101
, SUM(isd_00102) isd_00102
, SUM(isd_00101) + SUM(isd_00102) isd_001_total
FROM (SELECT SUBSTRING(a.emd_cd, 1, 2) cd1
, a.emd_cd cd2
, b.ctprvn_nm nm1
, b.sigungu_nm nm2
, COUNT(*) OVER() total_cnt
, COUNT(CASE WHEN a.dsstrwry_grp_code = 'ISD_001' AND a.dsstrwry_sys_code = 'ISD_00101' THEN 1 END) isd_00101
, COUNT(CASE WHEN a.dsstrwry_grp_code = 'ISD_001' AND a.dsstrwry_sys_code = 'ISD_00102' THEN 1 END) isd_00202
FROM netn_dsstrwry_region a
INNER JOIN netn_sigungu_229 b
ON a.sigungu_cd = b.sigungu_cd
AND a.use_at = 'Y'
AND a.sigungu_cd <> '99999'
GROUP BY a.emd_cd, b.ctprvn_nm, b.sigungu_nm
) a
GROUP BY total_cnt, ROLLUP((cd1, nm1), (cd2, nm2))
ORDER BY cd1 NULLS FIRST
, cd2 NULLS FIRST
;
기존 소스에서 수정하다 보니 t2를빼도 잘돌아가는거 확인했네요 .. 놓치던 부분 감사합니다
생각나는대로 union all을 덕지덕지 붙여서 또 시간을 갈아넣고 결과 뽑아내기에 급급하게 처리하게되었는데요..
올려주신 소스에도 적용해서 돌려 봤는데요
제가 억지로 짜낸 소스 수백줄이 해결됨에 감탄했습니다.. 감사합니다
) 버튼을 클릭하여 작성 하시면 됩니다.