使用grouping sets语法与拆分sql查询时间对比

最新推荐文章于 2024-08-16 21:30:01 发布
最新推荐文章于 2024-08-16 21:30:01 发布
阅读量1.4k 收藏
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/a80090023/article/details/123184760
版权

Hive GroupingSets SQL优化 大数据 资源效率
关键词由CSDN通过智能技术生成

背景：为满足业务方分析需求，数仓基础统计中存在大量多维度的指标计算。原有计算中根据不同维度拆分多个sql串行提交hive集群进行查询，多个任务在map阶段存在重复读取源数据等问题，因此引入grouping sets语法合并业务逻辑为一条sql。

一次map一次reduce完成一个基础指标的统计，减少集群资源浪费。现以安卓国内版天活跃启动统计任务为例进行测试。

1.sql1 按province,model维度统计source为dw的前后台启动活跃数据。

sql1
SELECT SOURCE,
       province,
       model,
       sum(front_start_pv),
       sum(if(front_start_pv>0,1,0)),
       sum(all_start_pv),
       sum(1)
FROM
  (SELECT SOURCE,
          province,
          model,
          deviceId,
          sum(front_start_pv) AS front_start_pv,
          sum(all_start_pv) AS all_start_pv
   FROM dw_wps_android.dwd_app_start_d
   WHERE dt='2020-04-04'
     AND SOURCE='dw'
   GROUP BY SOURCE,
            province,
            model,
            deviceId) a
GROUP BY SOURCE,
         province,
         model

2.sql2 按channel,province,model维度统计source为dw的前后台启动活跃数据。 

sql2
SELECT SOURCE,
       channel,
       province,
       model,
       sum(front_start_pv),
       sum(if(front_start_pv>0,1,0)),
       sum(all_start_pv),
       sum(1)
FROM
  (SELECT SOURCE,
          channel,
          province,
          model,
          deviceId,
          sum(front_start_pv) AS front_start_pv,
          sum(all_start_pv) AS all_start_pv
   FROM dw_wps_android.dwd_app_start_d
   WHERE dt='2020-04-04'
     AND SOURCE='dw'
   GROUP BY SOURCE,
            channel,
            province,
            model,
            deviceId) a
GROUP BY SOURCE,
         channel,
         province,
         model

 

 sql3 按app_version,province,model维度统计source为dw的前后台启动活跃数据。

sql3
SELECT SOURCE,
app_version,
province,
model,
sum(front_start_pv),
sum(if(front_start_pv>0,1,0)),
sum(all_start_pv),
sum(1)
FROM
(SELECT SOURCE,
app_version,
province,
model,
deviceId,
sum(front_start_pv) AS front_start_pv,
sum(all_start_pv) AS all_start_pv
FROM dw_wps_android.dwd_app_start_d
WHERE dt='2020-04-04'
AND SOURCE='dw'
GROUP BY SOURCE,
app_version,
province,
model,
deviceId) a
GROUP BY SOURCE,
app_version,
province,
model

 

 4.sql4 按app_version,channel,province,model维度统计source为dw的前后台启动活跃数据。

sql4
SELECT SOURCE,
app_version,
channel,
province,
model,
sum(front_start_pv),
sum(if(front_start_pv>0,1,0)),
sum(all_start_pv),
sum(1)
FROM
(SELECT SOURCE,
app_version,
channel,
province,
model,
deviceId,
sum(front_start_pv) AS front_start_pv,
sum(all_start_pv) AS all_start_pv
FROM dw_wps_android.dwd_app_start_d
WHERE dt='2020-04-04'
AND SOURCE='dw'
GROUP BY SOURCE,
app_version,
channel,
province,
model,
deviceId) a
GROUP BY SOURCE,
app_version,
channel,
province,
model

 

 5.sql5 按province,model维度统计source为所有平台的前后台启动活跃数据

