Postgres - 使用聚合函式返回不同的列？

我目前有一個包含原始溫度數據的表格，如下所示：

我編寫了一個查詢來查找按時間戳日期分組的數據的最小值、最大值和平均值：

drop table solardb.weatherinfodaily;

select date_trunc('day',ts) as ts,
min("Air Temperature (Actual) (Degrees C)")*9/5 + 32 as "Daily Min Temp (F)",
avg("Air Temperature (Actual) (Degrees C)")*9/5 + 32 as "Daily Avg Temp (F)",
max("Air Temperature (Actual) (Degrees C)")*9/5 + 32 as "Daily Max Temp (F)"
into solardb.weatherinfodaily
from solardb.weatherdata
group by date_trunc('day',ts) 
order by date_trunc('day',ts);

select * from solardb.weatherinfodaily;

我想返回與聚合函式關聯的時間戳，但我還沒有想出辦法來做到這一點。

即查詢將返回：

ts，每日最低溫度，當最低溫度發生時來自原始數據表的時間戳，每日平均溫度，每日最高溫度，當最高溫度發生時來自原始數據的時間戳。

我該如何編寫這樣的查詢？

您需要將目前查詢的結果與溫度和日期值的基表（兩次）進行自聯接。您的查詢可能如下所示：

with agg (day, min_c, avg_c, max_c ) as (
   select date_trunc('day',ts) as ts,
   min("Air Temperature (Actual) (Degrees C)") as "Daily Min Temp (F)",
   avg("Air Temperature (Actual) (Degrees C)"),
   max("Air Temperature (Actual) (Degrees C)")
   from solardb.weatherdata
   group by date_trunc('day',ts) 
   order by date_trunc('day',ts)
)
select 
 agg.day as ts,
 agg.min_c*9/5 + 32  as "Daily Min Temp (F)",
 data_min.ts as "Time of minimum",
 agg.avg_c*9/5 + 32 as "Daily Avg Temp (F)",
 agg.max_c*9/5 + 32 as "Daily Max Temp (F)",
 data_max.ts as "Time of maximum"
from agg 
join solardb.weatherdata data_min 
 on agg.min_c = data_min."Air Temperature (Actual) (Degrees C)"
 and agg.day = date_trunc('day',data_min.ts)
join solardb.weatherdata data_max 
 on agg.max_c = data_max."Air Temperature (Actual) (Degrees C)"
 and agg.day = date_trunc('day',data_max.ts)

以上未經測試。我使用 CTE 使邏輯更加明顯，但也可以使用簡單的子選擇。

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