关键词:
大家好,我是只谈技术不剪发的 Tony 老师。
最近有个朋友分享了他参加了某互联网(直播带货)公司数据分析师的实习岗面试,以下是我对相关 SQL 笔试题的解析,使用的数据库是 MySQL 8.0。
如果觉得文章有用,欢迎评论📝、点赞👍、推荐🎁
第 1 题:直播间人气值
问题描述
直播开播记录表 t1 包含以下字段:
- 主播 id:author_id
- 直播间 id:live_id
- 开播时长:live_duration
直播观看记录表 t2 包含以下字段:
- 观众 id:user_id
- 直播间 id:live_id
- 观看时长:watching_duration
要求计算直播间的人气值,输出结果格式如下:
主播 id | 直播间 id | acu |
---|
其中,ACU 为平均同时在线人数(Average concurrent users),计算方式为:观众侧观看时长/某场直播的开播时长,没有人观看的时候显示为 0。
问题解析
首先,通过关联开播记录表 t1 和观看记录表 t2 可以得到计算 ACU 所需的信息,然后将所有观众的观看时长加起来,除以开播时长即可。
-- 创建示例表
CREATE TABLE t1 (author_id integer, live_id integer, live_duration integer);
INSERT INTO t1 VALUES (1, 1, 120), (2, 2, 180), (3, 3, 60);
CREATE TABLE t2 (user_id integer, live_id integer, watching_duration integer);
INSERT INTO t2 VALUES (1, 1, 30), (2, 1, 40), (3, 1, 50);
INSERT INTO t2 VALUES (3, 2, 30), (4, 2, 60);
-- 计算 ACU
SELECT t1.author_id, t1.live_id, sum(t2.watching_duration)/t1.live_duration AS acu
FROM t1
JOIN t2 ON (t2.live_id = t1.live_id)
GROUP BY t1.author_id, t1.live_id;
author_id|live_id|acu |
---------+-------+------+
1| 1|1.0000|
2| 2|0.5000|
以上查询使用到了内连接、GROUP BY 分组以及 SUM 聚合函数进行分组统计。
但是,以上查询还存在一个问题:直播间 3 没有观众,导致内连接查询没有返回结果。所以,我们还需要处理一下没有人观看的情况,方法就是使用左连接查询:
SELECT t1.author_id, t1.live_id, COALESCE(sum(t2.watching_duration), 0)/t1.live_duration AS acu
FROM t1
LEFT JOIN t2 ON (t2.live_id = t1.live_id)
GROUP BY t1.author_id, t1.live_id;
author_id|live_id|acu |
---------+-------+------+
1| 1|1.0000|
2| 2|0.5000|
3| 3|0.0000|
除了左连接之外,我们还使用了 COALESCE 函数,将空值转换为 0。当然也可以使用 IFNULL 函数。
第 2 题:累计销售金额
问题描述
销售记录表 t3 包含以下字段:
- 用户 id:user_id
- 销售日期:sell_day
- 销售金额:amount
要求计算每个用户首次销售日期后 30 天累计销售金额,输出结果格式如下:
用户 id | 首次销售日期 | 首次销售日期后 30 天累计销售金额 |
---|
问题解析
这个问题可以拆分为两个步骤,首先查找每个用户首次销售日期。这个可以通过 GROUP BY 基于用户 id 分组,然后使用聚合函数 MIN 返回最早的销售日期。实现的代码如下:
-- 创建示例表
CREATE TABLE t3 (user_id integer, sell_day date, amount numeric);
INSERT INTO t3 VALUES (1, '2021-01-01', 100), (1, '2021-01-02', 100), (1, '2021-01-29', 100),(1, '2021-02-01', 100);
INSERT INTO t3 VALUES (2, '2021-01-10', 200), (2, '2021-01-11', 200), (2, '2021-01-12', 200),(2, '2021-01-13', 200);
-- 查找每个用户的首次销售日期
SELECT user_id, min(sell_day) AS first_day
FROM t3
GROUP BY user_id;
user_id|first_day |
-------+----------+
1|2021-01-01|
2|2021-01-10|
然后,我们可以基于这些首次销售日期统计 30 天内的累计销售金额:
WITH s AS (
SELECT user_id, min(sell_day) AS first_day
FROM t3
GROUP BY user_id
)
SELECT s.user_id, s.first_day, sum(t3.amount) total_amount
FROM s
JOIN t3
ON (t3.user_id = s.user_id AND t3.sell_day BETWEEN s.first_day AND s.first_day + INTERVAL '29' DAY)
GROUP BY s.user_id, s.first_day;
user_id|first_day |total_amount|
-------+----------+------------+
1|2021-01-01| 300|
2|2021-01-10| 800|
以上查询中的 WITH 子句定义了一个通用表表达式,包含了每个用户的首次销售日期,然后和 t3 进行连接查询,返回了所需的数据。
对于这个问题,我们也可以使用窗口函数解决。例如:
SELECT *
FROM (SELECT user_id, sell_day, amount,
RANK() OVER (PARTITION BY user_id ORDER BY sell_day) AS rk,
first_value(amount) OVER (PARTITION BY user_id ORDER BY sell_day) AS fisrt_day,
sum(amount) OVER (PARTITION BY user_id ORDER BY sell_day RANGE BETWEEN CURRENT ROW AND INTERVAL '30' DAY FOLLOWING)
FROM t3) t
WHERE rk = 1;
第 3 题:渠道销量占比
问题描述
商家卖货记录表 t4 包含以下字段:
- 卖家 id:seller_id
- 买家 id:buyer_id
- 物品 id:item_id
- 销量:order_cnt
- 单价:price
- 商品渠道:source_type(1 表示自建商品,0 表示其他)
要求计算每个商家的自建商品销量占总销量的比例,输出信息的结构如下:
卖家 id | 自建商品销量占比 |
---|
问题解析
这个问题比较简单,只需要按照商家统计自建商品的销量和总销量,然后两者相除就可以了。例如:
-- 创建示例表
CREATE TABLE t4 (seller_id integer, buyer_id integer, item_id integer, order_cnt integer, price numeric, source_type tinyint);
INSERT INTO t4 VALUES (1, 11, 1, 5, 9.9, 1), (1, 12, 2, 15, 16.0, 0);
INSERT INTO t4 VALUES (2, 13, 3, 10, 89, 0), (2, 14, 4, 1, 2000, 0);
-- 每个商家自建商品销量占总销量的比例
SELECT seller_id,
sum(CASE source_type WHEN 1 THEN order_cnt ELSE 0 END)/sum(order_cnt) AS ratio
FROM t4
GROUP BY seller_id;
seller_id|ratio |
---------+------+
1|0.2500|
2|0.0000|
以上查询使用了两个 SUM 函数,第一个函数中包含了一个 CASE 表达式,它的作用就是统计自建商品的销量。第二个 SUM 函数的作用是统计所有商品的总销量。
第 4 题:畅销商品
问题描述
商家卖货记录表 t5 包含以下字段:
- 卖家 id:seller_id
- 买家 id:buyer_id
- 物品 id:item_id
- 物品数量:num
要求计算每个卖家销量最高的商品,输出信息包含以下内容:
卖家 id | 物品 id | 物品总销量 |
---|
问题解析
这是一类分组排名问题,需要按照卖家分组,计算销量最高的商品。这类问题使用窗口函数解决最方便,例如:
-- 创建示例表
CREATE TABLE t5 (seller_id int, buyer_id int, item_id int, num int);
INSERT INTO t5 VALUES (1, 11, 1, 100), (1, 12, 1, 200), (1, 12, 2, 300);
INSERT INTO t5 VALUES (2, 11, 1, 100), (2, 12, 3, 200);
-- 计算每个卖家销量最高的商品
WITH sales AS (
SELECT seller_id, item_id, sum(num) AS ss
FROM t5
GROUP BY seller_id, item_id
),
sales_rank AS (
SELECT seller_id, item_id, ss, rank() OVER (PARTITION BY seller_id ORDER BY ss DESC) AS rk
FROM sales)
SELECT *
FROM sales_rank
WHERE rk = 1;
seller_id|item_id|ss |rk|
---------+-------+---+--+
1| 1|300| 1|
1| 2|300| 1|
2| 3|200| 1|
首先,WITH 子句定义了两个通用表表达式。其中 sales 包含了每个卖家、每种物品的总销量,sales_rank 基于这个 sales 计算了同一个卖家的不同物品的销量排名。最后的查询语句返回了排名最高的商品。
卖家 1 的商品 1 和商品 2 的销量相同,因此返回了两条记录。
总结
对于数据分析岗而言,SQL 分组聚合、CASE 表达式、窗口函数的掌握是基本要求,随着 MySQL 8.0 的推出使得这一切变得更加简单。
网易笔试题——算法岗(代码片段)
目录1、大数相乘2、大数相加3、大数相减1、大数相乘publicstaticvoidbigNumberSimpleMulti(Stringf,Strings)System.out.print("乘法:"+f+"*"+s+"=");//获取首字符,判断是否是符号位,先处理符号位charsignA=f.charAt(0);charsignB=s.charAt(0);charsign=\'+\';if( 查看详情
数据挖掘2022年2023届秋招kanaries雾角科技算法岗笔试题(代码片段)
Kanaries雾角科技算法岗位笔试笔试时间:2022年10月13号时长:120分钟几乎是刷过的算法题,最后一题是难度题,其他都是中等题目。1、LeetCode2038.如果相邻两个颜色均相同则删除当前颜色(1)题目总共有n个... 查看详情
数据挖掘顺丰公司数据挖掘笔试题(代码片段)
【数据挖掘】顺丰公司数据挖掘笔试题1、二叉排序树的链表节点定义如下:typedefstructBiTnodeintkey_value;structBiTnode*L,*R;/节点的左、右树指针/请补充完整查找键值key的函数。(D)BSTreelookup_key(BSTreeroot,intkey)if()returnNULL;elsei... 查看详情
全国排名前十名某大型软件公司数据库笔试题,分享给大家
1)在java线程中wait和sleep方法的不同?2)truncate与delete的区别?3)说出一些常用的类,包,接口,请各举5个4)数组有没有length()这个方法?String有没有length()这个方法?5)数据库中leftjoin的意思,和在什么场合中应用? 6)Iterator和ListI... 查看详情
2018腾讯校招软件开发岗在线笔试题
不定项选择题(20道题):1.SQL语句中,from,join,where,having,orderby,groupby,limit之间的执行顺序是怎样的?2.innerjoin与leftjoin的执行结果一样吗3.HTTP的返回代码中,200,201,301,307,403,5xx各代表什么含义4.QQ用户有8种状态(在线,忙碌,隐身.... 查看详情
数据挖掘搜狐公司数据挖掘工程师笔试题(代码片段)
1单选题1、下面(D)不是金庸撰写的小说?A、天龙八部B、鹿鼎记C、侠客行D、天涯明月刀飞雪连天射白鹿,笑书神侠倚碧鸳。《飞狐外传》《雪山飞狐》《连城诀》《天龙八部》《射雕英雄传》《白马啸西风》《... 查看详情
某大型数据公司的笔试题
一.基础(你没看错这真的是基础)1.String、StringBuilder和StringBuffer的区别?2.volatile 的理解?3.简述synchronized 和java.util.concurrent.locks.Lock的异同?4.什么场景下使用HashMap、LinkedHashMap、ConcurrentHashMap、WeakHashMap?哪些是线程安全的... 查看详情
字节跳动笔试题——算法岗
目录1.写一个函数,将单向链表反转 查看详情
数据挖掘2022年联想公司数据挖掘工程师笔试题(代码片段)
选择题1、顺序表存储的特点解析:1.随机访问2.存储密度高3.扩展容量不方便4.插入、删除数据元素不方便2、16、9、49、7、1、45、23、13,增量为4,第一轮希尔排序后,前四位数字1、9、23、716和1交换,9位置不... 查看详情
数据挖掘2022年2023届秋招知能科技公司机器学习算法工程师笔试题(代码片段)
岗位:高级机器学习算法工程师笔试时间:2022-9-281简答题1、神经网络中防止过拟合的方法(1)降低模型复杂度(2)正则化,正则化正是通过在损失函数上添加额外的参数稀疏性惩罚项(正则项... 查看详情
头条笔试题2018后端第二批(代码片段)
头条笔试题2018后端第二批标签(空格分隔):笔试题描述:为了不断优化推荐效果,今日头条每天要存储和处理海量数据。假设有这样一种场景:我们对用户按照它们的注册时间先后来标号,对于一类文章,每个用户都有不同... 查看详情
某大厂的笔试题,解压压缩的字母串(代码片段)
这几天看到一个大厂的面试题,感觉比较有意思,是学习递归的好题目,下面和大家分享一下这道题的解法。题目说明:压缩的字母规则是,连续相同的字母串压缩成:连续的个数+[字母串]。如aaa,压缩... 查看详情
mysql/hivesql笔试题:hivesql(代码片段)
题目一:计算平台的每一个用户发过多少朋友圈、获得多少点赞已知,数据如下:T1:10万行数据 T2:1000万行数据(注:没有被点赞的日记此表不做记录)需求:请用sql计算出如下结果:题目二:处理产品版本号版本号... 查看详情
2022年100家公司真实的面试题笔试题汇总(代码片段)
...对加班的看法?是否可以加班?4.你心目中理想的公司怎么样?理想的工作环境?理想的同事关系?5.你觉得这笔试答的如何?优势在哪?劣势在哪?6.如果初面没成功,你怎么说服我给你复试机... 查看详情
软件公司笔试题
1.java基本数据类型 byteshortintlongfloatbooleandoublechar2。单例模式第一种(懒汉,线程不安全):Java代码 public class Singleton { private static Single 查看详情
mysql/hivesql笔试题:hivesql(代码片段)
4手写HQL第4题已知一个表STG.ORDER,有如下字段:Date,Order_id,User_id,amount。请给出sql进行统计:数据样例:2017-01-01,10029028,1000003251,33.57。1)给出2017年每个月的订单数、用户数、总成交金额。2)给出2017年11月的新客数(指在11月才有第... 查看详情
冒死潜入某个外包公司获得的珍贵java基础笔试题(附答案)(代码片段)
前言大家好,我是程序员manor,上次货拉拉面试太打击心情,于是我决定找些小boss打打让自己愉悦起来,于是就有了下面👇这篇Java基础面试笔试题,文章末尾给出答案JAVA基础笔试题目一、单选题(共8题,每题2分)1、下列语... 查看详情
冒死潜入某个外包公司获得的珍贵java基础笔试题(附答案)(代码片段)
前言大家好,我是程序员manor,上次货拉拉面试太打击心情,于是我决定找些小boss打打让自己愉悦起来,于是就有了下面👇这篇Java基础面试笔试题,文章末尾给出答案JAVA基础笔试题目一、单选题(共8题,每题2分)1、下列语... 查看详情