代码编织梦想

摘要:

分析aggregate的场景.

集合函数对值集进行操作。它们通常与GROUP BY子句一起使用以将值分组为子集。

grouy by说明:

GROUP BY子句允许使用WITH ROLLUP修饰符,该修饰符使摘要输出包括代 table 更高级别(即超级聚合)摘要操作的额外行。 ROLLUP从而使您可以通过单个查询回答多个分析级别的问题。例如,ROLLUP可用于提供对 OLAP(在线分析处理)操作的支持。

如果有多个GROUP BY列,则ROLLUP的作用更为复杂。在这种情况下,每当除最后一个分组列之外的任何其他列中的值发生变化时,查询都会产生一个额外的超级汇总摘要行。

SQL-92 和更早版本不允许选择列 table,HAVING条件或ORDER BY列 table 引用未在GROUP BY子句中命名的未聚合列的查询。例如,此查询在标准 SQL-92 中是非法的,因为选择列 table 中未聚合的name列未出现在GROUP BY中。

为了使查询在 SQL-92 中合法,必须从选择列 table 中省略name列或在GROUP BY子句中命名。

SQL:1999 和更高版本允许在功能上依赖于GROUP BY列的每个非可选功能 T301 此类非聚合:如果namecustid之间存在这种关系,则查询合法。例如,custidcustomers的主键就是这种情况。

MySQL 5.7.5 及更高版本实现对功能依赖性的检测。如果启用了ONLY_FULL_GROUP_BY SQL 模式(默认情况下是默认设置),则 MySQL 拒绝选择列 table,HAVING条件或ORDER BY列 table 引用未在GROUP BY子句中命名且在功能上不依赖于它们的未聚合列的查询。 (在 5.7.5 之前,MySQL 不检测功能依赖关系,默认情况下未启用ONLY_FULL_GROUP_BY。有关 5.7.5 之前的行为的说明,请参见MySQL 5.6 参考手册。)

启用ONLY_FULL_GROUP_BY SQL 模式时,MySQL 5.7.5 和更高版本还允许在GROUP BY子句中未命名的非聚合列,但前提是该列限于单个值

DDL:

表结构:

mysql> desc part;
+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| p_partkey     | int(11)       | NO   | PRI | NULL    |       |
| p_name        | varchar(55)   | NO   |     | NULL    |       |
| p_mfgr        | char(25)      | NO   |     | NULL    |       |
| p_brand       | char(10)      | NO   |     | NULL    |       |
| p_type        | varchar(25)   | NO   |     | NULL    |       |
| p_size        | int(11)       | NO   |     | NULL    |       |
| p_container   | char(10)      | NO   |     | NULL    |       |
| p_retailprice | decimal(15,2) | NO   |     | NULL    |       |
| p_comment     | varchar(23)   | NO   |     | NULL    |       |
+---------------+---------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

原始的包含子查询及聚合的语句:

select
	p_brand,
	p_type,
	p_size,
	count(distinct ps_suppkey) as supplier_cnt
from
	partsupp,
	part
where
	p_partkey = ps_partkey
	and p_brand <> 'Brand#45'
	and p_type not like 'MEDIUM POLISHED%'
	and p_size in (49,14,23,45,19,3,36,9)
	and ps_suppkey not in (
	select
		s_suppkey
	from
		supplier
	where
		s_comment like '%Customer%Complaints%' )
group by
	p_brand,
	p_type,
	p_size
order by
	supplier_cnt desc,
	p_brand,
	p_type,
	p_size;

聚合查询语句:

select
	p_brand,
	p_type,
	p_size,
	count(*)
from
	part
group by
	p_brand,
	p_type,
	p_size
limit 10;

执行分析:

explain分析:

mysql> explain select
    -> p_brand,
    -> p_type,
    -> p_size,
    -> count(*)
    -> from
    -> part
    -> group by
    -> p_brand,
    -> p_type,
    -> p_size
    -> limit 10 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: part
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2000000
     filtered: 100.00
        Extra: Using temporary; Using filesort
1 row in set, 1 warning (0.00 sec)

调用堆栈:

(gdb) bt
#0  Tianmu::core::AggregationAlgorithm::Aggregate (this=0x7fe9845ed580, just_distinct=false, limit=@0x7fe9845ed600: 10, offset=@0x7fe9845ed608: 0, sender=0x7fe9544690b0)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-131-20220805/storage/tianmu/core/aggregation_algorithm.cpp:39
#1  0x0000000002dee8ea in Tianmu::core::TempTable::Materialize (this=0x7fe954464ef0, in_subq=false, sender=0x7fe9544690b0, lazy=false)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-131-20220805/storage/tianmu/core/temp_table.cpp:1972
#2  0x0000000002d36ec0 in Tianmu::core::Engine::Execute (this=0x76351d0, thd=0x7fe954037ae0, lex=0x7fe954039e08, result_output=0x7fe9540196d8, unit_for_union=0x0)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-131-20220805/storage/tianmu/core/engine_execute.cpp:426
#3  0x0000000002d36062 in Tianmu::core::Engine::HandleSelect (this=0x76351d0, thd=0x7fe954037ae0, lex=0x7fe954039e08, result=@0x7fe9845edd18: 0x7fe9540196d8, setup_tables_done_option=0, 
    res=@0x7fe9845edd14: 0, optimize_after_tianmu=@0x7fe9845edd0c: 1, tianmu_free_join=@0x7fe9845edd10: 1, with_insert=0)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-131-20220805/storage/tianmu/core/engine_execute.cpp:232
