テーブルに自動採番(オートインクリメント)する列を作成する

今回もDBの話です。DBはMySQLやその互換のAuroraを想定しています。
テーブルに行を追加していくとき、自動的に通し番号でIDをふって欲しいことはよくあります。
MySQLにはそれ専用の、AUTO_INCREMENT属性があります。

参考: 3.6.9 AUTO_INCREMENT の使用

さて、CREATE TABLEするときに列名の横に、 AUTO_INCREMENT をつけるだけで使えるように見えますが、それだとエラーになります。


> CREATE TABLE
    sample_table1 (
        id INTEGER AUTO_INCREMENT,
        name VARCHAR(255)
    );
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

it must be defined as a key とある通り、主キーかユニークキーを設定した列でないと、AUTO_INCREMENT属性は設定できません。
ついでに説明しておくと、 there can be only one auto columnとある通り、1テーブルに設定できるのは1列までです。


> CREATE TABLE
    sample_table1 (
        id INTEGER PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(255)
    );
Query OK, 0 rows affected (0.06 sec)

さて、これで連番が振られることを見ておきましょう。


> INSERT INTO 
    sample_table1 (name)
VALUES
   ('suzuki'), ('sato'), ('tanaka');
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

> SELECT * FROM sample_table1;
+----+--------+
| id | name   |
+----+--------+
|  1 | suzuki |
|  2 | sato   |
|  3 | tanaka |
+----+--------+
3 rows in set (0.01 sec)

連番が振られていますね。

僕はこれ、列の最大値の次の値が採番されていくと勘違いしていたのですが、実はAUTO_INCREMENTで振られる番号は別のところに保存されています。


> SHOW CREATE TABLE sample_table1\G
*************************** 1. row ***************************
       Table: sample_table1
