时间戳

首页 > 解决方案 > SQL如何按状态选择交易金额最高的客户

sql - SQL如何按状态选择交易金额最高的客户

问题描述

我正在尝试编写一个 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

标签： sqldatabasepostgresql

解决方案

首先，您需要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 有几个问题。我将从了解聚合函数开始。你有一个SUM()别名HighestSum。它只是每个客户的总数。

推荐阅读