Home » RDBMS Server » Performance Tuning » Returning all rows that equal a max(value) (Oracle,11.1.0.6,Linux)
 Returning all rows that equal a max(value) [message #479144] |
Thu, 14 October 2010 13:56  |
mandie722
Messages: 2
Registered: October 2010
|
Junior Member |
|
|
I'm trying to write a query that counts how many sessions are active during a 1 second time interval, then returns the maximum number of sessions active during any time interval, and all the time intervals that hit that max.
Here's a sample of the inner query results:
"INTERVAL_VALUE" "SESSIONS"
"13:14:47" 13
"13:14:52" 13
"13:14:54" 13
"13:19:05" 4
"13:19:28" 4
"13:19:37" 4
"13:09:45" 1
"13:09:46" 1
"13:10:15" 11
"13:10:21" 11
The max(sessions) is 13, so what I want the final output to be is:
"INTERVAL_VALUE" "SESSIONS"
"13:14:47" 13
"13:14:52" 13
"13:14:54" 13
I have a query that gives me what I want, but it's incredibly inefficient, and I'm hoping someone can give me a more efficient/more elegant way to get this information.
Here is the create sql for the test data:
CREATE TABLE "SESSION_TABLE"
(
"SESSIONKEY" NUMBER,
"SESSION_START_TIME" TIMESTAMP,
"SESSION_END_TIME" TIMESTAMP,
CONSTRAINT "PK_SESSIONKEY" PRIMARY KEY ("SESSIONKEY")
);
CREATE TABLE "SYSTEM_SETTINGS"
(
"SETTING_TYPE" VARCHAR2(25 CHAR),
"SETTING_VALUE" VARCHAR2(1024 CHAR)
);
INSERT INTO SYSTEM_SETTINGS(SETTING_TYPE,SETTING_VALUE)
VALUES ('LICENSELIMIT',100);
INSERT INTO SESSION_TABLE (SESSIONKEY, SESSION_START_TIME, SESSION_END_TIME)
VALUES (1,TO_TIMESTAMP('13-OCT-10 13:10:01','DD-MON-YY HH24:MI:SS'),
TO_TIMESTAMP('13-OCT-10 13:15:01','DD-MON-YY HH24:MI:SS'));
INSERT INTO SESSION_TABLE (SESSIONKEY, SESSION_START_TIME, SESSION_END_TIME)
VALUES (2,TO_TIMESTAMP('13-OCT-10 13:10:01','DD-MON-YY HH24:MI:SS'),
TO_TIMESTAMP('13-OCT-10 13:15:01','DD-MON-YY HH24:MI:SS'));
INSERT INTO SESSION_TABLE (SESSIONKEY, SESSION_START_TIME, SESSION_END_TIME)
VALUES (3,TO_TIMESTAMP('13-OCT-10 13:10:01','DD-MON-YY HH24:MI:SS'),
TO_TIMESTAMP('13-OCT-10 13:15:01','DD-MON-YY HH24:MI:SS'));
INSERT INTO SESSION_TABLE (SESSIONKEY, SESSION_START_TIME, SESSION_END_TIME)
VALUES (4,TO_TIMESTAMP('13-OCT-10 13:10:01','DD-MON-YY HH24:MI:SS'),
TO_TIMESTAMP('13-OCT-10 13:15:01','DD-MON-YY HH24:MI:SS'));
INSERT INTO SESSION_TABLE (SESSIONKEY, SESSION_START_TIME, SESSION_END_TIME)
VALUES (5,TO_TIMESTAMP('13-OCT-10 13:10:01','DD-MON-YY HH24:MI:SS'),
TO_TIMESTAMP('13-OCT-10 13:15:01','DD-MON-YY HH24:MI:SS'));
INSERT INTO SESSION_TABLE (SESSIONKEY, SESSION_START_TIME, SESSION_END_TIME)
VALUES (6,TO_TIMESTAMP('13-OCT-10 13:10:01','DD-MON-YY HH24:MI:SS'),
TO_TIMESTAMP('13-OCT-10 13:15:01','DD-MON-YY HH24:MI:SS'));
INSERT INTO SESSION_TABLE (SESSIONKEY, SESSION_START_TIME, SESSION_END_TIME)
VALUES (7,TO_TIMESTAMP('13-OCT-10 13:10:01','DD-MON-YY HH24:MI:SS'),
TO_TIMESTAMP('13-OCT-10 13:15:01','DD-MON-YY HH24:MI:SS'));
INSERT INTO SESSION_TABLE (SESSIONKEY, SESSION_START_TIME, SESSION_END_TIME)
VALUES (8,TO_TIMESTAMP('13-OCT-10 13:10:01','DD-MON-YY HH24:MI:SS'),
TO_TIMESTAMP('13-OCT-10 13:15:01','DD-MON-YY HH24:MI:SS'));
INSERT INTO SESSION_TABLE (SESSIONKEY, SESSION_START_TIME, SESSION_END_TIME)
VALUES (9,TO_TIMESTAMP('13-OCT-10 13:10:01','DD-MON-YY HH24:MI:SS'),
TO_TIMESTAMP('13-OCT-10 13:15:01','DD-MON-YY HH24:MI:SS'));
INSERT INTO SESSION_TABLE (SESSIONKEY, SESSION_START_TIME, SESSION_END_TIME)
VALUES (10,TO_TIMESTAMP('13-OCT-10 13:08:01','DD-MON-YY HH24:MI:SS'),
TO_TIMESTAMP('13-OCT-10 13:12:01','DD-MON-YY HH24:MI:SS'));
INSERT INTO SESSION_TABLE (SESSIONKEY, SESSION_START_TIME, SESSION_END_TIME)
VALUES (11,TO_TIMESTAMP('13-OCT-10 13:12:01','DD-MON-YY HH24:MI:SS'),
TO_TIMESTAMP('13-OCT-10 13:15:01','DD-MON-YY HH24:MI:SS'));
INSERT INTO SESSION_TABLE (SESSIONKEY, SESSION_START_TIME, SESSION_END_TIME)
VALUES (12,TO_TIMESTAMP('13-OCT-10 13:10:01','DD-MON-YY HH24:MI:SS'),
TO_TIMESTAMP('13-OCT-10 13:11:00','DD-MON-YY HH24:MI:SS'));
INSERT INTO SESSION_TABLE (SESSIONKEY, SESSION_START_TIME, SESSION_END_TIME)
VALUES (13,TO_TIMESTAMP('13-OCT-10 13:19:01','DD-MON-YY HH24:MI:SS'),
TO_TIMESTAMP('13-OCT-10 13:20:01','DD-MON-YY HH24:MI:SS'));
INSERT INTO SESSION_TABLE (SESSIONKEY, SESSION_START_TIME, SESSION_END_TIME)
VALUES (14,TO_TIMESTAMP('13-OCT-10 13:19:01','DD-MON-YY HH24:MI:SS'),
TO_TIMESTAMP('13-OCT-10 13:20:01','DD-MON-YY HH24:MI:SS'));
INSERT INTO SESSION_TABLE (SESSIONKEY, SESSION_START_TIME, SESSION_END_TIME)
VALUES (15,TO_TIMESTAMP('13-OCT-10 13:19:01','DD-MON-YY HH24:MI:SS'),
TO_TIMESTAMP('13-OCT-10 13:20:01','DD-MON-YY HH24:MI:SS'));
INSERT INTO SESSION_TABLE (SESSIONKEY, SESSION_START_TIME, SESSION_END_TIME)
VALUES (16,TO_TIMESTAMP('13-OCT-10 13:19:01','DD-MON-YY HH24:MI:SS'),
TO_TIMESTAMP('13-OCT-10 13:20:01','DD-MON-YY HH24:MI:SS'));
INSERT INTO SESSION_TABLE (SESSIONKEY, SESSION_START_TIME, SESSION_END_TIME)
VALUES (17,TO_TIMESTAMP('13-OCT-10 13:15:01','DD-MON-YY HH24:MI:SS'),
TO_TIMESTAMP('13-OCT-10 13:15:02','DD-MON-YY HH24:MI:SS'));
INSERT INTO SESSION_TABLE (SESSIONKEY, SESSION_START_TIME, SESSION_END_TIME)
VALUES (18,TO_TIMESTAMP('13-OCT-10 13:13:01','DD-MON-YY HH24:MI:SS'),
TO_TIMESTAMP('13-OCT-10 13:15:01','DD-MON-YY HH24:MI:SS'));
INSERT INTO SESSION_TABLE (SESSIONKEY, SESSION_START_TIME, SESSION_END_TIME)
VALUES (19,TO_TIMESTAMP('13-OCT-10 13:13:01','DD-MON-YY HH24:MI:SS'),
TO_TIMESTAMP('13-OCT-10 13:15:01','DD-MON-YY HH24:MI:SS'));
INSERT INTO SESSION_TABLE (SESSIONKEY, SESSION_START_TIME, SESSION_END_TIME)
VALUES (20,TO_TIMESTAMP('13-OCT-10 13:10:25','DD-MON-YY HH24:MI:SS'),
TO_TIMESTAMP('13-OCT-10 13:15:04','DD-MON-YY HH24:MI:SS'));
Here is my query that works:
SELECT
maxval.interval_value,
allval.sessions,
licenselimit
FROM
(SELECT
CASE WHEN sessions = (SELECT MAX(SESSIONS) FROM (
WITH all_periods AS
(
SELECT
TO_DATE ('13-OCT-10', 'DD-Mon-YY') + ( (LEVEL - 1) /
(24 * 60 * 60) ) AS period_start,
TO_DATE ('13-OCT-10', 'DD-Mon-YY') + ( LEVEL /
(24 * 60 * 60) ) AS next_period_start
FROM dual
CONNECT BY LEVEL <= 24 * 60 * 60
)
SELECT
TO_CHAR (ap.period_start, 'HH24:MI:SS') interval_value,
COUNT(*) sessions
FROM
all_periods ap
JOIN session_table sh ON
(sh.session_start_time < ap.next_period_start
AND sh.session_end_time > ap.period_start)
OR (sh.session_start_time < ap.next_period_start
AND sh.session_end_time is null)
WHERE
sh.session_start_time BETWEEN TO_DATE ('13-OCT-10', 'DD-Mon-YY')
AND TO_DATE ('13-OCT-10', 'DD-Mon-YY')+1
GROUP BY
TO_CHAR (ap.period_start, 'HH24:MI:SS')
ORDER BY
TO_CHAR (ap.period_start, 'HH24:MI:SS')
)) THEN interval_value END interval_value
FROM
( WITH all_periods AS
(
SELECT
TO_DATE ('13-OCT-10', 'DD-Mon-YY') + ( (LEVEL - 1) /
(24 * 60 * 60) ) AS period_start,
TO_DATE ('13-OCT-10', 'DD-Mon-YY') + ( LEVEL /
(24 * 60 * 60) ) AS next_period_start
FROM dual
CONNECT BY LEVEL <= 24 * 60 * 60
)
SELECT
TO_CHAR (ap.period_start, 'HH24:MI:SS') interval_value,
COUNT(*) sessions
FROM
all_periods ap
JOIN session_table sh ON
(sh.session_start_time < ap.next_period_start
AND sh.session_end_time > ap.period_start)
OR (sh.session_start_time < ap.next_period_start
AND sh.session_end_time is null)
WHERE
sh.session_start_time BETWEEN TO_DATE ('13-OCT-10', 'DD-Mon-YY')
AND TO_DATE ('13-OCT-10', 'DD-Mon-YY')+1
GROUP BY
TO_CHAR (ap.period_start, 'HH24:MI:SS')
)
) maxval JOIN (
WITH all_periods AS
(
SELECT
TO_DATE ('13-OCT-10', 'DD-Mon-YY') + ( (LEVEL - 1) /
(24 * 60 * 60) ) AS period_start,
TO_DATE ('13-OCT-10', 'DD-Mon-YY') + ( LEVEL /
(24 * 60 * 60) ) AS next_period_start
FROM dual
CONNECT BY LEVEL <= 24 * 60 * 60
)
SELECT
TO_CHAR (ap.period_start, 'HH24:MI:SS') interval_value,
COUNT(*) sessions
FROM
all_periods ap
JOIN session_table sh ON
(sh.session_start_time < ap.next_period_start
AND sh.session_end_time > ap.period_start)
OR (sh.session_start_time < ap.next_period_start
AND sh.session_end_time is null)
WHERE
sh.session_start_time BETWEEN TO_DATE ('13-OCT-10', 'DD-Mon-YY')
AND TO_DATE ('13-OCT-10', 'DD-Mon-YY')+1
GROUP BY
TO_CHAR (ap.period_start, 'HH24:MI:SS')
) allval ON maxval.interval_value = allval.interval_value,
( SELECT
setting_value licenselimit
FROM
system_settings
WHERE
setting_type = 'LICENSELIMIT'
);
Thanks for any advice.
|
|
|
|
| Re: Returning all rows that equal a max(value) [message #479145 is a reply to message #479144] |
Thu, 14 October 2010 14:14  |
 |
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
SQL> with
2 minmax as (
3 select min(session_start_time) mintim,
4 max(session_end_time) maxtim
5 from session_table
6 ),
7 times as (
8 select mintim+(level-1)/86400 tim
9 from minmax
10 connect by level <= ((maxtim+0) - (mintim+0)) * 86400 + 1
11 ),
12 counts as (
13 select tim, count(*) nb,
14 max(count(*)) over () maxnb
15 from times, session_table
16 where tim between session_start_time and session_end_time
17 group by tim
18 )
19 select tim, nb
20 from counts
21 where nb = maxnb
22 order by 1
23 /
TIM NB
-------------------- ----------
13-OCT-2010 13:15:01 14
Regards
Michel
|
|
|
|
| Re: Returning all rows that equal a max(value) [message #479252 is a reply to message #479145] |
Fri, 15 October 2010 10:06 |
mandie722
Messages: 2
Registered: October 2010
|
Junior Member |
|
|
Thank you for this, I hadn't realize my query was returning inaccurate results until I compared it to yours in detail. My query execution time has gone from 30 seconds down to less than 1 second.
I learned a lot more about using with. I appreciate the help.
|
|
|
|
Goto Forum:
Current Time: Tue Apr 30 04:27:38 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
