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

29 Apr 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 
收件人：Communication channel for MonetDB users 
主题：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  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@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  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@monetdb.org
...
...
...
https://www.monetdb.org/mailman/listinfo/users-list
...
...
...
...
...
...
...
...
...
...
_______________________________________________
...
...
users-list mailing list
...
...
users-list@monetdb.org
...
...
https://www.monetdb.org/mailman/listinfo/users-list
...
...
...
...
...

...
_______________________________________________
...
users-list mailing list
...
users-list@monetdb.org
...
https://www.monetdb.org/mailman/listinfo/users-list
...

...
_______________________________________________

users-list mailing list

users-list@monetdb.org

https://www.monetdb.org/mailman/listinfo/users-list

chenkaijiang＠bigknow.com.cn

tags

participants (1)