CODEWITHSUNDEEP
sqlpostgresql
Chen Xu
Chen Xu

Reputation: 57

Postgresql LAG() Function to show zero instead of null

Here's my code:

WITH WEEKCTE AS (SELECT date_trunc('week', day::date) AS anchor, AVG(value) AS average
FROM daily_metrics
WHERE metric = 'daily-active-users'
GROUP BY anchor
ORDER BY anchor)

SELECT*,
(average - lag(average) OVER (ORDER BY anchor))/lag(average) OVER (ORDER BY anchor) AS delta
FROM WEEKCTE

Here's the result: enter image description here

But I want the first row of delta to show 0 instead of null. I tried with lag(average,0,0) instead of lag(average) to offset by 0 and set default to 0 but it does not work either.

Upvotes: 3

Views: 3086

Answers (2)

Ed Bangga
Ed Bangga

Reputation: 13026

use coalesce()

WITH WEEKCTE AS (SELECT date_trunc('week', day::date) AS anchor, AVG(value) AS average
FROM daily_metrics
WHERE metric = 'daily-active-users'
GROUP BY anchor
ORDER BY anchor)

SELECT*,
coalesce(((average - lag(average) OVER (ORDER BY anchor))/lag(average) OVER (ORDER BY anchor)), 0) AS delta
FROM WEEKCTE

Thought its not possible to get any 0 for lag(average), its best practice to avoid divided by 0 possibilities.

SELECT*,
coalesce(((average - /coalesce(lag(average), 1) OVER (ORDER BY anchor))/coalesce(lag(average), 1) OVER (ORDER BY anchor)), 0) AS delta
FROM WEEKCTE

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270993

The simplest method is to use coalesce():

  SELECT w.*,
        coalesce( (average - lag(average) OVER (ORDER BY anchor)) /
                   lag(average) OVER (ORDER BY anchor), 0) AS delta
  FROM WEEKCTE w;

Note that you do not need the CTE. You can use window functions and aggregation together:

SELECT date_trunc('week', day::date) AS anchor,
       AVG(value) AS average,
       COALESCE(average / LAG(AVG(value)) OVER (ORDER BY MIN(day)) - 1, 0)
FROM daily_metrics
WHERE metric = 'daily-active-users'
GROUP BY anchor
ORDER BY anchor;

Upvotes: 4

Related Questions