摘要:
分析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 此类非聚合：如果name和custid之间存在这种关系，则查询合法。例如，custid是customers的主键就是这种情况。
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。)
启用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_: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_: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_: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_:1495
#9 0x00000000024573a7 in do_command (thd=0x7fe954037ae0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-131-20220805/sql/sql_: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_:313
#11 0x0000000002c6dbae in pfs_spawn_thread (arg=0x874bff0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-131-20220805/storage/perfschema/: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)
