回复:Re: SUM() OVER (ORDER BY..)

chenkaijiang at bigknow.com.cn chenkaijiang at bigknow.com.cn
Fri Apr 29 10:25:14 CEST 2016


An alternative way is to use temp tables to get the same result:
select c1, sum(c2) over(partition by c3) from t1; ==>
create temp table tmp1 (sum_c2 int, c3 int);
insert into tmp1 select sum(c2), c3 from t1 group by c3;
select c1, sum_c2, c3 from t1 join tmp1 on t1.c3=tmp1.c3;


----- 原始邮件 -----
发件人:Roberto Cornacchia <roberto.cornacchia at gmail.com>
收件人:Communication channel for MonetDB users <users-list at monetdb.org>
主题:Re: SUM() OVER (ORDER BY..)
日期:2016-4-29 15:46:57

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






-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.monetdb.org/pipermail/users-list/attachments/20160429/a94edd3d/attachment.html>


More information about the users-list mailing list