面试速通-SQL编码题

标签：编码速通 ip cost user SQL 数据 id select

〇、前话

SQL题是数据相关的面试无可避免的小BOSS，虽然不同公司面试出题会披上不同的马甲，侧重各异，但总结起来题型就是那4、5种。下面让我们花上几分钟一起来通关它吧！

一、连续日期计算

1，例题

表字段：用户ID，账户余额，系统余额日期。求出余额大于1000的用户的最大连续天数。

2，解题要点

关键要点是找到能标识连续的字段标记。其次要区分出来每个用户要满足的条件。

3，题解

1）过滤余额大于1000的用户的记录。2）按用户ID开窗，按日期进行升序排序，并生成排序序数。3）使用系统余额日期减去1970-01-01获得日期间隔，即生成自然排序序数。4）使用自然序数减去排序序数，生成自然连续的标记。自然序数减去排序序数间隔相同的记录集合，该部分记录是连续的，否则不连续。5）再按题目要求进行统计最大值的聚合计算。

4，真题回顾

二、开窗函数的妙用

1，例题

有效值追溯问题：现在有一张商品入库表，包括商品id、商品成本和入库日期3个字段，由于某些原因，导致部分商品的成本缺失（为0或者没有值都是缺失），这样不利于我们计算成本。所以现在要把缺失的商品进价补充完整，补充的依据是使用相同商品的最近一次有效成本作为当前商品的成本。比如2023-11-04号101商品的cost就需要用300.39填充。

2，解题要点

题目中「相同商品的最近一次有效成本」这个问题具象起来就是按时间排序之后每个商品价格记录的上一条记录，并找到合适的方法取出放到本条商品价格记录即可。

lag() over()开窗函数就正好适合解决这个问题。返回窗口内上一行数据中的对应字段值。参考：https://spark.apache.org/docs/latest/api/sql/#lag附：开窗函数类的题目属于见过就会做的类型。常用的聚合、排序开窗都要滚瓜烂熟。

例如：(1) sum() over() ,要注意开窗中加order by和不加order by的区别；(2)lag() over()；(3)last_value() over() ；(4)row_number() over() ，要注意rank\dense_rank\row_number三者的区别。

3，题解

select product_id
,date
,last_value(cost,true) over(partition by paroduct_id order by date asc) as cost
from (
select product_id
,date
,if(cost=0 or cost is null,null,cost) as cost 
from product_table 
)

4，真题回顾

三、简单Join&过滤聚合

1，例题

问题：现在有一张用户登陆日志表，该表包括user_id,ip,log_time三个字段，现在需要找出共同使用ip数量超过3个(含)的所有用户对。比如下面的示例数据，101和102用户共同使用的ip为4个，101和103用户共同使用的ip为3个，102和103用户共同使用的ip为3个。

2，解题要点

按IP关联join就能找到使用相同ip的用户。但要注意谓词下推，提高处理效率。

3，题解

select t1.user_id,t2.user_id,count(*)
from (
select user_id,ip  from(select distinct user_id,ip from tmp)
)t1
full outer join (
select user_id,ip  from(select distinct user_id,ip from tmp)
)t2
on t1.user_id>t2.user_id and t1.ip=t2.ip
group by t1.user_id,t2.user_id having count(*)>=3

4，真题回顾

四、启闭标记统计

1，例题

如下为某电商公司用户访问网站的数据，包括用户id和访问时间两个字段。现有如下规则：如果某个用户的连续的访问记录时间间隔小于60秒，则属于同一个会话，现在需要计算每个用户有多少个会话。比如A用户在第1秒，60秒，200秒，230秒有四次访问记录，则该用户有2个会话，其中第一个会话是第1秒和第60秒的记录，第二个会话是第200秒和230秒的记录。

2，解题要点

如何用数据具象来标识一次会话是关键。参考下面的flag，每个用户按时间戳顺排之后，上次会话记录和本次会话记录如果在60秒内标记为0，否则标记为1。作为一次会话的开启和关闭标记。再将flag进行逐行累加，这时候就能得出总共有多少次会话了。

3，题解

select product_id
,date
,last_value(cost,true) over(partition by paroduct_id order by date asc) as cost
from (
select product_id
,date
,if(cost=0 or cost is null,null,cost) as cost 
from product_table 
)

4，真题回顾

五、互相关注的人

1，例题

现在有一张relation表，里面只有两个字段：from_user和to_user，代表关注关系从from指向to，即from_user关注了to_user。现在要找出互相关注的所有人。

2，解题要点

其实就是找（from_user=x，to_user=y）和（from_user=y，to_user=x）同时存在的人。暴力解法是直接用relation表自关联，使用左表的to_user和右表的from_user进行left join。右表记录非空的则是存在互相关注的人。优化解是建立relation表的copy表并交换from_user和to_user字段位置。再union之后，统计相同记录行数，大于2的为相互关注的人。

3，题解

with relation as
(
select "乔峰" as from_user,"段誉" as to_user
  union all
select "乔峰" as from_user,"虚竹" as to_user
  union all
select "虚竹" as from_user,"乔峰" as to_user
  union all
select "徐风年" as from_user,"徐骁" as to_user
  union all
select "徐骁" as from_user,"徐风年" as to_user
)
select from_user,to_user,count(*)
from (
select from_user,to_user from tmp 
union all 
select from_user as to_user ,to_user as from_user 
from tmp 
)group by from_user,to_user having count(*) > 1

4，真题回顾

无，但这种题在社区类业务岗位的面试中经常遇到。

六、SQL拓展问题

结合SQL探究SparkSQL、HiveSQL知识点

1，例题

表字段如下user_id bigintTimestamp bigint tag_id string 取每个用户最早和最晚的tag_id和对应的时间戳。因为表数据量级非常大！注意要使用效率最高的方式。

2，解题要点

1）不使用row_number()而使用max,min。因为max,min可以局部排序，而row_number是全局排序的。2）bigint和string可以相加，向下兼容合并。user_id和tag_id可以合并为1个字段，避免多次的sort join。3）分组的逻辑，对user_id中的热点值进行处理，避免倾斜。对于user_id中的热点值可以单独处理完成之后再union在结果中。

3，题解

Select 
user_id
,tag_id
  ,casewhenTimestamp=max_ts then ‘max’
when Timestamp=min_ts then ‘min’
  endastype
from(Select 
user_id
,tag_id
  ,Timestamp
max(Timestamp) over(partition by user_id+tag_id) as max_ts,
min(Timestamp) over(partition by user_id+tag_id) as min_ts
  Fromtable 
)whereTimestamp=max_ts orTimestamp=min_ts

4，真题回顾