sql - 根据整列的平均值过滤表格

Question

我曾尝试根据列的平均值过滤表格，但不知何故它不起作用。它不返回任何东西。平均值约为 102，用它替换 AVG 子句确实会返回正确的行。

我怎样才能让它正常工作？

SELECT
ce.customer_id,
ce.first_name,
ce.total

FROM
(SELECT 
p.customer_id,
c.first_name,
SUM(p.amount) AS total

FROM payment as p
JOIN customer as c ON p.customer_id=c.customer_id
GROUP BY p.customer_id,c.first_name) AS ce

GROUP BY ce.customer_id,ce.first_name,ce.total

HAVING ce.total > AVG(ce.total)

Answer 1

您的查询有多个错误。外部 GROUP BY 没有意义。

我的建议是使用窗口函数。在这种情况下，您需要总数的平均值，因此如下所示:

SELECT ce.customer_id, ce.first_name, ce.total
FROM (SELECT p.customer_id, c.first_name, SUM(p.amount) AS total,
             AVG(SUM(p.amount)) OVER () as avg_total
      FROM payment p JOIN
           customer c
           ON p.customer_id = c.customer_id
      GROUP BY p.customer_id, c.first_name
     ) ce
WHERE ce.total > avg_total