【秋招笔试冲次】SQL复习1

【秋招笔试冲次】SQL复习1SQL 笔试面试秋招复习 代码部分 1 计算相邻两个月差的最大值 假设需要进行 row number 排序 这类题有两种方法 自查询和 lag lead 自查询 select a month b month max a num b num

大家好,我是讯享网,很高兴认识大家。
SQL笔试面试秋招复习-代码部分 --------------------------------------- 1.计算相邻两个月差的最大值(假设需要进行row_number()排序) 这类题有两种方法:自查询和lag/lead 自查询: select a.month,b.month,max(a.num-b.num) -----from a,b from (select a.*,row_number()over(order by month) as rn) t1 join (select b.*,row_number()over(order by month) as rn) t2 where t1.rn = t2.rn-1 lag/lead 数据t示例如下 id firstname lastname 1 a 3 b 7 c select t2.* from (select t1.id, t1.firstname, t1.lastname, case when (t1.prev_val is null then null else t1.id-t1.prev_val end) as prev_diff, case when (t1.next_val is null then null else t1.next_val-t1.id end)as next_diff from (select t.id, t.firstname, t.lastname, lag(t.id)over(order by t.id asc) prev_val, lead(t.id)over(order by t.id asc) next_val from t ) t1 ) t2 where t2.prev_diff<=4 or t2.next_diff<=4 上面这个是一个稍微复杂点的情况:写的时候不确定的点有:rownumber要groupby嘛/case when 的格式 解答:要groupby, ------------------------------------------------ 2.工作中常常遇到coalesce函数,这个函数是做什么用的: coalesce是在oracle数据库和DB2数据库中的函数,想当于ifnull,可以把下一个空值转变为一个实际的值 coalesce(c.share_pv, 0) 3.lateral view explode 用法1:把存在同一行的数据炸裂开,然后按照列去保存数据,相当于列转行 select *,if(instr(feed_id,'_')>0,if(event_id in ('1-1-1-43','1-1-1-44'),split(feed_id,'_')[1],split(feed_id,'_')[0]),feed_id) as item_id from realtime_ods.ods_main_pkg_bury LATERAL VIEW explode(split(free_name2,',')) tab AS feed_id where dt between '${dt}' and '${dt}' and event_id in ('1-1-1-43','1-1-1-44','1-4-1-1','1-4-1-2','1-4-1-3') 把一个字段里的按照逗号分开,再炸裂 用法2:lateral view explode(split(concat(type1,',',type2,',',type3,',',type4,',',type5,',',type6,',',type7,',',type8),',')) myTable as new_tag ------------------------------------------------ 4.连续登录问题 #SQL淘宝店铺分析184 #用rownumber和datesub解决 select user_id,count(days) as days_count from (select a.*, date_sub(sales_date,interval rn day) as days from (select sales_date, user_id,row_number()over(partition by user_id order by sales_date) rn from sales_tb group by sales_date, user_id) a ) b group by user_id having days_count>=2 #知乎最大回答问题天数大于等于3 194 select author_id,author_level,days_cnt from (select author_id,count(days) as days_cnt from (select a1.* ,date_sub(answer_date,interval rn day) as days from (select answer_date,author_id,row_number()over(partition by author_id order by answer_date) as rn from answer_tb group by answer_date,author_id) a1 ) a group by days_cnt having days_cnt>=3) b join author_tb on a.author_id = b. 5.留存问题 #用户的次日留存率 select a1.dt,round(count(a2.uid)/count(a1.uid),2) uv_rate from (select uid,min(date(in_time)) dt from tb_user_log group by uid ) a1-------所有用户唯一一次的最小登陆时间表 left join (select uid,date(in_time) from tb_user_log union select uid,date(out_time) from tb_user_log) a2 on a1.uid = a2.uid and a1.dt = date_sub(a2.dt, interval 1 day) where date_format(a1.dt,'%Y-%m') = '2021-11' group by a1.dt order by a1.dt #零食类商品中复购率top3高的商品 171 select t1.product_id, from (select product_id,min(event_time) as min_event_time from tb_order_overall a join (select order_id,product_id from tb_order_detail where status = '1' ) b on a.order_id = b.order_id ) t1 left join (select product_id,event_time from tb_order_overall a join (select order_id,product_id from tb_order_detail where status = '1' ) b on a.order_id = b.order_id ) t2 on t1.product_id = t2.product_id where 留存问题29 解法1:表里的数据可以看作是全部第一天来刷题了的,那么我们需要构造出第二天来了的字段,因此可以考虑用left join把第二天来了的拼起来, 限定第二天来了的可以用date_add(date1, interval 1 day)=date2筛选,并用device_id限定是同一个用户。 select count(distinct b.device_id,date1)/count(distinct a.device_id, date) as avg_ret from (select distinct device_id, date from question_practice_detail) a left join (select distinct device_id, date_add(date,interval 1 day) as date1 from question_practice_detail) b on a.device_id = b.device_id and a.date = b.date1 left join完就是这个样子 1 1234|2021-08-16|None|None 2 1234|2021-08-17|1234|2021-08-17 3 1235|2021-08-18|None|None 4 1236|2021-08-20|None|None 5 1236|2021-08-21|1236|2021-08-21 6 1237|2021-08-15|1237|2021-08-15 7 1237|2021-08-14|None|None 留存问题164:每天新用户的次日留存率 原始数据 id in_time out_time 1 101|2021-11-01|2021-11-01 2 102|2021-10-30|2021-10-30 3 109|2021-10-31|2021-10-31 4 102|2021-11-01|2021-11-01 5 103|2021-11-01|2021-11-01 6 101|2021-11-02|2021-11-02 7 101|2021-11-02|2021-11-02 8 104|2021-11-02|2021-11-02 9 105|2021-11-02|2021-11-02 10 106|2021-11-02|2021-11-02 11 107|2021-11-03|2021-11-03 12 104|2021-11-03|2021-11-03 13 105|2021-11-03|2021-11-03 14 109|2021-11-04|2021-11-04 #每天的新用户表 select uid, min(date(in_time)) as dt from tb_user_log group by uid 1 101|2021-11-01 2 102|2021-10-30 3 109|2021-10-31 4 103|2021-11-01 5 104|2021-11-02 6 105|2021-11-02 7 106|2021-11-02 8 107|2021-11-03 select uid, date(in_time) from tb_user_log union select uid, date(out_time) from tb_user_log 在这里union相当于去重 1 101|2021-11-01 2 102|2021-10-30 3 109|2021-10-31 4 102|2021-11-01 5 103|2021-11-01 6 101|2021-11-02 7 104|2021-11-02 8 105|2021-11-02 9 106|2021-11-02 10 107|2021-11-03 11 104|2021-11-03 12 105|2021-11-03 13 109|2021-11-04 #一个月的用户活跃表 codes select a.dt,round(count(b.uid)/count(a.uid),2) uv_rate from ( select uid, min(date(in_time)) as dt from tb_user_log group by uid ) a left join( select uid, date(in_time) dt from tb_user_log union select uid, date(out_time) from tb_user_log ) b on a.uid = b.uid and a.dt = date_sub(b.dt,interval 1 day) where date_format(a.dt,'%Y-%m') = '2021-11' group by a.dt order by a.dt 1/2/3/4/5/6/7/14/30日留存 select login, count(distinct uid) as 首日 count(distinct if(datediff(a.login,b.login)=1,user_id,null)) as 第二日留存 from (select a.uid,b.uid,a.login,b.login from ( select uid,login from table ) a left join ( select uid login from table ) b on a.uid = b.uid where b.login > a.login order by a.uid,a.login )c group by login 166每天的日活数及新用户占比 select dt, count(a.uid) as dau, round(count(b.uid) / count(a.uid),2) as un_new_ratio from( select uid, date(in_time) dt from tb_user_log union select uid, date(out_time) from tb_user_log ) a left join ( select uid, min(date(in_time)) dt1 from tb_user_log group by uid ) b on a.uid = b.uid and a.dt = b.dt1 group by dt order by dt 窗口函数rownumber 136每类试卷得分前3名 SELECT tag,uid,ranking FROM ( SELECT b.tag,a.uid, ROW_NUMBER()OVER(PARTITION BY tag ORDER BY max(a.score) DESC,min(a.score) DESC,a.uid DESC) ranking /*先按照最高分,再按照最低分排,最后按照uid排序 */ FROM exam_record a LEFT JOIN examination_info b ON a.exam_id=b.exam_id GROUP BY b.tag,a.uid)t1 WHERE ranking<=3 连续登陆问题 SELECT user_id,COUNT(1) days_count FROM (SELECT DISTINCT sales_date,user_id,row_number()OVER(PARTITION BY user_id ORDER BY sales_date) rk FROM sales_tb)t1 GROUP BY user_id,DATE_SUB(sales_date,INTERVAL rk DAY) HAVING COUNT(1)>=2 select t1.author_id, count(DISTINCT t1.answer_date) cnt from ( #第二步:作差。用answer_date-ranking,得到日期dt。(dt是判断连续天数的关键) select t.author_id, t.answer_date, t.answer_date-t.ranking dt FROM (#第一步:排序。按照回答日期,因为一天可能回答多个问题,所以用dense_rank() select author_id, answer_date, dense_rank() over (PARTITION BY author_id order by answer_date) ranking from answer_tb )t )t1 GROUP BY t1.author_id,t1.dt having count(DISTINCT t1.answer_date)>=3

