꿈꾸는 개발자, DBA 커뮤니티 구루비

last_value 문의드립니다

by 김용한 [SQL Query] LAST_VALUE [2017.09.20 16:33:17]

( 기간조건이 있는 ) 날짜별데이터로 group by 한 집계데이터를 날짜컬럼으로 pivot 한후 다시 group by rollup 으로 합계 소계까지낸 집계데이터가있는데 결과 데이터(day1~day10)가 0인경우

grouping(gubun1) or grouping(gubun2) = 1, 0 인경우를 불문하고

last_value 함수로 0 이 아닌 값을 가져와야합니다

말로적으려니 참경우에 없고 성의도없는 질문같아서 죄송하네요..

실제로 제가 last_value로 해보니 데이터가 몇개없을때는

속도에 문제없지만 데이터개수가 늘어날수록 쿼리속도는 느려졌습니다

데이터가 0인경우 최근 이전날짜의 0이 아닌값을 가져와야하는데

속도가나오게 쿼리를 짤려면 어떻게해야할까요?

샘플이필요하시면 다시 만들어서 올릴께요...

기간조건이 day1(20170901) ~ day10(20170910) 이라면

gubun1 , gubun2, day1, day2, day3, ....., day 10

aa , bb, , 0 , 100, 0, 100, 10, 100, 0 , 0, 0, 10

이렇다면

예를들어서 day3 의값이 0이라면 day2의값 100이 last_value 값이됩니다..

day1의 값이 0이라면 day1(20170901) 보다 작은 0이아닌 최대의 날짜의 데이터를 가져와야합니다

만약 20170817 값이 1 이고 20170818~20170831 값이 전부 0 이라면 20170817의 1값이 last_value가 돼겠죠..

by 마농 [2017.09.20 16:35:28]

작성하신 쿼리를 올려주세요.

by 김용한 [2017.09.20 16:40:11]

보안에 민감한 자료가있어서

일단 유사하게 쿼리를 짜서

정리해서 내일올려보겠습니다...

by 우리집아찌 [2017.09.20 17:28:40]

보안에 민감하시면 컬럼/테이블명만 바꿔서 올려주세요.

어설프게 가공하시면 두번 하시게됩니다.

by 김용한 [2017.09.21 09:13:23]

우리집아찌님이 말씀하신데로 테이블명 컬럼명만 바꿔서 올립니다

( 데이터및 결과화면이 필요하시면 다시 정리해서 올리겠습니다 )

0.아래의쿼리는 나름 LAST_VALUE를 써서 해본겁니다.

- WITH T1 까지가 1차적인 ROLLUP이고 밑의 T2 부터는 ROLLUP한데이터를 가공하기위한거니 신경안쓰셔도됩니다.

1. 기간조회는 2017-07-03 ~ 2017-07-30 을입력하면 화면달력에서 주차를

2017년 27주 ~ 2017년 30주로 검색이됩니다.

3개의 테이블입니다

TARGET_TABLE (메인테이블)

WEEK_T(입력날짜가 어느주에 속하는지 (년/월/주)로 가져오는 기준테이블)

LN_T( 라인테이블입니다)

--------------
TARGET_TABLE
--------------
F_CD
P_CD
YY
MM
WK
MD
LN
T_INI
T_TG
T_RS
BR
T_RT
RD

--------------
WEEK_T
--------------
YY
MM
WK
START_DT
END_DT
D_FLAG
U_FLAG
--------------
LN_T
--------------
LN_CD
LN_NM

요청하신쿼리는 아래와같습니다

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

