SUM() OVER (ORDER BY..)

Stefan Manegold Stefan.Manegold at cwi.nl
Fri Apr 29 13:17:08 CEST 2016


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


More information about the users-list mailing list