postgresql - SP/View 每日聚合 3 小时值

Question

我正在尝试将 3 小时的预测值分组到每日表中，问题是我需要处理对值的非标准分组操作。我附上一个例子(由 Openweather 提供)。

time                  temp  press   desc     w_sp w_dir
"2017-12-20 00:00:00" -4.49 1023.42 "clear"  1.21 198.501
"2017-12-20 03:00:00" -2.51 1023.63 "clouds" 1.22 180.501
"2017-12-20 06:00:00" -0.07 1024.43 "clouds" 1.53 169.503
"2017-12-20 09:00:00"  0.57 1024.83 "snow"   1.77 138.502
"2017-12-20 12:00:00"  0.95 1024.41 "snow"   1.61 271.001
"2017-12-20 15:00:00" -0.47 1024.17 "snow"   0.61 27.5019
"2017-12-20 18:00:00" -2.52 1024.52 "clear"  1.16 13.0007
"2017-12-20 21:00:00" -2.63 1024.73 "clear"  1.07 131.504

在我的例子中，我应该根据前 2 个出现标签的组合来评估整体的每日气象描述，关于风向我不能对 8 个值求平均值，我必须应用特定的公式。

我熟悉 sql 但不太熟悉 postgres 存储过程，我想我需要像游标这样的东西，但我在这里有点迷路。我相信这可以通过多种方式实现，但我要求你给我路径。到目前为止，我有一个存储过程的草稿，但我有点无能

CREATE FUNCTION meteo_forecast_daily () 
RETURNS TABLE (
forecasting_date DATE,
temperature NUMERIC,
pressure NUMERIC,
description VARCHAR(20),
w_speed NUMERIC,
w_dir NUMERIC
) 
AS $$
DECLARE 
clouds INTEGER;
snow INTEGER;
clear INTEGER;
rain INTEGER;
thunderstorm INTEGER;
BEGIN
 RETURN QUERY SELECT
 m.forecasting_time::date as forecasting_date,
 avg(m.temperature) as temperature
 avg(m.pressure) as pressure
 description???
 avg(m.w_sp) as w_speed
 w_dir????
 FROM
 meteo_forecast_last_update m
 WHERE
 forecasting_time > now()
 group by forecasting_date;
END; $$ 
LANGUAGE 'plpgsql';

因此我的问题是，如何检索每个日期的 8 个元素并以某种方式分别处理它们？

期望的结果:

time          temp press    desc                    w_sp  w_dir
"2017-12-20" -4.49 1023.42 "clear,clouds,rain,..."  1.21 (198.501, 212.23..)
"2017-12-21" -4.49 1023.42 "rain,snow,rain,..."     1.45 (211.501, 112.26..)
"2017-12-22" -4.49 1023.42 "clear,clouds,rain,..."  1.89 (156.501, 312.53..)

提前致谢，新年快乐:)

Answer 1

你应该通过

SELECT m.forecasting_time::date AS forecasting_date,
    AVG(m.temperature) as temperature,
    AVG(m.pressure) as pressure,
    STRING_AGG(DISTINCT m.description, ',') AS description,
    AVG(m.w_sp) as w_speed,
    ARRAY_AGG(m.w_dir) AS w_dir
FROM meteo_forecast_last_update m
WHERE m.forecasting_time > now()
GROUP BY 1 ORDER BY 1;

您可以在聚合函数内部使用 DISTINCT。它仅对不同的值应用聚合函数。