Create Table: CREATE TABLE `sample_table1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.01 sec)

AUTO_INCREMENT=4 と入っているように、テーブルが属性として次の番号を持っています。
そのため、今入っているレコードを消して、新たに行を追加すると、4番から採番されます。


> DELETE FROM sample_table1 WHERE id >= 2;
Query OK, 2 rows affected (0.02 sec)

> INSERT INTO 
    sample_table1 (name)
VALUES
   ('kobayashi'), ('ito'), ('takahashi');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

> SELECT * FROM sample_table1;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | suzuki    |
|  4 | kobayashi |
|  5 | ito       |
|  6 | takahashi |
+----+-----------+
4 rows in set (0.01 sec)

ユニーク性を担保する上では便利な仕組みですね。

テーブルの属性としてカウンタを持っているので、 ALTER TABLE 文を使ってリセットしたり、任意の値に設定したりできます。
構文はこうです。


ALTER TABLE [テーブル名] AUTO_INCREMENT = [値];

やってみます。


> ALTER TABLE sample_table1 AUTO_INCREMENT = 100;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

> INSERT INTO 
    sample_table1 (name)
VALUES
   ('watanabe');
Query OK, 1 row affected (0.01 sec)

> SELECT * FROM sample_table1;
+-----+-----------+
| id  | name      |
+-----+-----------+
|   1 | suzuki    |
|   4 | kobayashi |
|   5 | ito       |
|   6 | takahashi |
| 100 | watanabe  |
+-----+-----------+
5 rows in set (0.00 sec)

予想通りに動きました。

MySQLでテーブルに主キー(PRIMARY KEY)制約を設定する

主キー(プライマリーキー)の説明自体はWikipediaを参照。
参考: 主キー – Wikipedia

これをSQLで設定する方法について紹介します。
(なお、DBは MySQL やその互換のAuroraを想定しています。)

新規に作成するテーブルに設定するときは次の構文で設定できます。
参考: 13.1.7 ALTER TABLE 構文


CREATE TABLE
    [テーブル名] (
        [主キーの列名] [データ型] NOT NULL PRIMARY KEY,
        [列名1] [データ型],
        ・・・
    );

NOT NULL は省略可能です。省略しても主キーには自動的にNOT NULL制約が付与されます。

やってみましょう。


>CREATE TABLE
    sample_table1 (
        id INTEGER PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        comment VARCHAR(255)
    );
Query OK, 0 rows affected (0.19 sec)

# 確認
> DESCRIBE sample_table1;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(255) | NO   |     | NULL    |       |
| comment | varchar(255) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

NOT NULL は省略しましたが、DESCRIBE の結果でわかる通り、id列にNOT NULL制約が入っていますね。

複数列の組み合わせで主キーを設定する場合は、次のように
PRIMARY KEY ([列名], [列名]) と言うのを書いても設定できます。

構文のイメージはこうです。もちろんですが、複数列の組み合わせでなく1列に対して設定する時もこの構文は使えます。


CREATE TABLE
    [テーブル名] (
        [主キーの列名1] [データ型] NOT NULL,
        [主キーの列名2] [データ型] NOT NULL,
        [列名1] [データ型],
        ・・・,
        PRIMARY KEY ([主キーの列名1], [主キーの列名2])
    );

例えば、 コード と 日付 に対してユニークなレコードを持つテーブルを作る場合は次のようになります。


>CREATE TABLE
    sample_table2 (
        code INTEGER,
        date DATE,
        value INTEGER,
        PRIMARY KEY (code, date)
    );
Query OK, 0 rows affected (0.07 sec)

> DESCRIBE sample_table2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| code  | int(11) | NO   | PRI | NULL    |       |
| date  | date    | NO   | PRI | NULL    |       |
| value | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

主キーを設定した列に対しては自動的にインデックスが作成されます。(インデックスの名前はPRIMARYになります)。
これも一応みておきましょう。


> SHOW INDEX FROM sample_table2\G
*************************** 1. row ***************************
        Table: sample_table2
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: code
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: sample_table2
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 2
  Column_name: date
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.00 sec)

あまり使わないのですが、設定されている主キーを外すときは、 ALTER TABLE 文を使います。

構文は次の通りです。
参考: 13.1.7 ALTER TABLE 構文


ALTER TABLE [テーブル名] DROP PRIMARY KEY;

やってみます。


> ALTER TABLE sample_table2 DROP PRIMARY KEY;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 結果確認
> DESCRIBE sample_table2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| code  | int(11) | NO   |     | NULL    |       |
| date  | date    | NO   |     | NULL    |       |
| value | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

# インデックスも消える
> SHOW INDEX FROM sample_table2\G
Empty set (0.00 sec)

インデックスは一緒に消えてしまいますが、 NOT NULL制約は残りましたね。

主キーが設定されてないテーブルに後から設定するのも ALTER TABLE 文を使います。


ALTER TABLE [テーブル名] ADD PRIMARY KEY ([列名]);

やってみます。(さっき消したキーを復活させます。)


> ALTER TABLE sample_table2 ADD PRIMARY KEY (code, date);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 結果確認
> DESCRIBE sample_table2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| code  | int(11) | NO   | PRI | NULL    |       |
| date  | date    | NO   | PRI | NULL    |       |
| value | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

MySQLのテーブルにレコード作成時刻や更新時刻を自動で記録する列を作る

たまに新しいテーブルを作るたびに調べてるのでメモです。
(タイトルにMySQLと書いてますが、実際はそれと互換のAuroraでやってます。)

DBのテーブルに新しい行を追加したり、更新したりするときにその時刻を記録する列を用意しておくと言うのは結構一般的なことだと思います。
phpMyAdmin からGUIでポチポチ設定してもいいのですが、SQLでもできた方が楽です。

さて、その方法ですが、MySQLのドキュメントにそのまま書いてあります。
11.3.5 TIMESTAMP および DATETIME の自動初期化および更新機能

DEFAULT CURRENT_TIMESTAMP で、行作成時にタイムスタンプが記録され、
ON UPDATE CURRENT_TIMESTAMP で、行更新時にタイムスタンプも更新されます。

列名として、 created_at, updated_at を使う場合は次のようにすれば良いでしょう。
例えば、タイムスタンプの他にidとnameを格納するテーブルを作るとしたら次のようになります。
(ついでに NOT NULL制約も入れました。)


CREATE TABLE
    sample_table (
        id INTEGER,
        name VARCHAR(255),
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    );

さて、試してみましょう。まず作成時刻から。


INSERT INTO
    sample_table(id, name)
VALUES
    (1, 'テスト1'),
    (2, 'テスト2'),
    (3, 'テスト3')
;

SELECT * FROM sample_table;
+------+------------+---------------------+---------------------+
| id   | name       | created_at          | updated_at          |
+------+------------+---------------------+---------------------+
|    1 | テスト1    | 2021-04-04 23:36:29 | 2021-04-04 23:36:29 |
|    2 | テスト2    | 2021-04-04 23:36:29 | 2021-04-04 23:36:29 |
|    3 | テスト3    | 2021-04-04 23:36:29 | 2021-04-04 23:36:29 |
+------+------------+---------------------+---------------------+
3 rows in set (0.00 sec)

時刻が勝手に入りましたね。

次に更新です。


UPDATE
    sample_table
SET
    name = 'test 2' 
WHERE
    id = 2
;

SELECT * FROM sample_table;
+------+------------+---------------------+---------------------+
| id   | name       | created_at          | updated_at          |
+------+------------+---------------------+---------------------+
|    1 | テスト1    | 2021-04-04 23:36:29 | 2021-04-04 23:36:29 |
|    2 | test 2     | 2021-04-04 23:36:29 | 2021-04-04 23:38:26 |
|    3 | テスト3    | 2021-04-04 23:36:29 | 2021-04-04 23:36:29 |
+------+------------+---------------------+---------------------+
3 rows in set (0.01 sec)

id = 2 のレコードの updated_at も更新されました。

SQLでデータベースやテーブルの文字コードや照合順序の設定を変える

前回の記事でRDSのクラスタ単位の文字コードや照合順序をutf8mb4に対応させる方法を書きましたが、
後から設定を変えても既存のデータベースやテーブルの設定はそのまま変わりません。

変更する場合はそれぞれ ALTER 文を実行して変更していく必要があります。

まず、テーブルの設定を変更する場合は、次の構文を使います。

ALTER TABLE tbl_name
CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];
(参考: 13.1.7 ALTER TABLE 構文)


-- 元の設定を確認
MySQL [sample_db]> SHOW CREATE TABLE sample_table\G
*************************** 1. row ***************************
       Table: sample_table
Create Table: CREATE TABLE `sample_table` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `text` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

-- 設定を変更
MySQL [sample_db]> ALTER TABLE sample_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 変更結果を確認
MySQL [sample_db]> SHOW CREATE TABLE sample_table\G
*************************** 1. row ***************************
       Table: sample_table
Create Table: CREATE TABLE `sample_table` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `text` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.01 sec)

ちなみに、 “CONVERT TO” をつけ忘れると、テーブル全体のデフォルト設定だけ変更されて、列の設定が元のままになります。
(列ごとに、個別の文字コード設定を行いたい場合はこちらの方法を使うことになりそうです。)


-- 元の設定を確認
MySQL [sample_db]> SHOW CREATE TABLE sample_table2\G
*************************** 1. row ***************************
       Table: sample_table2
Create Table: CREATE TABLE `sample_table2` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `text` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

-- 設定を変更 (CONVERT TO 無し)
MySQL [sample_db]> ALTER TABLE sample_table2 CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 変更結果を確認
MySQL [sample_db]> SHOW CREATE TABLE sample_table2\G
*************************** 1. row ***************************
       Table: sample_table2
Create Table: CREATE TABLE `sample_table2` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `text` varchar(255) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

各列のCHARACTERが latin1 のままですね。

続いて、データベースのデフォルトの文字コードと照合順序の設定の変更方法です。
新規作成したテーブルはデータベースのデフォルト設定を引き継ぐので、多くのテーブルを作る場合は、最初にデータベース単位で設定しておくべきでしょう。

DATABSE単位の設定は文字コード周りくらいしかないので、 ALTER DATABASE のドキュメントもシンプルです。

参考: 13.1.1 ALTER DATABASE 構文

ALTER DATABASE sample_db CHARACTER SET utf8mb4 COLLATE utf8mb4_bin


-- 元の設定を確認
MySQL [sample_db]> SHOW CREATE DATABASE sample_db;
+-----------+----------------------------------------------------------------------+
| Database  | Create Database                                                      |
+-----------+----------------------------------------------------------------------+
| sample_db | CREATE DATABASE `sample_db` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+-----------+----------------------------------------------------------------------+
1 row in set (0.00 sec)

-- 設定を変更 
MySQL [sample_db]> ALTER DATABASE sample_db CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
Query OK, 1 row affected (0.03 sec)

-- 変更結果を確認
MySQL [sample_db]> SHOW CREATE DATABASE sample_db;
+-----------+-------------------------------------------------------------------------------------------+
| Database  | Create Database                                                                           |
+-----------+-------------------------------------------------------------------------------------------+
| sample_db | CREATE DATABASE `sample_db` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */ |
+-----------+-------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

PrestoのUNNESTを利用した横縦変換

以前Prestoのクエリで縦横変換(pivot)を行う方法を初回しましたが、今回はその逆で横縦変換(unpivot)を紹介します。

参考: PrestoのMap型を使った縦横変換

参考記事と逆の変換やるわけですね。
そのため元のテーブルがこちら。

横持ちのテーブル
uid c1 c2 c3
101 11 12 13
102 21 22 23

結果として出力したいテーブルがこちらになります。

縦持ちのテーブル (vtable)
uid key value
101 c1 11
101 c2 12
101 c3 13
102 c1 21
102 c2 22
102 c3 23

一番シンプルな書き方は、UNIONを使う方法だと思います。
key の値ごとにvalue を抽出してそれぞの結果を縦に積み上げます。


SELECT
    uid,
    'c1' AS key,
    c1 AS value
FROM
    htable
UNION ALL SELECT
    uid,
    'c2' AS key,
    c2 AS value
FROM
    htable
UNION ALL SELECT
    uid,
    'c3' AS key,
    c3 AS value
FROM
    htable

ただ、この書き方には課題もあって、元の列数が多いとクエリが非常に冗長になります。
そこで、 UNNESTを使った方法を紹介しておきます。

ドキュメントは SELECT の説明のページの途中に UNNESTの章があります。

これを使うと次の様に書けます。


SELECT
    uid,
    t.key,
    t.value
FROM
    htable
CROSS JOIN UNNEST (
  array['c1', 'c2', 'c3'],
  array[c1, c2, c3]
) AS t (key, value)

とてもシンプルに書けました。

PrestoのMap型を使った縦横変換

前回の記事で紹介したPrestoのMap型ですが、これを使うとデータの縦横変換(ピボット)がスマートに行えます。

参考: ピボットテーブル&チャート

上記リンク先のトレジャーデータの記事中の画像のテーブルを例に説明します。

縦持ちのテーブル (vtable)
uid key value
101 c1 11
101 c2 12
101 c3 13
102 c1 21
102 c2 22
102 c3 23

このようなテーブルを次のように変換したいとします。

横持ちのテーブル
uid c1 c2 c3
101 11 12 13
102 21 22 23

この変換は、MAP_AGGを使って、key列とvalue列の対応のMapを一旦作成し、
それぞれのMapから各Keyの値を取り出すことで実現できます。
具体的にクエリにしたのが次です。


SELECT
    uid,
    kv['c1'] AS c1,
    kv['c2'] AS c2,
    kv['c3'] AS c3
FROM (
    SELECT
        uid,
        MAP_AGG(key, value) AS kv
    FROM
        vtable
    GROUP BY
        uid
) AS t

個人的には服問い合わせはあまり好きではなく、PrestoではWITHを使って書きたいので次のようにすることが多いです。


WITH
    t AS (
        SELECT
            uid,
            MAP_AGG(key, value) AS kv
        FROM
            vtable
        GROUP BY
            uid
    )
SELECT
    uid,
    kv['c1'] AS c1,
    kv['c2'] AS c2,
    kv['c3'] AS c3
FROM
    t

Prestoではない、(MAP型のない)通常のSQLでは次のように書かないといけないのですが、
上記のMap型を使ったものの方が随分すっきりかけているように見えます。
(MAP_AGGを知らない人には読めないのが難点ですが)


SELECT
    uid,
    MAX(
        CASE WHEN key = 'c1' THEN
            value
        ELSE
            NULL
        END
    ) AS c1,
    MAX(
        CASE WHEN key = 'c2' THEN
            value
        ELSE
            NULL
        END
    ) AS c2,
    MAX(
        CASE WHEN key = 'c3' THEN
            value
        ELSE
            NULL
        END
    ) AS c3
FROM
    vtable
GROUP BY
    uid

PrestoのMap型について

Prestoには Mapというデータ型があります。
これは Pythonのdictと似たようなデータ型で、 キーと値のペアで構成されるものです。

ドキュメントを見ると、
MAP(ARRAY['foo', 'bar'], ARRAY[1, 2]) というMAPを作る関数の例が紹介されていますが、実行すると次のような結果を得ることができます。


-- クエリ
SELECT
    MAP(ARRAY['foo', 'bar'], ARRAY[1, 2])

-- 以下結果
{"bar":2,"foo":1}

以前紹介した、 TD_PARSE_AGENT という関数がありますが、この結果もMapです。
参考: TreasureDataのTD_PARSE_AGENT関数が便利

Map から、キーをしていて値を取得するときは、上のTD_PARSE_AGENTの記事でやっているように、map に [‘key’]をつけて取得するか、
ELEMENT_AT(map(K, V), key) を使います。
個人的にはPythonなどと近い書き方の [ ] を使う方法の方が好きです。

そのほかの、Mapの使い方ですが、ドキュメントの
6.17. Map Functions and Operators
というページにまとまっているのでこちらがわかりやすいです。

さて、 Tableのある列の値を キーとし、別の列の値を値とするMapを作りたくなる場合があります。
そのようなときは、 MAP_AGG という関数が使えます。
ドキュメントでは別のページにあるので探しにくいのですが、こちらにあります。
6.14. Aggregate Functions
の Map Aggregate Functions。

キーに指定した列に重複があったらどうなるかが気になったのですが、
試してみたところ、バリューの列のどれかの値が何らかの規則で一つだけ選ばれて採用されるようです。
(どのような基準で選ばれているのかは結局わかりませんでした。使うときは注意が必要ですね。)

キーに重複があり、バリューを(配列の形で)全部残したいときは MULTIMAP_AGG が使えます。

MAP_AGG で MAPを作って、 map[key] で値にアクセスする、ということだけ覚えておけば、
特に問題なく使うことができると思います。

ROLLUPやCUBEで発生したNULLと、元々あったNULLを区別する

今回もPrestoの話題です。
最近の更新で、
ROLLUPやCUBE,GROUPING SETなどを使って、総計を出したり、複数の条件での集計をまとめて行う方法を紹介しましたが、
これらの操作を行うと、多くのNULL値が発生します。
ROLLUPであれば、値が入ってるのが個別の集計で、NULLになっている行の値が総計と判定できるのですが、
ここで元々その列にNULLがあると、見分けがつかず、面倒なことになります。

単なるイメージですが、こんな感じの結果が出ます。
|gender|cnt|
|男性|100|
|女性|200|
|NULL|50| # これは元々NULL
|NULL|350| # ROLLUPで発生したNULL

このような、元々あったNULLと集計によって発生したNULLを見分けるのに、
GROUPING という専用の関数が用意されています。
ドキュメントはこちらのページのGROUPING Operationを参照。

SELECT句の中で、 grouping(col1, …, colN) のように使い、整数値を返します。
結果の数値が少し癖があるのですが、引数の一番右側の列(つまりcolN)が1,
そこから順番に、colN-1が2, colN-2が4($=2^2$)と、順番にビットの桁が割り当てられ、
その列の値が、ROLLUPやCUBEによって発生したNULLになっているビットの和を返します。
(わかりにくいですね。)

会社のDWHに打った実クエリを貼るわけにいかないのでドキュメントで紹介されている例をそのまま転載します。


SELECT
    origin_state,
    origin_zip,
    destination_state,
    SUM(package_weight),
    GROUPING(
        origin_state,
        origin_zip,
        destination_state
    )
FROM
    shipping
GROUP BY
    GROUPING SETS (
        (origin_state),
        (origin_state, origin_zip),
        (destination_state)
    );

-- 結果
origin_state | origin_zip | destination_state | _col3 | _col4
--------------+------------+-------------------+-------+-------
California   | NULL       | NULL              |  1397 |     3
New Jersey   | NULL       | NULL              |   225 |     3
New York     | NULL       | NULL              |     3 |     3
California   |      94131 | NULL              |    60 |     1
New Jersey   |       7081 | NULL              |   225 |     1
California   |      90210 | NULL              |  1337 |     1
New York     |      10002 | NULL              |     3 |     1
NULL         | NULL       | New Jersey        |    58 |     6
NULL         | NULL       | Connecticut       |  1562 |     6
NULL         | NULL       | Colorado          |     5 |     6
(10 rows)

grouping(
origin_state,
origin_zip,
destination_state
)
と指定されている各行が順番に、4,2,1のビットに対応していて、
例えば、結果の3行目、New Yorkの行であれば、
origin_zipとdestination_stateの行がGROUPING SETSによって発生したNULLになっているので、
2+1で3となっています。

この性質を使って、例えば NULL を’合計’とか’小計’とかに書き換えて返すクエリを構築することができます。

Prestoで複数種類の集約をまとめて行う

今回もPrestoのクエリのテクニックです。
前回の記事で、ROLLUPを使って集約(GROUP BY)した値と総計を同時に計算する方法を紹介しましたが、
もっと柔軟に、いろいろな組み合わせで集約をしたい場面があります。
面倒なのでいつも個別のクエリで出力してUNIONしたりpandasで結合したりしていますが、
Prestoにはそのような時に使える構文として、GROUPING SETSというのが用意されています。
ドキュメントのクエリをそのまま紹介させていただきますが、
次のように書きます。


SELECT
    origin_state,
    origin_zip,
    destination_state,
    SUM(package_weight)
FROM
    shipping
GROUP BY
    GROUPING SETS (
        (origin_state),
        (origin_state, origin_zip),
        (destination_state)
    );

こうすると、origin_state で集約したpackage_weightの合計 (この時origin_zipと、destination_stateはNULL)、
origin_stateと origin_zip で集約したpackage_weightの合計 (この時destination_stateはNULL)、
destination_state で集約したpackage_weightの合計 (この時origin_stateと、origin_zipはNULL)、
がまとめて出力されます。


 origin_state | origin_zip | destination_state | _col0
--------------+------------+-------------------+-------
 New Jersey   | NULL       | NULL              |   225
 California   | NULL       | NULL              |  1397
 New York     | NULL       | NULL              |     3
 California   |      90210 | NULL              |  1337
 California   |      94131 | NULL              |    60
 New Jersey   |       7081 | NULL              |   225
 New York     |      10002 | NULL              |     3
 NULL         | NULL       | Colorado          |     5
 NULL         | NULL       | New Jersey        |    58
 NULL         | NULL       | Connecticut       |  1562
(10 rows)

GROUPING SETS の中に () を入れてやれば全部の合計も出せます。
個別にクエリを書いてUNION ALLでつなげるのに比べると、記述量を劇的に減らせますね。

さらに、いくつかの列について、全ての組み合わせで、GROUPING SETS を作りたい場合、CUBE という演算子が使えます。


SELECT
    origin_state,
    destination_state,
    SUM(package_weight)
FROM
    shipping
GROUP BY
    CUBE(
        origin_state,
        destination_state
    );

と、次のクエリは同じ意味です。


SELECT
    origin_state,
    destination_state,
    SUM(package_weight)
FROM
    shipping
GROUP BY
    GROUPING SETS (
        (origin_state, destination_state),
        (origin_state),
        (destination_state),
        ()
    );

CUBEの中に入れてる列が2つだとそうでもないですが、これが3列も4列もとなっていくとかなり記述量が変わってきます。

ROLLUPを使った合計の計算

(注意)prestoを前提とします。 MySQLにもROLLUPはありますが少し書き方が違うようです。

最近よく使うようになった、ROLLUPという文法の紹介です。
(これまではTableauかPythonで計算するか、どうしてもSQLで関係つさせたい時はUNIONして対応していた。)

SQLでGROUP BYを使って何か集計した時、それらの合計(や、全体の平均、カウントなど)も出したいという場面はよくあります。
パソコンとスマホとか、男性と女性とか、で集計して、同時に全体の数値も見たいという場合ですね。

そのような時に ROLLUP を使えます。
ドキュメントはこのページの中。
実データを出せないのでイメージになってしまうのですが、
例えば、userテーブルのレコード数をgender列の値別に数える場合、通常は、


SELECT
    gender,
    COUNT(*) AS cnt
FROM
    user
GROUP BY
    gender

とやって、

|gender|cnt|
|男性|100|
|女性|200|

のような結果を得ると思います。
ここに、合計も一緒に出したい場合、


SELECT
    gender,
    COUNT(*) AS cnt
FROM
    user
GROUP BY
    gender
UNION ALL SELECT
    NULL AS gender
    COUNT(*) AS cnt
FROM
    user

のように書くと一応算出できるのですが、ちょっと要領の悪い書き方になります。

これが、ROLLUPを使って、


SELECT
    gender,
    COUNT(*) AS cnt
FROM
    user
GROUP BY
    ROLLUP(gender)

とすると、
|gender|cnt|
|男性|100|
|女性|200|
|NULL|300|
のような結果を得ることができます。

さらに ROLLUP はカンマ区切りで複数列指定することもでき、
そうすると段階的に小計を出してくれます。(これは何か手頃のデータで試すのが一番良いです。)


SELECT
    gender,
    age,
    COUNT(*) AS cnt
FROM
    user
GROUP BY
    ROLLUP(gender, age)

とすると、出力は次のようになります。
|gender|age|cnt|
|男性|20|30|
|男性|30|70|
|男性|NULL|100|
|女性|20|80|
|女性|30|120|
|女性|NULL|200|
|NULL|NULL|300|

(年齢が20と30の二通りなんてデータもなかなか無いでしょうが、ただの例なのでご了承ください。)
慣れると便利なのでためしてみてください。