Hi, Julian I think that aggregation on over is belong streaming sql window grammar, and it's valid with a key-by window. Is that if I want to describe a key-by window I should use like over(partition by XXX rows 10 preceding). And what I'm confused is the difference between partition and group grammar? Thanks very much.
Best, Yuhong Sender: Julian Hyde [mailto:[email protected]] Send Time: 2017年1月15日 1:01 Receiver: [email protected] Theme: Re: Over window with groupby The query is not valid. (Try on other databases; they will tell you the same thing. Except that MySQL might do something dumb.) Suppose you have two records with aser = 1, one which occurs at record 100, the other at record 1 million. The first record will sum amount over rows 90..100, the second over rows 999,990..1,000,000. We need to include one total row in the output for for aser = 1. Which is the right rolling sum to include in the total? The answer is that it isn’t well-defined, therefore the query is not valid. > On Jan 13, 2017, at 5:52 PM, Hongyuhong <[email protected]> wrote: > > Hi, > I have a SQL like > "select aser, sum(amount) over (rows 10 preceding) FROM OrderA group by > aser" > And it will fail with an error Expression 'amount' is not being > grouped, But the attr 'amount' should not need to beging grouped, is that not > support now? > > And I saw if I have no groupby it will generate LogicalProject, else it will > be LogicalAggregate. > Cause I need a window relnode, if project I can use the > ProjectToWindowRule, but with aggregate relnode, It seems difficult to get > the window, Is there any method ? > > Thanks very much. > > Best, > Yuhong