讯享网

7.having和where的区别
having是在分组后对数据进行过滤:group by having
where是在分组前对数据进行过滤
having后面可以使用聚合函数
where后面不可以使用聚合

讯享网13. 增删改基本操作 1.插入数据 所以我们在插入数据的时候,方法之一: 可以指定插入的列名, 这样就不用填写id这一列的数据,让他自增。具体插入代码如下: INSERT INTO exam_record (uid, exam_id, start_time, submit_time, score) VALUES (1001, 9001, '2021-09-01 22:11:12', '2021-09-01 23:01:12', 90), (1002, 9002, '2021-09-04 07:01:02', NULL, NULL); 方法之二:把id的值设置为NULL或0,这样MySQL会自己处理这个自增的id列。 具体代码如下: INSERT INTO exam_record VALUES (NULL, 1001, 9001, '2021-09-01 22:11:12', '2021-09-01 23:01:12', 90), (NULL, 1002, 9002, '2021-09-04 07:01:02', NULL, NULL); 其实还有一个方法三:直接填入id值,不过仅针对插入数据不多的时候使用,大家了解一下: INSERT INTO exam_record VALUES (1, 1001, 9001, '2021-09-01 22:11:12', '2021-09-01 23:01:12', 90), (2, 1002, 9002, '2021-09-04 07:01:02', NULL, NULL); 普通插入(全字段):INSERT INTO table_name VALUES (value1, value2, ...) 普通插入(限定字段):INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...) 多条一次性插入:INSERT INTO table_name (column1, column2, ...) VALUES (value1_1, value1_2, ...), (value2_1, value2_2, ...), ... 从另一个表导入:INSERT INTO table_name SELECT * FROM table_name2 [WHERE key=value] UPDATE examination_info SET tag = "Python" WHERE tag = "PYTHON"; DELETE FROM exam_record WHERE TIMESTAMPDIFF(MINUTE, start_time, submit_time) < 5 AND score < 60; 修改表名 alter table user_info add school varchar(15) after level; 增加列在某列之后 alter table 增加的表格 add 增加列的名称 数据类型 位置(after level 在level 之后) alter table user_info change job profession varchar(10); 更换列的名称及数据类型 alter table user_info change 原列名 修改列名 修改数据类型 alter table user_info modify achievement int(11) default 0; 更改数据类型 alter table 表名 modify 修改列名称 数据类型 默认值等 ------------------------------------------------------------------------------- CREATE TABLE [IF NOT EXISTS] tb_name -- 不存在才创建,存在就跳过 (column_name1 data_type1 -- 列名和类型必选 [ PRIMARY KEY -- 可选的约束,主键 | FOREIGN KEY -- 外键,引用其他表的键值 | AUTO_INCREMENT -- 自增ID | COMMENT comment -- 列注释(评论) | DEFAULT default_value -- 默认值 | UNIQUE -- 唯一性约束,不允许两条记录该列值相同 | NOT NULL -- 该列非空 ], ... ) [CHARACTER SET charset] -- 字符集编码 [COLLATE collate_value] -- 列排序和比较时的规则(是否区分大小写等) ALTER TABLE 表名 修改选项 { ADD COLUMN <列名> <类型> -- 增加列 | CHANGE COLUMN <旧列名> <新列名> <新列类型> -- 修改列名或类型 | ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT } -- 修改/删除 列的默认值 | MODIFY COLUMN <列名> <类型> -- 修改列类型 | DROP COLUMN <列名> -- 删除列 | RENAME TO <新表名> -- 修改表名 | CHARACTER SET <字符集名> -- 修改字符集 | COLLATE <校对规则名> } -- 修改校对规则(比较和排序时用到) 创建索引 CREATE INDEX idx_duration ON examination_info(duration); CREATE UNIQUE INDEX uniq_idx_exam_id ON examination_info(exam_id); CREATE FULLTEXT INDEX full_idx_tag ON examination_info(tag); 删除索引 方法一: alter table examination_info drop index uniq_idx_exam_id; alter table examination_info drop index full_idx_tag; 方法二: drop index uniq_idx_exam_id on examination_info; drop index full_idx_tag on examination_info;

