数据科学
掌握解决真实的 SQL CASE WHEN 问题的方法
使用 CASE WHEN 的真实面试问题！
在文章5个高级 SQL 概念中，您可以探索 CASE..WHEN 语句的基础知识，以及它的用例。然而，在这些文章中，我没有提到 CASE WHEN 的实际应用。
因此，我通过领英与多个体育和电子商务公司的数据科学专业人士取得了联系，并收集了这两个在求职面试中最常被问到的 SQL CASE WHEN 示例。
为什么 CASE WHEN 是最常被问到的概念之一？
因为，在 SQL 中，CASE WHEN 语句有助于在查询数据时实现If..Else逻辑。
您经常需要根据某些条件提取或汇总数据。当然，您可以使用 WHERE 子句应用这些条件，但是当您想基于这些条件创建新列时，CASE..WHEN非常方便，您必须使用它。
在本文中，您将看到两个真实且常见的面试问题，您可以使用 SQL CASE WHEN 解决。
您将学习解决这些问题的方法，最终如何使用 CASE WHEN 获得所需的输出。此外，您将学习如何将复杂的查询分解为简单易懂的步骤。
为了给您一个快速概述，以下是您将在本快速阅读中探索的两个问题。
· 示例 1：创建体育锦标赛的积分表 · 示例 2：查找电子商务网站的新客户和重复客户
在本文末尾获取示例数据集的 CSV 文件。
让我们从由一位体育分析师提出的问题开始。他们的分析团队需要根据不同团队之间的比赛总数创建积分表。
这就是为什么他们在每次数据分析师面试中都会问这个问题的原因。
示例 1：创建体育锦标赛的积分表
这是将表从长格式（行数>列数）转换为宽格式（列数>行数）的经典场景。这也被称为数据透视，是 SQL 中 CASE WHEN 的一个重要用例。
在这种情况下，您有一个包含参赛队伍的名称和获胜者的表。您需要创建一个积分表，其中包含每个队伍参加的比赛数量，赢得的比赛数量，输掉的比赛数量以及打成平局的比赛数量的信息。
让我们看看如何解决这类问题 —
这是输入表，每行代表两个队伍之间的比赛，列 winner 表示哪个队伍获胜。获胜者列中的 NULL 值表示比赛是平局，即没有任何一支队伍获胜。
让我们将这个问题分解为以下子任务。
- 查找每个队伍赢得的比赛总数
- 查找每个队伍输掉的比赛总数
- 查找没有任何一支队伍获胜的比赛总数
- 查找每个队伍参加的比赛总数
要了解一支球队赢了多少场比赛，您需要了解每场比赛哪个球队赢了比赛。您可以通过将team_1和team_2列与winner列进行比较来实现这一点。
因此，对于特定的行，当team_1和winner列的值相等时，team_1是赢家。
您可以使用SQL中的CASE..WHEN..THEN语句完全相同的逻辑来进行翻译，如下所示。
SELECT team_1 , team_2 , winner , CASE WHEN team_1 = winner THEN 1 ELSE 0 END as win_flag , CASE WHEN winner IS NULL THEN 1 ELSE 0 END as draw_flagFROM analyticswithsuraj.teams如上面的查询所示，您将创建一个额外的列win_flag。当一个团队是赢家时，您将为该列分配值1。同样，如果winner列为空，则将为draw_flag列分配值1。
因此，上述查询将为team_1列中的所有团队创建以下输出。
类似地，当team_2列和winner列的值相等时，team_2是赢家。因此，您可以为team_2中的所有团队编写完全相同的查询
SELECT team_1 , team_2 , winner , CASE WHEN team_2 = winner THEN 1 ELSE 0 END as win_flag , CASE WHEN winner IS NULL THEN 1 ELSE 0 END as draw_flagFROM analyticswithsuraj.teams其中，您将获得team_2中的值的以下输出
好的，上述两个查询仅供您理解。实际上，您可以为team_1和team_2列中的每个团队创建一个单独的CTE，如下所示。
WITH win_draw_flag AS(SELECT team_1 as team , CASE WHEN team_1 = winner THEN 1 ELSE 0 END as win_flag , CASE WHEN winner IS NULL THEN 1 ELSE 0 END as draw_flagFROM analyticswithsuraj.teamsUNION ALLSELECT team_2 as team , CASE WHEN team_2 = winner THEN 1 ELSE 0 END as win_flag , CASE WHEN winner IS NULL THEN 1 ELSE 0 END as draw_flagFROM analyticswithsuraj.teams)这将创建一个像这样的CTE – 我只是为了您的理解而显示。
请记住，您仍然处于长表格形式中，现在您已经获得了每个团队是否赢得比赛的信息。
接下来，您只需要简单地对这些列进行聚合，以获取每个团队参加比赛的总次数、赢得的次数和输掉的次数。您可以使用以下查询完成。
SELECT team , COUNT(*) AS matches_played , SUM(win_flag) AS matches_won , COUNT(*) - SUM(win_flag) - SUM(draw_flag) AS matches_lost , SUM(draw_flag) AS matches_drawFROM win_draw_flagGROUP BY teamORDER BY team其中，COUNT(*)给出了每个团队在CTE win_draw_flag中出现的总次数，从中减去赢得的比赛次数和平局次数，将给出每个团队输掉的比赛总次数。
不需要单独创建CTE，你也可以像下面这样编写查询，并将整个CASE..WHEN查询作为子查询传递。
SELECT team , COUNT(*) AS matches_played , SUM(win_flag) AS matches_won , COUNT(*) - SUM(win_flag) - SUM(draw_flag) AS matches_lost , SUM(draw_flag) AS matches_drawFROM ( SELECT team_1 as team , CASE WHEN team_1 = winner THEN 1 ELSE 0 END as win_flag , CASE WHEN winner IS NULL THEN 1 ELSE 0 END as draw_flag FROM analyticswithsuraj.teams UNION ALL SELECT team_2 as team , CASE WHEN team_2 = winner THEN 1 ELSE 0 END as win_flag , CASE WHEN winner IS NULL THEN 1 ELSE 0 END as draw_flag FROM analyticswithsuraj.teams ) AS win_draw_flagGROUP BY teamORDER BY team它的输出结果与上面提到的完全相同。
嗯，解决这个问题可以有多种方法 – 我发现这种方法更简单。如果你对这个问题有其他解决方法，请随时在评论中提到。
示例2：查找电子商务网站的新客户和重复客户
这是比较日期并使用CASE..WHEN语句实现If..Else逻辑的经典示例之一。在任何与客户打交道的公司中，你都可能遇到这种类型的问题。
场景是这样的 – 你有一个电子商务网站，每天都有客户访问和购买产品。你的任务是在每天识别有多少新客户和有多少重复客户。
这是一个输入表 – orders – 在这里你可以看到客户ABC101，BCD201和ABD101在多个日期访问网站并购买不同的产品。
让我们将问题分解为以下子任务 –
- 找到第一次访问网站的时间，即第一个日期
- 将第一个日期与订单日期进行比较，以决定客户是重复访问者还是第一次访问者
通过使用GROUP BY将所有记录按customer_id分组，并找到order_date的最小值，你可以轻松解决第一个子任务，如下所示。
SELECT customer_id , MIN(order_date) as first_order_dateFROM analyticswithsuraj.ordersGROUP BY customer_id
这很简单！
接下来，为了将first_order_date与每个order_date进行比较，首先需要将两个列放在单个表中。
你可以通过在customer_id上执行JOIN来轻松实现。在这里，你可以使用上面的查询创建一个CTE，这样你就可以得到一个临时表来与输入表进行连接。
WITH first_orders AS(SELECT customer_id , MIN(order_date) as first_order_dateFROM analyticswithsuraj.ordersGROUP BY customer_id)SELECT t1.* , t2.first_order_dateFROM analyticswithsuraj.orders AS t1INNER JOIN first_orders AS t2 ON t1.customer_id = t2.customer_id
现在，您已经将两列放在同一个表中，可以将order_date与first_order_date进行比较，并实现以下If..Else逻辑。
- 如果first_order_date和order_date相同，则客户是新客户
- 如果first_order_date和order_date不同，则客户是重复客户
因此，理想情况下，您需要创建两列来使用SQL中的CASE WHEN实现上述两个If..Else语句。
您不需要创建任何单独的表，而是可以在上面的查询中添加两列，其中包含了两个表的连接。以下是如何完成的。
WITH first_orders AS(SELECT customer_id , MIN(order_date) as first_order_dateFROM analyticswithsuraj.ordersGROUP BY customer_id)SELECT t1.* , t2.first_order_date , CASE WHEN t1.order_date=t2.first_order_date THEN 1 ELSE 0 END AS new_customer_flag , CASE WHEN t1.order_date!=t2.first_order_date THEN 1 ELSE 0 END AS repeat_customer_flagFROM analyticswithsuraj.orders AS t1INNER JOIN first_orders AS t2 ON t1.customer_id = t2.customer_id
作为结果，当列first_order_date和order_date相等时，列new_customer_flag将为1。类似地，当列first_order_date和order_date不同时，列repeat_customer_flag将为1。
现在，最后一步只需按order_date对所有记录进行分组，并求出列new_customer_flag和repeat_customer_flag的总和。
为此，您需要上面的表格，可以通过创建另一个CTE来实现，如下所示。
WITH first_orders AS(SELECT customer_id , MIN(order_date) as first_order_dateFROM analyticswithsuraj.ordersGROUP BY customer_id),customers AS(SELECT t1.* , t2.first_order_date , CASE WHEN t1.order_date=t2.first_order_date THEN 1 ELSE 0 END AS new_customer_flag , CASE WHEN t1.order_date!=t2.first_order_date THEN 1 ELSE 0 END AS repeat_customer_flagFROM analyticswithsuraj.orders AS t1INNER JOIN first_orders AS t2 ON t1.customer_id = t2.customer_id)SELECT order_date , SUM(new_customer_flag) AS number_of_new_customers , SUM(repeat_customer_flag) AS number_of_repeat_customersFROM customersGROUP BY order_dateORDER BY order_date
这就是您将获得所需输出的方法。您可以通过与输入表进行比较来交叉检查结果。
再次，您可以采取不同的方法来解决这个问题 – 这是我找到的最简单的方法。不要忘记在下面的评论中提及您的方法。
总之，
在本文中，您了解了如何处理使用SQL中的CASE WHEN解决实际场景的问题，并将复杂的SQL查询分解为简单的查询。希望您喜欢这篇文章。
基于CASE WHEN、RANK()、ROW_NUMBER()和GROUP BY的案例研究和问题在数据科学的工作面试中很常见。将问题分解为较小的子任务可以向面试官展示您解决问题的方法和思路。
因此，这个主题肯定对于提升您在SQL CASE WHEN、GROUP BY方面的技能，并在下一次求职面试中取得成功非常有用。
有兴趣阅读更多VoAGI的故事吗？
💡 考虑成为VoAGI会员，以访问VoAGI上的无限故事和每日有趣的VoAGI新闻简报。我将获得您费用的一小部分，并且您不需要支付额外费用。
💡 确保注册并加入其他200多人，以便不再错过有关数据科学指南、技巧和提示以及SQL和Python最佳实践的文章。
感谢您的阅读！
数据集：这些是我为这些示例创建的虚拟数据集。您可以免费从我的Github存储库下载它们 – 示例1和示例2。