SQL> select * from event_log;
EVENT_ID EVENT_DATE
---------- -------------------
228 2012-03-07 15:13:53
9 2012-03-07 01:20:25
76 2012-03-07 22:27:24
15 2012-03-07 07:32:07
106 2012-03-08 15:35:01
104 2012-03-07 00:55:58
105 2012-03-06 01:06:36
85 2012-03-08 08:59:29
284 2012-03-08 03:42:35
39 2012-03-09 05:49:49
64 2012-03-06 12:51:51
33 2012-03-06 01:00:19
37 2012-03-06 13:00:27
....
SQL> SELECT event_id, COUNT(*)
2 FROM event_log
3 GROUP BY event_id
4 ORDER BY 2 DESC;
EVENT_ID COUNT(*)
---------- ----------
9 15
3 15
1 12
8 12
5 12
30 12
38 11
106 11
61 11
54 10
47 10
12 10
65 10
7 10
22 9
34 9
100 9
29 9
73 9
6 9
...
SQL> SELECT event_id, cnt
2 FROM (SELECT event_id, COUNT(*) cnt
3 FROM event_log
4 GROUP BY event_id
5 ORDER BY 2 DESC)
6 WHERE ROWNUM <= 5;
EVENT_ID CNT
---------- ----------
3 15
9 15
1 12
5 12
30 12
SQL> SELECT event_id, cnt, maxdate
2 FROM (SELECT event_id, COUNT(*) cnt, MAX(event_date) maxdate
3 FROM event_log
4 GROUP BY event_id
5 ORDER BY 2 DESC, 3 desc)
6 WHERE ROWNUM <= 6;
EVENT_ID CNT MAXDATE
---------- ---------- -------------------
3 15 2012-03-09 04:12:36
9 15 2012-03-08 17:46:10
30 12 2012-03-10 02:19:51
1 12 2012-03-09 22:35:27
5 12 2012-03-09 08:51:27
8 12 2012-03-09 07:08:20
SQL> SELECT event_id, cnt, maxdate
2 FROM (SELECT event_id, COUNT(*) cnt, MAX(event_date) maxdate
3 FROM event_log
4 GROUP BY event_id
5 ORDER BY 2 DESC, 3 desc)
6 WHERE ROWNUM <= 5;
EVENT_ID CNT MAXDATE
---------- ---------- -------------------
3 15 2012-03-09 04:12:36
9 15 2012-03-08 17:46:10
30 12 2012-03-10 02:19:51
1 12 2012-03-09 22:35:27
5 12 2012-03-09 08:51:27
SQL> SELECT event_id, event_date
2 FROM (SELECT event_id, event_date
3 FROM event_log
4 ORDER BY event_date DESC)
5 WHERE ROWNUM <= 10;
EVENT_ID EVENT_DATE
---------- -------------------
67 2012-03-10 08:20:37
254 2012-03-10 06:43:31
30 2012-03-10 02:19:51
1 2012-03-09 22:35:27
134 2012-03-09 19:05:10
16 2012-03-09 16:17:44
68 2012-03-09 15:33:54
200 2012-03-09 13:35:05
146 2012-03-09 12:23:27
190 2012-03-09 12:09:28
SQL> SELECT event_id, event_date
2 FROM (SELECT event_id, event_date
3 FROM event_log
4 WHERE event_id NOT IN (SELECT event_id
5 FROM (SELECT event_id, COUNT(*) cnt, MAX(event_date) maxdate
6 FROM event_log
7 GROUP BY event_id
8 ORDER BY 2 DESC, 3 DESC)
9 WHERE ROWNUM <= 5)
10 ORDER BY event_date DESC)
11 WHERE ROWNUM <= 10;
EVENT_ID EVENT_DATE
---------- -------------------
67 2012-03-10 08:20:37
254 2012-03-10 06:43:31
134 2012-03-09 19:05:10
16 2012-03-09 16:17:44
68 2012-03-09 15:33:54
200 2012-03-09 13:35:05
146 2012-03-09 12:23:27
190 2012-03-09 12:09:28
79 2012-03-09 12:01:51
168 2012-03-09 11:09:08
10 rows selected.
SQL> SELECT event_id, cnt, maxdate
2 FROM (SELECT event_id, COUNT(*) cnt, MAX(event_date) maxdate
3 FROM event_log
4 GROUP BY event_id
5 ORDER BY 2 DESC, 3 DESC)
6 WHERE ROWNUM <= 5
7 union all
8 SELECT event_id, null, event_date
9 FROM (SELECT event_id, event_date
10 FROM event_log
11 WHERE event_id NOT IN (SELECT event_id
12 FROM (SELECT event_id, COUNT(*) cnt, MAX(event_date) maxdate
13 FROM event_log
14 GROUP BY event_id
15 ORDER BY 2 DESC, 3 DESC)
16 WHERE ROWNUM <= 5)
17 ORDER BY event_date DESC)
18 WHERE ROWNUM <= 10;
EVENT_ID CNT MAXDATE
---------- ---------- -------------------
3 15 2012-03-09 04:12:36
9 15 2012-03-08 17:46:10
30 12 2012-03-10 02:19:51
1 12 2012-03-09 22:35:27
5 12 2012-03-09 08:51:27
67 2012-03-10 08:20:37
254 2012-03-10 06:43:31
134 2012-03-09 19:05:10
16 2012-03-09 16:17:44
68 2012-03-09 15:33:54
200 2012-03-09 13:35:05
146 2012-03-09 12:23:27
190 2012-03-09 12:09:28
79 2012-03-09 12:01:51
168 2012-03-09 11:09:08
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2002 consistent gets
0 physical reads
0 redo size
721 bytes sent via SQL*Net to client
407 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
570 sorts (memory)
0 sorts (disk)
15 rows processed
SQL> SELECT event_id,
2 event_date,
3 ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY event_date DESC) datern,
4 COUNT(*) OVER (PARTITION BY event_id) cnt
5 FROM event_log;
EVENT_ID EVENT_DATE DATERN CNT
---------- ------------------- ---------- ----------
0 2012-03-08 06:21:59 1 5
0 2012-03-07 17:47:54 2 5
0 2012-03-07 05:56:34 3 5
0 2012-03-07 02:42:24 4 5
0 2012-03-06 18:33:37 5 5
1 2012-03-09 22:35:27 1 12
1 2012-03-08 22:20:09 2 12
1 2012-03-08 03:28:43 3 12
1 2012-03-08 02:23:03 4 12
1 2012-03-08 00:21:47 5 12
1 2012-03-07 11:16:54 6 12
1 2012-03-07 07:39:22 7 12
1 2012-03-07 02:17:49 8 12
1 2012-03-07 01:58:17 9 12
1 2012-03-06 19:53:21 10 12
1 2012-03-06 17:11:25 11 12
1 2012-03-05 17:36:21 12 12
2 2012-03-08 07:56:31 1 4
2 2012-03-07 22:11:33 2 4
2 2012-03-07 04:12:57 3 4
2 2012-03-06 09:25:23 4 4
3 2012-03-09 04:12:36 1 15
3 2012-03-08 22:18:34 2 15
3 2012-03-08 08:23:16 3 15
3 2012-03-07 16:07:27 4 15
...
SQL> SELECT event_id,
2 event_date,
3 datern,
4 cnt,
5 ROW_NUMBER() OVER (ORDER BY cnt DESC, event_date DESC) cntrn
6 FROM (SELECT event_id,
7 event_date,
8 ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY event_date DESC) datern,
9 COUNT(*) OVER (PARTITION BY event_id) cnt
10 FROM event_log)
11 WHERE datern = 1;
EVENT_ID EVENT_DATE DATERN CNT CNTRN
---------- ------------------- ---------- ---------- ----------
3 2012-03-09 04:12:36 1 15 1
9 2012-03-08 17:46:10 1 15 2
30 2012-03-10 02:19:51 1 12 3
1 2012-03-09 22:35:27 1 12 4
5 2012-03-09 08:51:27 1 12 5
8 2012-03-09 07:08:20 1 12 6
106 2012-03-08 15:35:01 1 11 7
38 2012-03-08 15:22:04 1 11 8
61 2012-03-08 09:57:17 1 11 9
54 2012-03-09 00:18:46 1 10 10
65 2012-03-08 17:19:22 1 10 11
12 2012-03-08 07:54:54 1 10 12
47 2012-03-08 06:22:32 1 10 13
7 2012-03-08 05:12:03 1 10 14
26 2012-03-09 06:42:13 1 9 15
100 2012-03-08 23:24:27 1 9 16
34 2012-03-08 19:07:45 1 9 17
33 2012-03-08 14:47:33 1 9 18
6 2012-03-08 13:20:39 1 9 19
73 2012-03-08 08:57:17 1 9 20
22 2012-03-08 05:05:41 1 9 21
29 2012-03-08 04:27:15 1 9 22
77 2012-03-07 18:43:47 1 9 23
25 2012-03-09 10:45:12 1 8 24
21 2012-03-09 10:21:11 1 8 25
39 2012-03-09 05:49:49 1 8 26
83 2012-03-09 00:55:41 1 8 27
40 2012-03-09 00:44:21 1 8 28
112 2012-03-08 19:51:43 1 8 29
10 2012-03-08 16:43:33 1 8 30
56 2012-03-08 10:02:15 1 8 31
23 2012-03-08 06:51:25 1 8 32
24 2012-03-08 06:16:38 1 8 33
72 2012-03-07 18:31:40 1 8 34
...
SQL> SELECT event_id,
2 event_date,
3 datern,
4 cnt,
5 cntrn,
6 CASE WHEN cntrn <= 5 THEN cntrn END countgroup
7 FROM (SELECT event_id,
8 event_date,
9 datern,
10 cnt,
11 ROW_NUMBER() OVER (ORDER BY cnt DESC, event_date DESC) cntrn
12 FROM (SELECT event_id,
13 event_date,
14 ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY event_date DESC) datern,
15 COUNT(*) OVER (PARTITION BY event_id) cnt
16 FROM event_log)
17 WHERE datern = 1)
18 ORDER BY countgroup nulls last;
EVENT_ID EVENT_DATE DATERN CNT CNTRN COUNTGROUP
---------- ------------------- ---------- ---------- ---------- ----------
3 2012-03-09 04:12:36 1 15 1 1
9 2012-03-08 17:46:10 1 15 2 2
30 2012-03-10 02:19:51 1 12 3 3
1 2012-03-09 22:35:27 1 12 4 4
5 2012-03-09 08:51:27 1 12 5 5
8 2012-03-09 07:08:20 1 12 6
106 2012-03-08 15:35:01 1 11 7
38 2012-03-08 15:22:04 1 11 8
61 2012-03-08 09:57:17 1 11 9
54 2012-03-09 00:18:46 1 10 10
65 2012-03-08 17:19:22 1 10 11
12 2012-03-08 07:54:54 1 10 12
47 2012-03-08 06:22:32 1 10 13
7 2012-03-08 05:12:03 1 10 14
26 2012-03-09 06:42:13 1 9 15
100 2012-03-08 23:24:27 1 9 16
34 2012-03-08 19:07:45 1 9 17
33 2012-03-08 14:47:33 1 9 18
6 2012-03-08 13:20:39 1 9 19
73 2012-03-08 08:57:17 1 9 20
22 2012-03-08 05:05:41 1 9 21
29 2012-03-08 04:27:15 1 9 22
77 2012-03-07 18:43:47 1 9 23
25 2012-03-09 10:45:12 1 8 24
21 2012-03-09 10:21:11 1 8 25
39 2012-03-09 05:49:49 1 8 26
83 2012-03-09 00:55:41 1 8 27
40 2012-03-09 00:44:21 1 8 28
112 2012-03-08 19:51:43 1 8 29
10 2012-03-08 16:43:33 1 8 30
56 2012-03-08 10:02:15 1 8 31
23 2012-03-08 06:51:25 1 8 32
24 2012-03-08 06:16:38 1 8 33
72 2012-03-07 18:31:40 1 8 34
64 2012-03-07 12:52:13 1 8 35
...
SQL> SELECT event_id,
2 event_date,
3 datern,
4 cnt,
5 cntrn,
6 countgroup
7 FROM (SELECT event_id,
8 event_date,
9 datern,
10 cnt,
11 cntrn,
12 CASE WHEN cntrn <= 5 THEN cntrn END countgroup
13 FROM (SELECT event_id,
14 event_date,
15 datern,
16 cnt,
17 ROW_NUMBER() OVER (ORDER BY cnt DESC, event_date DESC) cntrn
18 FROM (SELECT event_id,
19 event_date,
20 ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY event_date D
ESC)
21 datern,
22 COUNT(*) OVER (PARTITION BY event_id) cnt
23 FROM event_log)
24 WHERE datern = 1))
25 ORDER BY countgroup NULLS LAST, event_date DESC;
EVENT_ID EVENT_DATE DATERN CNT CNTRN COUNTGROUP
---------- ------------------- ---------- ---------- ---------- ----------
3 2012-03-09 04:12:36 1 15 1 1
9 2012-03-08 17:46:10 1 15 2 2
30 2012-03-10 02:19:51 1 12 3 3
1 2012-03-09 22:35:27 1 12 4 4
5 2012-03-09 08:51:27 1 12 5 5
67 2012-03-10 08:20:37 1 4 94
254 2012-03-10 06:43:31 1 1 188
134 2012-03-09 19:05:10 1 1 189
16 2012-03-09 16:17:44 1 7 36
68 2012-03-09 15:33:54 1 6 57
200 2012-03-09 13:35:05 1 1 190
146 2012-03-09 12:23:27 1 6 58
190 2012-03-09 12:09:28 1 1 191
79 2012-03-09 12:01:51 1 7 37
168 2012-03-09 11:09:08 1 2 146
25 2012-03-09 10:45:12 1 8 24
87 2012-03-09 10:30:38 1 4 95
21 2012-03-09 10:21:11 1 8 25
41 2012-03-09 07:47:26 1 5 76
8 2012-03-09 07:08:20 1 12 6
93 2012-03-09 07:04:00 1 7 38
172 2012-03-09 06:58:36 1 2 147
52 2012-03-09 06:45:10 1 2 148
26 2012-03-09 06:42:13 1 9 15
15 2012-03-09 05:57:06 1 6 59
39 2012-03-09 05:49:49 1 8 26
27 2012-03-09 05:12:15 1 6 60
98 2012-03-09 04:44:38 1 6 61
82 2012-03-09 04:41:59 1 7 39
...
SQL> SELECT event_id, event_date, cnt
2 FROM (SELECT event_id,
3 event_date,
4 datern,
5 cnt,
6 cntrn,
7 countgroup
8 FROM (SELECT event_id,
9 event_date,
10 datern,
11 cnt,
12 cntrn,
13 CASE WHEN cntrn <= 5 THEN cntrn END countgroup
14 FROM (SELECT event_id,
15 event_date,
16 datern,
17 cnt,
18 ROW_NUMBER() OVER (ORDER BY cnt DESC, event_date DESC) cntrn
19 FROM (SELECT event_id,
20 event_date,
21 ROW_NUMBER()
22 OVER (PARTITION BY event_id ORDER BY event_date DE
SC)
23 datern,
24 COUNT(*) OVER (PARTITION BY event_id) cnt
25 FROM event_log)
26 WHERE datern = 1))
27 ORDER BY countgroup NULLS LAST, event_date DESC)
28 WHERE ROWNUM <= 15;
EVENT_ID EVENT_DATE CNT
---------- ------------------- ----------
3 2012-03-09 04:12:36 15
9 2012-03-08 17:46:10 15
30 2012-03-10 02:19:51 12
1 2012-03-09 22:35:27 12
5 2012-03-09 08:51:27 12
67 2012-03-10 08:20:37 4
254 2012-03-10 06:43:31 1
134 2012-03-09 19:05:10 1
16 2012-03-09 16:17:44 7
68 2012-03-09 15:33:54 6
200 2012-03-09 13:35:05 1
146 2012-03-09 12:23:27 6
190 2012-03-09 12:09:28 1
79 2012-03-09 12:01:51 7
168 2012-03-09 11:09:08 2
15 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
750 bytes sent via SQL*Net to client
407 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
15 rows processed
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (9)
Author
Commented:Also, I prefer to write in a conversational style when discussing a topic without rigor. Contrast to some of the other topics I've written about. Different styles depending on the nature of the discussion.
Thanks again though. I'll your position into consideration when writing my next article.
Commented:
From a different point of view, the style here actually appealed to me - and made the article an enjoyable read.
I agree that style should be dependent on the topic, on the target audience and on the mood/tone the Author wants to set. The style for a formal research paper should be different than that for an article aimed at people who are kicked back, feet up on the desk, after work with a beer and burger, reading the article more for the fun of it than out of necessity (picturing some of the Experts here answering questions in their spare time :-) ).
Those are two opposite extremes of course, but just examples of why you might want to use conversational vs formal styles.
Commented:
Be Familiar with the data
look for exceptions (or "magic" values)
look at the constraints (eg. can there be nulls?)
Think Hard about the Requirements
Consider alternatives
Confirm requirements from early results - this can lead to 'unstated' requirements
Perform Trials
consider the results, are they meeting the requirments?
discuss results (can lead to 'unstated requirements')
iterate (more trials)
Consider Performance!
don't just guess, get facts
use those facts to help decide: Am I finished?
query duration isn't necessarily the best measure
'gets and sorts' statistics will better reflect scalability
I especially liked this, now I know why I joined in
Thank you sdstuber
Author
Commented:If you're not interested in my intro, an outline isn't going to sell it.
If you are interested, then you don't need it. Including one would be redundant since the actual text is pretty short.
Thanks again for reading!
Commented:
This sight and the experts that take the time to help are a true blessing and I am grateful for that. I look forward to the day I can do the same.
Thanks sdstuber!
View More