数据蛙9套SQL面试题笔记

数据分析SQL面试题目9套汇总

• 题目来源：

https://www.jianshu.com/p/0f165dcf9525

关于这套题的笔记

一、

解题思路：

• 1、用concat实现连接 2、还需要按照“用户号”分组，将每组中的前两个“场景”号间接，这时需要用到 GROUP_CONCAT
  函数（注意必须和GROUP BY 语句合用），即 GROUP_CONCAT(changjing SEPARATOR’-’) …
  GROPY BY userid

  3、对相同“用户号”、相同“场景”取最小的访问时间
  4、整体思路：首先按照“用户号”和“场景”分组，取最小访问时间，然后，以”用户号“为组，按照“用户号”、“场景”和“访问时间”排序，序号为新的一列；将以上查询命名为新表；用concat和GROUP_CONCAT 实现连接，注意要求新的序号列小于2.

注意：排序的过程可以用窗口函数来做，mysql8.0以上版本才支持窗口函数

##利用窗口函数(mysql8以上版本)
SELECT CONCAT(t.userid,'-',GROUP_CONCAT(t.changjing SEPARATOR'-')) AS result
FROM(
SELECT userid,changjing,inttime,
	row_number() over(PARTITION BY userid ORDER BY userid,changjing,inttime) AS new_rank
FROM(
	SELECT userid,changjing, MIN(inttime) inttime FROM datafrog_test1 GROUP BY userid,changjing
) tem

) t
WHERE t.new_rank<=2
GROUP BY t.userid;

##不利用窗口函数
SELECT CONCAT(t.userid,'-',GROUP_CONCAT(t.changjing SEPARATOR'-')) AS result
FROM(
 SELECT userid,changjing,inttime,
     IF(@tmp=userid,@rank:=@rank+1,@rank:=1) AS new_rank,
     @tmp:=userid AS tmp
 FROM (SELECT userid,changjing, MIN(inttime) inttime FROM datafrog_test1 GROUP BY userid,changjing)temp
 ORDER BY userid,changjing
    )t
WHERE t.new_rank<=2
GROUP BY t.userid;

三个排序窗口函数：

• RANK()：计算排序（如果存在相同位次的记录，则会跳过之后的位次，比如：1,2,2,4）
• DENSE_RANK()：计算排序（即使存在相同位次的记录，也不会跳过之后的位次，比如：1,2,2,3）
• ROW_NUMBER()：赋予连续且唯一的位次，比如：1,2,3,4

二、

• mysql 导入中文数据乱码
  导入语句：

LOAD DATA INFILE 'D:/camera.csv' 
INTO TABLE userinfo
FIELDS TERMINATED BY ','
IGNORE  1 LINES;

这里要把原来的默认路径改为空；
先用

show variables like '%secure%';

找到my文件的路径，然乎按记事本打开my,ini文件，把原路径改为空
即，secure-file-priv=""

首先：

set names gbk;

然后导入还是会报错，百度一下，说是，需要建立gbk编码的库，和gbk编码的表
重新建库建表的语句：

CREATE DATABASE xiangji CHARACTER SET GBK COLLATE gbk_chinese_ci;
USE xiangji;
CREATE TABLE userinfo(
uid VARCHAR(10),
app_name VARCHAR(20) CHARACTER SET GBK COLLATE gbk_chinese_ci,
duration INT(10),
times INT(10),
dayno VARCHAR(30)
);

现在导入不报错了，但是中文字体是乱码，然后就继续百度：
又一次找到my.ini这个文件，打开它，改两个地方;

图源：https://jingyan.baidu.com/article /4ae03de3ebb0d83eff9e6b16.html

改完了重启mysql（计算机管理），汉字就不是乱码了。
以上，over。回归正题,原文解释很清晰，这里就不介绍了。

SELECT day1,COUNT(DISTINCT a.uid) AS 活跃用户数,
COUNT(DISTINCT CASE WHEN day2-day1=1 THEN a.uid END) AS 次日留存用户数,
COUNT(DISTINCT CASE WHEN day2-day1=3 THEN a.uid END) AS 三日留存用户数,
COUNT(DISTINCT CASE WHEN day2-day1=7 THEN a.uid END) AS 七日留存用户数,
CONCAT(ROUND(COUNT(DISTINCT CASE WHEN day2-day1=1 THEN a.uid END)/COUNT(DISTINCT a.uid)*100,2),'%') AS 次日留存率,
CONCAT(ROUND(COUNT(DISTINCT CASE WHEN day2-day1=3 THEN a.uid END)/COUNT(DISTINCT a.uid)*100,2),'%') AS 三日留存率,
CONCAT(ROUND(COUNT(DISTINCT CASE WHEN day2<