Window functions

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

Window functions

Simon Slavin-3
I ran into this two-part article, probably on Hacker News:

<http://www.helenanderson.co.nz/sql-window-functions-part-1/>

I tried comparing it with

<https://www.sqlite.org/windowfunctions.html>

but I don't know enough to be able to tell whether the language used in the article is compatible with the way window functions are implemented in SQLite.  Could someone who knows more than I do take a look and post a summary ?
_______________________________________________
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: Window functions

Dan Kennedy-4

On 4/9/62 23:14, Simon Slavin wrote:
> I ran into this two-part article, probably on Hacker News:
>
> <http://www.helenanderson.co.nz/sql-window-functions-part-1/>
>
> I tried comparing it with
>
> <https://www.sqlite.org/windowfunctions.html>
>
> but I don't know enough to be able to tell whether the language used in the article is compatible with the way window functions are implemented in SQLite.  Could someone who knows more than I do take a look and post a summary ?

I only skimmed it, but I think everything there is applicable to SQLite.

Although I think she's using "window frame" differently to the way we
do. Not that it matters too much, as the term only occurs once in each
of the two blog entries anyway.

Dan.


_______________________________________________
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: Window functions

David Raymond
In reply to this post by Simon Slavin-3
Kind of annoying that when the author shows a screenshot of the sample data he's using for his queries that he doesn't include 2 of the fields that are in the queries. Makes it harder to "play along at home"

For their ntile example (on page2) I don't think I've seen a window function used with a "group by". Does the ntile un-group the groups? Something just looks wrong there between the query and the results shown below it. But like you I don't know enough to say if that's right or if it's on crack.


-----Original Message-----
From: sqlite-users <[hidden email]> On Behalf Of Simon Slavin
Sent: Wednesday, September 04, 2019 12:15 PM
To: SQLite mailing list <[hidden email]>
Subject: [sqlite] Window functions

I ran into this two-part article, probably on Hacker News:

<http://www.helenanderson.co.nz/sql-window-functions-part-1/>

I tried comparing it with

<https://www.sqlite.org/windowfunctions.html>

but I don't know enough to be able to tell whether the language used in the article is compatible with the way window functions are implemented in SQLite.  Could someone who knows more than I do take a look and post a summary ?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
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: Window functions

Dan Kennedy-4

On 5/9/62 00:13, David Raymond wrote:
> Kind of annoying that when the author shows a screenshot of the sample data he's using for his queries that he doesn't include 2 of the fields that are in the queries. Makes it harder to "play along at home"
>
> For their ntile example (on page2) I don't think I've seen a window function used with a "group by". Does the ntile un-group the groups? Something just looks wrong there between the query and the results shown below it. But like you I don't know enough to say if that's right or if it's on crack.

You can run window functions on aggregate queries. The windowing step
occurs logically after the aggregation.

It still looks right to me. Each output row contains a unique
combination of territoryid/customerid, so no need for any "un-grouping".
Of course, the input data doesn't feature any rows with duplicate
territoryid/customerid values, so running the query without the GROUP BY
and replacing "sum(subtotal)" with "subtotal" would produce the same
results.

Dan



>
>
> -----Original Message-----
> From: sqlite-users <[hidden email]> On Behalf Of Simon Slavin
> Sent: Wednesday, September 04, 2019 12:15 PM
> To: SQLite mailing list <[hidden email]>
> Subject: [sqlite] Window functions
>
> I ran into this two-part article, probably on Hacker News:
>
> <http://www.helenanderson.co.nz/sql-window-functions-part-1/>
>
> I tried comparing it with
>
> <https://www.sqlite.org/windowfunctions.html>
>
> but I don't know enough to be able to tell whether the language used in the article is compatible with the way window functions are implemented in SQLite.  Could someone who knows more than I do take a look and post a summary ?
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users