#4  0x0000000002e1e8f3 in Tianmu::dbhandler::TIANMU_HandleSelect (thd=0x7fe954037ae0, lex=0x7fe954039e08, result=@0x7fe9845edd18: 0x7fe9540196d8, setup_tables_done_option=0, res=@0x7fe9845edd14: 0, 
    optimize_after_tianmu=@0x7fe9845edd0c: 1, tianmu_free_join=@0x7fe9845edd10: 1, with_insert=0)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-131-20220805/storage/tianmu/handler/ha_rcengine.cpp:82
#5  0x000000000246221a in execute_sqlcom_select (thd=0x7fe954037ae0, all_tables=0x7fe95445cdf0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-131-20220805/sql/sql_parse.cc:5182
#6  0x000000000245b59e in mysql_execute_command (thd=0x7fe954037ae0, first_level=true) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-131-20220805/sql/sql_parse.cc:2831
#7  0x00000000024631e3 in mysql_parse (thd=0x7fe954037ae0, parser_state=0x7fe9845eeeb0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-131-20220805/sql/sql_parse.cc:5621
#8  0x000000000245847b in dispatch_command (thd=0x7fe954037ae0, com_data=0x7fe9845ef650, command=COM_QUERY) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-131-20220805/sql/sql_parse.cc:1495
#9  0x00000000024573a7 in do_command (thd=0x7fe954037ae0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-131-20220805/sql/sql_parse.cc:1034
#10 0x0000000002589f7d in handle_connection (arg=0x7630cb0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-131-20220805/sql/conn_handler/connection_handler_per_thread.cc:313
#11 0x0000000002c6dbae in pfs_spawn_thread (arg=0x874bff0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-131-20220805/storage/perfschema/pfs.cc:2197
#12 0x00007fe9e02d5ea5 in start_thread () from /lib64/libpthread.so.0
#13 0x00007fe9de70cb0d in clone () from /lib64/libc.so.6

执行结果:

mysql> select
    -> p_brand,
    -> p_type,
    -> p_size,
    -> count(*)
    -> from
    -> part
    -> group by
    -> p_brand,
    -> p_type,
    -> p_size
    -> limit 10;
+----------+------------------------+--------+----------+
| p_brand  | p_type                 | p_size | count(*) |
+----------+------------------------+--------+----------+
| Brand#15 | ECONOMY POLISHED BRASS |     31 |       16 |
| Brand#55 | SMALL PLATED STEEL     |     38 |       11 |
| Brand#55 | LARGE POLISHED BRASS   |     14 |        8 |
| Brand#11 | LARGE POLISHED COPPER  |     29 |       14 |
| Brand#25 | LARGE POLISHED COPPER  |     20 |       12 |
| Brand#11 | PROMO PLATED TIN       |      2 |       15 |
| Brand#13 | MEDIUM BURNISHED TIN   |     31 |       11 |
| Brand#54 | PROMO ANODIZED COPPER  |     27 |       12 |
| Brand#33 | PROMO BRUSHED BRASS    |     14 |       17 |
| Brand#42 | LARGE PLATED COPPER    |     50 |       14 |
+----------+------------------------+--------+----------+
10 rows in set (2.89 sec)

将聚合等价转换为条件联合判断场景:

等价的条件判断:

select
	p_brand,
	p_type,
	p_size,
	p_container,
	p_comment
from
	part
where
	p_brand = 'Brand#15'
	and p_type = 'ECONOMY POLISHED BRASS'
	and p_size = 31 ;

执行结果:

mysql> select
    -> p_brand,
    -> p_type,
    -> p_size,
    -> p_container,
    -> p_comment
    -> from
    -> part
    -> where
    -> p_brand = 'Brand#15'
    -> and p_type = 'ECONOMY POLISHED BRASS'
    -> and p_size = 31 ;
