List:General Discussion« Previous MessageNext Message »
From:Travis Ard Date:August 1 2010 11:35pm
Subject:RE: query results group/summed by interval
View as plain text  
You could also pre-define your intervals in a subquery using UNION and join that to your
original table like so:

select ifnull(sum(calls), 0) as calls, n as queue_seconds
from 
(select 0 as n union 
select 5 union 
select 10 union 
select 15) as step
left join calls on calls.queue_seconds > (step.n - 5) and calls.queue_seconds <=
step.n
group by n;

+-------+---------------+
| calls | queue_seconds |
+-------+---------------+
|   250
|             0 |
|   168
|             5 |
|   268 |            10 |
|     0
|            15 |
+-------+---------------+
4 rows in set (0.00 sec)

-Travis

----------------------------------------
> Date: Sun, 1 Aug 2010 13:16:36 +0100
> From: nuno.tavares@stripped
> To: mgainty@stripped
> CC: cuong.manh@stripped; aveekm@stripped; mustafa.pk@stripped;
> mysql@stripped
> Subject: RE: query results group/summed by interval
>
> Hi all,
>
> Aveeks solution should work if you have at least one call for each intervall.
> It's the classical GROUP BY solution that only works on the available dataset.
> Although it should work pretty well in the cited scenario, you will miss
> intervals (from a "all intervals report" point of view) if indeed there are
> intervals (of more than 5 minutes, in this example) when there were no calls at
> all.
>
> I had a somewhat similar problem (running the second scenario, though) and this
> is the solution I setup (this was a Data Warehouse and that's why you'll read
> about partition pruning, dataset was dozens of Gigs):
>
>
> http://gpshumano.blogs.dri.pt/2009/09/28/finding-for-each-time-interval-how-many-records-are-ocurring-during-that-interval/
>
> This might become handy if Ghulam understands the differences between my
> scenario and his.
>
> Hope that helps,
> -NT
>
>
>
>
> Quoting Martin Gainty :
>
> >
> > no that would give you the count for each second interval instead of using
> > the interval variable 5
> >
> > Aveeks floor:
> > FLOOR(X) Returns the largest integer value not greater than X.
> >
> > 1st (seconds/5) interval example
> > 5/5=1
> > floor(5/5) = 1
> > supplied value would truncate and give you the int not greater than X
> > then multiply by 5
> > 1*5=5
> > is correct
> >
> > Aveeks sum function:
> > SUM([DISTINCT] expr)
> > Returns the sum of expr. If the return set has no rows, SUM() returns NULL.
> > The DISTINCT keyword can be used in MySQL 5.0 to sum only the distinct values
> > of expr.
> > SUM() returns NULL if there were no matching rows.
> > sum(calls) from calls group by 5 * floor(seconds/5)
> > sum(calls) from calls group by 5 * floor(5/5)
> > sum(calls) from class group by 5 * 1
> > sum(calls) from class group by 5
> > is correct
> >
> > 2nd(seconds/5) interval example
> > 10/5=2
> > floor(10/5)=2
> > supplied value would truncate and give you the int not greater than X
> > then multiply by 5
> > 2*5=10
> > is correct
> >
> > Aveeks sum function
> > sum(calls) from calls group by 5 * floor(seconds/5)
> > sum(calls) from calls group by 5 * floor(10/5)
> > sum(calls) from class group by 5 * 2
> > sum(calls) from class group by 10
> > would be applicable only if the interval was 10
> >
> > Aveek if your interval is 5 change:
> > sum(calls) from calls group by 5 * floor(seconds/5)
> > to
> > sum(calls) from calls group by floor(seconds/5)
> >
> > Martin Gainty
> > ______________________________________________
> > Verzicht und Vertraulichkeitanmerkung/Note de déni et de
> confidentialité
> >
> > Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger
> > sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung
> > oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich
> > dem Austausch von Informationen und entfaltet keine rechtliche
> > Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen
> > wir keine Haftung fuer den Inhalt uebernehmen.
> > Ce message est confidentiel et peut être privilégié. Si vous
> n'êtes pas le
> > destinataire prévu, nous te demandons avec bonté que pour
> satisfaire informez
> > l'expéditeur. N'importe quelle diffusion non autorisée ou la copie
> de ceci
> > est interdite. Ce message sert à l'information seulement et n'aura pas
> > n'importe quel effet légalement obligatoire. Étant donné
> que les email
> > peuvent facilement être sujets à la manipulation, nous ne pouvons
> accepter
> > aucune responsabilité pour le contenu fourni.
> >
> >
> >
> >
> >
> > > Date: Sat, 31 Jul 2010 10:31:43 +0700
> > > From: cuong.manh@stripped
> > > To: aveekm@stripped
> > > CC: mustafa.pk@stripped; mysql@stripped
> > > Subject: Re: query results group/summed by interval
> > >
> > > Hi Aveek,
> > >
> > > I think Ghulam just want to count calls for each intervals
> > > so the query should looks like this:
> > >
> > > select count(*) as total_calls, queue_seconds
> > > from calls group by queue_seconds order by total_calls;
> > >
> > >
> > > ----- Original Message -----
> > > From: "Aveek Misra" 
> > > To: "Ghulam Mustafa" , mysql@stripped
> > > Sent: Tuesday, July 27, 2010 5:54:13 PM
> > > Subject: RE: query results group/summed by interval
> > >
> > > try this ...
> > >
> > > select 5 * floor(seconds/5) as start, 5 * floor(seconds/5) + 5 as end,
> > sum(calls) from calls group by 5 * floor(seconds/5);
> > >
> > > This should give you an output of the type
> > >
> > > +-------+------+------------+
> > > | start | end | sum(calls) |
> > > +-------+------+------------+
> > > | 0 | 5 | 387 |
> > > | 5 | 10 | 225 |
> > > | 10 | 15 | 74 |
> > > +-------+------+------------+
> > >
> > >
> > > Thanks
> > > Aveek
> > > ________________________________________
> > > From: Ghulam Mustafa [mustafa.pk@stripped]
> > > Sent: Tuesday, July 27, 2010 3:53 PM
> > > To: mysql@stripped
> > > Subject: query results group/summed by interval
> > >
> > > Hi everyone,
> > >
> > > i have two columns (seconds, number of calls), i need to produce a
> > > report which will show total number of calls in intervals (let'say 10
> > > seconds interval), i know i can do this programmability in my script but
> > > i was wondering if it's possible to accomplish this behavior within
> > > mysql. for example i have following data.
> > >
> > > +----------+---------------+
> > > | calls | queue_seconds |
> > > +----------+---------------+
> > > | 250 | 0.00 |
> > > | 28 | 1.00 |
> > > | 30 | 2.00 |
> > > | 56 | 3.00 |
> > > | 23 | 4.00 |
> > > | 31 | 5.00 |
> > > | 33 | 6.00 |
> > > | 50 | 7.00 |
> > > | 49 | 8.00 |
> > > | 62 | 9.00 |
> > > | 74 | 10.00 |
> > > ...
> > > ... and so on...
> > > ...
> > > +----------+---------------+
> > >
> > > now result should look like this with a 5 seconds interval.
> > >
> > > +----------+---------------+
> > > | count(*) | queue_seconds |
> > > +----------+---------------+
> > > | 250 | 0.00 |
> > > | 168 | 5.00 |
> > > | 268 | 10.00 |
> > > ...
> > > ... and so on...
> > > ...
> > > +----------+---------------+
> > >
> > > i would really appreciate your help.
> > >
> > > Best Regards.
> > >
> > > --
> > > Ghulam Mustafa
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe: http://lists.mysql.com/mysql?unsub=1
> > >
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:
> > http://lists.mysql.com/mysql?unsub=1
> > >
> > >
> > > --
> > > Best Regards,
> > > Cuongmc.
> > >
> > > --
> > > Nguyen Manh Cuong
> > > Phong Ky Thuat - Cong ty Vien Thong So - VTC
> > > Dien thoai: 0912051542
> > > Gmail : philipscuong@stripped
> > > YahooMail : philipscuong@stripped
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe: http://lists.mysql.com/mysql?unsub=1
> > >
> >
>
>
> --
> Nuno Tavares
> +351 93 618 40 86
> dri Consultoria Informatica
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
 		 	   		  
Thread
query results group/summed by intervalGhulam Mustafa27 Jul
  • RE: query results group/summed by intervalAveek Misra27 Jul
    • Re: query results group/summed by intervalNguyen Manh Cuong31 Jul
RE: query results group/summed by intervalMartin Gainty31 Jul
RE: query results group/summed by intervalMartin Gainty31 Jul
  • RE: query results group/summed by intervalnuno.tavares1 Aug
    • RE: query results group/summed by intervalTravis Ard2 Aug