最近面试太难了。

傷城~ 2023-09-29 15:32 15阅读 0赞

![ac84b714694c590f5ec9b01330882f8b.gif][]

在面试数据分析师时，往往会考察一下SQL的掌握程度。

最近有位同学面试了几家，分享了一些觉得**有些难度的SQL面试题**：比如会让你用SQL实现行转列和列转行操作、用SQL计算留存、用SQL计算中位数、还有**如何统计用户最大连续登录天数？**

当然这种题变形也很多，连续打卡天数、连续学习天数，连续点击天数等等都是同一个类型，今天我们将会给大家分享SQL和Pandas的多种做法。让大家一次搞懂，**下次面试不难**!

作者简介

小小明，数据、Python爱好者，CSDN博客专家。个人博客地址：*https://blog.csdn.net/as604049322*

![e9e6a572067b23409ac4b815f0829922.png][]

计算每一个用户的最大连续登录天数，由左变换到右边。

## SQL 8.0窗口函数 ##

实现思路：

1.  对用户ID和登录日期去重
2.  对每个用户ID按照日期顺序进行编号
3.  将登录日期减去编号对应的天数，使连续的日期转换为同一天

将连续日期转换为同一个日期之后就可以按照这个字段分组，后面就简单了。下面我们一步步看：

对用户ID和登录日期**去重**：

SELECT?DISTINCT?role_id,$part_date?`date`?FROM?role_login;

对每个用户ID按照日期顺序进行编号，并将登录日期减去该编号对应的天数（可以一步到位）：

SELECT
    ?role_id,`date`,
    ?DATE_SUB(`date`,INTERVAL?(row_number()?OVER(PARTITION?BY?role_id?ORDER?BY?`date`))?DAY)?data_group
    FROM(
    ?SELECT?DISTINCT?role_id,$part_date?`date`?FROM?role_login
    )?a;

![957d5ef632b89cc31e47ffd666549c88.png][]

从结果我们可以看到已经成功的使连续的日期都转换到同一天。

然后我们就可以基于该结果统计每个用户的所有连续日期段：

SELECT
    ?role_id,
    ?MIN(DATE)?begin_date,
    ?MAX(DATE)?end_date,
    ?COUNT(*)?continuous_days
    FROM(
    ?SELECT
    ??role_id,`date`,
    ??DATE_SUB(`date`,INTERVAL?(row_number()?OVER(PARTITION?BY?role_id?ORDER?BY?`date`))?DAY)?data_group
    ?FROM(
    ??SELECT?DISTINCT?role_id,$part_date?`date`?FROM?role_login
    ?)?a
    )?b
    GROUP?BY?role_id,data_group;

结果：

![7ae214fcabe1c28ee291844a74b5ea87.png][]

然后通过窗口函数标注每个用户的连续日期排名：

SELECT
    ?role_id,begin_date,end_date,
    ?continuous_days?,
    ?row_number()?OVER?(PARTITION?BY?role_id?ORDER?BY?continuous_days?DESC)?rk
    FROM(
    ?SELECT
    ??role_id,
    ??MIN(DATE)?begin_date,
    ??MAX(DATE)?end_date,
    ??COUNT(*)?continuous_day

[ac84b714694c590f5ec9b01330882f8b.gif]: https://img-blog.csdnimg.cn/img_convert/ac84b714694c590f5ec9b01330882f8b.gif
[e9e6a572067b23409ac4b815f0829922.png]: https://img-blog.csdnimg.cn/img_convert/e9e6a572067b23409ac4b815f0829922.png
[957d5ef632b89cc31e47ffd666549c88.png]: https://img-blog.csdnimg.cn/img_convert/957d5ef632b89cc31e47ffd666549c88.png
[7ae214fcabe1c28ee291844a74b5ea87.png]: https://img-blog.csdnimg.cn/img_convert/7ae214fcabe1c28ee291844a74b5ea87.png