PostgreSQL高级查询：精确识别客户活跃状态与订单历史

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（订单插入时间）等字段。我们需要结合这两个表的信息：

1. 确定哪些客户在Customers表中没有任何活跃记录（即所有与该customer_number关联的记录中，active都为FALSE）。
2. 在这些客户中，找出那些最近一次订单的insert_time早于当前日期 - 180天的客户。

2.2 解决方案：子查询与日期函数

我们可以通过嵌套查询和日期函数来解决这个问题。

超级简历WonderCV

免费求职简历模版下载制作，应届生职场人必备简历制作神器

150

查看详情

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) < CURRENT_DATE - INTERVAL '180 day': 筛选出那些最新订单时间早于当前日期180天前的客户。CURRENT_DATE获取当前日期，INTERVAL '180 day'用于日期减法。

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高级查询技巧，开发者和数据分析师能够更精准、高效地从复杂数据中提取有价值的信息，支持业务决策。

以上就是PostgreSQL高级查询：精确识别客户活跃状态与订单历史的详细内容，更多请关注php中文网其它相关文章！