SQL大神带你飞 | 24春招京东SQL真题解析-商品id数据清洗统计

最近做了京东的24年春招题，题目如下：

题目分析

表结构：

• order_log 表：包含以下字段：
  • order_id：订单号，格式为order_id_product_id，其中product_id以p开头。
  • uid：用户ID。
  • logtime：日志采集时间。
  • time：客户端记录时间。
  • pay_method：支付方式。

目标：

• 找出订单号中出现次数最多的product_id。

输出要求：

• 返回product_id和其出现的次数。
• 按出现次数降序排列，取出现次数最多的一个product_id。

知识点关键词

• 字符串处理：SUBSTRING_INDEX
• 分组统计：GROUP BY
• 排序：ORDER BY
• 限制结果集：LIMIT

解答步骤

1. 提取product_id

我们使用SUBSTRING_INDEX函数从order_id中提取product_id。order_id的格式为order_id_product_id，我们需要提取最后一部分。

select 
    substring_index(order_id, '_', -1) as product_id

• SUBSTRING_INDEX(order_id, '_', -1) AS product_id：提取order_id中最后一个_之后的部分作为product_id。

除此之外，我们也可以用REGEXP_SUBSTR函数从order_id中提取product_id，REGEXP_SUBSTR 是 MySQL 8.0+ 中引入的一个函数，用于根据正则表达式提取字符串中的特定部分。 语法： REGEXP_SUBSTR(str, pattern, position, occurrence, match_type)

• str：要进行匹配的字符串。
• pattern：正则表达式模式。
• position：可选，指定从哪个位置开始匹配，默认为 1。
• occurrence：可选，指定匹配的第几次出现，默认为 1。
• match_type：可选，指定匹配类型，如 'c' 表示区分大小写，'i' 表示不区分大小写。

2. 统计每个product_id的出现次数

我们使用COUNT函数统计每个product_id的出现次数，并通过GROUP BY子句按product_id分组。

count(*) as cnt
from order_log
group by product_id

• COUNT(*) AS cnt：统计每个product_id的出现次数。
• GROUP BY product_id：按product_id分组。

3. 排序并限制输出

我们按出现次数降序排列，并限制结果集为1条记录，以获取出现次数最多的product_id。

order by cnt desc
limit 1

• ORDER BY cnt DESC：按出现次数降序排列。
• LIMIT 1：限制结果集为1条记录。

完整代码

select 
    #regexp_substr(order_id,'p[0-9]+$') as product_id,#(方法一)
    substring_index(order_id,'_',-1) as product_id,#（方法二）
    count(*) as cnt
from order_log
group by product_id
order by cnt desc
limit 1;

近似题目推荐

1. 获取employees中的first_name
   • 知识点：字符串处理、分组统计、排序