SQL - 获取条件限制和总计数 - 是否需要子查询？

Question

我正在尝试获取我们每个客户在过去 31 天内订购的订单数量，以及他们曾经订购的总数。我想要的输出看起来像:

count    total    name           customer ID
--------------------------------------------
3        10       Sarah Smith    22
2        8        John Jenkins   15
1        5        Delia Denton   44

到目前为止，我有以下内容，可以完美满足第一个要求:

SELECT COUNT(o.id), CONCAT(c.first_name, ' ', c.last_name) AS name, c.id AS "customer ID"
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE order_date > (CURRENT_DATE - 31)
GROUP BY name, "customer ID"
ORDER BY count DESC

产生:

count    name           customer ID
-----------------------------------
3        Sarah Smith    22
2        John Jenkins   15
1        Delia Denton   44

但是，我对子查询的有限了解并没有扩展到如何集成子查询来检索此结果集中每个客户的订单总数。 (本质上是相同的查询，但没有日期限制。)我不确定是否(或如何)我应该以某种方式对所有订单的总集使用它作为过滤器，或者是否正确的做法是获取返回的 ID在这里并将它们插入另一个查询。我应该怎么办？我正在使用 PostgreSQL 9.4。

Answer 1

遍历整个表并使用条件聚合来计算过去 31 天的计数。

SELECT
    COUNT(CASE WHEN order_date > (CURRENT_DATE - 31) THEN o.id END) AS cnt_31,
    COUNT(o.id) AS total,     -- now this will count the grand total
    CONCAT(c.first_name, ' ', c.last_name) AS name,
    c.id AS "customer ID"
FROM orders o
INNER JOIN customers c
    ON c.id = o.customer_id
GROUP BY name, "customer ID"
HAVING COUNT(CASE WHEN order_date > (CURRENT_DATE - 31) THEN o.id END) > 0
ORDER BY count DESC

我的查询实际上所做的只是将逻辑从 WHERE 子句移动到过去 31 天的计数中。由于这个新查询是针对整个表的，因此原始计数现在对应于预期输出中的 total。