sqlite 3.25.1 windows function. So it should be?

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

sqlite 3.25.1 windows function. So it should be?

Djelf
Hi!

Maybe I do not understand something, but does it really have to work this
way?

DROP TABLE IF EXISTS t;
CREATE TABLE t (v1,v2,v3,v4);
INSERT INTO t (v1,v2,v3,v4) VALUES
(1,1,10000,0),(1,1,0,1),(2,1,0,10),(3,1,0,100),(3,1,0,1000);
SELECT
        v1,v2,sum(v3+v4) OVER (PARTITION BY v2 ORDER BY v1) as val
FROM t

v1 v2 val
1 1 10001
1 1 10001
2 1 10011
3 1 11111
3 1 11111

SELECT
        v1,v2,sum(v3+v4) OVER (PARTITION BY v2 ORDER BY v1) as val
FROM t
GROUP BY v1,v2;

v1 v2 val
1 1 10000
2 1 10010
3 1 10110

Yes, I know that v1 is duplicated, but whether the result should disappear
10011?

---
Anton Azanov



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite 3.25.1 windows function. So it should be?

Clemens Ladisch
Djelf wrote:

> INSERT INTO t (v1,v2,v3,v4) VALUES
> (1,1,10000,0),(1,1,0,1),(2,1,0,10),(3,1,0,100),(3,1,0,1000);
>
> SELECT
> v1,v2,sum(v3+v4) OVER (PARTITION BY v2 ORDER BY v1) as val
> FROM t
> GROUP BY v1,v2;
>
> v1 v2 val
> 1 1 10000
> 2 1 10010
> 3 1 10110
>
> Yes, I know that v1 is duplicated, but whether the result should disappear
> 10011?

Looks correct.  The Postgres manual explains it this way:
> If the query contains any window functions, these functions are
> evaluated after any grouping, aggregation, and HAVING filtering is
> performed. That is, if the query uses any aggregates, GROUP BY, or
> HAVING, then the rows seen by the window functions are the group rows
> instead of the original table rows from FROM/WHERE.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite 3.25.1 windows function. So it should be?

Djelf

I understood what's the matter. The query was incorrect.

I tested this on posgresql.

SQLSTATE[42803]: Grouping error: 7 ERROR:  column "t.v3" must appear in the
GROUP BY clause or be used in an aggregate function

It seems to me that sqlite should issue a similar message.

Correct query is

SELECT
v1,v2,sum(sum(v3+v4)) OVER (PARTITION BY v2 ORDER BY v1) as val
FROM t
GROUP BY v1,v2;

v1 v2 val
1 1 10001
2 1 10011
3 1 11111





--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite 3.25.1 windows function. So it should be?

Clemens Ladisch
Djelf wrote:
> SQLSTATE[42803]: Grouping error: 7 ERROR:  column "t.v3" must appear in the GROUP BY clause or be used in an aggregate function
>
> It seems to me that sqlite should issue a similar message.

This is allowed for compatibility with MySQL.
And there is a case with min()/max() where this is actually useful:
<https://www.sqlite.org/lang_select.html#bareagg>


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users