postgresql - SP/View每日汇总3小时值

我试图将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

在我的情况下，我应该根据前两个出现标签的组合来评估总体每日气象描述，关于风向，我不能平均8个值，我必须应用一个特定的公式。
我熟悉sql，但不太熟悉postgres存储过程，我想我需要一些类似cursor的东西，但我在这里有点迷路了。我相信这可以在很多方面实现，但我要求你给我一条路。到目前为止，我有一个存储过程的草稿，但是我有点不清楚

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..)

提前谢谢，新年快乐：）

最佳答案

你应该通过

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。它只对不同的值应用聚合函数。