Griffio

© 2024. All rights reserved.

More SqlDelight (Window Functions with PostgreSql)

25 Oct 2024

Initial Support for Window Function Calls in SqlDelight 2.1.0-SNAPSHOT

Not currently supported in SqlDelight grammar is WINDOW clause

SELECT wf1() OVER w
FROM table_name 
WINDOW w AS (PARTITION BY c1 ORDER BY c2);

Example

A window function performs a calculation across a set of table rows. This is comparable to the type of calculation that can be done with an aggregate function. However, window functions do not cause rows to become grouped into a single output row like non-window aggregate calls would. Instead, the window function is able to access more than just the current row of the query result and how it relates in aggregate to the other rows (e.g. rank, row number)

https://github.com/griffio/sqldelight-postgres-window-functions

Schema

CREATE TABLE scores (
  name TEXT NOT NULL,
  points INTEGER NOT NULL
);

Queries

select:
SELECT
  name,
  RANK() OVER (ORDER BY points DESC) rank,
  DENSE_RANK() OVER (ORDER BY points DESC) dense_rank,
  ROW_NUMBER() OVER (ORDER BY points DESC) row_num,
  LAG(points) OVER (ORDER BY points DESC) lag,
  LEAD(points) OVER (ORDER BY points DESC) lead,
  NTILE(6) OVER (ORDER BY points DESC) ntile,
  CUME_DIST() OVER (ORDER BY points DESC) cume_dist,
  PERCENT_RANK() OVER (ORDER BY points DESC) percent_rank
FROM scores;

selectAvgByPartition:
SELECT
  name,
  AVG(points) OVER (
    PARTITION BY name
    ORDER BY points
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS moving_avg
FROM scores;

selectSumByPartition:
SELECT
  name,
  SUM(points) OVER (
    PARTITION BY name
    ORDER BY points
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    EXCLUDE CURRENT ROW 
  ) AS running_total
FROM scores;

Previous Posts