SQL面试题

发表于 | 分类于

关于SQL的几个面试题或练习题，在解题的时候，同时考虑用pandas来解，所以做个总结。

题目一

题目描述

用户日志表log，有用户编号cid，时间dt两个字段，查找每个用户成为新增用户的时间

HiveSQL解

思路：

  1. 对每个用户的出现时间进行排名
  2. 从排名中挑选出每个用户的排名为1的时间，即首次出现时间，也就是成为新增用户的时间
1
2
3
4
5
6
7
8
select a.cid,
        a.dt
from
    (select cid,
            dt,
            row_number() over(partition by cid order by dt) as rank
    from log)a
where a.rank=1;

Python解

现在需要新增加一列is_new，判断当天该用户是否是新用户，若是，则对应is_new的值设为1，否则设为0。

思路：

  1. 统计每个用户出现的最早时间（成为新用户时间），并用dict(zip())构造一个 用户ID：最早时间 字典
  2. 将原df_log每个cid的dt与字典的dt比较，若相同就把相应行的is_new设为1，否则为0

具体实现：

  1. 构造log数据框
1
2
3
cid=[1,2,2,3,4,3,5]
dt=['2019-08-01','2019-08-01','2019-08-02','2019-08-02','2019-08-03','2019-08-04','2019-08-01']
df_log=pd.DataFrame({'cid':cid,'dt':dt})

log数据框：
log

  1. 统计每个用户成为新增用户时间，按cid分组，求最早时间
1
minTime = df_log.groupby('cid').dt.min().tolist()
  1. 统计独立用户
1
cid = set(df_log['cid'].tolist())
  1. 构造字典：用户: 成为新增用户的时间
1
cid_mintime_dict = dict(zip(cid,minTime))
  1. 将df_log每行的dt与字典的dt比较，若相同，就把新增的is_new列设置为1，否则为0
1
2
3
4
5
6
7
8
def match(df):
    if df['dt']==cid_mintime_dict[df['cid']]:
        return 1
    else:
        return 0

df_log['is_new']=df_log.apply(lambda x:match(x),axis=1)
df_log

结果：
log_result

题目二

题目描述

有两张表，t1和t2；t1有用户编号uid和页面浏览量pageviews两个字段；t2有用户编号uid和用户所在组别groupid两个字段；查询每组用户的总阅读量
两张表为：
t1 t2

HiveSQL 解

说明这是列转行问题，用函数lateral view explode拆分字段的多个值。

思路：

  1. 先把t2表的groupid拆分开，将得到的结果重命名
  2. 以新表为主，左连接t1表，对groupid分组，统计每组pageviews的和
1
2
3
4
5
6
7
8
select b.groupid,
        sum(b.pageviews) as total_pageviews
from
    ((select t2.uid as uid,t3.groupid as groupid
        from t2
        lateral view explode(groupid) t3 as groupid)a
    left join t1 on a.uid=t1.uid)b
group by b.groupid;

Python 解

思路：

  1. 先将groupid列按属性值拆分成多行
  2. 合并两张表，对合并后的表按照groupid进行分组，统计pageviews的和

具体实现：

** 说明：data_t1 表示 t1 的数据框， data_t2 表示 t2 的数据框 **

  1. 先拆分groupid，并重命名为group
1
newGroup = data_t2['groupid'].str.split(',', expand=True).stack().reset_index(level=1,drop=True).rename('group')

newGroup的结果为：
1

  1. 把newGroup合并到data_t2上，得到新的data_t2
1
2
data_t2 = data_t2.drop('groupid', axis=1).join(newGroup)
data_t2.head()

结果为：
2

  1. 合并新的data_t2和data_t1，以data_t2为主
1
2
df_combined = pd.merge(data_t1,data_t2,on='uid',how='right')
df_combined.head()

结果为：
3

  1. 按照group分组，统计总浏览量
1
df_combined.groupby('group')['pageviews'].sum()

最终结果：
4

题目三

题目描述

浏览量表V，有用户编号cid，时间dt，浏览量visits字段；
选出连续3天或以上浏览量大于10的用户。

HiveSQL解

巧妙利用排序，把日期进行从小到大排序，用日期(天)本身减去日期的排名，若日期所在行的差值相同说明这些日期是连续的。

思路：

  1. 子查询从浏览量大于10的数据中选出用户编号cid、日期(天)与日期排名差值diff
  2. 外层查询选择cid，按照用户编号cid和diff分组，having过滤出计数大于等于3的
1
2
3
4
5
6
7
8
9
10
11
12
select b.cid
from 
        (select a.cid,
                a.diff,
                count(*) as num
        from
                (select cid,
                        date_sub(dt,row_number() over(partition by a.cid order by dt)) as diff
                from V
                where visits>10)a
        group by a.cid,a.diff)b
where b.num>=3;

Python解

  • 具体实现：
  1. 构造数据
1
2
3
4
5
id=[1,2,3,4,5,6,7,8,9,10]
dt =['2019-08-15','2019-08-16','2019-08-17','2019-08-18','2019-08-19','2019-08-20','2019-08-21','2019-08-22','2019-08-23','2019-08-24']
visits =[11,12,20,8,7,10,17,18,6,12]
df =pd.DataFrame ({'id':id,'dt':dt,'visits':visits})
df

V表数据框：
V

  1. 选出连续3天及以上visits大于等于10的那部分日期
1
2
3
4
5
6
7
8
9
10
11
12
13
i =0
while i<len(df):
    count =0
    while (i+count)<len(df):
        if df['visits'][i+count]>=10:
             count +=1
        else:
            break 
    if count >2:
        print(df['dt'][i:i+count])
        i +=count 
    else:
        i +=1

输出结果：
V-result

-------------完-------------