데이터베이스 질문과 답변
일자관련 쿼리질문입니다.
0
2
1,647
by sql_master [2013.02.04 23:40:00]
안녕하세요 .. 제가해보다가 안되서 이렇게 글남기게됩니다.
date amt
20120228 102345
20120227 21485
20120226 1111
20120225 54748
20120215 123474
20120214 4444
20120213 8424
20120201 1248
결과
amt
179689
테이블이 있을때 현재일자가 20120228이라고 치면 밑으로 내려가다보면 24일이 빕니다. 그럼 20120228부터 20120225일까지의 금액을 더하는것입니다.
즉, 현재일자를기준으로 과거로 내려가면서 빈 일자를 만나면 그전까지의 데이터의 amt값을 모두 더하는것입니다.
물론 데이터는 항상 다릅니다.
고수님들 부탁드립니다.
by 오케클릭
[2013.02.05 04:02:40]
-- 핵심은 lead 함수를 써서 체크했습니다. WITH t (dt, amt) AS ( SELECT '20120228', 102345 from dual union all SELECT '20120227', 21485 from dual union all SELECT '20120226', 1111 from dual union all SELECT '20120225', 54748 from dual union all SELECT '20120215', 123474 from dual union all SELECT '20120214', 4444 from dual union all SELECT '20120213', 8424 from dual union all SELECT '20120201', 1248 from dual ) SELECT sum_amt FROM ( SELECT dt, amt, SUM(amt) OVER (ORDER BY dt desc) AS sum_amt, CASE WHEN lead(dt,1) OVER (ORDER BY dt desc) != TO_CHAR(TO_DATE(dt, 'yyyymmdd')-1,'yyyymmdd') THEN '1' ELSE '0' END AS CHECK_DT FROM T ORDER BY dt desc ) WHERE check_dt = '1' AND ROWNUM = 1
by 약쟁이총각
[2013.02.05 11:36:03]
WITH t AS (
SELECT '20120228' dt , 102345 amt from dual union all
SELECT '20120227', 21485 from dual union all
SELECT '20120226', 1111 from dual union all
SELECT '20120225', 54748 from dual union all
SELECT '20120215', 123474 from dual union all
SELECT '20120214', 4444 from dual union all
SELECT '20120213', 8424 from dual union all
SELECT '20120201', 1248 from dual
)
select min(dt) from_dt, max(dt) to_dt,sum(amt) total_amt
from (SELECT * FROM T order by dt )
group by to_date(dt,'yyyymmdd') - rownum
order by from_dt
SELECT '20120228' dt , 102345 amt from dual union all
SELECT '20120227', 21485 from dual union all
SELECT '20120226', 1111 from dual union all
SELECT '20120225', 54748 from dual union all
SELECT '20120215', 123474 from dual union all
SELECT '20120214', 4444 from dual union all
SELECT '20120213', 8424 from dual union all
SELECT '20120201', 1248 from dual
)
select min(dt) from_dt, max(dt) to_dt,sum(amt) total_amt
from (SELECT * FROM T order by dt )
group by to_date(dt,'yyyymmdd') - rownum
order by from_dt
) 버튼을 클릭하여 작성 하시면 됩니다.