데이터베이스 질문과 답변

합계 쿼리 질문입니다.. 0 2 654

by tester0101 [2020.01.08 16:59:27]

안녕하세요.

쿼리 작성하다 질문올려봅니다...

 

yyyymmdd user_id program_name used
2020-01-01 test1 notepad.exe 3
2020-01-01 test1 mspaint.exe 2
2020-01-01 test2 notepad.exe 3
2020-01-01 test2 mspaint.exe 3

 

대략 group by 를 사용하여 위와 같은 형태로 나오게 쿼리를 만들었는데..

 

yyyymmdd user_id program_name used sum_used
2020-01-01 test1 notepad.exe 3 6
2020-01-01 test1 mspaint.exe 2 5
2020-01-01 test2 notepad.exe 3 6
2020-01-01 test2 mspaint.exe 3 5

 

위 처럼 날짜, 프로그램명 별 used의 총 합인 sum_used를 각 row에 붙이고싶은데..

어떻게 하면 좋을까요?

 

yyyymmdd user_id program_name used sum_used ratio
2020-01-01 test1 notepad.exe 3 6 50
2020-01-01 test1 mspaint.exe 2 5 40
2020-01-01 test2 notepad.exe 3 6 50
2020-01-01 test2 mspaint.exe 3 5 60

 

이런식으로 프로그램 전체 사용시간 중 몇 퍼센트에 해당하는 지 뽑으려고하는데

더 좋은 방법이 있다면 알려주시면 감사하겠습니다ㅠㅠ

by 소주쵝오 [2020.01.08 17:10:46] 
with t(yyyymmdd, user_id, program_name) as
(select '2020-01-01', 'test1', 'notepad.exe' from dual union all
 select '2020-01-01', 'test1', 'notepad.exe' from dual union all
 select '2020-01-01', 'test1', 'notepad.exe' from dual union all
 select '2020-01-01', 'test1', 'mspaint.exe' from dual union all
 select '2020-01-01', 'test1', 'mspaint.exe' from dual union all
 select '2020-01-01', 'test2', 'notepad.exe' from dual union all
 select '2020-01-01', 'test2', 'notepad.exe' from dual union all
 select '2020-01-01', 'test2', 'notepad.exe' from dual union all
 select '2020-01-01', 'test2', 'mspaint.exe' from dual union all
 select '2020-01-01', 'test2', 'mspaint.exe' from dual union all
 select '2020-01-01', 'test2', 'mspaint.exe' from dual
)
select yyyymmdd
     , user_id
     , program_name
     , used
     , sum_used
     , used/sum_used*100 as ratio
from (select yyyymmdd
           , user_id
           , program_name
           , count(*) as used
           , sum(count(*)) over(partition by yyyymmdd, program_name) as sum_used
      from t
      group by yyyymmdd, user_id, program_name
      order by 1, 2, 3 desc
     )
;    

 

by 마농 [2020.01.09 11:37:48] 
WITH t(yyyymmdd, user_id, program_name) AS
(
SELECT '2020-01-01', 'test1', 'notepad.exe' FROM dual 
UNION ALL SELECT '2020-01-01', 'test1', 'notepad.exe' FROM dual
UNION ALL SELECT '2020-01-01', 'test1', 'notepad.exe' FROM dual
UNION ALL SELECT '2020-01-01', 'test1', 'mspaint.exe' FROM dual
UNION ALL SELECT '2020-01-01', 'test1', 'mspaint.exe' FROM dual
UNION ALL SELECT '2020-01-01', 'test2', 'notepad.exe' FROM dual
UNION ALL SELECT '2020-01-01', 'test2', 'notepad.exe' FROM dual
UNION ALL SELECT '2020-01-01', 'test2', 'notepad.exe' FROM dual
UNION ALL SELECT '2020-01-01', 'test2', 'mspaint.exe' FROM dual
UNION ALL SELECT '2020-01-01', 'test2', 'mspaint.exe' FROM dual
UNION ALL SELECT '2020-01-01', 'test2', 'mspaint.exe' FROM dual
)
SELECT yyyymmdd, user_id, program_name
     , COUNT(*) used
     , SUM(COUNT(*)) OVER(PARTITION BY yyyymmdd, program_name) sum_used
     , ROUND(RATIO_TO_REPORT(COUNT(*)) OVER(PARTITION BY yyyymmdd, program_name) * 100, 2) ratio
  FROM t
 GROUP BY yyyymmdd, user_id, program_name
 ORDER BY yyyymmdd, user_id, program_name
;

 

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