3. 辛普森悖论:
当我们在分析两种变量的相关性,例如男女的录取比例,有时会对数据进行分组研究,但在分组中都占优势的一方,有时候在
整体中反而处于劣势,
男            女            整体
100 49 49% A      20 15 75%     
20 1 5%  B        100 10 10%    
120 50 42%        120 25  21%
斟酌个别分组的权重,以一定的系数去消除以分组资料基数差异所造成的影响,
同时必需了解该情境是否存在其他潜在要因而综合考虑

4.数据倾斜

5.abtest和假设检验
面试常问
一、ABtest中第一类错误和第二类错误
第一类错误是原假设为真,却拒绝了原假设。在实际中即一个产品的改动没有显著收益而我们却认为有。
第二类错误是原假设不为真,我们却接受了原假设。现实中即一个产品的改动带来收益,由于我们却认为没有。
增加样本量可以同时降低这两类错误的概率,若样本量不变则减低其中一类错误的概率会导致另一类错误概率增大。
但在实际工作中,我们往往更加不能接受第一类错误,因为如果一个产品不好的改动会带来很大的负面影响,可能会影响日活和留存。
而提升留存和日活很难,但降低很容易,因此更加不能接受上线一个不好的优化改动。

二、如果你发现AB测试的结果在统计上来说是显著,但是在实际中却不显著,这是为什么?
这个可能的原因是我们在AB测试当中所选取的样本量过大,导致和总体数据量差异很小,
这样的话即使我们发现一个细微的差别,它在统计上来说是显著的,在实际的案例当中可能会变得不显著了。
举个栗子,对应到我们的互联网产品实践当中,我们做了一个改动,APP的启动时间的优化了0.001秒,
这个数字可能在统计学上对应的P值很小,也就是说统计学上是显著的,但是在实际中用户0.01秒的差异是感知不出来的。
那么这样一个显著的统计差别,其实对我们来说是没有太大的实际意义的。所以统计学上的显著并不意味着实际效果的显著。

