PostgreSQL

Window Function

First meet

SELECT
  *
FROM PARENT_ROWS
  LEFT JOIN SUB_ROWS ON SUB_ROWS.PARENT_ID = PARENT_ROWS.ID;

Find max sub_rows version with parent

SELECT
  RANKED_ROWS.*
FROM (
  SELECT
    PARENT_ROWS.*,
    ROW_NUMBER() OVER (
      PARTITION BY SUB_ROWS.PARENT_ID
      ORDER BY SUB_ROWS.VERSION DESC
    ) AS ROW_INDEX
  FROM PARENT_ROWS
    LEFT JOIN SUB_ROWS ON SUB_ROWS.PARENT_ID = PARENT_ROWS.ID
) RANKED_ROWS
WHERE RANKED_ROWS.ROW_INDEX = 1;

Null handing

SELECT
  *
FROM PARENT_ROWS
  LEFT JOIN SUB_ROWS ON SUB_ROWS.PARENT_ID = PARENT_ROWS.ID;

Find latest released sub_rows version with parent

SELECT
  RANKED_ROWS.*
FROM (
  SELECT
    PARENT_ROWS.*,
    ROW_NUMBER() OVER (
      PARTITION BY SUB_ROWS.PARENT_ID
      ORDER BY SUB_ROWS.REALEASED_AT DESC NULLS LAST
    ) AS ROW_INDEX
  FROM PARENT_ROWS
    LEFT JOIN SUB_ROWS ON SUB_ROWS.PARENT_ID = PARENT_ROWS.ID
) RANKED_ROWS
WHERE RANKED_ROWS.ROW_INDEX = 1;

Add column for aggregated

> SELECT AVG(AMOUNT) FROM TABLE;

avg
----------------
1724.124
> SELECT TABLE.*, AVG(AMOUNT) OVER () FROM TABLE;

id        | name    | amount    | avg      
------------------------------------------
1         | a       | 1000      | 1400
2         | b       | 400       | 1400
3         | c       | 2800      | 1400

Use empty condition window function

Partition with condition

> SELECT TABLE.*, AVG(AMOUNT) OVER (PARTITION BY amount < 1200) FROM TABLE;

id        | name    | amount    | avg      
------------------------------------------
1         | a       | 1000      | 700
2         | b       | 400       | 700
3         | c       | 2800      | 2800

Moving window

SELECT 
  TABLE.*, 
  min(amount) OVER (ORDER BY id ASC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
FROM TABLE;

id        | name    | amount    | min   
------------------------------------------
1         | a       | 1000      | 400
2         | b       | 400       | 400
3         | c       | 2800      | 400
4         | d       | 1000      | 1000
5         | e       | 1400      | 1000
6         | f       | 1200      | 1200

Alias condition

SELECT 
  TABLE.*, 
  min(amount) OVER (w),
  max(amount) OVER (w)
FROM TABLE
WINDOW w AS (PARTITION BY group);

id        | name    | amount    | group     | max      | min   
----------------------------------------------------------------
1         | a       | 1000      | a         | 2800     | 400
2         | b       | 400       | a         | 2800     | 400
3         | c       | 2800      | a         | 2800     | 400
4         | d       | 1000      | a         | 2800     | 400
5         | e       | 1400      | b         | 1400     | 1200
6         | f       | 1200      | b         | 1400     | 1200

deck

By Chia Yu Pai

Made with Slides.com

deck

  • 383

More from Chia Yu Pai