2

I have a Postgres 14 database with a table recording water levels in a number of locations. Readings are only taken on business days:

CREATE TABLE water_level (
   reading_id BIGINT GENERATED ALWAYS AS IDENTITY,
   location_id BIGINT,
   FOREIGN KEY(location_id) 
      REFERENCES locations(id),
   temperature NUMERIC,
   water_level NUMERIC,
   d_date DATE DEFAULT NOW()
);

An example of the data is:

| reading_id | location_id | temperature | water_level | d_date
| -----------| ------------| ------------| ------------|-------
| 1          | 1           | 17.9        | 145.2       | 2019-01-04
| 2          | 1           | 17.5        | 145.4       | 2019-01-05
| 3          | 1           | 17.4        | 145.5       | 2019-01-06
| 4          | 2           | 18.5        | 180.1       | 2019-01-04
| 5          | 2           | 18.7        | 180.2       | 2019-01-05
| 6          | 2           | 18.7        | 180.2       | 2019-01-06

I have a view that shows the changes over time:

CREATE VIEW current_status AS
SELECT location_id,
   MAX(e.water_level) filter (where e.d_date >= (CURRENT_DATE - INTERVAL '30 DAY')::DATE) day_high_30,
   MIN(e.water_level) filter (where e.d_date >= (CURRENT_DATE - INTERVAL '30 DAY')::DATE) day_low_30,
   MAX(e.water_level) all_time_high,
   MIN(e.water_level) all_time_low
   FROM water_level e
GROUP by location_id;

My Problem - I want the % change from 30 days ago but there may not be a record exactly 30 days ago (because it was a weekend or public holiday), it may be 29 or 28 days ago. So I want to find the water level <= 30 days ago, then calculate % compared to most recent reading
Expected Result I am trying to achieve:

CREATE VIEW current_status AS
SELECT location_id,
   MAX(e.water_level) filter (where e.d_date >= (CURRENT_DATE - INTERVAL '30 DAY')::DATE) day_high_30,
   MIN(e.water_level) filter (where e.d_date >= (CURRENT_DATE - INTERVAL '30 DAY')::DATE) day_low_30,
   MAX(e.water_level) all_time_high,
   MIN(e.water_level) all_time_low,
   (current_value - (water level <= 30 days ago) / (water level <= 30 days ago) * 100 percent_change_30_days
   FROM water_level e
GROUP by location_id;

1 Answer 1

2

You need windows functions, see : https://www.postgresql.org/docs/14/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS

In the exemple below, the value used for water level change calculation is the first value among the 30 days preceding the current value

SELECT DISTINCT ON (location_id)
location_id,
MAX(e.water_level) filter (where e.d_date >= (CURRENT_DATE - INTERVAL '30 DAY')::DATE)  OVER (PARTITION BY e.location_id) day_high_30,
MIN(e.water_level) filter (where e.d_date >= (CURRENT_DATE - INTERVAL '30 DAY')::DATE)  OVER (PARTITION BY e.location_id) day_low_30,
MAX(e.water_level) OVER (PARTITION BY e.location_id) all_time_high ,
MIN(e.water_level) OVER (PARTITION BY e.location_id) all_time_low,
    (e.water_level /
    FIRST_VALUE(e.water_level) OVER(PARTITION BY e.location_id ORDER BY d_date ASC RANGE '30 day' PRECEDING)) *100
    AS percent_change_30_days
FROM a.water_level e
ORDER BY location_id, d_date DESC 

You can avoid considering too close water level (<28 days) by adding an end_frame. Ex : RANGE BETWEEN '30 day' PRECEDING AND '28 day' PRECEDING. So, if no value between 30 and 28 days, the change will be NULL

2
  • 1
    Awesome! Thanks. I must have some dirty data as I'm getting division by zero error - does it make sense to wrap the entire denominator in a NULLIF. e.g. NULLIF(FIRST_VALUE(e.water_level) OVER(PARTITION BY e.location_id ORDER BY d_date ASC RANGE '30 day' PRECEDING),0)
    – rossco
    Commented Mar 18, 2022 at 20:50
  • Yes it make sense, use NULLIF to avoid division by zero is a good use case. See stackoverflow.com/questions/17681375/… for others tips
    – JB D
    Commented Mar 19, 2022 at 6:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.