1. 识别唯一活跃客户
在许多业务场景中，一个客户可能在系统中存在多条记录，但根据业务规则，通常只有一个记录应该被标记为“活跃”。本节的目标是识别那些在客户表中仅有一条记录，并且该记录被标记为活跃的客户。
1.1 业务场景与挑战
假设我们有一个Customers表，包含customer_number（客户编号）、customer_name（客户名称）、active（活跃标志，布尔值）等字段。理想情况下，对于同一个customer_number，应该只有一条记录且active为TRUE。我们需要找到那些完全符合这一条件的客户。
1.2 解决方案：使用条件聚合
PostgreSQL的FILTER子句在COUNT等聚合函数中提供了强大的条件聚合能力。我们可以利用它来同时检查记录总数和活跃记录数。
SELECT customer_number FROM Customers c GROUP BY customer_number HAVING COUNT(*) = 1 AND COUNT(*) FILTER (WHERE active) = 1;
代码解析：
- GROUP BY customer_number: 首先按客户编号对记录进行分组。
- HAVING COUNT(*) = 1: 确保每个客户编号只有一条记录。
- AND COUNT(*) FILTER (WHERE active) = 1: 在满足上一条件的基础上，进一步确保这唯一的一条记录必须是活跃的（即active为TRUE）。
这种方法比尝试先过滤再计数的传统方式更简洁和高效，因为它在一个GROUP BY操作中完成了所有必要的检查。
2. 查找无近期订单的非活跃客户
第二个常见需求是识别那些在系统中没有任何活跃记录，并且在过去指定天数内（例如180天）没有下达任何订单的客户。这对于清理数据、识别潜在流失客户或进行特定营销活动至关重要。
2.1 业务场景与挑战
除了Customers表，我们还有一个order_master表，包含customer_number、deliverydate（交货日期）、order_number（订单编号）、insert_time（订单插入时间）等字段。我们需要结合这两个表的信息：
- 确定哪些客户在Customers表中没有任何活跃记录（即所有与该customer_number关联的记录中，active都为FALSE）。
- 在这些客户中，找出那些最近一次订单的insert_time早于当前日期 - 180天的客户。
2.2 解决方案：子查询与日期函数
我们可以通过嵌套查询和日期函数来解决这个问题。
SELECT cu.customer_number
FROM order_master om
JOIN (
SELECT customer_number
FROM Customers c
GROUP BY customer_number
HAVING COUNT(*) FILTER (WHERE active) = 0
) AS cu ON om.customer_number = cu.customer_number
GROUP BY cu.customer_number
HAVING MAX(om.insert_time) < CURRENT_DATE - INTERVAL '180 day';代码解析：
-
内部子查询 (cu):
SELECT customer_number FROM Customers c GROUP BY customer_number HAVING COUNT(*) FILTER (WHERE active) = 0
这个子查询的作用是识别那些在Customers表中没有任何活跃记录的客户。COUNT(*) FILTER (WHERE active) = 0精确地筛选出所有记录的active字段都为FALSE的客户编号。
-
外部查询:
- JOIN ... ON om.customer_number = cu.customer_number: 将内部子查询的结果（即非活跃客户编号列表）与order_master表连接起来，以便获取这些非活跃客户的订单信息。
- GROUP BY cu.customer_number: 再次按客户编号分组，目的是找到每个非活跃客户的最新订单时间。
- HAVING MAX(om.insert_time)
2.3 扩展：获取非活跃客户的订单详情
如果不仅需要客户编号，还需要获取这些非活跃客户的详细订单信息，可以使用公共表表达式（CTE）来提高查询的可读性和模块化。
WITH inactive_cust AS (
SELECT cu.customer_number
FROM order_master om
JOIN (
SELECT customer_number
FROM Customers c
GROUP BY customer_number
HAVING COUNT(*) FILTER (WHERE active) = 0
) AS cu ON om.customer_number = cu.customer_number
GROUP BY cu.customer_number
HAVING MAX(om.insert_time) < CURRENT_DATE - INTERVAL '180 day'
)
SELECT c.customer_number, c.customer_name,
o.order_number, o.insert_time
FROM inactive_cust ic
JOIN Customers c ON ic.customer_number = c.customer_number
JOIN order_master o ON ic.customer_number = o.customer_number;代码解析：
- inactive_cust CTE: 这个CTE包含了上一节中识别出的所有无近期订单的非活跃客户的customer_number。
-
主查询:
- 将inactive_cust CTE与Customers表连接，获取客户名称等详细信息。
- 再与order_master表连接，获取这些客户的所有订单编号和插入时间。
- 注意: 如果Customers表可能存在同一个customer_number有多个customer_name的情况，需要对Customers表进行去重或选择逻辑。这里假设customer_number和customer_name是唯一对应的。如果需要确保只获取一个客户名称，可以在Customers表加入DISTINCT或GROUP BY。
3. 注意事项与总结
- 条件聚合 (FILTER 子句)：这是PostgreSQL特有的功能，极大地简化了在聚合过程中应用条件筛选的逻辑，提高了查询效率和可读性。
- 日期函数 (CURRENT_DATE, INTERVAL)：在处理时间序列数据时非常有用，能够动态地计算日期范围，避免硬编码日期。
- 子查询与CTE: 对于复杂的查询，合理使用子查询和CTE可以分解问题，使SQL代码更易于理解和维护。CTE尤其适用于需要多次引用相同中间结果的场景。
- 性能优化: 对于大型表，确保在customer_number、active和insert_time等常用作连接或筛选条件的列上建立索引，可以显著提升查询性能。
- 数据一致性: 确保Customers表和order_master表之间的customer_number字段具有良好的数据一致性，是所有连接查询正确执行的基础。
- 业务逻辑理解: 在编写复杂查询之前，务必清晰理解业务需求，例如“非活跃”的具体定义（是active=false的单条记录，还是没有任何active=true的记录）。本文的解决方案采用了更严格的“没有任何活跃记录”的定义。
通过掌握这些PostgreSQL高级查询技巧，开发者和数据分析师能够更精准、高效地从复杂数据中提取有价值的信息，支持业务决策。
