SUM() OVER (ORDER BY..)

Anthony Damico ajdamico at gmail.com
Fri Apr 29 14:01:58 CEST 2016


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/c08da2f2/attachment.html>


More information about the users-list mailing list