데이터베이스 질문과 답변
안녕하세요. 도저히 답이 안나와서 이곳에 질문해봅니다.
| 10:30 | 0 |
| 10:32 | 1 |
| 10:34 | 2 |
| 10:36 | 3 |
| 10:38 | 4 |
| 10:40 | 10 |
| 10:42 | 0 |
| 10:44 | 1 |
| 10:46 | 3 |
| 10:48 | 5 |
| 10:50 | 7 |
| 10:52 | 9 |
| 10:54 | 8 |
| 10:56 | 7 |
| 10:58 | 0 |
| 11:00 | 5 |
| 11:02 | 6 |
| 11:04 | 7 |
| 11:06 | 15 |
| 11:08 | 14 |
| 11:10 | 15 |
| 11:12 | 15 |
| 11:14 | 15 |
| 11:16 | 10 |
| 11:18 | 5 |
| 11:20 | 0 |
위와 같은 데이터가 있을 때, 0 이후에 발생한 수치 중 각 최대값을 더하고 싶습니다.(추가로 횟수도요...)
위 데이터라면 10:40의 10, 10:52의 9, 11:06의 15가 더해진 34의 값을 얻고싶습니다.
참고로 시간으로는 구분이 불가능한 상황입니다.
방법을 가르쳐 주시면 감사하겠습니다..
by 마농
[2016.05.10 17:30:45]
WITH t AS ( SELECT '10:30' tm, 0 v FROM dual UNION ALL SELECT '10:32', 1 FROM dual UNION ALL SELECT '10:34', 2 FROM dual UNION ALL SELECT '10:36', 3 FROM dual UNION ALL SELECT '10:38', 4 FROM dual UNION ALL SELECT '10:40', 10 FROM dual UNION ALL SELECT '10:42', 0 FROM dual UNION ALL SELECT '10:44', 1 FROM dual UNION ALL SELECT '10:46', 3 FROM dual UNION ALL SELECT '10:48', 5 FROM dual UNION ALL SELECT '10:50', 7 FROM dual UNION ALL SELECT '10:52', 9 FROM dual UNION ALL SELECT '10:54', 8 FROM dual UNION ALL SELECT '10:56', 7 FROM dual UNION ALL SELECT '10:58', 0 FROM dual UNION ALL SELECT '11:00', 5 FROM dual UNION ALL SELECT '11:02', 6 FROM dual UNION ALL SELECT '11:04', 7 FROM dual UNION ALL SELECT '11:06', 15 FROM dual UNION ALL SELECT '11:08', 14 FROM dual UNION ALL SELECT '11:10', 15 FROM dual UNION ALL SELECT '11:12', 15 FROM dual UNION ALL SELECT '11:14', 15 FROM dual UNION ALL SELECT '11:16', 10 FROM dual UNION ALL SELECT '11:18', 5 FROM dual UNION ALL SELECT '11:20', 0 FROM dual ) SELECT SUM(MAX(v)) sum_peak , COUNT(*) cnt_peak FROM (SELECT tm, v , SUM(DECODE(v, 0, 1, 0)) OVER(ORDER BY tm) grp FROM t ) GROUP BY grp HAVING MAX(v) != 0 ;
by 광훨
[2016.05.10 17:45:51]
와... 감사합니다.
응용해보겠습니다. ^^
) 버튼을 클릭하여 작성 하시면 됩니다.