SUM() OVER (ORDER BY..)

Anthony Damico ajdamico at gmail.com
Fri Apr 29 14:10:25 CEST 2016


might be able to use this.. will try..
http://stackoverflow.com/questions/2120544/how-to-get-cumulative-sum

On Fri, Apr 29, 2016 at 8:01 AM, Anthony Damico <ajdamico at gmail.com> wrote:

> thanks stefan (and also kai jiang)!  i am trying to implement a weighted
> percentile strategy which uses that code.. i actually don't want the
> PARTITION BY, i would want it to be ORDER BY instead.  the `cumjobs` column
> needs to be cumulative for a weighted percentile to work--   is there any
> way to reconfigure that sql command so that the `cumjobs` column
> cumulatively counts upwards based on the size of the duration column?
>
> http://stackoverflow.com/questions/16450779/another-approach-to-percentiles
>
> On Fri, Apr 29, 2016 at 7:17 AM, Stefan Manegold <Stefan.Manegold at cwi.nl>
> wrote:
>
>> Anthony,
>>
>> while MonetDB indeed does not support this syntax,
>> I'm also puzzled what kind of semantic or result you'd expect from this
>> syntax?
>>
>>     SELECT
>>         batch_id,
>>         job_count,
>>         SUM(job_count) OVER (PARTITION BY duration) as cumjobs,
>>         SUM(job_count) over () as totjobs,
>>         duration
>>     FROM
>>         test_data ;
>>
>> The only possible result would IMHO be a
>> table with as many rows as the input table test_data has,
>> columns batch_id, job_count, duration identical to the input,
>> one additional column cumjobs with the same value, i.e., sum
>> of all job_count within each duration, replicated for all rows
>> with same duration,
>> and one additional column totjobs, with the same value (global
>> sum of all job_count values) replicated for each row.
>>
>> If that's what you want, this should do the job:
>>
>> SELECT
>>         batch_id,
>>         job_count,
>>         cumjobs,
>>         totjobs,
>>         duration
>> FROM
>>         test_data AS td
>>         NATURAL JOIN
>>         (
>>                 SELECT
>>                         duration,
>>                         SUM(job_count) AS cumjobs
>>                 FROM
>>                         test_data
>>                 GROUP BY
>>                         duration
>>         ) AS cj
>>         ,
>>         (
>>                 SELECT
>>                         SUM(job_count) AS totjobs
>>                 FROM
>>                         test_data
>>         ) AS tj
>> ;
>>
>>
>> Best,
>> Stefan
>>
>> ----- On Apr 29, 2016, at 9:46 AM, Roberto Cornacchia
>> roberto.cornacchia at gmail.com wrote:
>>
>> > No, indeed, this syntax is not supported.
>> >
>> >
>> > On 29 April 2016 at 09:41, Anthony Damico < ajdamico at gmail.com > wrote:
>> >
>> >
>> >
>> > hmm, that gives me the same error..
>> >
>> > SELECT mpg, wt, SUM(wt) OVER (PARTITION BY hp) AS cum_wgt, SUM(wt) over
>> () AS
>> > tot_wgt, hp FROM mtcars;
>> >
>> >
>> > Error in .local(conn, statement, ...) :
>> > Unable to execute statement 'SELECT
>> > mpg,
>> > wt,
>> > SUM(wt) OVER (PARTITION BY hp) AS cum_wgt,
>> > SUM(wt) over () AS tot_wgt,
>> > hp
>> > FROM mtc...'.
>> > Server says 'ParseException:SQLparser:SELECT: function 'sum' not found'.
>> >
>> >
>> > sorry if i'm doing something silly..
>> >
>> >
>> >
>> >
>> > On Fri, Apr 29, 2016 at 3:37 AM, Roberto Cornacchia <
>> > roberto.cornacchia at gmail.com > wrote:
>> >
>> >
>> >
>> > I think what you are looking for is SUM(..) OVER(PARTITION BY ..)
>> >
>> > Otherwise where would the groups for the sum come from?
>> >
>> > Roberto
>> >
>> > On 29 April 2016 at 09:28, Anthony Damico < ajdamico at gmail.com > wrote:
>> >
>> >
>> >
>> > hi, monetdb does not support SUM() OVER commands.. does anyone have a
>> smart
>> > alternative to implement this? thanks
>> >
>> >
>> > SELECT
>> > batch_id,
>> > job_count,
>> > SUM(job_count) OVER (ORDER BY duration) as cumjobs,
>> > SUM(job_count) over () as totjobs,
>> > duration
>> > FROM
>> > test_data ;
>> >
>> >
>> > _______________________________________________
>> > users-list mailing list
>> > users-list at monetdb.org
>> > https://www.monetdb.org/mailman/listinfo/users-list
>> >
>> >
>> >
>> > _______________________________________________
>> > users-list mailing list
>> > users-list at monetdb.org
>> > https://www.monetdb.org/mailman/listinfo/users-list
>> >
>> >
>> >
>> > _______________________________________________
>> > users-list mailing list
>> > users-list at monetdb.org
>> > https://www.monetdb.org/mailman/listinfo/users-list
>> >
>> >
>> >
>> > _______________________________________________
>> > users-list mailing list
>> > users-list at monetdb.org
>> > https://www.monetdb.org/mailman/listinfo/users-list
>>
>> --
>> | Stefan.Manegold at CWI.nl | DB Architectures   (DA) |
>> | www.CWI.nl/~manegold/  | Science Park 123 (L321) |
>> | +31 (0)20 592-4212     | 1098 XG Amsterdam  (NL) |
>> _______________________________________________
>> users-list mailing list
>> users-list at monetdb.org
>> https://www.monetdb.org/mailman/listinfo/users-list
>>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.monetdb.org/pipermail/users-list/attachments/20160429/376547a7/attachment-0001.html>


More information about the users-list mailing list