Oracle How to Generate Multiple Ranges Based on a Result Set

aggregateoracle

Given a table like this:

with qtys as (
  select 3 as qty from dual union all
  select 10 as qty from dual union all
  select 9 as qty from dual union all
  select 7 as qty from dual union all
  select 9 as qty from dual union all
  select 3 as qty from dual
)

I want to find the greatest common value of QTY.

I have thought of transforming each of these into a range and then getting the value from the result set that has the same count as the count in the table QTYS.

Only I'm not certain how to do this, and I'd like to do it with plain SQL in Oracle 11g, not a DECLARE…BEGIN…END PL/SQL block.

I've searched around and found many different methods of generating a single range based on a number, but I haven't found anything that would generate multiple ranges from zero to the value in the table and union them together as a single result set.

For example, given the above table I think if I could turn it into this:

Then I could find the count(1) of each of these and match it to the count(1) of the table QTYS, and use that value:

select count(1)
, qty
from (
  -- resultant ranges
)
group by qty
having count(1) = (
  select count(1)
  from qtys
);

In the case of the example table above, QTYS, the result of this query would be 3.

Maybe there is a better way to do this that I'm not thinking of, but I'm not coming to any conclusions yet.

Any thoughts?

Best Answer

Solved this with help from https://stackoverflow.com/questions/3664903/how-can-i-return-multiple-identical-rows-based-on-a-quantity-field-in-the-row-it

The resulting answer with the original table looks like this:

with qtys as (
  select 3 as qty from dual union all
  select 10 as qty from dual union all
  select 9 as qty from dual union all
  select 7 as qty from dual union all
  select 9 as qty from dual union all
  select 3 as qty from dual
)
, results as (
  select n qty
  from qtys x
  join (
    select rownum n
    from dual
    connect by level <= (
      select max(qty)
      from qtys
    )
  ) y on y.n <= x.qty
)
select max(qty) qty
from (
  select count(1)
  , qty
  from results
  group by qty
  having count(1) = (
    select count(1)
    from qtys
  )
);

I'm certain I can condense it a little, but it produces the desired result.

EDIT

I suppose looking closer at this taking the minimum would have produced the same, desired result:

with qtys as (
  select 3 as qty from dual union all
  select 10 as qty from dual union all
  select 9 as qty from dual union all
  select 7 as qty from dual union all
  select 9 as qty from dual union all
  select 3 as qty from dual
)
select min(qty) qty
from qtys;

Related Solutions

SQL Data aggregation

You can use analytic functions to achieve this:

select distinct pm.city cityid,
    to_char(trunc(pm.sampledate, 'MM'), 'YYYY-MM') as timepoint,
    count(distinct me.id)
        over (partition by pm.city, trunc(pm.sampledate, 'MM')) as mecount,
    count(distinct me.hotpointid)
        over (partition by pm.city, trunc(pm.sampledate, 'MM')) as hpcount,
    count(distinct me.id)
        over (partition by trunc(pm.sampledate, 'MM')) as totmecount,
    count(distinct me.hotpointid)
        over (partition by trunc(sampledate, 'MM')) as tothpcount
from upme me
join appmday pm on pm.apid = me.id
where pm.sampledate between to_date('2011-11-01', 'yyyy-MM-dd')
    and to_date('2012-1-31', 'yyyy-MM-dd');

Or instead of a distinct, use a subquery and a row_number() calculation to remove the duplicates:

select cityid, to_char(timepoint, 'YYYY-MM') as timepoint,
    mecount, hpcount, totmecount, tothpcount
from (
    select pm.city cityid,
        row_number() over (partition by pm.city, trunc(pm.sampledate, 'MM')
            order by pm.city, trunc(pm.sampledate, 'MM')) as rn,
        trunc(pm.sampledate, 'MM') as timepoint,
        count(distinct me.id)
            over (partition by pm.city, trunc(pm.sampledate, 'MM')) as mecount,
        count(distinct me.hotpointid)
            over (partition by pm.city, trunc(pm.sampledate, 'MM')) as hpcount,
        count(distinct me.id)
            over (partition by trunc(pm.sampledate, 'MM')) as totmecount,
        count(distinct me.hotpointid)
            over (partition by trunc(sampledate, 'MM')) as tothpcount
    from upme me
    join appmday pm on pm.apid = me.id
    where pm.sampledate between to_date('2011-11-01', 'yyyy-MM-dd')
        and to_date('2012-1-31', 'yyyy-MM-dd')
)
where rn = 1;

If your existing query is slow you may see a performance difference between the two options, otherwise it's a matter or preference.

(Also you may be aware of this, but if your sampledate has a time component, having your where clause end on '2012-1-31' means you'll only get data up to the start of that day, and not for example anything at 2012-01-31 01:00.)

Postgresql – Retrieve title from rows with a maximum count in other columns

Depending on your exact requirements, something along these lines should work:

Subqueries to determine most common attributes

SELECT DISTINCT title
FROM   archived
WHERE  showurl = (
   SELECT showurl
   FROM   archived
   GROUP  BY 1
   ORDER  BY count(*) DESC, 1
   LIMIT  1
   )
AND    reference = (
   SELECT reference
   FROM   archived
   GROUP  BY 1
   ORDER  BY count(*) DESC, 1
   LIMIT  1
   );

I added DISTINCT to the outer SELECT to fold duplicate titles. It's undefined what you need exactly.

Shorter with window functions

A more modern / sophisticated / elegant approach would be with two window functions in the ORDER BY clause, allowing for a very terse query:

SELECT title
FROM   archived
ORDER  BY count(*) OVER (PARTITION BY showurl)   DESC
         ,count(*) OVER (PARTITION BY reference) DESC
LIMIT 1;

Not necessarily faster than the first solution, though. Again, guessing what you might want exactly, I only retrieve a single winning title.

The trick here is to partition the rows by showurl and reference, run a count over each partition and order descending by that count. This way, you can determine a winner in a single query level.

This will not work, if there is no row with the most common showurl and the most common reference at the same time. Again, exact requirements would be needed ...

-> SQLfiddle demo.