SQL_求店铺的topN && 开窗函数数据倾斜

这篇具有很好参考价值的文章主要介绍了SQL_求店铺的topN && 开窗函数数据倾斜。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

某互联网大厂的一道比较有深度的面试题,

参考文章 : hive|性能优化|_Hive ROW_NUMBER TopN 性能优化

问题

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

                user_id shop

                u1  a
                u2  b
                u1  b
                u1  a
                u3  c
                u4  b
                u1  a
                u2  c
                u5  b
请统计:
(1)每个店铺的UV(访客数)
(2)每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数

问题(1)解法

问题一的目的是考察能否在大数据量下考虑用 group做去重预聚合,而不是直接用count(distinct)

正确解法 

with 
user_dist_log as (
    select 
        user_id,
        shop,
        count(1) as pv_cnt
    from visit
    group by 
        user_id,
        shop
)

select 
    shop,
    count(1) as uv 
from user_dist_log
group by shop;

问题(2)解法

问题二的目的是考察开窗函数情况下的数据倾斜如何解决
由于开窗函数是把数据分发到同一个 executor 进行单点的数据排序,那么热点数据很容易导致数据倾斜,所以这里的解决方案是2步聚合的方式,加盐打散,二次聚合。

具体代码文章来源地址https://www.toymoban.com/news/detail-606728.html

with
user_dist_log as (
	select 
		user_id,
		shop,
		count(1) as pv_cnt
	from visit
	group by 
		user_id,
		shop
)



select 
    shop,
    user_id,
    shop_rank2
from 
(
    select 
        shop,
        user_id,
        row_number() over(partition by shop order by pv_cnt desc) as shop_rank2
    from 
    (
        select 
            shop,
            rand1,
            user_id,
            pv_cnt 
        from 
        (
            select 
                shop,
                ceil(rand()*100) as rand1,
                user_id,
                pv_cnt,
                row_number() over(partition by shop, ceil(rand()*100) order by pv_cnt desc) as shop_rank
            from user_dist_log
        ) mid_tmp
        where shop_rank <= 3
    )
)
where shop_rank2 <= 3

测试数据构建

with 
visit as (
    select 
        'u1' as user_id,
        'a' as shop
    union all 
    select 
        'u2' as user_id,
        'b' as shop
    union all 
    select 
        'u1' as user_id,
        'b' as shop
    union all 
    select 
        'u1' as user_id,
        'a' as shop
    union all 
    select 
        'u3' as user_id,
        'c' as shop
    union all 
    select 
        'u4' as user_id,
        'b' as shop
    union all 
    select 
        'u1' as user_id,
        'a' as shop
    union all 
    select 
        'u2' as user_id,
        'c' as shop
    union all 
    select 
        'u5' as user_id,
        'b' as shop
),

到了这里,关于SQL_求店铺的topN && 开窗函数数据倾斜的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • SQL 的window开窗函数简单使用

    开窗函数不论是spark的还是clickhouse的在日常的查询中是一个很常用的功能,特别是他想要解决的问题和group by的很类似,这两种容易引起混淆,本文就简单的描述下开窗函数的简单用法 首先窗口函数和group by是完全没有交集的,他们完全没有任何关系,group by聚合数据后会导致

    2024年02月09日
    浏览(40)
  • SQL使用技巧(6)HIVE开窗函数

    开窗函数的使用铁律:不要滥用 先想明白自己要实现什么样的功能,然后再去实践。没有目的的尝试段不可取,会难以理解各开窗函数的真正用法。 如果没有现成可用HIVE库,可以参见本人 大数据单机学习环境搭建 系列文章。 1.1Hive建表 1.2数据准备 1.3保存为HDFS文件 1.4验证

    2024年02月08日
    浏览(57)
  • hive sql—开窗函数—累积求和和滑动求和

    数据集有三列数据,姓名、月份和数量: 图1 使用 sum() 函数和 over() 来实现,如下: 结果如下: 同一个name,后一个月份都是前几个月份的累加和 图2 需要稍微骚一点的操作,加上一个限制条件: 数字:可正可零可负,正往前,负向后;preceding:向前几行;following:向后几行

    2024年02月11日
    浏览(45)
  • 【SQL开发实战技巧】系列(二十七):数仓报表场景☞通过对移动范围进行聚集来详解分析函数开窗原理以及如何一个SQL打印九九乘法表

    【SQL开发实战技巧】系列(一):关于SQL不得不说的那些事 【SQL开发实战技巧】系列(二):简单单表查询 【SQL开发实战技巧】系列(三):SQL排序的那些事 【SQL开发实战技巧】系列(四):从执行计划讨论UNION ALL与空字符串UNION与OR的使用注意事项 【SQL开发实战技巧】系列

    2023年04月09日
    浏览(48)
  • Flink SQL TopN

    Flink SQL 对于批处理(Batch)和流处理(streaming)模式的SQL,都支持 Top-N 查询。 Top-N 查询可以根据指定列排序后获得前 N 个最小或最大值。并且该结果集还可用于进一步分析。 Flink 使用 OVER 窗口子句和过滤条件的组合来表达一个 Top-N 查询。借助 OVER 窗口的 PARTITION BY 子句能力

    2024年02月12日
    浏览(73)
  • SQL分组后取topN

    求每门课程中成绩最好的人 使用窗口函数: 利用表链接 使用子查询 求每门课程成绩前两人 使用窗口函数: 使用自身左链接 结果: 这是对CID分组,按分数排名的表 上图中3中不同的排名方式,对应sql窗口函数的三种 row_number : 就是按1-2-3顺序排 rank:就是按 1-1-3 排列--这就是 

    2024年02月10日
    浏览(36)
  • flink sql 实战实例 及延伸问题:聚合/数据倾斜/DAU/Hive流批一体 等

    ⭐ 需求:上游是一个 kafka 数据源,数据内容是用户 QQ 等级变化明细数据(time,uid,level)。需要你求出当前每个等级的用户数。 ⭐ 需求:数据源:用户心跳日志(uid,time,type)。计算分 Android,iOS 的 DAU,最晚一分钟输出一次当日零点累计到当前的结果。 经过测试 在fl

    2024年02月22日
    浏览(49)
  • ORACLE数据库 开窗函数

    开窗函数 2.1开窗函数的定义及语法 开窗函数(又名:分析函数,窗口函数,OLAP函数) 聚合函数:将数据按照一定的规则分组,统一分析各组的某项情况,每个分组返回一行结果 开窗函数:将数据按照一定的规则分组,统一分析各组的某项情况,每行数据返回一行结果 (

    2024年02月11日
    浏览(46)
  • SQL | 使用函数处理数据

    8.1-函数 SQL可以用函数来处理数据。函数一般是在数据上执行的,为数据的转换和处理提供了方便。 8.1.1 函数带来的问题 每种DBMS都有特定的函数,只有很少一部分函数,是被所有主要的DBMS等同的支持。 虽然所有的类型的函数一般都可以在每个DBMS中使用,但每个函数的名称

    2024年02月12日
    浏览(52)
  • MySQL8.0数据库开窗函数

          数据库开窗函数是一种在SQL中使用的函数,它可以用来对结果集中的数据进行分组和排序,以便更好地分析和处理数据。开窗函数与聚合函数不同,它不会将多行数据聚合成一行,而是保留每一行数据,并对其进行分组和排序。 常见的开窗函数包括ROW_NUMBER()、RANK()、

    2024年02月08日
    浏览(63)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包