postgresql 如何使用CASE函数编写多条件SQL查询

qcbq4gxm 于 2023-10-18 发布在 PostgreSQL

关注(0)|答案(3)|浏览(217)

The table任务是：计算同时进行以下操作的客户数量：
1.有超过5个付款超过5000美元
1.平均支付金额超过1万美元
我使用了窗口函数和子查询：

CREATE TABLE Customers (
client INT,
payment INT);

INSERT INTO Customers(client, payment) VALUES
(1, 1000),
(1, 7000),
(1, 6000),
(1, 50000),
(1, 5500),
(1, 5600),
(2, 1000),
(2, 1000);

select client, count(payment) from
(select *, avg(payment) over(partition by client) as avg_payment from Customers) as t1
where payment > 5000
group by client
having count(payment)>5

但我必须使它没有窗口函数和子查询。我被告知只有使用CASE函数才能做到这一点。如果有人能帮我优化我的查询，我会很高兴。

postgresql

来源：https://stackoverflow.com/questions/77268546/how-to-write-sql-query-with-multiple-conditions-using-case-function

3条答案

按热度按时间

alen0pnh1#

您可以通过将聚合直接放置在having子句中来摆脱子查询：

select client
from Customers
group by client
having count(*) filter(where payment > 5000) > 5
   and avg(payment) > 10000

（online demo）
我更喜欢count(*)而不是count(payment)，因为后者不计算具有NULL值的行，尽管由于> 5000条件，在这里这并不重要。
现在，你可以使用sum来代替filter，它有条件地对每行的1或0进行计数，并为此使用CASE语句：

…
having sum(case when payment > 5000 then 1 else 0 end) > 5

或

…
having count(case when payment > 5000 then 1 /* else null */ end) > 5

或

…
having sum((payment > 5000)::int) > 5

尽管使用filter要优雅和直接得多。postgresql - sql - count of true values。

赞(0）回复(0）举报 2023-10-18

iyr7buue2#

TLDR：工作提琴here
让我们将查询分解为几个部分：
1.找到客户谁有超过5个付款超过5000美元
您可以在WHERE子句中查询超过5，000美元的付款，然后在HAVING子句中指定“超过5笔付款”（按客户端ID聚合后）：

SELECT 
  client, 
  COUNT(*) AS payment_gt_5000
FROM customers
WHERE payment > 5000
GROUP BY client
HAVING COUNT(*) >= 5

(note我将>5更改为>=5，因为客户端ID 1正好有5个匹配的付款）。
然后，如果我们想捕获“平均支付价值超过10，000美元”，我们将使用非常类似的查询：

SELECT 
  client, 
  AVG(payment)
FROM customers
GROUP BY client
HAVING AVG(payment) > 10000

由于这两个查询非常相似，我们应该能够将它们合并组合起来。唯一棘手的部分是我们必须从WHERE子句中删除payment > 5000，因为我们想计算 * 所有 * 支付的平均值。等等，这是一只鸟！是飞机！这是条件聚合的拯救：

SELECT 
  client, 
  COUNT(CASE WHEN payment > 5000 THEN 1 END) AS payment_gt_5000,
  AVG(payment) AS avg_payment
FROM customers
GROUP BY client
HAVING
    COUNT(CASE WHEN payment > 5000 THEN 1 END) >= 5
    AND AVG(payment) > 10000

我们没有将payment > 5000应用于WHERE子句，所以我们得到了所有付款的平均值。但是我们仍然得到了> 5000（COUNT(CASE WHEN payment > 5000 THEN 1 END)）的付款计数，所以我们仍然可以在HAVING条款中找出哪些客户有5+超过5000美元的付款。

赞(0）回复(0）举报 2023-10-18

epggiuax3#

从技术上讲，基于“计算客户数量...”这个问题，没有一种方法可以使用一个没有连接的选择语句来完成这一任务。
它需要一个窗口函数或CTE或子查询来返回聚合。这是因为如果没有窗口函数（这是初始平均值和计算客户端ID所必需的），就无法在同一选择中运行两个不同的分组

赞(0）回复(0）举报 2023-10-18