Re: Group by wrong results?

The 2nd query I posted is returning the data I want (max number of sessions per minute, not a total count of sessions per minute). I pulled the queries apart and checked the data output.

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-l