/* LAST_VALUE 이용한값처리 */
WITH T AS (
  SELECT A.MD
        ,A.LN
        ,DECODE( B.IDX,1,'GUBUN1',2,'GUBUN2',3,'GUBUN3',4,'GUBUN4') GUBUN
        ,SUM( DECODE( A.YY||A.WK, '201727', DECODE( B.IDX, 1, A.T_TG, 2, A.T_RS, 3, A.BR, 4, A.T_RT,0), 0  )) "201727"
        ,SUM( DECODE( A.YY||A.WK, '201728', DECODE( B.IDX, 1, A.T_TG, 2, A.T_RS, 3, A.BR, 4, A.T_RT,0), 0  )) "201728"
        ,SUM( DECODE( A.YY||A.WK, '201729', DECODE( B.IDX, 1, A.T_TG, 2, A.T_RS, 3, A.BR, 4, A.T_RT,0), 0  )) "201729"
        ,SUM( DECODE( A.YY||A.WK, '201730', DECODE( B.IDX, 1, A.T_TG, 2, A.T_RS, 3, A.BR, 4, A.T_RT,0), 0  )) "201730"
        ,SUM( CASE WHEN A.YY||A.WK IN (SELECT YY || LPAD(WK,2,0) 
                                         FROM WEEK_T 
                                        WHERE START_DT <= '20170730'
                                          AND END_DT >= '20170703'
                                          AND D_FLAG = 'N' AND U_FLAG = 'Y' AND D_FLAG = 'N' AND U_FLAG = 'Y'
                                      ) THEN DECODE(B.IDX,1,A.T_TG,2,A.T_RS,3,A.BR,4,A.T_RT,0)
                   ELSE 0
               END) TOT
        ,B.IDX
  FROM (
        SELECT NVL(A.MD,B.MD) MD
              ,NVL(A.LN,B.LN) LN
              ,NVL(A.YY,B.YY) YY
              ,NVL(A.MM,B.MM) MM
              ,NVL(A.WK,B.WK) WK
              ,NVL(TO_NUMBER(LAST_VALUE(DECODE(NVL(A.T_TG,B.T_TG),0,NULL,NVL(A.T_TG,B.T_TG)) IGNORE NULLS) OVER (PARTITION BY A.F_CD,A.P_CD,A.MD,A.LN ORDER BY A.YY,A.MM,A.WK)),0) T_TG
              ,NVL(TO_NUMBER(LAST_VALUE(DECODE(NVL(A.T_RS,B.T_RS),0,NULL,NVL(A.T_RS,B.T_RS)) IGNORE NULLS) OVER (PARTITION BY A.F_CD,A.P_CD,A.MD,A.LN ORDER BY A.YY,A.MM,A.WK)),0) T_RS
              ,NVL(TO_NUMBER(LAST_VALUE(DECODE(NVL(A.BR,B.BR),0,NULL,NVL(A.BR,B.BR)) IGNORE NULLS) OVER (PARTITION BY A.F_CD,A.P_CD,A.MD,A.LN ORDER BY A.YY,A.MM,A.WK)),0) BR
              ,NVL(TO_NUMBER(LAST_VALUE(DECODE(NVL(A.T_RT,B.T_RT),0,NULL,NVL(A.T_RT,B.T_RT)) IGNORE NULLS) OVER (PARTITION BY A.F_CD,A.P_CD,A.MD,A.LN ORDER BY A.YY,A.MM,A.WK)),0) T_RT
--              ,NVL(A.T_TG,B.T_TG) T_TG
--              ,NVL(A.T_RS,B.T_RS) T_RS
--              ,NVL(A.BR,B.BR) BR
--              ,NVL(A.T_RT,B.T_RT) T_RT
          FROM (
                SELECT A.MD
                      ,A.LN
                      ,NVL(A.YY,B.YY) YY
                      ,NVL(A.MM,B.MM) MM
                      ,NVL(A.WK,B.WK) WK
                      ,DENSE_RANK() OVER (ORDER BY A.MD,A.LN) DR
                      ,A.T_TG
                      ,A.T_RS
                      ,A.BR
                      ,A.T_RT
                  FROM (SELECT YY YY 
                              ,LPAD(MM,2,0) MM
                              ,LPAD(WK,2,0) WK 
                          FROM WK_T 
                         WHERE START_DT <= '20170730'
                           AND END_DT >= '20170703'
                           AND D_FLAG = 'N' AND U_FLAG = 'Y'
                         ORDER BY YY, MM, WK
                        ) B
                       LEFT OUTER JOIN
                        TARGET_TABLE A
                       PARTITION BY (A.MD, A.LN ) ON (
                        A.YY = B.YY
                    AND A.MM = B.MM
                    AND A.WK = B.WK
                        )
                  WHERE 1=1
                    AND A.LN <> '9999'
                  ORDER BY
                        A.MD
                       ,A.LN
                       ,NVL(A.YY,B.YY)
                       ,NVL(A.MM,B.MM)
                       ,NVL(A.WK,B.WK)
               ) A
            FULL OUTER JOIN
               (SELECT * FROM TARGET_TABLE
                 WHERE LN <> '9999'
               )B
            ON 1=1
           AND A.MD       = B.MD
           AND A.LN       = B.LN
           AND A.YY       = B.YY
           AND A.MM       = B.MM
           AND A.WK       = B.WK
         ORDER BY
               1,2,3,4,5 
       )A
      ,(SELECT LEVEL IDX FROM DUAL CONNECT BY LEVEL<=4)B
 WHERE 1=1
 GROUP BY
       A.MD
      ,A.LN
      ,B.IDX
 ORDER BY
       A.MD
      ,A.LN
      ,B.IDX
)
/* 1차적인 rollup값을 구한다 */
SELECT MD,LN,GUBUN
            ,CASE WHEN "201727" = 0 AND GR>0 AND IDX = 2 THEN NULL ELSE "201727" END "201727"
            ,CASE WHEN "201728" = 0 AND GR>0 AND IDX = 2 THEN NULL ELSE "201728" END "201728"
            ,CASE WHEN "201729" = 0 AND GR>0 AND IDX = 2 THEN NULL ELSE "201729" END "201729"
            ,CASE WHEN "201730" = 0 AND GR>0 AND IDX = 2 THEN NULL ELSE "201730" END "201730"
            ,CASE WHEN TOT = 0 AND GR>0 AND IDX = 2 THEN NULL ELSE TOT END TOT
      ,GR,IDX,GR1
  FROM (
        SELECT DECODE(1, GROUPING(A.MD), 'Total', A.MD) MD
              ,DECODE(1, GROUPING(A.MD), '', GROUPING(A.LN), 'Sub Total', A.LN) LN
              ,A.GUBUN
              ,CASE WHEN MAX(A.IDX) IN (1,2) THEN ROUND(SUM("201727"),1) WHEN MAX(A.IDX) IN (3,4) THEN ROUND(SUM("201727"),0) ELSE 0 END "201727"
              ,CASE WHEN MAX(A.IDX) IN (1,2) THEN ROUND(SUM("201728"),1) WHEN MAX(A.IDX) IN (3,4) THEN ROUND(SUM("201728"),0) ELSE 0 END "201728"
              ,CASE WHEN MAX(A.IDX) IN (1,2) THEN ROUND(SUM("201729"),1) WHEN MAX(A.IDX) IN (3,4) THEN ROUND(SUM("201729"),0) ELSE 0 END "201729"
              ,CASE WHEN MAX(A.IDX) IN (1,2) THEN ROUND(SUM("201730"),1) WHEN MAX(A.IDX) IN (3,4) THEN ROUND(SUM("201730"),0) ELSE 0 END "201730"
              ,CASE WHEN MAX(A.IDX) IN (1,2) THEN ROUND(SUM(TOT),1) WHEN MAX(A.IDX) IN (3,4) THEN ROUND(SUM(TOT),0) ELSE 0 END TOT
              ,TO_CHAR(DENSE_RANK() OVER (ORDER BY A.MD NULLS FIRST
                                         )-1) GR
              ,MAX(A.IDX) IDX
              ,NVL(A.LN,'9999') GR1
          FROM T A
         GROUP BY ROLLUP(A.MD, (A.LN)), GUBUN
        HAVING NOT( MAX(A.IDX) IN (1,3,4) AND GROUPING(A.MD) = 0 AND GROUPING(A.LN) = 0 )
         ORDER BY A.MD NULLS FIRST
                , A.LN  NULLS FIRST
                , MAX(A.IDX)
    )