sql5
SELECT 'all' AS SOURCE,
       province,
       model,
       sum(front_start_pv),
       sum(if(front_start_pv>0,1,0)),
       sum(all_start_pv),
       sum(1)
FROM
  (SELECT 'all' AS SOURCE,
          province,
          model,
          deviceId,
          sum(front_start_pv) AS front_start_pv,
          sum(all_start_pv) AS all_start_pv
   FROM dw_wps_android.dwd_app_start_d
   WHERE dt='2020-04-04'
   GROUP BY 'all',
            province,
            model,
            deviceId) a
GROUP BY 'all',
         province,
         model

 

 6.sql6 按channel,province,model维度统计source为所有平台的前后台启动活跃数据

sql6
SELECT 'all' AS SOURCE,
       channel,
       province,
       model,
       sum(front_start_pv),
       sum(if(front_start_pv>0,1,0)),
       sum(all_start_pv),
       sum(1)
FROM
  (SELECT 'all' AS SOURCE,
          channel,
          province,
          model,
          deviceId,
          sum(front_start_pv) AS front_start_pv,
          sum(all_start_pv) AS all_start_pv
   FROM dw_wps_android.dwd_app_start_d
   WHERE dt='2020-04-04'
   GROUP BY 'all',
            channel,
            province,
            model,
            deviceId) a
GROUP BY 'all',
         channel,
         province,
         model

 

 7.sql7 按app_version,province,model维度统计source为所有平台的前后台启动活跃数据

sql7
 SELECT 'all' AS SOURCE,
       app_version,
       province,
       model,
       sum(front_start_pv),
       sum(if(front_start_pv>0,1,0)),
       sum(all_start_pv),
       sum(1)
FROM
  (SELECT 'all' AS SOURCE,
          app_version,
          province,
          model,
          deviceId,
          sum(front_start_pv) AS front_start_pv,
          sum(all_start_pv) AS all_start_pv
   FROM dw_wps_android.dwd_app_start_d
   WHERE dt='2020-04-04'
   GROUP BY 'all',
            app_version,
            province,
            model,
            deviceId) a
GROUP BY 'all',
         app_version,
         province,
         model

 

 8.sql8 按app_version,channel,province,model维度统计source为所有平台的前后台启动活跃数据

sql8
SELECT 'all' AS SOURCE,
       app_version,
       channel,
       province,
       model,
       sum(front_start_pv),
       sum(if(front_start_pv>0,1,0)),
       sum(all_start_pv),
       sum(1)
FROM
  (SELECT 'all' AS SOURCE,
          app_version,
          channel,
          province,
          model,
          deviceId,
          sum(front_start_pv) AS front_start_pv,
          sum(all_start_pv) AS all_start_pv
   FROM dw_wps_android.dwd_app_start_d
   WHERE dt='2020-04-04'
   GROUP BY 'all',
            app_version,
            channel,
            province,
            model,
            deviceId) a
GROUP BY 'all',
         app_version,
         channel,
         province,
         model

 

 9.sql9 合并source,app_version,channel,province,model维度组合统计前后台启动活跃数据

sql9
SELECT nvl(source,"all") as source,
       app_version,
       channel,
       province,
       model,
       sum(front_start_pv) as front_start_pv,
       sum(if(front_start_pv>0,1,0)) as front_start_uv,
       sum(all_start_pv) as all_start_pv,
       sum(1) as all_start_uv
FROM
  (SELECT source,
          nvl(app_version,"dw_null") as app_version,
          nvl(channel,"dw_null") as channel,
          nvl(province,"dw_null") as province,
          nvl(model,"dw_null") as model,
          deviceId,
          sum(front_start_pv) as front_start_pv,
          sum(all_start_pv) as all_start_pv
   FROM dw_wps_android.dwd_app_start_d
   WHERE dt='2020-04-04'
   GROUP BY source,
            nvl(app_version,"dw_null"),
            nvl(channel,"dw_null"),
            nvl(province,"dw_null"),
            nvl(model,"dw_null"),
            deviceId) a
