안녕하세요.
쿼리 작성하다 질문올려봅니다...
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 |
이런식으로 프로그램 전체 사용시간 중 몇 퍼센트에 해당하는 지 뽑으려고하는데
더 좋은 방법이 있다면 알려주시면 감사하겠습니다ㅠㅠ
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 ) ;
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 ;