;

by 김용한 [2017.09.21 09:38:23]

MD	LN	GUBUN	201727	201728	201729	201730	TOT	GR	IDX	GR1
Total		GUBUN1	1473.3	2120.1	2468.3	2822.3	8884.1	0	1	9999
Total		GUBUN2	1789.8	2481.8	2899.7	3378.4	10549.6	0	2	9999
Total		GUBUN3	30097	73566	94428	65274	263365	0	3	9999
Total		GUBUN4	-49017	-138317	-147006	-252082	-586422	0	4	9999
MD1	Sub Total	GUBUN1	12.2	24.3	24.3	24.3	85.1	1	1	9999
MD1	Sub Total	GUBUN2	15.2	30.4	30.4	30.4	106.4	1	2	9999
MD1	Sub Total	GUBUN3	0	0	0	0	0	1	3	9999
MD1	Sub Total	GUBUN4	0	-66	-66	-66	-197	1	4	9999
MD1	LN1	GUBUN2		15.2	15.2	15.2	45.6	1	2	LN1
MD1	LN2	GUBUN2	15.2	15.2	15.2	15.2	60.8	1	2	LN1

MD  LN  GUBUN   201727  201728  201729  201730  TOT GR  IDX GR1
Total       GUBUN1  1473.3  2120.1  2468.3  2822.3  8884.1  0   1   9999
Total       GUBUN2  1789.8  2481.8  2899.7  3378.4  10549.6 0   2   9999
Total       GUBUN3  30097   73566   94428   65274   263365  0   3   9999
Total       GUBUN4  -49017  -138317 -147006 -252082 -586422 0   4   9999
MD1 Sub Total   GUBUN1  12.2    24.3    24.3    24.3    85.1    1   1   9999
MD1 Sub Total   GUBUN2  15.2    30.4    30.4    30.4    106.4   1   2   9999
MD1 Sub Total   GUBUN3  0   0   0   0   0   1   3   9999
MD1 Sub Total   GUBUN4  0   -66 -66 -66 -197    1   4   9999
MD1 LN1 GUBUN2      15.2    15.2    15.2    45.6    1   2   LN1
MD1 LN2 GUBUN2  15.2    15.2    15.2    15.2    60.8    1   2   LN1

