当前位置：网站首页>SQL trill interview: send you a universal template, to?(key, each user to log on to the maximum number of consecutive monthly)

SQL trill interview: send you a universal template, to?(key, each user to log on to the maximum number of consecutive monthly)

2022-08-09 18:20:00 【51CTO】

【面试题】

有一张“用户登陆记录表”,包含两个字段：用户id、日期.

SQL抖音面试题：Send you a universal template,要吗？(重点、The maximum number of consecutive login days per user per month)_窗口函数

SQL抖音面试题：Send you a universal template,要吗？(重点、The maximum number of consecutive login days per user per month)_默认值_02

【问题】查询2021年每个月,The maximum number of consecutive days each user can log in.

【解题步骤】

1. A universal template for running questions

在《拼多多面试题：如何找出连续出现N次的内容？》Said to meet“连续问题”如何解决,And sent a universal template,模板使用的是窗口函数解决连续问题.

       
select distinct 列1
       
from (
       
select 列1,
       
lead(列1,1) over(order by 序号) as 列2,
       
lead(列1,2) over(order by 序号) as 列3,
       
...
       
lead(列1,n-1) over(order by 列) as 列n,
       
from 表名
       
) as a
       
where (a.列1 = a.列2 and ... and a.列1 = a.列n);
      
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

SQL抖音面试题：Send you a universal template,要吗？(重点、The maximum number of consecutive login days per user per month)_默认值_03

2. 窗口函数

窗口函数lead使用方法：

       
lead(字段名,N,默认值) over(partion by …order by …)
      
1.
2.

SQL抖音面试题：Send you a universal template,要吗？(重点、The maximum number of consecutive login days per user per month)_默认值_04

默认值是指：当向上Nline or downNrow value,If it has exceeded the range of table rows and columns,This default value will be used as the return value of the function,若没有指定默认值,则返回Null.

窗口函数leadYou can get the last number of each fieldn个值,并生成新的一列.

And this question describes“The user logs in continuously”中的“连续”It can be understood that the user's current login date is one day away from the next login date of this month.

We can use window functions firstlead获取“The user's next login date in the current month”：

       
select 用户id,
       
       month(日期) as 月,
       
       日期,
       
       lead(日期,1,'Last login date of the month') over(partition by month(日期), 用户id order by 日期) as The user's next login date in the current month
       
from 用户登陆记录表;
      
1.
2.
3.
4.
5.
6.

SQL抖音面试题：Send you a universal template,要吗？(重点、The maximum number of consecutive login days per user per month)_窗口函数_05

SQL抖音面试题：Send you a universal template,要吗？(重点、The maximum number of consecutive login days per user per month)_用户登陆_06

SQL抖音面试题：Send you a universal template,要吗？(重点、The maximum number of consecutive login days per user per month)_窗口函数_07

当“日期”is when the user logged in on the last day of the month,记录为“Last login date of the month”,如果不进行设置,将会返回Null,不利于理解.

从结果看,我们可以获得以下信息：

1）当“日期”与“The user's next login date in the current month”Only one day away,That is, the user's current login is a continuous login;

2）当“日期”与“The user's next login date in the current month”The difference is more than one day,That is, the user's current login is the last day of continuous login（It is also possible to log in for only one day）;

3）当“The user's next login date in the current month”等于“Last login date of the month”,That is, the user's login is the last day of the month.

这样,It is possible to judge the continuous login of the user.

Next, we will solve the calculation of the number of consecutive login days for each user.

3. 子查询

SQL抖音面试题：Send you a universal template,要吗？(重点、The maximum number of consecutive login days per user per month)_默认值_08

SQL抖音面试题：Send you a universal template,要吗？(重点、The maximum number of consecutive login days per user per month)_用户登陆_09

There is a certain relationship between the number of consecutive login days of each user and the user login sequence,At this point, we can use a subquery to query the reading order of users in this month,使用窗口函数row_number:

       
       select *,
       
       lead(日期,1,'Last login date of the month') over(partition by 月, 用户id order by Monthly login order) as The user's next login date in the current month
       
from (
       
select 用户id,
       
       month(日期) as 月,
       
       日期,
       
       row_number() over (partition by month(日期), 用户id order by 日期) as Monthly login order
       
from 用户登陆记录表
       
) as t1;
      