+----------+------------------------+--------+-------------+------------------------+
| p_brand  | p_type                 | p_size | p_container | p_comment              |
+----------+------------------------+--------+-------------+------------------------+
| Brand#15 | ECONOMY POLISHED BRASS |     31 | LG PKG      | leep qu                |
| Brand#15 | ECONOMY POLISHED BRASS |     31 | JUMBO BOX   | lly reg                |
| Brand#15 | ECONOMY POLISHED BRASS |     31 | WRAP DRUM   | bout the final,        |
| Brand#15 | ECONOMY POLISHED BRASS |     31 | SM PKG      | l theodolites. even fr |
| Brand#15 | ECONOMY POLISHED BRASS |     31 | WRAP DRUM   |  packag                |
| Brand#15 | ECONOMY POLISHED BRASS |     31 | JUMBO PKG   |  blithely regular depo |
| Brand#15 | ECONOMY POLISHED BRASS |     31 | SM PKG      | ccount                 |
| Brand#15 | ECONOMY POLISHED BRASS |     31 | WRAP CAN    |  deposits haggle fin   |
| Brand#15 | ECONOMY POLISHED BRASS |     31 | MED CASE    | sides the ironic,      |
| Brand#15 | ECONOMY POLISHED BRASS |     31 | JUMBO PACK  | xes. c                 |
| Brand#15 | ECONOMY POLISHED BRASS |     31 | MED JAR     | ide of the pack        |
| Brand#15 | ECONOMY POLISHED BRASS |     31 | JUMBO PKG   | s about the care       |
| Brand#15 | ECONOMY POLISHED BRASS |     31 | SM DRUM     | ong the                |
| Brand#15 | ECONOMY POLISHED BRASS |     31 | WRAP CAN    | e qui                  |
| Brand#15 | ECONOMY POLISHED BRASS |     31 | SM BOX      | ages wake final        |
| Brand#15 | ECONOMY POLISHED BRASS |     31 | WRAP BOX    | platelets.             |
+----------+------------------------+--------+-------------+------------------------+
16 rows in set (0.03 sec)

版权声明:本文为博主原创文章,遵循 CC 4.0 BY 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/adofsauron/article/details/126404389

nvidia-smi系列命令总结_michael丶bear的博客-爱代码爱编程_nvidia-smi

1  NVIDIA-SMI介绍         nvidia-smi简称NVSMI,提供监控GPU使用情况和更改GPU状态的功能,是一个跨平台工具,它支持所有标准的NVIDIA驱动程序支持的Linux发行版以及从WindowsServer 2008 R2开始的64位的系统。该工具是N卡驱动附带的,只要安装好驱动后就会有它。     Windows下程序

java-mongo复杂管道聚合aggregate的填坑之路(分页、allowdiskuse、统计)_小红_su的博客-爱代码爱编程_allowdiskuse

因项目需要,要对mongodb中的数据,做排序再做group还要做总数统计还要对结果筛选,而且数据量又是百万级别的,看了整整一天的spring-data-mongo的源码、mongo-driver的源码、还逛了大半天国外论坛,总算是把功能搞出来了,在此做一下笔记。 一、遇到的坑 1、对大数据量的东西,首先实现起来还要考虑性能考虑内存

mysql查询优化:group by_resemble_的博客-爱代码爱编程

目录 一、group by group by 优化方法 — 索引 松散索引扫描(Loose Index Scan) 为什么松散索引扫描的效率会很高? 紧凑索引扫描(Tight Index Scan) group by 优化方法 — 直接排序 二、group by 与 distinct 三、排序不一致问题 一、group by 当我们执

【clickhouse系列】clickhouse-docker安装和使用_sky_geek2008的博客-爱代码爱编程

实践过程中参考了别人的博客 【博客1】 【博客2】 但是写的都不是很详细,有很多坑都是需要自己去踩的。 1、docker 安装方式介绍(其他方式安装自行百度)----docker没玩过的同学,请自行百度!(如果有

springboot-noclassdeffounderror: org/eclipse/jetty/server/session/sessiondatastore-爱代码爱编程

Springboot-NoClassDefFoundError: org/eclipse/jetty/server/session/SessionDataStore 在使用Springboot,部署到外部tomcat时报了

mysql groupby 索引问题__内啥玩意啊的博客-爱代码爱编程_groupby 索引

今天执行下面的语句,发现没有走索引: SELECT * FROM `a` LEFT JOIN `b` ON `a`.`log_id` = `b`.`id` LEFT JOIN `c` ON `a`.`another

mysql aggregate_MySQL-滥用聚合函数(MySQL- Misuse of aggregate function)-爱代码爱编程

MySQL-滥用聚合函数(MySQL- Misuse of aggregate function) 我一直在尝试运行以下命令: select s.name, s.nr from sub s group by s.name having (select count(s.name) from sub s group by s.name) >

Canal-adapter实时增量同步Mysql数据到Doris-爱代码爱编程

一.版本介绍: canal.admin :1.1.4 canal.deployer :1.1.4 canal.adapter :1.1.4 Doris :0.14/0.15 三.多源配置: 1.部署Canal 不做详细介绍,可以参考官网部署。地址:https://github.com/alibaba/canal 2.部署Doris Apa

论文阅读 [TPAMI-2022] Average Top-k Aggregate Loss for Supervised Learning-爱代码爱编程

论文阅读 [TPAMI-2022] Average Top-k Aggregate Loss for Supervised Learning 论文搜索(studyai.com) 搜索论文: Average Top-k Aggregate Loss for Supervised Learning 搜索论文: http://www.studyai.com