三、如果你发现你在AB测试当中所选取的指标在统计上来说都是不显著的,你该怎么去判断这个实验的收益?
对于这种情况,我们所选取的一种通用的方式是将这个指标去拆分成每一天去观察。
如果指标的变化曲线每一天实验组都高于对照组,即使他在统计上来说是不显著的,
我们也认为在这样一个观测周期内,实验组的关键指标表现是优于对照组的,
那么结合这样一个观测,我们最终也可以得出这个优化可以上线的结论。

四、实验组优于对照组就能上线?
不一定。在做这个优化的时候,可能会对其他部门产生一些负向的影响,进而导致公司收入的下降。
所以我们在进行AB测试的时候,必须要综合评估所有方面的一些指标变动,
同时对于收益和损失来做一个评估,才能确认这个优化可以最终上线。
五、AB测试是必须的么?
面试官问:AB测试成本很高,每个调整都需要AB测试么?
但是比如说面试官问到每一个关于我们现在想要上线一个小改动,或者说要上线一个小调整,你会如何去验证这个调整的收益?
你都用AB测试去进行回答的话,他可能会反问,AB测试是需要成本的,你不觉得每一次我们如果都需要通过一个AB测试去验证的话,成本过高吗
?对于这种情况,你可以说如果只是验证一个小按钮或者一个小改动,我们可以在界面上去设置一个开关,用户可以通过开关的形式自行决定采用哪一种方式。
那么最后我们可以通过这个开关的相关指标去判断用户对于哪一种形式有更大的倾向性。或者有时候我们可以去做用户调研,比如说通过访谈或者说是设计问卷的形式,
去收集一些用户的反馈或者他们关于这些小变动的体验,所以并不是绝对的。
六、AB测试的优缺点是什么?
优点:
可以科学量化策略的效果,避免拍脑袋决定造成的损失。
为创新项目提供数据支持,有利于探究新想法。
缺点:
数据采集积累,降低了策略的迭代效率。
不同策略的维护和开发,增加了开发工作量。
七、实验效果不显著怎么办?
实验效果不显著的原因可以分成两类:
第一类:线上策略不佳,无明显差异。再观察每天的指标对比,若无好的效果,中止实验。
第二类:实验的灵敏度不够高。
针对第二类问题,我们可以通过以下方法进行优化

