아래와 같이 테이블a가 있는데요
각각의 송신bpm과 수신bpm을 더해서 나타낼려고 하는데
쿼리를 못짜고 있어 문의 드립니다. (_ _)
기대 결과값
code val time name
001 4 08:52 메모리사용률
001 14 08:53 메모리사용률
006007 5 08:52 totalBpm
006007 10 08:53 totalBpm
006007 2 08:54 totalBpm
table a
code val time name
001 4 08:52 메모리사용률
001 14 08:53 메모리사용률
006 3 08:52 송신bpm
006 4 08:53 송신bpm
007 2 08:52 수신bpm
007 6 08:53 수신bpm
007 2 08:54 수신bpm
with a (code,val,time,name) as ( 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 '007',2,'08:54','수신bpm' from dual) select time,'006007' code,sum(val) val,'totalBpm' name from a group by time order by time;
WITH T (CODE, VAL, TIME, NAME) AS ( 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 '007', '2', '08:54','수신bpm' FROM DUAL ) SELECT CODE ,VAL ,TIME ,'TOTALBPM' AS NAME FROM ( SELECT LISTAGG(CODE, ',') WITHIN GROUP(ORDER BY TIME) OVER(PARTITION BY TIME)AS CODE ,SUM(VAL) OVER(PARTITION BY TIME) AS VAL ,TIME ,ROW_NUMBER() OVER(PARTITION BY TIME ORDER BY TIME) AS RN FROM T ) WHERE RN = 1 이렇게 나오면 되는게 맞는지요?
CODE 컬럼 값이 송수신BPM(006,007) 일 경우만 VAL 값을 합해주는 경우인가요?
기준이 명확하지가 않네요 일단 요청하신 데이터만 보고 작성해본 쿼리입니다.
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 '007', '2', '08:54','수신bpm' 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
하나만더 문의드리겠습니다.(_ _)
코드값 008,009 추가하게 된다면 쿼리가 어떻게되나요?
code val time name
001 4 08:52 메모리사용률
001 14 08:53 메모리사용률
006007 5 08:52 totalBpm
006007 10 08:53 totalBpm
006007 2 08:54 totalBpm
008009 5 08:54 totalP
table a
code val time name
001 4 08:52 메모리사용률
001 14 08:53 메모리사용률
006 3 08:52 송신bpm
006 4 08:53 송신bpm
007 2 08:52 수신bpm
007 6 08:53 수신bpm
008 2 08:54 송신P
009 3 08:54 수신P
(006, 007), (008,009) 을 묶을 때 기준은 time 값이 동일한 것인가요?
메모리사용률도 TIME이 동일하다면 묶어야 하나요?
송신만 있고 수신은 없으면요?
말씀하신 기준으로만 답변달아봅니다.
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 '007', '2', '08:54','수신bpm' FROM DUAL UNION ALL SELECT '008', '2', '08:54','송신P' FROM DUAL UNION ALL SELECT '009', '3', '08:54','수신P' FROM DUAL ) SELECT CASE WHEN CODE IN ('006','007') THEN '006007' WHEN CODE IN ('008','009') THEN '008009' ELSE CODE END CODE ,SUM(VAL) VAL ,TIME ,MAX(CASE WHEN CODE IN ('006','007') THEN 'TotalBpm' WHEN CODE IN ('008','009') THEN 'TotalIP' ELSE NAME END) NAME FROM T GROUP BY CASE WHEN CODE IN ('006','007') THEN '006007' WHEN CODE IN ('008','009') THEN '008009' ELSE CODE END, TIME ORDER BY 1,3,2