You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Hongyuhong <ho...@huawei.com> on 2017/01/14 01:52:59 UTC
Over window with groupby
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
Re: Over window with groupby
Posted by Julian Hyde <jh...@apache.org>.
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 <ho...@huawei.com> 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:jhyde@apache.org]
> Send Time: 2017年1月15日 1:01
> Receiver: dev@calcite.apache.org
> 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 <ho...@huawei.com> 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
>
Re: Over window with groupby
Posted by Hongyuhong <ho...@huawei.com>.
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:jhyde@apache.org]
Send Time: 2017年1月15日 1:01
Receiver: dev@calcite.apache.org
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 <ho...@huawei.com> 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
Re: Over window with groupby
Posted by Julian Hyde <jh...@apache.org>.
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 <ho...@huawei.com> 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