1.
2.
3.
4.
5.
6.
7.
8.
9.

SQL抖音面试题：Send you a universal template,要吗？(重点、The maximum number of consecutive login days per user per month)_窗口函数_10

SQL抖音面试题：Send you a universal template,要吗？(重点、The maximum number of consecutive login days per user per month)_窗口函数_11

SQL抖音面试题：Send you a universal template,要吗？(重点、The maximum number of consecutive login days per user per month)_窗口函数_12

可以看出,when the continuation is terminated,即：

1）“日期”与“The user's next login date in the current month”The difference is more than one day;

2）“The user's next login date in the current month”等于“Last login date of the month”;

两种情况.

After filtering these two cases out,The number of consecutive days a user has logged in is ：The current login sequence minus the previous login sequence.

       
select *,
       
       lag(Monthly login order,1) over(partition by 月, 用户id order by Monthly login order) as The previous login sequence
       
from (
       
select *,
       
       lead(日期,1,'Last login date of the month') over(partition by 月, 用户id order by Monthly login order) as The user's next login date in the current month
       
from (
       
select 用户id,
       
       month(日期) as 月,
       
       日期,
       
       row_number() over (partition by month(日期), 用户id order by 日期) as Monthly login order
       
from 用户登陆记录表
       
) as t1
       
) as t2
       
where date_sub(The user's next login date in the current month,interval 1 day) <> 日期 or The user's next login date in the current month = 'Last login date of the month';
      
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

SQL抖音面试题：Send you a universal template,要吗？(重点、The maximum number of consecutive login days per user per month)_用户登陆_13

SQL抖音面试题：Send you a universal template,要吗？(重点、The maximum number of consecutive login days per user per month)_窗口函数_14

SQL抖音面试题：Send you a universal template,要吗？(重点、The maximum number of consecutive login days per user per month)_用户登陆_15

“The previous login sequence”为Null时,用0代替（使用coalesce函数）,那么“Monthly login order”减去“The previous login sequence”This is the number of consecutive login days.

4. 汇总分析

最后获取“每个月,The maximum number of consecutive days each user can log in”,使用group by函数.

       
select 月,
       
       用户id,
       
       max(连续登陆天数) as The maximum number of consecutive login days
       
from (
       
select *,
       
       Monthly login order - coalesce(lag(Monthly login order,1) over(partition by 月, 用户id order by Monthly login order),0) as 连续登陆天数
       
from (
       
select *,
       
       lead(日期,1,'Last login date of the month') over(partition by 月, 用户id order by Monthly login order) as The user's next login date in the current month
       
from (
       
select 用户id,
       
       month(日期) as 月,
       
       日期,
       
       row_number() over (partition by month(日期), 用户id order by 日期) as Monthly login order
       
from 用户登陆记录表
       
) as t1
       
) as t2
       
where date_sub(The user's next login date in the current month,interval 1 day) <> 日期 or The user's next login date in the current month = 'Last login date of the month'
       
) as t3
       
group by 月,用户id;
      
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.

SQL抖音面试题：Send you a universal template,要吗？(重点、The maximum number of consecutive login days per user per month)_用户登陆_16

SQL抖音面试题：Send you a universal template,要吗？(重点、The maximum number of consecutive login days per user per month)_窗口函数_17

SQL抖音面试题：Send you a universal template,要吗？(重点、The maximum number of consecutive login days per user per month)_窗口函数_18

【本题考点】

1.考查对窗口函数的了解,要把《猴子从零学会SQL》The window function mentioned in the above can be solved4Class interview questions to remember;

2.Examine your understanding of subqueries;

3.Examine your knowledge of sequential problems,Universal templates can be applied.

原网站

版权声明
本文为[51CTO]所创，转载请带上原文链接，感谢
https://yzsam.com/2022/221/202208091500389463.html

当前位置：网站首页>SQL trill interview: send you a universal template, to?(key, each user to log on to the maximum number of consecutive monthly)

SQL trill interview: send you a universal template, to?(key, each user to log on to the maximum number of consecutive monthly)

边栏推荐

猜你喜欢

随机推荐