Conditional lead/lag function PostgreSQL?

by

Your definition:

activity from group B always takes place after activity from group A.

.. logically implies that there is, per user, 0 or 1 B activity after 1 or more A activities. Never more than 1 B activities in sequence.

You can make it work with a single window function, DISTINCT ON and CASE, which should be the fastest way for few rows per user (also see below):

SELECT name
     , CASE WHEN a2 LIKE 'B%' THEN a1 ELSE a2 END AS activity
     , CASE WHEN a2 LIKE 'B%' THEN a2 END AS next_activity
FROM  (
   SELECT DISTINCT ON (name)
          name
        , lead(activity) OVER (PARTITION BY name ORDER BY time DESC) AS a1
        , activity AS a2
   FROM   t
   WHERE (activity LIKE 'A%' OR activity LIKE 'B%')
   ORDER  BY name, time DESC
   ) sub;

db<>fiddle here

An SQL CASE expression defaults to NULL if no ELSE branch is added, so I kept that short.

Assuming time is defined NOT NULL. Else, you might want to add NULLS LAST. Why?

(activity LIKE 'A%' OR activity LIKE 'B%') is more verbose than activity ~ '^[AB]', but typically faster in older versions of Postgres. About pattern matching:

Conditional window functions?

That’s actually possible. You can combine the aggregate FILTER clause with the OVER clause of window functions. However:

  1. The FILTER clause itself can only work with values from the current row.

  2. More importantly, FILTER is not implemented for pure genuine functions like lead() or lag() (up to Postgres 13) – only for aggregate functions.

If you try:

lead(activity) FILTER (WHERE activity LIKE 'A%') OVER () AS activity

Postgres will tell you:

FILTER is not implemented for non-aggregate window functions

About FILTER:

Performance

For few users with few rows per user, pretty much any query is fast, even without index.

For many users and few rows per user, the first query above should be fastest. See:

For many rows per user, there are (potentially much) faster techniques, depending on details of your setup. See:

More Related Contents:

  1. Select first row in each GROUP BY group?
  2. Optimize GROUP BY query to retrieve latest row per user
  3. PostgreSQL unnest() with element number
  4. Dynamic alternative to pivot with CASE and GROUP BY
  5. PostgreSQL: running count of rows for a query ‘by minute’
  6. Calculating Cumulative Sum in PostgreSQL
  7. GROUP BY and aggregate sequential numeric values
  8. How to select id with max date group by category in PostgreSQL?
  9. PostgreSQL equivalent for TOP n WITH TIES: LIMIT “with ties”?
  10. Can I do a max(count(*)) in SQL?
  11. Get the distinct sum of a joined table column
  12. Window Functions: last_value(ORDER BY … ASC) same as last_value(ORDER BY … DESC)
  13. Window Functions or Common Table Expressions: count previous rows within range
  14. SQL query to get most recent row for each instance of a given key
  15. Optimize groupwise maximum query
  16. Postgres window function and group by exception
  17. Get values from first and last row per group
  18. Referencing current row in FILTER clause of window function
  19. How to group timestamps into islands (based on arbitrary gap)?
  20. How to use a ring data structure in window functions
  21. Get most common value for each value of another column in SQL
  22. PostgreSQL: top n entries per item in same table
  23. Any downsides of using data type “text” for storing strings?
  24. Query for array elements inside JSON type
  25. Return multiple columns of the same row as JSON array of objects
  26. Getting all Buildings in range of 5 miles from specified coordinates
  27. Change PostgreSQL columns used in views
  28. Way to try multiple SELECTs till a result is available?
  29. PostgreSQL IF statement
  30. Call a set-returning function with an array argument multiple times

Leave a Comment