How to use a ring data structure in window functions

by
  • Use COALESCE like @Justin provided.

  • With first_value() / last_value() you need to add an ORDER BY clause to the window definition or the order is undefined. You just got lucky in the example, because the rows happen to be in order right after creating the dummy table.
    Once you add ORDER BY, the default window frame ends at the current row, and you need to special case the last_value() call – or revert the sort order in the window frame like demonstrated in my first example.

  • When reusing a window definition multiple times, an explicit WINDOW clause simplifies syntax a lot:

SELECT ring, part, ARRAY[
          coalesce(
             lag(part) OVER w
            ,first_value(part) OVER (PARTITION BY ring ORDER BY part DESC))
         ,part
         ,coalesce(
             lead(part) OVER w
            ,first_value(part) OVER w)
         ] AS neighbours
FROM   rp
WINDOW w AS (PARTITION BY ring ORDER BY part);

Better yet, reuse the same window definition, so Postgres can calculate all values in a single scan. For this to work we need to define a custom window frame:

SELECT ring, part, ARRAY[
          coalesce(
             lag(part) OVER w
            ,last_value(part) OVER w)
         ,part
         ,coalesce(
             lead(part) OVER w
            ,first_value(part) OVER w)
         ] AS neighbours
FROM   rp
WINDOW w AS (PARTITION BY ring
             ORDER BY part
             RANGE BETWEEN UNBOUNDED PRECEDING
                       AND UNBOUNDED FOLLOWING)
ORDER  BY 1,2;

You can even adapt the frame definition for each window function call:

SELECT ring, part, ARRAY[
          coalesce(
             lag(part) OVER w
            ,last_value(part) OVER (w RANGE BETWEEN CURRENT ROW
                                                AND UNBOUNDED FOLLOWING))
         ,part
         ,coalesce(
             lead(part) OVER w
            ,first_value(part) OVER w)
         ] AS neighbours
FROM   rp
WINDOW w AS (PARTITION BY ring ORDER BY part)
ORDER  BY 1,2;

Might be faster for rings with many parts. You’ll have to test.

SQL Fiddle demonstrating all three with an improved test case. Consider query plans.

More about window frame definitions:

More Related Contents:

  1. PostgreSQL unnest() with element number
  2. Dynamic alternative to pivot with CASE and GROUP BY
  3. PostgreSQL: running count of rows for a query ‘by minute’
  4. Calculating Cumulative Sum in PostgreSQL
  5. GROUP BY and aggregate sequential numeric values
  6. PostgreSQL equivalent for TOP n WITH TIES: LIMIT “with ties”?
  7. Get the distinct sum of a joined table column
  8. Window Functions: last_value(ORDER BY … ASC) same as last_value(ORDER BY … DESC)
  9. Window Functions or Common Table Expressions: count previous rows within range
  10. Postgres window function and group by exception
  11. Referencing current row in FILTER clause of window function
  12. How to group timestamps into islands (based on arbitrary gap)?
  13. Conditional lead/lag function PostgreSQL?
  14. Select rows which are not present in other table
  15. Optimize GROUP BY query to retrieve latest row per user
  16. How to implement a many-to-many relationship in PostgreSQL?
  17. Check if value exists in Postgres array
  18. Is there a shortcut for SELECT * FROM?
  19. sql statement error: “column .. does not exist”
  20. Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?
  21. Sorting null values after all others, except special
  22. Reference alias in WHERE clause
  23. Can I do a max(count(*)) in SQL?
  24. ERROR: functions in index expression must be marked IMMUTABLE in Postgres
  25. Why postgres returns unordered data in select query, after updation of row?
  26. What is the difference between a LATERAL JOIN and a subquery in PostgreSQL?
  27. Parallel unnest() and sort order in PostgreSQL
  28. Query for count of distinct values in a rolling date range
  29. Postgres: convert single row to multiple rows (unpivot)
  30. Get most common value for each value of another column in SQL

Leave a Comment