如何計算最近日期的百分比變化

我有一個 Postgres 14 數據庫，其中的表格記錄了許多位置的水位。讀數僅在工作日進行：

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()
);

數據範例如下：

| 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

我有一個顯示隨時間變化的視圖：

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;

我的問題 - 我想要 30 天前的百分比變化，但可能沒有準確的 30 天前的記錄（因為它是周末或公共假期），可能是 29 天或 28 天前。所以我想找到 <= 30 天前的水位，然後計算與

我試圖達到的最近讀數預期結果相比的百分比：

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;

您需要 Windows 功能，請參閱：https ://www.postgresql.org/docs/14/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS

在下面的例子中，用於水位變化計算的值是目前值前 30 天中的第一個值

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 

您可以通過添加end_frame來避免考慮太接近水位（<28 天）。例如：RANGE BETWEEN '30 day' PRECEDING AND '28 day' PRECEDING。因此，如果 30 到 28 天之間沒有值，則更改將是NULL

引用自：https://dba.stackexchange.com/questions/309904