拼多多2018秋季数据分析笔试题

订单表结构如下：

+------+------------+
| id   | order_date |
+------+------------+
|    1 | 2018-09-15 |
|    1 | 2018-09-10 |
|    1 | 2018-09-01 |
|    2 | 2018-09-08 |
|    2 | 2018-09-05 |
|    2 | 2018-08-25 |
+------+------------+

题目：输出每个id，时间间隔最大的两个order_date

• 每个id，按订单时间排序

mysql> select *,row_number() over(partition by id order by order_date desc) as row_num from test;
+------+------------+---------+
| id   | order_date | row_num |
+------+------------+---------+
|    1 | 2018-09-15 |       1 |
|    1 | 2018-09-10 |       2 |
|    1 | 2018-09-01 |       3 |
|    2 | 2018-09-08 |       1 |
|    2 | 2018-09-05 |       2 |
|    2 | 2018-08-25 |       3 |
+------+------------+---------+
6 rows in set (0.00 sec)

• 订单时间错位

select a.id,a.order_date,
lag(a.order_date,1) over(order by id) as `lag`
from
(select *,
 row_number() over(partition by id order by order_date desc) as row_num 
 from test)a;
 
 +------+------------+------------+
| id   | order_date | lag        |
+------+------------+------------+
|    1 | 2018-09-15 | NULL       |
|    1 | 2018-09-10 | 2018-09-15 |
|    1 | 2018-09-01 | 2018-09-10 |
|    2 | 2018-09-08 | 2018-09-01 |
|    2 | 2018-09-05 | 2018-09-08 |
|    2 | 2018-08-25 | 2018-09-05 |
+------+------------+------------+

• 相邻订单时间相减

select b.id,b.order_date,b.lag,datediff(b.order_date,b.lag)as diff
from
(
  select a.id,a.order_date,
lag(a.order_date,1) over(order by id) as `lag`
from
(select *,
 row_number() over(partition by id order by order_date desc) as row_num 
 from test)a
)b;

+------+------------+------------+------+
| id   | order_date | lag        | diff |
+------+------------+------------+------+
|    1 | 2018-09-15 | NULL       | NULL |
|    1 | 2018-09-10 | 2018-09-15 |   -5 |
|    1 | 2018-09-01 | 2018-09-10 |   -9 |
|    2 | 2018-09-08 | 2018-09-01 |    7 |
|    2 | 2018-09-05 | 2018-09-08 |   -3 |
|    2 | 2018-08-25 | 2018-09-05 |  -11 |
+------+------------+------------+------+

• 取最大值

select c.id,c.order_date,c.lag,abs(c.diff) as abs_diff
from
(
select b.id,b.order_date,b.lag,datediff(b.order_date,b.lag)as diff
from
(
  select a.id,a.order_date,
lag(a.order_date,1) over(order by id) as `lag`
from
(select *,
 row_number() over(partition by id order by order_date desc) as row_num 
 from test)a
)b
  )c;
  
+------+------------+------------+----------+
| id   | order_date | lag        | abs_diff |
+------+------------+------------+----------+
|    1 | 2018-09-15 | NULL       |     NULL |
|    1 | 2018-09-10 | 2018-09-15 |        5 |
|    1 | 2018-09-01 | 2018-09-10 |        9 |
|    2 | 2018-09-08 | 2018-09-01 |        7 |
|    2 | 2018-09-05 | 2018-09-08 |        3 |
|    2 | 2018-08-25 | 2018-09-05 |       11 |
+------+------------+------------+----------+

• 取出符合要求的id

select d.id,d.order_date,d.lag,d.abs_diff,
max(abs_diff) over(partition by id)
from
(
select c.id,c.order_date,c.lag,abs(c.diff) as abs_diff
from
(
select b.id,b.order_date,b.lag,datediff(b.order_date,b.lag)as diff
from
(
  select a.id,a.order_date,
lag(a.order_date,1) over(order by id) as `lag`
from
(select *,
 row_number() over(partition by id order by order_date desc) as row_num 
 from test)a
)b
  )c
  )d
group by d.id,d.order_date,d.lag

参考资料

拼多多数据分析岗笔试一些可能用到的知识点