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

오라클 쿼리문의드려요

by alan [2016.08.09 13:08:11]

아래쿼리에서 008,009도 그룹화하여 합을 구해서 나타내고 싶은데

parition조건을 어떻게 해야할지 잘안되네요ㅜ

기대 결과값

code val time name

001 4 08:52 메모리사용률

001 14 08:53 메모리사용률

006_007 5 08:52 totalBpm

006_007 10 08:53 totalBpm

008_009 28 08:52 totalp

WITH T (CODE, VAL, TIME, NAME) AS
( SELECT '001', '4', '08:52','메모리사용률' FROM DUAL
UNION ALL
SELECT '001', '14', '08:53','메모리사용률' FROM DUAL
UNION ALL
SELECT '006', '3', '08:52','송신bpm' FROM DUAL
UNION ALL
SELECT '006', '4', '08:53','송신bpm' FROM DUAL
UNION ALL
SELECT '007', '2', '08:52','수신bpm' FROM DUAL
UNION ALL
SELECT '007', '6', '08:53','수신bpm' FROM DUAL
UNION ALL
SELECT '008', '12', '08:52','수신p' FROM DUAL
UNION ALL
SELECT '009', '16', '08:52','송신p' FROM DUAL

)
SELECT CODE ,
VAL ,
TIME ,
NAME
FROM
(SELECT LISTAGG(CODE, ',') WITHIN GROUP(
ORDER BY TIME) OVER(PARTITION BY
CASE
WHEN CODE IN ( '006', '007' )
THEN NULL
ELSE CODE
END, TIME) AS CODE ,
SUM(VAL) OVER(PARTITION BY
CASE
WHEN CODE IN ( '006', '007' )
THEN NULL
ELSE CODE
END, TIME) AS VAL ,
TIME ,
ROW_NUMBER() OVER(PARTITION BY
CASE
WHEN CODE IN ( '006', '007' )
THEN NULL
ELSE CODE
END, TIME ORDER BY TIME) AS RN ,
CASE
WHEN CODE IN ( '006', '007' )
THEN 'TOTALBPM'
ELSE NAME
END AS NAME
FROM T
)
WHERE RN = 1

by 박영식 [2016.08.09 13:40:36]

WITH T (CODE, VAL, TIME, NAME) AS
  ( SELECT '001', '4', '08:52','메모리사용률' FROM DUAL
  UNION ALL
  SELECT '001', '14', '08:53','메모리사용률' FROM DUAL
  UNION ALL
  SELECT '006', '3', '08:52','송신bpm' FROM DUAL
  UNION ALL
  SELECT '006', '4', '08:53','송신bpm' FROM DUAL
  UNION ALL
  SELECT '007', '2', '08:52','수신bpm' FROM DUAL
  UNION ALL
  SELECT '007', '6', '08:53','수신bpm' FROM DUAL
  UNION ALL
  SELECT '008', '12', '08:52','수신p' FROM DUAL
  UNION ALL
  SELECT '009', '16', '08:52','송신p' FROM DUAL
  )
SELECT L_CODE
      ,VAL
      ,TIME
      ,CASE WHEN CODE IN ( '006', '007' ) THEN 'TOTALBPM'
            WHEN CODE IN ( '008', '009' ) THEN 'TOTALP'
                                          ELSE NAME
       END                                AS NAME
  FROM (
SELECT CODE
      ,LISTAGG(CODE, ',') WITHIN GROUP(ORDER BY CODE, TIME) OVER(PARTITION BY TIME, SUBSTR(NAME, 3)) AS L_CODE
      ,SUM(VAL) OVER(PARTITION BY TIME, SUBSTR(NAME, 3) ORDER BY TIME) AS VAL
      ,TIME
      ,NAME
      ,ROW_NUMBER() OVER(PARTITION BY TIME, SUBSTR(NAME, 3) ORDER BY TIME) AS RN
  FROM T
       )
 WHERE RN = 1
ORDER BY L_CODE, TIME

설명하신게 이해는 잘안되지만..

by alan [2016.08.09 14:55:30]

자꾸번거롭게 하여 죄송합니다.....

NAME 컬럼을 빼고여 원래 없는값인데 제가임의로 만든건데

혼란만 가중된거같네요...

007과008이 같은 시간안에 data가들어오기 때문에

007과008의 값은 더해서 한로우에 보여준다.

마찬가지로 008과 009도

같은 시간안에 data가들어오기 때문에

008과 009의 값도 더해서 한로우에 보여준다.

code와 time으로 (006,007) ,(008,009)을 한그룹으로 묶고

더할려고 하는데

partition by time, case when code in(006,007) then null

when code in(008,009) then null

이런식으로 해야되는거아닌가요? 잘안되네요..

기대 결과값

code val time

001 4 08:52

001 14 08:53

006_007 5 08:52

006_007 10 08:53

008_009 28 08:52

SELECT '001', '4', '08:52'FROM DUAL
UNION ALL
SELECT '001', '14', '08:53' FROM DUAL
UNION ALL
SELECT '006', '3', '08:52' FROM DUAL
UNION ALL
SELECT '006', '4', '08:53'FROM DUAL
UNION ALL
SELECT '007', '2', '08:52' FROM DUAL
UNION ALL
SELECT '007', '6', '08:53' FROM DUAL
UNION ALL
SELECT '008', '12', '08:52' FROM DUAL
UNION ALL
SELECT '009', '16', '08:52' FROM DUAL

by swlee [2016.08.09 14:16:43]

with t (code, val, time, name) as
( select '001', '4', '08:52','메모리사용률' from dual union all
  select '001', '14', '08:53','메모리사용률' from dual union all
  select '006', '3', '08:52','송신bpm' from dual union all
  select '006', '4', '08:53','송신bpm' from dual union all
  select '007', '2', '08:52','수신bpm' from dual union all
  select '007', '6', '08:53','수신bpm' from dual union all
  select '008', '12', '08:52','수신p' from dual union all
  select '009', '16', '08:52','송신p' from dual)
select LISTAGG(CODE, '_') WITHIN GROUP(ORDER BY CODE, TIME) code
      ,sum(val) val,time,decode(gb,1,'totalBpm',2,'totalp',max(name)) name
from (select code, val, time, name
           , case when code in ('006','007') then 1 when code in ('008','009') then 2 end gb
      from t) 
group by time, gb
order by 1,2;

by 마농 [2016.08.12 16:49:45]

SELECT LISTAGG(code, '_') WITHIN GROUP(ORDER BY code) code
     , SUM(val) val
     , time
  FROM t
 GROUP BY DECODE(code, '007', '006', '009', '008', code), time
 ORDER BY code, time
;

댓글등록

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

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

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

데이터베이스 질문과 답변