I don’t understand your requirements exactly, but you should definitely look into PARTITION BY. It comes up very often in real-life queries.
One way to clarify your requirements is to ask how many records you want your query to omit. If state has 50 values and your source table has 1M values then a query with “GROUP BY state” will return 50 rows, whereas a query with “sum(…) OVER (…)” will return 1M values. (The value of each of those "sum(…) OVER (…)” will depend on the “…” and in particular whether it contains a “PARTITION BY”.) Julian > On Jan 15, 2017, at 5:44 PM, Hongyuhong <[email protected]> wrote: > > 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 >
