3

I have a problem with sql data aggregation problem.here is the sample sql.I am using oracle database.

select pm.city cityid, to_char(pm.sampledate, 'yyyy-MM') timepoint, count(distinct   me.id) mecount, count(distinct me.hotpointid) hpcount
from upme me, appmday pm
where pm.apid = me.id
          and pm.sampledate between to_date('2011-11-01', 'yyyy-MM-dd') and
          to_date('2012-1-31', 'yyyy-MM-dd')
group by pm.city, to_char(pm.sampledate, 'yyyy-MM')

above i get the count of my data.but i also i want to get the overall result like

select   to_char(pm.sampledate, 'yyyy-MM'), count(distinct me.id), count(distinct me.hotpointid)
from upme me, appmday pm
        where pm.apid = me.id
              and pm.sampledate between to_date('2011-11-01', 'yyyy-MM-dd') and
              to_date('2012-1-31', 'yyyy-MM-dd')
              group by to_char(pm.sampledate, 'yyyy-MM')

basically i want get some result from sql ,in the same sql i want to get the count from the previous result,they both show in the result in one sql. i did not want to union these two sql ,because each sql is very slow.

did someone have a idea? or it cannot be done by one sql without union?

thank you for the reply.

4
  • 1
    What database system, and which version?? SQL is just the Structured Query Language - a language used by many database systems - SQL is NOT a database product... stuff like this is very often vendor-specific - so we really need to know what database system you're using....
    – marc_s
    Feb 20, 2012 at 8:45
  • Should be oracle ( to_char, to_data, ... )
    – danihp
    Feb 20, 2012 at 8:51
  • i found there is a dba forum.so i also post it there.
    – snow8261
    Feb 20, 2012 at 10:50
  • Can you provide your table structure, some sample data and expected output?
    – Alex Poole
    Feb 20, 2012 at 12:03

2 Answers 2

4

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.)

1
  • the row_number() calculation is fast than the distinct one.thanks a lot.
    – snow8261
    Feb 20, 2012 at 12:51
1

You would need to use analytic functions to get that result. I'm not completely sure that I understand exactly what results you're looking for, but something like this would appear to be what you're looking for.

select distinct
       pm.city cityid, 
       to_char(pm.sampledate, 'yyyy-MM') timepoint, 
       count(distinct   me.id) 
         over (partition by to_char(pm.sampledate, 'yyyy-mm'), pm.city) mecount_1, 
       count(distinct me.hotpointid) 
         over (partition by to_char(pm.sampledate, 'yyyy-mm'), pm.city) hpcount_1,
       count(distinct   me.id) 
         over (partition by to_char(pm.sampledate, 'yyyy-mm')) mecount_2, 
       count(distinct me.hotpointid) 
         over (partition by to_char(pm.sampledate, 'yyyy-mm')) hpcount_2
  from upme me,
       appmday pm
 where pm.apid = me.id
   and pm.sampledate between to_date('2011-11-01', 'yyyy-MM-dd') and
          to_date('2012-1-31', 'yyyy-MM-dd')
1
  • thanks a lot. but i found it takes more time than just union these two sql.
    – snow8261
    Feb 20, 2012 at 11:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.