I made gifs to explain the different types of window functions: https://dataschool.com/how-to-teach-people-sql/how-window-fu...
Introduction to Window Functions in SQL
21–30 of 47 posts
Re: Introduction to Window Functions in SQL
#22SQL is only secondary in my work but I'd rather go with the explicit and verbose queries rather than with stuff with surprises like how the last_value gives wrong results when used the way you'd expect i.e. the reverse of first_value.
Re: Introduction to Window Functions in SQL
#23I find window functions to be an excellent way to find the max version of a set of things. The trick is to partition by some columns (similar to how you would use a group by), order by descending on your version number field, and use the row_number() function which is very lightweight. Then you filter for all entries where rownumber = 1 and voila you have the max version without having to link back on yourself!
Yes! I use this daily.
Re: Introduction to Window Functions in SQL
#24What i like most about window functions is that they give me a way to do a sort of 'extended group by' which i have always wanted.
If you want to know the highest salary in each department, that's easy:
select department, max(gross_salary)
from salary
group by department
select department, first_name, max(gross_salary)
from salary
group by department
Window functions let you re-frame this kind of group by like this:
select department, gross_salary
from (
select *, row_number() over (partition by department order by gross_salary desc) as n
from salary
) _
where n = 1
select department, first_name, gross_salary
from (
select *, row_number() over (partition by department order by gross_salary desc) as n
from salary
) _
where n = 1
Re: Introduction to Window Functions in SQL
#25A few cool tricks I use with window functions: 1- To find blocks of contiguous values, you can use something similar to Gauss' trick for calculating arithmetic progressions: sort them by descending order and add each value to the row number. All contiguous values will add to the same number. You can then apply max/min and get rows that correspond to the blocks of values. select min(n), max(n) from ( select n, n+row_n…
Could you please develop how to do paging with window functions?
It's been some time since I've done serious work with SQL yet I remember that all paging solutions I've found (eg. top results on SO) are always platform-specific so having a platform-independent way of doing it would mean I could finally try to remember it.
Re: Introduction to Window Functions in SQL
#26I've been working on a messaging app, and at the beginning I started out with Elasticsearch. Now, ES is really a wonderful tool and it got me pretty far, but its lack of window functions made one particular requirement impossible that we all take for granted in messengers: a listing of most-recently updated threads represented by the most recent message on each thread, sorted reverse chronologically. Here is the SQL that makes that happen:
SELECT
tm.*
FROM
(
SELECT
row_number() OVER (
PARTITION BY tm_1.thread_id
ORDER BY tm_1.delivered_at DESC
) AS "row",
tm_1.*
FROM
thread_message tm_1
) tm
WHERE
tm."row" = 1;
Re: Introduction to Window Functions in SQL
#27What i like most about window functions is that they give me a way to do a sort of 'extended group by' which i have always wanted. If you want to know the highest salary in each department, that's easy: select department, max(gross_salary) from salary group by department If you want to know who it is who earns that salary, you might try to do this: select department, first_name, max(gross_salary) from salary group by…
> If SQL had a way of picking one row from a group, rather than aggregating over it, that would be immensely useful.
Well, there is a way which is window functions :) as shown by you.
The idea to expect exactly one first name of the person with the biggest salary is kinda wrong, since there can be more than one person, and this can obviously not described as a single column per group.
Note that aggregating is not limited to min, max, sum, etc. Postgres, for example, has array_agg which aggregates individual columns from each record of a group into an array, if that becomes necessary.
Re: Introduction to Window Functions in SQL
#28I've been encountering a lot of issues, when doing dedicated queries, specially when a team is small, I've found is sometimes more useful to just do the grouping or left join (if more than 2 are required) manually.
The problem is ofc scalability, if your team is small it is better to just have some few, but very specific queries, and do whatever transactions required on a layer above.
It may be the difference between correcting a few lines, vs reworking 20+ different queries, and testing each one individually.
Re: Introduction to Window Functions in SQL
#29A few cool tricks I use with window functions: 1- To find blocks of contiguous values, you can use something similar to Gauss' trick for calculating arithmetic progressions: sort them by descending order and add each value to the row number. All contiguous values will add to the same number. You can then apply max/min and get rows that correspond to the blocks of values. select min(n), max(n) from ( select n, n+row_n…
Could you please develop how to do paging with window functions? It's been some time since I've done serious work with SQL yet I remember that all paging solutions I've found (eg. top results on SO) are always platform-specific so having a platform-independent way of doing it would mean I could finally try to remember it.
row_number() !
As in: SELECT * FROM (SELECT *, row_number() OVER (order by ) as rowidx FROM your_table) as numbered WHERE rowidx > ? AND rowid < ?
Re: Introduction to Window Functions in SQL
#30Wow I love me a window function!
Favorite/weirdest thing I learned to do most recently with window functions is using/abusing min() and max(). If there is no value in a column in a particular window, min and max will give NULL.
We use this to do really interesting stuff (entirely in sql!) like "partition usage into sessions where a session is defined as a bunch of activity by a user where they did an action at least once every 30 minutes"