校招算法笔面试 | SQL笔试面试编程题-商品销售总额分布

题目

题目链接

这道题目要求我们统计商品名称为“anta”的商品在选择支付方式步骤中各个支付渠道的使用次数，我们要做的事情如下：

1. 确定总体问题

我们需要统计商品名称为“anta”的商品在选择支付方式步骤中，各个支付渠道的使用次数，将结果按使用次数降序排列，并处理支付方式的脏数据。

2. 分析关键问题

• 连接表：将user_client_log和product_info表连接起来，以便获取每个商品的名称。
• 筛选记录：筛选出商品名称为“anta”且步骤为“select”的记录。
• 处理脏数据：将支付方式为空的记录标记为“error”。
• 统计支付方式使用次数：对每个支付方式的使用次数进行计数。
• 排序输出：按使用次数降序排列。

3. 解决每个关键问题的代码及讲解

步骤1：连接表

我们使用JOIN将user_client_log和product_info表连接起来：

from
    user_client_log u
    join product_info p on u.product_id = p.product_id

• JOIN product_info p ON u.product_id = p.product_id：通过商品ID连接两个表，以便获取每个商品的名称。

步骤2：筛选记录

我们使用WHERE子句筛选出商品名称为“anta”且步骤为“select”的记录：

where
    p.product_name = 'anta' and step = 'select'

• WHERE p.product_name = 'anta' AND step = 'select'：筛选出符合条件的记录。

步骤3：处理脏数据

我们使用CASE WHEN语句将支付方式为空的记录标记为“error”：

select 
    case when u.pay_method = '' then 'error' else u.pay_method end as pay_method

• CASE WHEN u.pay_method = '' THEN 'error' ELSE u.pay_method END AS pay_method：若pay_method是空字符串，则标记为NULL，否则依然使用pay_method原值。

步骤4：统计支付方式使用次数

我们使用COUNT函数对每个支付方式的使用次数进行计数，并按支付方式分组：

count(*) as num
group by 
    u.pay_method

• COUNT(*) AS num：计算每个支付方式的使用次数。
• GROUP BY u.pay_method：按支付方式分组。

步骤5：排序输出

我们使用ORDER BY按使用次数降序排列输出结果：

order by
    num desc

完整代码

select 
    case when u.pay_method = '' then 'error' else u.pay_method end as pay_method,
    count(*) as num
from
    user_client_log u
    join product_info p on u.product_id = p.product_id
where
    p.product_name = 'anta' and step = 'select'
group by 
    u.pay_method
order by
    num desc;