데이터베이스 질문과 답변

SQL 출력결과 여쭤봅니다. 0 5 1,177

by h2wshot [PL/SQL] [2016.02.23 16:00:58]

 

with TAB AS (

SELECT '2016-02-01' as "date" ,1001 AS "DB_ID",    100 AS "TOT_CNT",        50 AS "ERR_CNT" FROM DUAL  UNION ALL
SELECT '2016-02-02' as "date" ,1001 AS "DB_ID",    0        AS "TOT_CNT" ,0    AS "ERR_CNT"  FROM DUAL UNION ALL
SELECT '2016-02-03' as "date" ,1001 AS "DB_ID",    0        AS "TOT_CNT" ,0    AS "ERR_CNT"  FROM DUAL UNION ALL
SELECT '2016-02-04' as "date" ,1001 AS "DB_ID",    0        AS "TOT_CNT" ,0    AS "ERR_CNT"  FROM DUAL UNION ALL
SELECT '2016-02-05' as "date" ,1001 AS "DB_ID",    0        AS "TOT_CNT" ,0    AS "ERR_CNT"  FROM DUAL UNION ALL
SELECT '2016-02-06' as "date" ,1001 AS "DB_ID",    0        AS "TOT_CNT" ,0    AS "ERR_CNT"  FROM DUAL UNION ALL
SELECT '2016-02-07' as "date" ,1001 AS "DB_ID",    0        AS "TOT_CNT" ,0    AS "ERR_CNT"  FROM DUAL UNION ALL
SELECT '2016-02-08' as "date" ,1001 AS "DB_ID",    0        AS "TOT_CNT" ,0    AS "ERR_CNT"  FROM DUAL UNION ALL
SELECT '2016-02-09' as "date" ,1001 AS "DB_ID",    889 AS "TOT_CNT",        50 AS "ERR_CNT" FROM DUAL  UNION ALL
SELECT '2016-02-10' as "date" ,1001 AS "DB_ID",    0        AS "TOT_CNT" ,0    AS "ERR_CNT"  FROM DUAL UNION ALL
SELECT '2016-02-11' as "date" ,1001 AS "DB_ID",    0        AS "TOT_CNT" ,0    AS "ERR_CNT"  FROM DUAL UNION ALL
SELECT '2016-02-12' as "date" ,1001 AS "DB_ID",    0        AS "TOT_CNT" ,0    AS "ERR_CNT"  FROM DUAL UNION ALL
SELECT '2016-02-13' as "date" ,1001 AS "DB_ID",    0        AS "TOT_CNT" ,0    AS "ERR_CNT"  FROM DUAL UNION ALL
SELECT '2016-02-14' as "date" ,1001 AS "DB_ID",    0        AS "TOT_CNT" ,0    AS "ERR_CNT"  FROM DUAL UNION ALL
SELECT '2016-02-15' as "date" ,1001 AS "DB_ID",    0        AS "TOT_CNT" ,0    AS "ERR_CNT"  FROM DUAL UNION ALL
SELECT '2016-02-16' as "date" ,1001 AS "DB_ID",    0        AS "TOT_CNT" ,0    AS "ERR_CNT"  FROM DUAL UNION ALL
SELECT '2016-02-17' as "date" ,1001 AS "DB_ID",    0        AS "TOT_CNT" ,0    AS "ERR_CNT"  FROM DUAL UNION ALL
SELECT '2016-02-18' as "date" ,1001 AS "DB_ID",    0        AS "TOT_CNT" ,0    AS "ERR_CNT"  FROM DUAL UNION ALL
SELECT '2016-02-19' as "date" ,1001 AS "DB_ID",    0        AS "TOT_CNT" ,0    AS "ERR_CNT"  FROM DUAL UNION ALL
SELECT '2016-02-20' as "date" ,1001 AS "DB_ID",    0        AS "TOT_CNT" ,0    AS "ERR_CNT"  FROM DUAL 
)

SELECT * FROM TAB

이라는 데이터가 있습니다.

 

