每天一道大厂SQL题【Day02】电商场景TopK统计

大家好&＃xff0c;我是Maynor。相信大家和我一样&＃xff0c;都有一个大厂梦&＃xff0c;作为一名资深大数据选手&＃xff0c;深知SQL重要性&＃xff0c;接下来我准备用100天时间&＃xff0c;基于大数据岗面试中的经典SQL题&＃xff0c;以每日1题的形式&＃xff0c;带你过一遍热门SQL题并给出恰如其分的解答。

一路走来&＃xff0c;随着问题加深&＃xff0c;发现不会的也愈来愈多。但底气着实足了不少&＃xff0c;相信不少朋友和我一样&＃xff0c;日积月累才是最有效的学习方式&＃xff01;

每日鸡汤&＃xff1a;

你必须非常努力&＃xff0c;才能看起来毫不费力。

第2题&＃xff1a;电商场景TopK统计

需求

有50W个京东店铺&＃xff0c;每个顾客访客访问任何一个店铺的任何一个商品时都会产生一条访问日志&＃xff0c; 访问日志存储的表名为Visit&＃xff0c;访客的用户id为user_id&＃xff0c;被访问的店铺名称为shop&＃xff0c;数据如下&＃xff1a;

请统计&＃xff1a;
&＃xff08;1&＃xff09;每个店铺的UV&＃xff08;访客数&＃xff09;
(2)每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数
实现

数据准备

CREATE TABLE test_sql.test2 (
user_id string, shop string )
ROW format delimited FIELDS TERMINATED BY &＃39;\t&＃39;;
INSERT INTO TABLE test_sql.test2 VALUES ( &＃39;u1&＃39;, &＃39;a&＃39; ),
( &＃39;u2&＃39;, &＃39;b&＃39; ),
( &＃39;u1&＃39;, &＃39;b&＃39; ),
( &＃39;u1&＃39;, &＃39;a&＃39; ),
( &＃39;u3&＃39;, &＃39;c&＃39; ),
( &＃39;u4&＃39;, &＃39;b&＃39; ),
( &＃39;u1&＃39;, &＃39;a&＃39; ),
( &＃39;u2&＃39;, &＃39;c&＃39; ),
( &＃39;u5&＃39;, &＃39;b&＃39; ),
( &＃39;u4&＃39;, &＃39;b&＃39; ),
( &＃39;u6&＃39;, &＃39;c&＃39; ),
( &＃39;u2&＃39;, &＃39;c&＃39; ),
( &＃39;u1&＃39;, &＃39;b&＃39; ),
( &＃39;u2&＃39;, &＃39;a&＃39; ),
( &＃39;u2&＃39;, &＃39;a&＃39; ),
( &＃39;u3&＃39;, &＃39;a&＃39; ),
( &＃39;u5&＃39;, &＃39;a&＃39; ),
( &＃39;u5&＃39;, &＃39;a&＃39; ),
( &＃39;u5&＃39;, &＃39;a&＃39; );


思路分析

1 每个店铺的UV&＃xff08;访客数&＃xff09;
使用 SQL 或者其他数据库查询语言进行统计。
先通过 group by 按店铺名称进行分组&＃xff0c;然后使用 count(distinct user_id) 计算每个店铺的访客数。
2 每个店铺访问次数top3的访客信息
使用 SQL 或者其他数据库查询语言进行统计。
先通过 group by 按店铺名称和用户id进行分组&＃xff0c;然后使用 count(*) 计算每个用户在每个店铺的访问次数。
使用 order by 对访问次数进行排序&＃xff0c;然后使用 limit 3 限制输出的数量。
输出店铺名称、访客id、访问次数。

答案获取

建议你先动脑思考&＃xff0c;动手写一写再对照看下答案&＃xff0c;如果实在不懂可以点击下方卡片&＃xff0c;回复:大厂sql 即可。
参考答案适用HQL&＃xff0c;SparkSQL&＃xff0c;FlinkSQL&＃xff0c;即大数据组件&＃xff0c;其他SQL需自行修改。

加技术群讨论

点击下方卡片关注 联系我进群

或者直接私信我进群

文末SQL小技巧

提高SQL功底的思路。
1、造数据。因为有数据支撑&＃xff0c;会方便我们根据数据结果去不断调整SQL的写法。
造数据语法既可以create table再insert into&＃xff0c;也可以用下面的create temporary view xx as values语句&＃xff0c;更简单。
其中create temporary view xx as values语句&＃xff0c;SparkSQL语法支持&＃xff0c;hive不支持。
2、先将结果表画出来&＃xff0c;包括结果字段名有哪些&＃xff0c;数据量也画几条。这是分析他要什么。
从源表到结果表&＃xff0c;一路可能要走多个步骤&＃xff0c;其实就是可能需要多个子查询&＃xff0c;过程多就用with as来重构提高可读性。
3、要由简单过度到复杂&＃xff0c;不要一下子就写一个很复杂的。
先写简单的select * from table…,每个中间步骤都执行打印结果&＃xff0c;看是否符合预期&＃xff0c; 根据中间结果&＃xff0c;进一步调整修饰SQL语句&＃xff0c;再执行&＃xff0c;直到接近结果表。
4、数据量要小&＃xff0c;工具要快&＃xff0c;如果用hive&＃xff0c;就设置set hive.exec.mode.local.auto&＃61;true;如果是SparkSQL&＃xff0c;就设置合适的shuffle并行度&＃xff0c;set spark.sql.shuffle.partitions&＃61;4;

后记

&＃x1f4e2;博客主页&＃xff1a;https://manor.blog.csdn.net

&＃x1f4e2;欢迎点赞 &＃x1f44d; 收藏 ⭐留言 &＃x1f4dd; 如有错误敬请指正&＃xff01;
&＃x1f4e2;本文由 Maynor 原创&＃xff0c;首发于 CSDN博客&＃x1f649;
&＃x1f4e2;不能老盯着手机屏幕&＃xff0c;要不时地抬起头&＃xff0c;看看老板的位置⭐
&＃x1f4e2;专栏持续更新,欢迎订阅&＃xff1a;https://blog.csdn.net/xianyu120/category_12182595.html