|
select
x.sttldate ,
x.mbrnm ,
case
when x.cid is null
or x.cid = '' then '비회원'
else x.cid
end cid ,
x.ordroutecd ,
x.ordroutenm ,
x.ordno ,
x.prdnm,
x.sttlmethod ,
x.sttlmethodnm ,
sum(x.salamt) as salamt ,
sum(x.cpnamt) as cpnamt ,
sum(x.usept) as usept ,
-- x.kindcd,
case
when x.kindcd = 'CT0201' then '제품할인'
when x.kindcd = 'CT0202' then '카테고리할인할인'
when x.kindcd = 'CT0203' then '장바구니할인지급'
when x.kindcd = 'CT0204' then '사은품지급'
when x.kindcd = 'CT0205' then '배송비할인'
else x.kindcd
end kindcd,
x.cpnnm,
sum(x.dceventprddcamt) as dceventprddcamt ,
sum(x.mbsamt) as mbsamt ,
sum(x.cardsttlamt) as cardsttlamt ,
sum(x.rsalamt) as rsalamt ,
sum(x.dcpackcost) as dcpackcost ,
sum(x.dcdlvcost) as dcdlvcost ,
sum(x.cancelcardsttlamt) as cancelamt ,
x.ordstatcd ,
x.ordstatnm
from
( select
to_char(a.sttldate,
'YYYY-MM-DD HH24:MM:SS') as sttldate ,
(select
cstmid
from
bncd.eccucm_cust
where
cid = a.cid) as mbrid ,
(select
reqnm
from
bncd.ecorcd_orsh
where
ordno = a.ordno) as mbrnm ,
a.cid ,
a.ordroutecd as ordroutecd ,
(select
cdnm
from
bncd.ecsocl_cocd
where
cd = a.ordroutecd
and usefl = 'Y') as ordroutenm ,
a.ordno as ordno ,
(select
array_to_string(array_agg(prdnm),',')
from
bncd.ecorcd_ordt
where
ordno = a.ordno
) as prdnm ,
a.sttlmethod as sttlmethod ,
(select
cdnm
from
bncd.ecsocl_cocd
where
cd = a.sttlmethod
and usefl = 'Y') as sttlmethodnm ,
a.salamt as salamt ,
(select
sum(prprddcamt)+ sum(prcartdcdivisamt)
from
bncd.ecorcd_ordt
where
ordno = a.ordno
and upporddtlseq = 0) as cpnamt ,
a.prdusept as usept ,
(select array_to_string(array_agg(c.kindcd),',') as kindcd
from bncd.eccucl_prcd b
left join bncd.eccacm_pmtn c
on b.cpnno = c.cpnno
where b.ordno = a.ordno
and b.cid = f.cid
and a.ordno is not null
and b.usefl = 'Y'
) as kindcd,
(select array_to_string(array_agg(c.cpnnm),',') as cpnnm
from bncd.eccucl_prcd b
left join bncd.eccacm_pmtn c
on b.cpnno = c.cpnno
where b.ordno = a.ordno
and b.cid = f.cid
and a.ordno is not null
and b.usefl = 'Y'
) as cpnnm,
(select
sum(case
when (salamt = dceventprddcamt
or dceventno = 0) then 0
else dceventprddcamt
end)
from
bncd.ecorcd_ordt
where
ordno = a.ordno
and upporddtlseq = 0) as dceventprddcamt,
(select
sum(case
when (salamt = mbsamt
or dceventno > 0) then 0
else dceventprddcamt
end)
from
bncd.ecorcd_ordt
where
ordno = a.ordno
and upporddtlseq = 0) as mbsamt ,
a.cardsttlamt as cardsttlamt ,
a.cardsttlamt - a.dcdlvcost as rsalamt ,
a.dcpackcost as dcpackcost ,
a.dcdlvcost as dcdlvcost ,
case
when a.ordstatcd = 'OR0113' then a.cardsttlamt
else 0
end as cancelcardsttlamt ,
a.ordstatcd as ordstatcd ,
(select
cdnm
from
bncd.ecsocl_cocd
where
cd = a.ordstatcd
and usefl = 'Y') as ordstatnm
from
bncd.ecorcm_ordr a
left join bncd.eccucm_cust f
on a.cid = f.cid
where
a.ordstatcd in (
'OR0105','OR0106'
)
and a.sttldate is not null
and to_char(a.sttldate,'YYYY-MM-DD') >= '20200101'
and to_char(a.sttldate,'YYYY-MM-DD') <= '20200201'
--and f.birth_day >= '19920101'
--and f.birth_day <= '20011231'
and a.orginordno is null
order by
sttldate desc)x
group by
x.sttldate,
x.mbrnm,
x.cid,
x.ordroutecd,
x.ordroutenm,
x.ordno,
x.prdnm,
x.kindcd,
x.cpnnm,
x.sttlmethod,
x.sttlmethodnm,
x.ordstatcd,
x.ordstatnm
이상 쿼리 전체이고 문제가 생기는부분은 서브쿼리로 가지고오는
(select array_to_string(array_agg(c.kindcd),',') as kindcd
from bncd.eccucl_prcd b
left join bncd.eccacm_pmtn c
on b.cpnno = c.cpnno
where b.ordno = a.ordno
and b.cid = f.cid
and a.ordno is not null
and b.usefl = 'Y'
) as kindcd,
(select array_to_string(array_agg(c.cpnnm),',') as cpnnm
from bncd.eccucl_prcd b
left join bncd.eccacm_pmtn c
on b.cpnno = c.cpnno
where b.ordno = a.ordno
and b.cid = f.cid
and a.ordno is not null
and b.usefl = 'Y'
) as cpnnm,
해당 부분입니다 이두컬럼만 array_to_string 로 한줄로 변환해서 가지고오는데
속도가 확느려지네요 조인이 잘못된부분이 있는건지 array_to_string 을 사용하면 대량데이터는 속도가 느려지는건지
저두컬럼만뺴면 2020년 전체데이터도 2초면되는데
2개컬럼추가하면 한달치 조회하는데만 2분정도가걸리네요 너무 두서없는 질문이라 여기신다면 죄송합니다.
|