WITH T1 까지 결과를 일부 발췌한 결과입니다.

by 마농 [2017.09.21 14:30:52]

쿼리가 너무 길고 복잡하네요.
테이블도 몇개 안되고 사용되는 항목도 많지 않은데..이렇게까지 복잡할 이유가 없네요.
기간 조건 주는 방법도 너무 복잡하구요.
target_table 자료 좀 보여주세요. 표형태로.
전체 건수는 어느정도, 월별 건수는?
조회 기간 이전의 0 이 아닌 자료를 찾는 방법을 고민해야 합니다.
조회 기간보다 이전 자료를 더 가져와야지만 last_value 를 적용할 수 있겟지요?
0 인 자료가 연속되는 기간은 최대 어느 정도?
이 기간 정도만 더 가져오면 될지? 아니면 처음부터 다 가져와야 할지 등등..고민이 필요합니다.

by 김용한 [2017.09.21 15:13:39]

네... 저도그렇게생각하고있습니다

다시정리해서 질문올리겠습니다...

by 김용한 [2017.09.22 08:51:26]

TARGET 테이블입니다

TARGET 테이블데이터는 이미 주차별로 집계됀 테이블입니다 그래서 WEEK 단위입니다.

F_CD	P_CD	YY	MM	WK	MD	LN	T_TG	T_RS	BR	T_RT
F01	P01	2017	7	26	M01	L01	10.8	13.5	0
F01	P01	2017	7	31	M02	L02	13.44	16.8	0
F01	P01	2017	7	29	M03	L03	14.1375	14.5	0
F01	P01	2017	7	28	M04	L04	16.8	21	0
F01	P01	2017	7	31	M05	L05	11.2	14	0
F01	P01	2017	7	27	M06	L06	13.52	16.9	0	-197.1666667
F01	P01	2017	7	28	M07	L05	5.56	14	0
F01	P01	2017	7	27	M01	L01	10.8	13.5	0
F01	P01	2017	7	27	M08	L07	10	14	0
F01	P01	2017	7	29	M09	L08	11.2	14	0
F01	P01	2017	7	30	M10	L09	13.775	14.5	0
F01	P01	2017	7	28	M11	L10	11.36	15	0
F01	P01	2017	7	29	M12	L11	8.24	10.3	0
F01	P01	2017	7	30	M13	L12	10.384	15	0
F01	P01	2017	7	27	M14	L13	5.384	14	0
F01	P01	2017	7	30	M03	L14	13.775	14.5	0
F01	P01	2017	7	27	M15	L15	14	17.5	0
F01	P01	2017	7	29	M16	L16	14.5	14.5	0
F01	P01	2017	7	31	M11	L17	11.36	14.2	0
F01	P01	2017	7	30	M17	L12	12	15.99	-31.5975	-127.3475