增加样本量:根据显著性检验的原理,只要实验组和对照组差值及样本方差不变的情况下,样本量足够大,我们总是可以得到显著性的结果。
减少样本均值的方差:减少样本均值方差的方法有减少离群值的影响,缩减方差(CUPED)的方法。
更换指标:更换一个方差更小的指标,比如某购物平台,实验指标一开始是用户购买的平均金额,我们可以更换为用户是否购买。对同一批样本,是否购买属于0-1分布,样本的均值方差自然比用户购买的平均金额小很多。

置信水平:可以接受的一类错误发生的概率 0.05
p值: 实际情况中第一类错误发生的概率
统计功效:1-第二类错误发生的概率
最小提升:提升了多少认为有进步

随机分配:AB 测试要求将用户随机分配到不同的实验组,以便在整个实验过程中,每个组的用户具有相似的特征。这有助于排除其他因素的干扰,确保实验结果的有效性。


讯享网

结果应用:根据实验结果和统计检验,可以选择性能更优的版本进行推广。这有助于提高关键指标,并为后续的优化提供数据支持。

在回答时,可以根据具体情况和面试官的关注点,适当举例和深入解释,展示自己对 AB 测试原理的理解和实践经验。
----------------------------------------------------------------------------------
AB测试的一般流程包括以下几个步骤:

确定目标:首先明确AB测试的目标,例如提高转化率、增加用户留存率或提高用户满意度等。

设计实验:设计A(控制组)和B(实验组)等不同的变体,确保它们之间的差异仅限于所测试的变量。例如,当测试不同的按钮颜色时,应确保页面的其他部分保持不变。

随机分配:将用户随机分配到不同的变体中,以确保实验组和控制组的用户分布相似,从而排除其他干扰因素。

收集数据:启动实验并收集足够的数据,以便进行统计分析。确保样本量足够大以支持结果的可靠性,并且实验时间足够长以捕捉到足够的数据变化。

分析结果:对数据进行统计检验,如t-检验、卡方检验等,以确定实验组和控制组之间的差异是否显著。计算P值以衡量观察到的数据与原假设(实验组和控制组之间没有差异)之间的一致性。

做出决策:根据分析结果,如果实验组和控制组之间的差异具有统计学意义,可以选择性能更优的变体进行推广。如果差异不显著,则可能需要重新设计实验或考虑其他优化方法。

持续优化:AB测试是一个持续迭代的过程。根据实验结果,可以继续优化并设计新的实验,以进一步提高关键指标。

6.大数定律/中心极限定理/样本量计算

快手一面SQL 1. select id,主播昵称,count(date) as 开播天数,sum(开播时长) as 总开播时长, case when sum(开播时长) <= 1 then '1h' when sum(开播时长) >= 2 and sum(开播时长) <= 5 then '2-5h' when sum(开播时长) > 5 and sum(开播时长) <= 10 then '5-10h' else '10h+' end from ks_live where substring(date,0,6) = '' group by id having count(date) >= 10 附加:在上述sql中增加一列【月总开播时长分层】,按1h、2-5h、5-10h,10h+进行分层; 2. select play_cnt, count(b.user_id) / count(a.user_id) as 次日留存率 from (select distinct play_cnt,user_id,p_date as dt from ks_game_photo )a left join (select distinct play_cnt,user_id,date_add(p_date,interval 1 day) as dt1 from ks_game_photo)b on a.user_id = b.user_id and a.dt = b.dt1 group by play_cnt order by play_cnt 
小讯
上一篇 2025-03-17 13:05
下一篇 2025-02-05 15:22

相关推荐

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容,请联系我们,一经查实,本站将立刻删除。
如需转载请保留出处:https://51itzy.com/kjqy/132486.html