【数据分析面试】11. 计算账户关闭率（SQL：评估不同查询方法的性能效率）

题目

给定一个账户状态表，编写一个查询以获取在2019年12月31日活跃并在2020年1月1日关闭的账户所占的百分比，以及在2019年12月31日活跃的总账户数。每个账户只有一条每日记录，显示其在当天结束时的状态。

注意：将结果四舍五入到小数点后两位。

Input:

account_status table

Column	Type
account_id	INTEGER
date	DATETIME
status	VARCHAR

account_id	date	status
1	2020-01-01	closed
1	2019-12-31	open
2	2020-01-01	closed

输出:

Column	Type
percentage_closed	FLOAT

答案

解题思路

关键是要圈定两组ID：31号集体的激活账户，以及1号的休眠账户。
具体解题步骤如下：

1. 首先，我们需要从账户状态表中筛选出在2019年12月31日活跃的账户。
2. 然后，我们需要计算这些账户中有多少个在2020年1月1日关闭。
3. 最后，我们将关闭的账户数量除以总的活跃账户数量，得到所需的百分比。
4. 使用ROUND函数将结果四舍五入到两位小数。

答案代码

下面是题目的不同解法，主要的答题思路一致。

解法一：

SELECT ROUND(
    (SELECT COUNT(*) FROM account_status WHERE date = '2020-01-01' AND status = 'closed')
    / (SELECT COUNT(*) FROM account_status WHERE date = '2019-12-31' AND status = 'open'),
    2
) AS percentage_closed

这种方法在处理大型数据集时可能会遇到性能问题，因为它执行了两次全表扫描。如果account_status表很大且没有适当的索引，这可能会导致查询速度较慢。然而，如果表很小或已经针对date和status列建立了索引，这可能是一个简单而有效的解决方案。

解法二：

SELECT 
	ROUND(
    SUM(CASE WHEN date = '2020-01-01' AND status = 'closed' THEN 1 ELSE 0 END) 
    / 
    COUNT(DISTINCT account_id) ,2)
    AS percentage_closed
FROM 
    account_status
WHERE 
    account_id IN (
        SELECT 
            account_id 
        FROM 
            account_status 
        WHERE 
            date = '2019-12-31' AND status = 'open'
    );

这种方法只需要一次全表扫描，并且利用了CASE语句来计算符合条件的行数。如果account_id有索引，那么子查询的性能会更好。这种方法通常比解法一更高效，特别是当处理大型数据集时。

解法三：

WITH closed_accounts AS (
    SELECT *
    FROM account_status
    WHERE DATE(date) = "2020-01-01"
        AND status = "closed"
),
open_accounts AS (
    SELECT *
    FROM account_status
    WHERE DATE(date) = "2019-12-31"
        AND status = "open"
)
SELECT
    ROUND(COUNT(c.account_id) / COUNT(o.account_id),2) AS percentage_closed
FROM open_accounts AS o
LEFT JOIN closed_accounts AS c
ON o.account_id = c.account_id

这种方法使用了两个CTE来创建临时结果集，然后进行连接操作。这可能会导致额外的性能开销，特别是在没有适当索引的情况下。然而，如果数据库优化器能够有效地执行这些操作，这种方法也可以提供良好的性能。

其他业务思考……

在实际工作环境中，查询代码需要考虑多个因素。 比如数据库的大小、性能要求、数据分布和索引策略等。

• 索引：确保account_status表上的date和status列有索引，这将显著提高查询性能。
• 数据量：如果数据量很大，倾向于选择那些减少全表扫描的方法。
• 资源限制：考虑到服务器的资源限制，如CPU和内存，选择资源消耗较少的方法。
• 维护性：选择易于理解和维护的代码，特别是在团队环境中。

基于上述考虑，解法二比其他两种答案更佳适用于真实工作场景，因为它通常在性能和维护性之间提供了一个好的平衡点。它避免了多次全表扫描，同时代码也相对简洁。

此外，还可以考虑以下改进：

• 使用索引视图或物化视图来预计算某些值，以减少实时计算的负担。
• 对于非常大的数据集，可以考虑使用并行查询或分布式计算来提高查询性能。
• 如果业务逻辑允许，可以定期汇总数据到另一个表中，这样查询可以直接从这个汇总表中获取数据，而不是实时计算。像这种账户状态的指标，通常更建议这种方法，方便每日追踪监控。

最终，最佳的选择应该基于具体的业务需求、数据特性和系统环境进行测试和评估。

代码汇总

CREATE TABLE account_status (
    account_id INT,
    date DATE,
    status VARCHAR(10)
);

INSERT INTO account_status (account_id, date, status) VALUES
(1, '2019-12-31', 'open'),
(1, '2020-01-01', 'closed'),
(1, '2020-01-02', 'closed'),
(1, '2020-01-03', 'closed'),
(2, '2019-12-30', 'open'),
(2, '2019-12-31', 'open'),
(2, '2020-01-01', 'open'),
(2, '2020-01-02', 'closed'),
(2, '2020-01-03', 'closed'),
(3, '2019-12-30', 'open'),
(3, '2019-12-31', 'open'),
(3, '2020-01-01', 'closed'),
(3, '2020-01-02', 'closed'),
(3, '2020-01-03', 'closed'),
(4, '2020-12-31', 'closed'),
(4, '2021-01-01', 'closed'),
(4, '2021-01-02', 'closed'),
(4, '2021-01-03', 'closed'),
(5, '2019-12-31', 'open'),
(5,'2020-01-01','closed'),
(5,'2020-01-02','closed');