date   DB_ID    TOT_CNT ERR_CNT
2016-02-01   1001   100   50
2016-02-02   1001   0   0
2016-02-03   1001   0   0
2016-02-04   1001   0   0
2016-02-05   1001   0   0
2016-02-06   1001   0   0
2016-02-07   1001   0   0
2016-02-08   1001   0   0
2016-02-09   1001   889   50
2016-02-10   1001   0   0
2016-02-11   1001   0   0
2016-02-12   1001   0   0
2016-02-13   1001   0   0
2016-02-14   1001   0   0
2016-02-15   1001   0   0
2016-02-16   1001   0   0
2016-02-17   1001   0   0
2016-02-18   1001   0   0
2016-02-19   1001   0   0
2016-02-20   1001   0   0

 

집합을 아래와 같이,

 

date   DB_ID    TOT_CNT ERR_CNT
2016-02-01   1001   100   50
2016-02-02   1001   100   0
2016-02-03   1001   100   0
2016-02-04   1001   100   0
2016-02-05   1001   100   0
2016-02-06   1001   100   0
2016-02-07   1001   100   0
2016-02-08   1001   100   0
2016-02-09   1001   889   50
2016-02-10   1001   889   0
2016-02-11   1001   889   0
2016-02-12   1001   889   0
2016-02-13   1001   889   0
2016-02-14   1001   889   0
2016-02-15   1001   889   0
2016-02-16   1001   889   0
2016-02-17   1001   889   0
2016-02-18   1001   889   0
2016-02-19   1001   889   0
2016-02-20   1001   889   0

 

이런 식으로 출력하고 싶습니다.

 

 

 

TOT_CNT가 0 값이면, 위에 값이 있는 데이터를 가져오고 싶은데 

그루핑이나 파티션을 어떻게 해줘야할지 모르겠습니다.

(ex, 2016년 02월 1일은 100이었고 2일은 0이면 2일을 그 전날인 값인 100으로..)

 

출력결과에 대한 SQL 여쭤봅니다.

 

감사합니다.

 

 

by 창조의날개 [2016.02.23 16:20:03] 

SELECT "date"
     , DB_ID
     , LAST_VALUE(NULLIF(TOT_CNT,0) IGNORE NULLS) OVER(ORDER BY "date") TOT_CNT
     , ERR_CNT
FROM TAB
;

 

by jkson [2016.02.23 16:30:18]

오~~ 간단하네요.

by h2wshot [2016.02.23 16:40:22]

감사합니다. PROSTGRE 에서는 Ignore nulls 가 안먹혀서.. 

다른 방법을 생각해봐야겠네요 죄송해요

by 마농 [2016.02.23 17:36:27] 
-- PostgreSQL
WITH tab AS
(
SELECT '2016-02-01' "date", 1001 "DB_ID", 100 "TOT_CNT", 50 "ERR_CNT"
UNION ALL SELECT '2016-02-02', 1001,   0,  0
UNION ALL SELECT '2016-02-03', 1001,   0,  0
UNION ALL SELECT '2016-02-04', 1001,   0,  0
UNION ALL SELECT '2016-02-05', 1001,   0,  0
UNION ALL SELECT '2016-02-06', 1001,   0,  0
UNION ALL SELECT '2016-02-07', 1001,   0,  0
UNION ALL SELECT '2016-02-08', 1001,   0,  0
UNION ALL SELECT '2016-02-09', 1001, 889, 50
UNION ALL SELECT '2016-02-10', 1001,   0,  0
UNION ALL SELECT '2016-02-11', 1001,   0,  0
UNION ALL SELECT '2016-02-12', 1001,   0,  0
UNION ALL SELECT '2016-02-13', 1001,   0,  0
UNION ALL SELECT '2016-02-14', 1001,   0,  0
UNION ALL SELECT '2016-02-15', 1001,   0,  0
UNION ALL SELECT '2016-02-16', 1001,   0,  0
UNION ALL SELECT '2016-02-17', 1001,   0,  0
UNION ALL SELECT '2016-02-18', 1001,   0,  0
UNION ALL SELECT '2016-02-19', 1001,   0,  0
UNION ALL SELECT '2016-02-20', 1001,   0,  0
)
SELECT DATE
     , "DB_ID"
     , (SELECT "TOT_CNT"
          FROM tab b
         WHERE "DB_ID" = a."DB_ID"
           AND "date" <= a."date"
           AND "TOT_CNT" > 0
         ORDER BY "date" DESC
         LIMIT 1
        ) TOT_CNT
     , "ERR_CNT"
  FROM tab a
;

 

by h2wshot [2016.02.29 09:00:44]

좋은 답변들 감사합니다.^.^

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입