如何基于JOIN结果计算列值，识别符合规则的旧账户？

需求说明 #

拥有Account、Property、Service三张表，需识别符合以下任一条件的旧账户：

• 账户下所有Property均处于关闭状态；
• 账户24个月内未购买过Service。

示例表结构及数据 #

-- ACCOUNT 表
+-------+-----------------------------------+
| ac_id | ac_email                          |
+-------+-----------------------------------+
|  1416 | bob@bob.com                       |
|  1419 | joe@joe.com                       |
+-------+-----------------------------------+

-- PROPERTY 表
+------+---------------+-------+
| p_id | p_closed      | ac_id |
+------+---------------+-------+
|    3 |  FALSE        |  1416 |
|    6 |  TRUE         |  1419 |
|    7 |  TRUE         |  1419 |
+------+---------------+-------+

-- SERVICE 表
+------+------------+
| p_id | s_saledate |
+------+------------+
|    3 | 2010-03-17 |
|    3 | 2011-02-16 |
|    6 | 2022-11-14 |
|    7 | 2022-01-24 |
+------+------------+

期望返回结果 #

+------------+-----------------------+-----------------------+--------------+------------------+
| account_id | email                 | all_properties_closed | property_ids | latest_sale_date |
+------------+-----------------------+-----------------------+--------------+------------------+
|       1416 | bob@bob.com           |       FALSE           |     3        | 2011-02-16       |
|       1419 | joe@joe.com           |       TRUE            |    6,7       | 2022-11-14       |
+------------+-----------------------+-----------------------+--------------+------------------+

注：Bob账户因2年以上未购买Service被返回；Joe账户因所有Property均关闭被返回。

当前查询语句 #

SELECT
 ac_id as account_id,
 GROUP_CONCAT(DISTINCT p_id) as property_ids,
 MAX(sale_date) as latest_sale_date
 FROM Account
 JOIN Property USING (ac_id)
 JOIN Service USING (p_id)
 GROUP BY ac.ac_id
 HAVING latest_sale_date < 2021-03-31

问题与解决方案 #

当前查询未计算all_properties_closed列，仅筛选了“24个月未购Service”的账户，遗漏了“所有Property关闭”的情况，同时INNER JOIN会过滤掉无Service记录的账户。

可以通过子查询聚合实现需求，无需使用ALL运算符，具体SQL如下：

SELECT
    a.ac_id AS account_id,
    a.ac_email AS email,
    pa.all_properties_closed,
    pa.property_ids,
    sa.latest_sale_date
FROM Account a
-- 关联Property聚合结果
LEFT JOIN (
    SELECT
        ac_id,
        -- 判断所有Property是否关闭：无未关闭的Property则为TRUE
        COUNT(CASE WHEN p_closed = FALSE THEN 1 END) = 0 AS all_properties_closed,
        GROUP_CONCAT(p_id ORDER BY p_id) AS property_ids
    FROM Property
    GROUP BY ac_id
) pa ON a.ac_id = pa.ac_id
-- 关联Service聚合结果
LEFT JOIN (
    SELECT
        p.ac_id,
        MAX(s.s_saledate) AS latest_sale_date
    FROM Service s
    JOIN Property p ON s.p_id = p.p_id
    GROUP BY p.ac_id
) sa ON a.ac_id = sa.ac_id
-- 筛选旧账户条件：满足任一即可
WHERE
    -- 所有Property均关闭
    pa.all_properties_closed = TRUE
    OR
    -- 24个月内无购买记录（包含无任何Service记录的情况）
    (sa.latest_sale_date IS NULL OR sa.latest_sale_date < DATE_SUB(CURDATE(), INTERVAL 24 MONTH))
ORDER BY a.ac_id;

代码解释 #

1. Property聚合子查询（pa）：

   • 通过COUNT(CASE WHEN p_closed = FALSE THEN 1 END) = 0判断该账户下是否所有Property都处于关闭状态；
   • 使用GROUP_CONCAT聚合该账户的所有Property ID。

2. Service聚合子查询（sa）：

   • 关联Property表获取对应的账户ID，计算每个账户的最新购买日期；
   • 使用LEFT JOIN确保无Service记录的账户也能被包含。

3. 主查询筛选条件：

   • 同时覆盖“所有Property关闭”和“24个月未购Service（含无购买记录）”两种旧账户判定规则。

内容的提问来源于stack exchange，提问作者J.spenc