GROUP BY source,
         app_version,
         channel,
         province,
         model
GROUPING SETS(
(source,province,model),
(source,channel,province,model),
(source,app_version,province,model),
(source,app_version,channel,province,model),
(province,model),
(channel,province,model),
(app_version,province,model),
(app_version,channel,province,model)
)HAVING source='dw' OR source is null

 

 

sql行号

数据行

time（秒）

sql1

191618

54.289

sql2

349692

186.34

sql3

811334

48.341

sql4

1059390

50.373

sql5

197314

66.308

sql6

405496

68.312

sql7

1077795

152.358

sql8

1376569

182.347

总和

5469208

808.668

sql行号

数据行

time（秒）

sql9

5469208

134.432

说明：sql都是都测试队列相同集群环境下测试的。都是在container复用的情况下进行测试，排除了第一个sql从0申请资源执行时间过长的因素。

结论：采用grouping set语法进行sql合并能有效减少hive任务执行时间，同时以上9个sql占用的集群资源基本相当，优化后会大量减少集群资源的消耗。之后会测试数仓批量任务执行优化前后的时间对比，进行更全面的对比。

wangfann
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
GBase 8a OLAP函数group by grouping sets的使用样例
jingjing1068的博客
05-30 866
GBase 8a的group grouping sets功能,对后面括号里的 n 个字段或表达式分别做 GROUP BY 操作，最后将结果合并在一起。等同于多个单独的group by后再做一个union all操作。 语法 GROUP BY GROUPING SETS( (…),(…),…) 说明 GROUP BY GROUPING SETS (A,B,C) (A、B、C 代表语法中的“(…)”) 首先对(A)进行 GROUP BY，然后对(B)进行 GROUP BY，然后对©进行 GROUP BY，最后
一文看懂hive中group by rollup/cube group by sets用法及区别（附案例）
Yuki的博客
11-17 5787
一文看懂group by rollup/cube group by sets用法及区别（附案例）
善用Grouping Sets 提升代码效率
王义凯 的博客
09-14 930
使用Grouping Sets可按照多个不同的维度组合进行聚合，减少了繁琐的代码，提升整体计算的效率。 比如，需要对国家，省份，城市各维度进行聚合时候，可能会这么写： select '国家' as gep_type,country as geo_name,count(*) as cnt from tbl group by country union all select '省份' as gep_type,province as geo_name,count(*) as cnt from tbl gr
【SQL常用语法】grouping sets, partition by
yangfeiwuqing的博客
07-17 327
1. grouping sets 参考：https://www.yiibai.com/sql/sql-grouping-sets.html 使用场景：在同一查询中定义多个分组集 例：如下为inventory表，希望从中查询分别以(warehouse,product),(warehouse),(product)分组的情况 SELECT * FROM inventory; +---------------+---------+----------+----------+ | warehouse
hive、spark、presto 中的增强聚合-grouping sets、rollup、cube
向热爱自己一样，热爱这个世界。
09-19 1349
如何在 hive、spark、presto 中使用增强聚合-grouping sets、rollup、cube
Group by后加rollup、cube、grouping sets的用法和区别
xiaobai_laobai的博客
09-03 2478
Group by后加rollup、cube、grouping sets的用法和区别 一、相关分析 通常当聚合率和数据量没有大于一定程度时，对于不涉及Rollup、Cube、Grouping_Sets这三种操作的聚合很少出现GC问题。对于Rollup、Cube、Grouping_Sets操作可采用如下优化方法避免GC。 Rollup / Cube / Grouping_Sets时，某些场景下，如果多维度的字段比较多，内存或者GC会造成性能问题。特别的， 在实现这三种操作 时， 记录数会出现倍数的膨胀， 调优的
SQL使用技巧(2)查询结果的分层汇总
LMTX069的博客
10-17 2618
查询结果的分层汇总cube() rollup() grouping set()
mysql cube用法_Group by后加rollup、cube、Grouping_Sets的用法区别
weixin_28406969的博客
01-27 1033
一、相关分析通常当聚合率和数据量没有大于一定程度时，对于不涉及Rollup、Cube、Grouping_Sets这三种操作的聚合很少出现GC问题。对于Rollup、Cube、Grouping_Sets操作可采用如下优化方法避免GC。1、Rollup / Cube / Grouping_Sets时，某些场景下，如果多维度的字段比较多，内存或者GC会造成性能问题。特别的， 在实现这三种操作 时， 记录...
flink1.13 sql基础语法（一）DDL、DML
qq_44665283的博客
07-02 2241
flink1.13 sql基础语法（一）DDL、DML
Oracle中用GROUPING SETS分组自定义汇总
03-03
为了获取多级或自定义的汇总，我们可以使用`GROUPING SETS`功能，它提供了更灵活的分组方式，同时避免了不必要的计算，提高了SQL查询的效率。 `GROUP BY GROUPING SETS`允许我们自定义想要的汇总级别，从而得到特定...
sql学习 grouping sets 之另一需求展现.sql
02-11
sql学习 grouping sets 之另一需求展现.sql
SQLServer中汇总功能的使用GROUPING,ROLLUP和CUBE
09-11
然而，GROUP BY还可以与GROUPING、ROLLUP和CUBE运算符结合使用，以实现更复杂的多维度汇总功能。这些高级的汇总方法在数据分析和报表生成中非常有用，尤其是在处理大型数据集时。 GROUPING运算符主要用于识别汇总行...
PGConf.CN2019大会资料 培训PPT--5-陈金豹--Grouping Sets
07-18
### Greenplum中的Grouping Sets实现 #### Greenplum简介 Greenplum是Pivotal公司的一款基于MPP（Massively Parallel Processing）架构的数据仓库产品，它能够在大规模数据集上提供快速的数据分析能力。Greenplom...
SQL FOREIGN KEY 约束
m0_50736744的博客
08-16 206
SQL FOREIGN KEY 约束一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY(唯一约束的键)。
Python中使用SQLModel操作MySQL数据库的详细教程，附多个实战案例及其完整源码
最新发布
Python私教
08-16 83
【代码】Python中使用SQLModel操作MySQL数据库的详细教程，附多个实战案例及其完整源码。
【sql】MySQL 中的数值格式化函数：FORMAT、ROUND 和 TRUNCATE
longforone的博客
08-15 336
FORMAT。
4.1 SQL的起源与发展
在路上的专栏
08-16 501
SQL（结构化查询语言）是数据库管理的基石，自20世纪70年代诞生以来，它不断发展和演化，成为全球最广泛使用的数据查询语言。本文将通过生动的故事和易于理解的语言，带你了解SQL的起源、发展历程及其对现代数据库管理的深远影响。文章内容既适合数据库新手，也对专业人士具有参考价值。
【PGCCC】pg_bestmatch.rs：使用 BM25 提升您的 PostgreSQL 文本查询#PCA
PGCCC的博客
08-16 223
BM25（最佳匹配 25）是信息检索中使用的概率排名函数，用于评估文档与查询的匹配程度。它根据词频 (TF) 和逆文档频率 (IDF) 计算相关性分数，并通过文档长度规范化来平衡这些分数。该公式可确保在文档中频繁出现的术语 (TF) 和在语料库中罕见的术语 (IDF) 具有适当的权重，从而提高搜索的准确性和相关性。BM25 在众多RAG 基准测试任务中已被证明优于基于密集向量的检索方法。
grouping 和grouping sets结合使用的语法
09-09
使用GROUP BY子句来对查询结果进行分组，使用GROUPING SETS子句来指定多个分组集。 语法如下： ``` SELECT column1, column2, ..., aggregate_function(column) FROM table_name WHERE condition GROUP BY ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值