TL;DR
WITH ROLLUPの結果行をHAVING条件に書けるようすることができる。
それ以外の時には使わない。
使い方。
そもそも WITH ROLLUP の使い方を知らないと楽しくもなんともないので
WITH ROLLUP の説明から。
まずは
WITH ROLLUP なしバージョン（SUM関数を噛ませてるのはあとで WITH ROLLUP した時のため）mysql80> SELECT Continent, Name, SUM(Population) AS Population FROM country GROUP BY Continent, Name;
+---------------+----------------------------------------------+------------+
| Continent | Name | Population |
+---------------+----------------------------------------------+------------+
| North America | Aruba | 103000 |
| Asia | Afghanistan | 22720000 |
| Africa | Angola | 12878000 |
..
| Africa | South Africa | 40377000 |
| Africa | Zambia | 9169000 |
| Africa | Zimbabwe | 11669000 |
+---------------+----------------------------------------------+------------+
239 rows in set (0.00 sec)
おっと… GROUP BYが暗黙のソートをしなくなった件 を垣間見ることもできた。
5.7とそれ以前と出力結果を一緒にするためには、
ORDER BY Continent, Name も追加する必要がある。
ともあれ、こんなフツーの
GROUP BY なクエリーに WITH ROLLUP を足してやるとmysql80> SELECT Continent, Name, SUM(Population) AS Population FROM country GROUP BY Continent, Name WITH ROLLUP;
+---------------+----------------------------------------------+------------+
| Continent | Name | Population |
+---------------+----------------------------------------------+------------+
| Asia | Afghanistan | 22720000 |
| Asia | Armenia | 3520000 |
| Asia | Azerbaijan | 7734000 |
..
| Asia | Yemen | 18112000 |
| Asia | NULL | 3705025700 |
| Europe | Albania | 3401200 |
..
| Europe | Yugoslavia | 10640000 |
| Europe | NULL | 730074600 |
| North America | Anguilla | 8000 |
..
| South America | Venezuela | 24170000 |
| South America | NULL | 345780000 |
| NULL | NULL | 6078749450 |
+---------------+----------------------------------------------+------------+
247 rows in set (0.00 sec)
こうなる。
Continent単位で合計したものが
Name IS NULL として集約行が作られて、全てを合計した値で Continent IS NULL, Name IS NULL として集約行が作られる。
日々の覚書: information_schemaでちょこちょこ使えるTIPS とかで使うのが気持ちいい。
これ、NULLになったカラムはSQLの中から条件指定が不可能（WHEREはGROUP BYが処理される前のフィルターだし、HAVINGフィルターよりも更に後に集約行が作成されるのでダメらしい（ MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.19.2 GROUP BY 修飾子 ）
なので、この集約行にだけアクセスしたい場合（最初からそこでGROUP BYしろよとは思うけれどなんでなのか俺もやりたがった記憶がある）、アプリケーションの中で結果セットを受け取ってからカラムがNULLかどうかチェックして集約行判定をしなければならなかった。
たとえばこんな風に（
!(defined($_->{Continent})) && !(defined(_->{Name})) ）my $conn= DBI->connect("dbi:mysql:world;mysql_socket=/usr/mysql/8.0.1/data/mysql.sock", "root", "");
my $sql= "SELECT Continent, Name, SUM(Population) AS Population FROM country GROUP BY Continent, Name WITH ROLLUP";
foreach (@{$conn->selectall_arrayref($sql, {Slice => {}})})
{
print Dumper $_ if !(defined($_->{Continent})) && !(defined($_->{Name}));
}
=pod
$VAR1 = {
'Continent' => undef,
'Name' => undef,
'Population' => '6078749450'
};
=cut
で、これをHAVINGの中で言及できるようにする関数が GROUPING らしい。
mysql80> SELECT Continent, Name, SUM(Population) AS Population FROM country GROUP BY Continent, Name WITH ROLLUP HAVING GROUPING(Continent) AND GROUPING(Name);
+-----------+------+------------+
| Continent | Name | Population |
+-----------+------+------------+
| NULL | NULL | 6078749450 |
+-----------+------+------------+
1 row in set (0.00 sec)
mysql80> SELECT Continent, Name, SUM(Population) AS Population FROM country GROUP BY Continent, Name WITH ROLLUP HAVING GROUPING(Name);
+---------------+------+------------+
| Continent | Name | Population |
+---------------+------+------------+
| Asia | NULL | 3705025700 |
| Europe | NULL | 730074600 |
| North America | NULL | 482993000 |
| Africa | NULL | 784475000 |
| Oceania | NULL | 30401150 |
| Antarctica | NULL | 0 |
| South America | NULL | 345780000 |
| NULL | NULL | 6078749450 |
+---------------+------+------------+
8 rows in set (0.00 sec)
最初っからそこで GROUP BY しなよ感があるけれど、GROUPING が1か0を返すからORDER BYで集約行を先に持ってこられたらいいかな？ と思った。
が
mysql80> SELECT Continent, Name, SUM(Population) AS Population FROM country GROUP BY Continent, Name WITH ROLLUP HAVING GROUPING(Name) ORDER BY GROUPING(Name);
ERROR 1221 (HY000): Incorrect usage of CUBE/ROLLUP and ORDER BY
WITH ROLLUPとORDER BYが同時に使えないという制約があるので並べ替えには使えなかった。
残念。。
0 件のコメント :
コメントを投稿