데이터베이스 질문과 답변
product company qty
a100 10 5
a100 20 10
a300 30 4
a100 10 8
b300 20 5
a100 20 5
위같은 데이타를 아래 데이타처럼 변경을 했습니다.
product company10 company 20 company30
1 a100 13 20 ......
2 a300 .... .... 4
3 b300 ...... 5 ......
.....은 값이 있는거고요 product별 회사별 갯수를 모아서 세로를 가로로 변환을 했습니다.
여기서 궁금한건 a100이라는 제품의 company10 과 company20 의 차이를 알고싶은데요 (+,-하고싶은데요) 어떡해 해야 할가요? 아 20,30 비교 30,40 비교 .......쭉나갑니다...;;
by finecomp
[2010.10.05 21:19:32]
pivot전 처음 조회 시 다음 값(또는 이전 값)과의 차이컬럼 하나 더 만든 후 이것도 같이 펼치세요.
, qty - LEAD(qty) OVER(PARTITION BY product ORDER BY company) AS diff_next
또는
, qty - LAG(qty) OVER(PARTITION BY product ORDER BY company) AS diff_prev
그 후 pivot 시,
SELECT product
, SUM(DECODE(company, '10', qty)) AS company10
, SUM(DECODE(company, '10', diff_next)) AS diff_10_next
, SUM(DECODE(company, '20', qty)) AS company20
, SUM(DECODE(company, '20', diff_next)) AS diff_20_next
이런식으로 함께 펼치는 SQL을 구성하시면 큰 무리없을 듯...;
, qty - LEAD(qty) OVER(PARTITION BY product ORDER BY company) AS diff_next
또는
, qty - LAG(qty) OVER(PARTITION BY product ORDER BY company) AS diff_prev
그 후 pivot 시,
SELECT product
, SUM(DECODE(company, '10', qty)) AS company10
, SUM(DECODE(company, '10', diff_next)) AS diff_10_next
, SUM(DECODE(company, '20', qty)) AS company20
, SUM(DECODE(company, '20', diff_next)) AS diff_20_next
이런식으로 함께 펼치는 SQL을 구성하시면 큰 무리없을 듯...;
by finecomp
[2010.10.05 21:20:57]
+,- 수식에서의 qty, LEAD, LAG 순서는 원하는 결과에 맞게 수정해 보세요...;
by 비기너
[2010.10.06 09:15:31]
with t as (
select 'a100' product,10 company,5 qty from dual union all
select 'a100',20,10 from dual union all
select 'b300',30,18 from dual union all
select 'a300',30,40 from dual union all
select 'a100',10,8 from dual union all
select 'a300',20,30 from dual union all
select 'a100',20,30 from dual union all
select 'b300',30,5 from dual union all
select 'a100',20,5 from dual
)
select product
,sum(decode(company,10,qty,0)) company10
,sum(decode(company,10,qty,0))- sum(decode(company,20,qty,0)) diff1
,sum(decode(company,20,qty,0)) company20
,sum(decode(company,20,qty,0)) - sum(decode(company,30,qty,0)) diff2
,sum(decode(company,30,qty,0)) company30
from t
group by product
select 'a100' product,10 company,5 qty from dual union all
select 'a100',20,10 from dual union all
select 'b300',30,18 from dual union all
select 'a300',30,40 from dual union all
select 'a100',10,8 from dual union all
select 'a300',20,30 from dual union all
select 'a100',20,30 from dual union all
select 'b300',30,5 from dual union all
select 'a100',20,5 from dual
)
select product
,sum(decode(company,10,qty,0)) company10
,sum(decode(company,10,qty,0))- sum(decode(company,20,qty,0)) diff1
,sum(decode(company,20,qty,0)) company20
,sum(decode(company,20,qty,0)) - sum(decode(company,30,qty,0)) diff2
,sum(decode(company,30,qty,0)) company30
from t
group by product
) 버튼을 클릭하여 작성 하시면 됩니다.