如何计算SQL中使用CASE创建的没有子查询的类别的出现次数

Question

上下文

问题

• order表包含id，。。。，total_amt_usd、account_id

• accounts表包含id、name，。。。

考虑上面的表，我想写一个SQL查询到：

1. 根据与客户购买相关的金额对客户进行分类。最高类别包括所有订单总销售额超过20万美元的人。中间类在20万到10万美元之间。最低的类别是10万美元以下的人。提供一个表，其中包括与每个帐户关联的级别。该表应包含帐户名称、该客户所有订单的总销售额和类别，并且应首先列出支出最高的客户
2. 提供与第1部分中创建的每个类别相关联的帐户数

回答

第1部分

SELECT a.name, SUM(o.total_amt_usd) AS total_amt_spent,
CASE WHEN SUM(o.total_amt_usd) >= 200000 THEN 'top'
WHEN SUM(o.total_amt_usd) >= 100000 AND SUM(o.total_amt_usd) < 200000 THEN 'mid'
ELSE 'low' END AS category
FROM orders o
JOIN accounts a
ON a.id=o.account_id
GROUP BY 1
ORDER BY 2 DESC;

第2部分

SELECT category, COUNT(category)
FROM (SELECT a.name, SUM(o.total_amt_usd) AS total_amt_spent,
CASE WHEN SUM(o.total_amt_usd) >= 200000 THEN 'top'
WHEN SUM(o.total_amt_usd) >= 100000 AND SUM(o.total_amt_usd) < 200000 THEN 'mid'
ELSE 'low' END AS category
FROM orders o
JOIN accounts a
ON a.id=o.account_id
GROUP BY 1
ORDER BY 2 DESC) AS table1
GROUP BY category;

---

查询

有没有任何方法可以在不使用子查询的情况下完成第2部分我曾尝试在同一个查询中包含COUNT()，但一直出现错误。

Answer 1

您需要两个级别的聚合。但您可能不需要join到accounts:

SELECT category, COUNT(*)
FROM (SELECT o.account_id, SUM(o.total_amt_usd) AS total_amt_spent,
(CASE WHEN SUM(o.total_amt_usd) >= 200000 THEN 'top'
WHEN SUM(o.total_amt_usd) >= 100000 THEN 'mid'
ELSE 'low'
END) AS category
FROM orders o
GROUP BY o.account_id
) a
GROUP BY category;

这假设名称在accounts表中是唯一的。

ORDER BY在子查询中不合适。我还简化了CASE表达式中的逻辑，利用了条件按顺序求值的事实。