How to calculate % change for nearest date



  • 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 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 


  • 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 (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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2