数据分析SQL面试题目9套-第1题（笔记）

题目来源：https://www.jianshu.com/p/0f165dcf9525

1 题目

建表语句：

create table datafrog_test1(
    userid varchar(20),
    changjing varchar(20),
    inttime varchar(20)
);

insert into datafrog_test1 values
(1,1001,1400),
(2,1002,1401),
(1,1002,1402),
(1,1001,1402),
(2,1003,1403),
(2,1004,1404),
(3,1003,1400),
(4,1004,1402),
(4,1003,1403),
(4,1001,1403),
(4,1002,1404),
(5,1002,1402),
(5,1002,1403),
(5,1001,1404),
(5,1003,1405);

2 解题

1）错误写法

# 错误写法，对于userid=5的场景有问题(因为userid=5的前两个changjing是重复的)
select concat_ws('-', userid, group_concat(changjing order by inttime separator '-'))
from(select userid,
            changjing,
            inttime,
            row_number() over (partition by userid order by inttime) as rn
    from datafrog_test1) t
where rn<=2
group by userid;

输出结果：

2）正确写法

# 正确写法
select concat_ws('-', userid, group_concat(changjing order by inttime separator '-'))
from(
    select userid,
            changjing,
            inttime,
            row_number() over (partition by userid order by inttime) as rn
    from(
        select userid,
               changjing,
               min(inttime) inttime
        from datafrog_test1
        group by userid, changjing
            )t2) t
where rn<=2
group by userid;

输出结果：

3 思路

1）由题目“求用户号对应的前两个不同场景”可知，需要分组排序获取每组的前两个场景，于是可以联想到窗口函数-序号函数
2）由输出结果“1-1001-1002”的形式可知，需要用到concat系列函数拼接不同字段

4 需要注意的点

单纯地在原表datafrog_test1分组排序取每组的前两个场景是有问题的，因为userid=5的时候，前两个场景是一样的（场景重复，需要先去重），可见解题中的1）错误写法。
需要先根据userid和changjing分组，取最小的inttime（即取最早的时间）

5 涉及到的知识点

1）窗口函数-序号函数（mysql 8.0）
2）concat_ws & group_concat

6 涉及到的知识点可参考的博客

MySQL操作实战（二）：窗口函数
浅析MySQL中concat以及group_concat的使用