데이터베이스 질문과 답변
sql 작성중 복잡한 경우가 있어 질문드립니다.
일자별 매출과 수금 데이터가 있습니다.
일자 매출 수금 매출-수금 매출-수금누적
2017.01.01 100 0 100 100
2017.02.01 100 50 50 150
2017.03.01 100 210 -110 90
2017.04.01 100 200 -100 -10
2017.05.01 100 50 50 40
2017.06.01 0 0 0 40
이와 같은 데이터가 있을경우
매출-수금 누적은 sum() over() 로 표기하면 가능할듯합니다.
테이블은 일자, 매출, 수금 이 컬럼으로 되어있으며
여기에 해당로우별 이전 값을 비교하여 매출-수금누적중 마이너스 금액일때의 최종 날짜를 가지고 싶습니다.
위의 예일경우 4월, 5월, 6월에 날짜가 2017-04-01 이 보이도록 하고 싶은데 어떤방법이 가능할지 궁금합니다.
감사합니다 ^^
WITH t AS
(
SELECT '2017.01.01' dt, 100 v1, 0 v2 FROM dual
UNION ALL SELECT '2017.02.01', 100, 50 FROM dual
--UNION ALL SELECT '2017.03.01', 100, 210 FROM dual -- 결과가 안맞음
UNION ALL SELECT '2017.03.01', 100, 160 FROM dual -- 결과에 맞도록 데이터 수정 --
UNION ALL SELECT '2017.04.01', 100, 200 FROM dual
UNION ALL SELECT '2017.05.01', 100, 50 FROM dual
UNION ALL SELECT '2017.06.01', 0, 0 FROM dual
)
SELECT dt
, v1
, v2
, v3
, v4
, LAST_VALUE(CASE WHEN v4 < 0 THEN dt END IGNORE NULLS) OVER(ORDER BY dt) v5
FROM (SELECT dt
, v1
, v2
, v1 - v2 v3
, SUM(v1 - v2) OVER(ORDER BY dt) v4
FROM t
)
;
답변감사드립니다. ^^
WITH T AS (
SELECT '2017.01.01' DT , 100 SALE , 0 COLLECT FROM DUAL UNION ALL
SELECT '2017.02.01' , 100 , 50 FROM DUAL UNION ALL
SELECT '2017.03.01' , 100 , 210 FROM DUAL UNION ALL
SELECT '2017.04.01' , 100 , 200 FROM DUAL UNION ALL
SELECT '2017.05.01' , 100 , 0 FROM DUAL UNION ALL
SELECT '2017.06.01' , 0 , 50 FROM DUAL
)
SELECT DT , SALE , COLLECT , SALE_COLLECT , ACCU_SALE_COLLECT, LAST_VALUE(DT2) IGNORE NULLS OVER(ORDER BY DT)
FROM ( SELECT DT , SALE , COLLECT
, SALE - COLLECT AS SALE_COLLECT
, SUM(SALE - COLLECT ) OVER(ORDER BY DT ) ACCU_SALE_COLLECT
, CASE WHEN SUM(SALE - COLLECT ) OVER(ORDER BY DT ) < 0 THEN DT END AS DT2
FROM T
)
답변감사드립니다. ^^
4.02 일 음수데이터가있을경우를 가정하고도 돌려봤습니다
with t(dd,pval,inval,gap1,sum1) as (
select '2017.01.01', 100, 0, 100, 100 from dual union all
select '2017.02.01', 100, 50, 50, 150 from dual union all
select '2017.03.01', 100, 160, -60, 90 from dual union all
select '2017.04.01', 100, 200, -100, -10 from dual union all
select '2017.04.02', 100, 200, -100, -110 from dual union all
select '2017.05.01', 200, 50, 150, 40 from dual union all
--select '2017.05.01', 100, 50, 50, 40 from dual union all
select '2017.06.01', 0, 0, 0, 40 from dual
)
select a.dd, a.pval, a.inval,a.gap1, a.sum1
,case when max(a.sum2) over () = a.sum2 then max(a.dd2) over () else '' end dd1
from (
select a.*
,case when max(a.sum2) over () = a.sum2 and a.f = 1 then a.dd else '' end dd2
from
(
select a.*
,case when a.sum1 < 0 then 1 else 0 end f
,sum(case when a.sum1 < 0 then 1 else 0 end) over (order by a.dd) sum2
,lag(a.sum1) over (order by a.dd) pre_sum
from t a
)a
)a
답변감사드립니다. ^^
예를들어서 음수값이 2개연속일때에 마지막 음수값을 기준으로 날짜가 들어가야하는거죠?
넵 이전날짜중 마지막 날짜를 가져와야합니다. ^^ 답변감사드립니다.
with t(dt, samt, inamt, sindif, sindifacc) as ( select '2017.01.01', 100, 0 , 100, 100 from dual union all select '2017.02.01', 100, 50 , 50, 150 from dual union all select '2017.03.01', 100, 210, -110, 90 from dual union all select '2017.04.01', 100, 200, -100, -10 from dual union all select '2017.05.01', 100, 50 , 50, 40 from dual union all select '2017.06.01', 0, 0 , 0, 40 from dual ) select least(dt,dt2) dtm, samt, inamt, sindif, sindifacc from ( select dt, samt, inamt, sindif, sindifacc , max(dt) keep(dense_rank first order by sign(sindif)) over() dt2 from t ) order by dt
) 버튼을 클릭하여 작성 하시면 됩니다.