SQL如何按州选择交易金额最高的客户

Question

我正在尝试编写一个 SQL 查询，返回每个州花费最多的五个客户的姓名和购买金额。

表架构

customers
|_state
|_customer_id
|_customer_name

transactions
|_customer_id
|_transact_amt

尝试看起来像这样

SELECT state, Sum(transact_amt) AS HighestSum
FROM (
    SELECT name, transactions.transact_amt, SUM(transactions.transact_amt) AS HighestSum
    FROM customers
    INNER JOIN customers ON transactions.customer_id = customers.customer_id
    GROUP BY state
) Q
GROUP BY transact_amt
ORDER BY HighestSum 

我迷路了。谢谢。

预期结果是每个州交易量排名前 5 位的客户的姓名。

ERROR:  table name "customers" specified more than once
SQL state: 42712

Answer 1

首先，您的JOIN 需要正确。其次，你想使用窗口函数:

SELECT ct.*
FROM (SELECT c.customer_id, c.name, c.state, SUM(t.transact_amt) AS total,
             ROW_NUMBER() OVER (PARTITION BY c.state ORDER BY SUM(t.transact_amt) DESC) as seqnum
      FROM customers c JOIN
           transaactions t
           ON t.customer_id = c.customer_id
      GROUP BY c.customer_id, c.name, c.state
     ) ct
WHERE seqnum <= 5;

您似乎有几个关于 SQL 的问题。我将从了解聚合函数开始。您有一个别名为 HighestSum 的 SUM()。它只是每个客户的总数。