Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Introduction to Window Functions in SQL

khashtamov.com

Post reply on HN
Print view View original

41–47 of 47 posts

Re: Introduction to Window Functions in SQL

#41

Post by fuy »

e12e wrote: post #36
bmn__ wrote: post #34

Earlier quoted context omitted.

If you mean pagination, then window functions are not the right tool. Keyset pagination in standard SQL: • https://use-the-index-luke.com/sql/partial-results/fetch-nex... • https://use-the-index-luke.com/no-offset

Both of these are bad for pagination - if the dataset isn't read-only. If the search criteria matches 100 rows at the time of the request, you may want to page through those matches, even if, by the time the client (human or machine) gets to page three, the query matches 80 or 110 rows - or worse, if the query still matches 100 rows, but not all of them are the same as the original 100! You would normally capture suc…

Could you elaborate on "using cursors" part? Are you talking about database-level cursors? If so, do you know any resources which cover this approach?

Re: Introduction to Window Functions in SQL

#42

Post by deepsun »

eatonphil wrote: post #20
llampx wrote: post #19

Question for the pros: In doing some data engineering work, I found that creating temporary tables and dropping them after the run was much more performant and memory-efficient than using CTEs. No other change was made to the queries in the CTE, just putting them in a separate CREATE TABLE AS... script before the part that needed the calculations. Why is this the case? Shouldn't CTEs be more efficient?

Until postgres 12 (if you were using postgres) CTEs were an optimization fence. Where filters would not get pushed down into the CTE if they were only specified outside the CTE (but in fields from the CTE). https://paquier.xyz/postgresql-2/postgres-12-with-materializ...

Well, filters won't get pushed down to previous CREATE TABLE either.

Re: Introduction to Window Functions in SQL

#43

Post by deepsun »

MSM wrote: post #37
llampx wrote: post #19

Question for the pros: In doing some data engineering work, I found that creating temporary tables and dropping them after the run was much more performant and memory-efficient than using CTEs. No other change was made to the queries in the CTE, just putting them in a separate CREATE TABLE AS... script before the part that needed the calculations. Why is this the case? Shouldn't CTEs be more efficient?

Really depends on query complexity. Keep in mind that creating a CTE doesn't really materialize any of the resulting steps' data, it's just a way to encapsulate logic. Chaining together complex CTEs is effectively the same as creating layers of views, which can also be a performance problem. In the case of a temp table, you're likely intelligently (manually) going through the process of whittling down the data to onl…

> creating a CTE doesn't really materialize any of the resulting steps' data

Well, at least with PostgreSQL -- all CTEs were always materialized. It was optimized only in the version 12.

Re: Introduction to Window Functions in SQL

#44

Post by edmundsauto »

davidhyde wrote: post #12

I 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!

Is this different than something like the following?

SELECT app_name, MAX_BY(version_number, updated_at) as version_number FROM table GROUP BY 1

Re: Introduction to Window Functions in SQL

#45

Post by ace32229 »

twic wrote: post #24

What 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…

Here's an alternative I use all the time (without window functions) that I don't think is widely known! Works on SQL Server and I think is more performant?:

  SELECT department, first_name, salary
  FROM salary AS s
  WHERE s.[salary] = (
    SELECT MAX(ex.[salary])
    FROM salary AS ex
    WHERE s.[department] = ex.[department])

Re: Introduction to Window Functions in SQL

#46

Post by kinetica_engr »

twic wrote: post #24

What 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…

With Kinetica you can use the 'arg_max' aggregate function

  select department, max(gross_salary), arg_max(gross_salary, first_name)
  from salary
  group by department
https://www.kinetica.com/docs/concepts/sql.html#sql-aggregat...
Post reply on HN