with t (f_cd, p_cd, yy, mm, wk, md, ln, t_tg, t_rs, br, t_rt ) as (
select 'F01','P01','2017','7','26','M01','L01',10.8,13.5,0,0 from dual union all
select 'F01','P01','2017','7','31','M02','L02',13.44,16.8,0,0 from dual union all
select 'F01','P01','2017','7','29','M03','L03',14.1375,14.5,0,0 from dual union all
select 'F01','P01','2017','7','28','M04','L04',16.8,21,0,0 from dual union all
select 'F01','P01','2017','7','31','M05','L05',11.2,14,0,0 from dual union all
select 'F01','P01','2017','7','27','M06','L06',13.52,16.9,0,-197.1666667 from dual union all
select 'F01','P01','2017','7','28','M07','L05',5.56,14,0,0 from dual union all
select 'F01','P01','2017','7','27','M01','L01',10.8,13.5,0,0 from dual union all
select 'F01','P01','2017','7','27','M08','L07',10,14,0,0 from dual union all
select 'F01','P01','2017','7','29','M09','L08',11.2,14,0,0 from dual union all
select 'F01','P01','2017','7','30','M10','L09',13.775,14.5,0,0 from dual union all
select 'F01','P01','2017','7','28','M11','L10',11.36,15,0,0 from dual union all
select 'F01','P01','2017','7','29','M12','L11',8.24,10.3,0,0 from dual union all
select 'F01','P01','2017','7','30','M13','L12',10.384,15,0,0 from dual union all
select 'F01','P01','2017','7','27','M14','L13',5.384,14,0,0 from dual union all
select 'F01','P01','2017','7','30','M03','L14',13.775,14.5,0,0 from dual union all
select 'F01','P01','2017','7','27','M15','L15',14,17.5,0,0 from dual union all
select 'F01','P01','2017','7','29','M16','L16',14.5,14.5,0,0 from dual union all
select 'F01','P01','2017','7','31','M11','L17',11.36,14.2,0,0 from dual union all
select 'F01','P01','2017','7','30','M17','L12',12,15.99,-31.5975,0 from dual
)
select t.* from t

by 마농 [2017.09.21 15:29:20]

-- 기간 검색 조건 주는 법
-- 시작일과 종료일을 교차 비교
SELECT *
  FROM week_t
 WHERE start_dt <= '20170730'
   AND   end_dt >= '20170703'
;

by 김용한 [2017.09.21 16:37:07]

마농님은 4경우) 만 체크하면 됀다는거죠?
 
1경우). 20170703 ----------------- 20170730
 
                       start_dt ---------------------------end_dt
 
                           2경우).20170703 -- 20170730
 
                                         3경우).  20170703 -------------- 20170730
 
4경우).   20170703 ---------------------------------------------- 20170730

by 마농 [2017.09.21 17:23:11]

-- 0)         7/03 ------------- 7/30             -- 조회기준
-- 1) s -------------- e                          -- 검색대상
-- 2)                 s --- e                     -- 검색대상
-- 3)                      s ----------------- e  -- 검색대상
-- 4) s -------------------------------------- e  -- 검색대상
-- 5) s --- e                                     -- 제외대상
-- 6)                                    s --- e  -- 제외대상
  
-- 0) 을 기준으로 검색 대상인 1),2),3),4) 는 모두
-- s 가 7/30 보다 작고.   AND start_dt <= '20170730'
-- e 가 7/03 보다 크죠.   AND   end_dt >= '20170703'
-- 이 두가지 조건으로 검색 대상인 1),2),3),4) 가 모두 검출됩니다.
-- 제외 대상이 5), 6) 은 제외되구요.

by 김용한 [2017.09.22 08:04:49]

오.... 그동안 궁금했던 기간체크가 이해가돼네요 감사드립니다 ~~ ^^

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변