RE: Group by wrong results?
Date: Thu, 9 Aug 2018 10:22:40 -0400
Message-ID: <04c801d42fec$6df4bcd0$49de3670$_at_rsiz.com>
two things: your “correct” query appears as blank to me, and I thought this revision might be useful to pin-pointing the bug IF not already solved and IF it returns correct results.
Glad you have got it solved.
mwf
From: Daniel Fink [mailto:daniel.fink_at_returnpath.com]
Sent: Wednesday, August 08, 2018 2:34 PM
To: mwf_at_rsiz.com
Cc: oracle-l_at_freelists.org
Subject: Re: Group by wrong results?
On Wed, Aug 8, 2018 at 12:12 PM Mark W. Farnham <mwf_at_rsiz.com> wrote:
Hmm.
with session_count as
(
select sample_time, count(1) sess_count, min(trunc(sample_time,’MI’) sample_minute
from dba_hist_active_sess_history
where sample_time > trunc(sysdate-(1/24))
group by sample_time
)
select sample_minute, max(sess_count) max_sessions
from session_count
group by sample_minute
order by sample_minute
If I understand, you want at most a single time row per minute with that row being the sample with the most sessions (the high sampled session_count in each minute sampled).
I don’t believe your code is WRONG, but I’m curious whether calculating the minute in the initial view gets you the correct answer. I just typed this in, so it’s possible I fubar’d the syntax.
mwf
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Daniel Fink (Redacted sender "daniel.fink" for DMARC)
Sent: Tuesday, August 07, 2018 12:26 PM
To: oracle-l_at_freelists.org
Subject: Group by wrong results?
A script to extract session count from ash data is not returning the right results using a TO_CHAR(sample_time, 'YYYYMMDD hh24:mi') group by unless I use a factored subquery.
Query 1
WITH session_count AS
( SELECT sample_time, count(1) sess_count
FROM dba_hist_active_sess_history
WHERE sample_time >= TRUNC(sysdate - (1/24))
GROUP BY sample_time
)
SELECT TO_CHAR(sample_time, 'YYYYMMDD hh24:mi') sample_minute,
MAX(sess_count) max_sessions
FROM session_count
GROUP BY TO_CHAR(sample_time, 'YYYYMMDD hh24:mi')
ORDER BY sample_minute
/
Wrong Results example
SAMPLE_MINUTE SESS_COUNT
- ----------
20180727 09:09 5
20180727 09:09 1
20180727 09:09 1
20180727 09:09 5
20180727 09:09 2
20180727 09:09 4
20180727 09:10 3
20180727 09:10 5
20180727 09:10 1
20180727 09:10 4
20180727 09:10 7
20180727 09:10 9
If I add another factored subquery, I get the right aggregation
WITH session_count AS
( SELECT sample_time, count(1) sess_count
FROM dba_hist_active_sess_history
WHERE sample_time >= sysdate - (1/24)
GROUP BY sample_time
),
session_minutes
AS
( SELECT TO_CHAR(sample_time, 'YYYYMMDD hh24:mi') sample_minute,
sess_count
FROM session_count
)
SELECT sample_minute,
MAX(sess_count)
FROM session_minutes
GROUP BY sample_minute
ORDER BY sample_minute
Correct results
SAMPLE_MINUTE MAX(SESS_COUNT)
- ---------------
20180727 09:01 22
20180727 09:02 22
20180727 09:03 15
20180727 09:04 10
20180727 09:05 11
20180727 09:06 10
20180727 09:07 20
20180727 09:08 20
20180727 09:09 5
20180727 09:10 9
20180727 09:11 12
20180727 09:12 12
20180727 09:13 9
20180727 09:14 6
20180727 09:15 3
Why is the first query (which looks correct to me) not properly aggregating?
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Aug 09 2018 - 16:22:40 CEST