如何基于其他列的值聚合一个列 - PostgreSQL

Question

假设我有一张表：

日期	水果	状态	水果数量
2022-01	苹果	成熟	3
2022-01	香蕉	成熟	5
2022-01	梨子	成熟	10
2022-01	葡萄	成熟	9
2022-02	苹果	成熟	3
2022-02	香蕉	成熟	3
2022-02	梨子	成熟	3
2022-02	葡萄	成熟	7

我想创建一个查询，根据它们的状态和日期对水果数量这一列进行聚合，并添加3个聚合列(AVG, MIN, MAX)，按照水果和日期排序。表格的输出结果应该是：

日期	水果	状态	数量	平均数	最大数	最小数
2022-01	苹果	成熟	3	6.5	10	3
2022-01	梨子	成熟	10	6.5	10	3
2022-01	香蕉	成熟	5	7	9	5
2022-01	葡萄	成熟	9	7	9	5
2022-02	苹果	成熟	3	3	3	3
2022-02	梨子	成熟	3	3	3	3
2022-02	香蕉	成熟	3	5	7	3
2022-02	葡萄	成熟	7	5	7	3

我感到很困惑，以下是我的进展：

SELECT 
    date, 
    fruit, 
    status,
    numberOfFruits,
    AVG(CASE WHEN "status" = 'ripe' THEN "numberOfFruits" ELSE "numberOfFruits" END) as AvgNumOfFruits, 
    MIN(CASE WHEN "status" = 'ripe' THEN "numberOfFruits" ELSE "numberOfFruits" END) as MingNumOfFruits,
    MAX(CASE WHEN "status" = 'ripe' THEN "numberOfFruits" ELSE "numberOfFruits" END) as MaxNumOfFruits
FROM fruitdata
GROUP BY 1, 2, 3, 4
ORDER BY date, status

查询仅重复这三个聚合列的numberofFruits。应该是“取具有成熟状态和日期2022-01的列numberOfFruits的平均值，并将其放入一个名为AvgNumOfFruits的新列中。”不知道如何将其转换为SQL。请注意，保留HTML标签。

如有建议/提示/帮助，请提供。谢谢！

- Jason

---

你可以简化一下：avg("numberOfFruits") filter (where status = 'ripe')，但如果你想要 avg()、min() 和 max()，就无法避免写三个表达式。顺便说一句：你真的应该避免使用那些可怕的带引号的标识符。 - user330315

三个表达式，每个都获取聚合值？例如一个获取 avg()，一个获取 min()，另一个获取 max()，然后最后将它们“组合”起来？抱歉，我还在努力提高我的 SQL 技能。 - Jason

听起来你想要按更少的条件进行分组...尝试使用GROUP BY 1,2或GROUP BY 1,2,3。 - Josh

Answer 1

聚合函数用于聚合行（将行组合以获取聚合值）。在您的情况下，您应该使用窗口函数，它可以在窗口（行的分区/组）上计算值，不需要聚合行。

SELECT *,
       AVG(numberOfFruits) OVER(PARTITION BY date, status) AS AvgNumOfFruits, 
       MAX(numberOfFruits) OVER(PARTITION BY date, status) AS MaxNumOfFruits,
       MIN(numberOfFruits) OVER(PARTITION BY date, status) AS MinNumOfFruits 
FROM fruitdata
ORDER BY date,
         status DESC

需要在 "date" 和 "status" 字段的组上计算相应的窗口函数。另一方面，排序是完全可选的，并由您决定（ORDER BY 子句）。

在此处检查演示 here。

- lemon