カテゴリー: SQL

たまに新しいテーブルを作るたびに調べてるのでメモです。
(タイトルに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 も更新されました。

前回の記事で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のクエリで縦横変換(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型ですが、これを使うとデータの縦横変換(ピボット)がスマートに行えます。

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

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

縦持ちのテーブル (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というデータ型があります。
これは 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] で値にアクセスする、ということだけ覚えておけば、
特に問題なく使うことができると思います。

今回も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のクエリのテクニックです。
前回の記事で、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列も４列もとなっていくとかなり記述量が変わってきます。

(注意)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の二通りなんてデータもなかなか無いでしょうが、ただの例なのでご了承ください。)
慣れると便利なのでためしてみてください。

注意:Prestoの方でクエリを書いていることを前提とします。

トレジャーデータを使っていて、各DBのそれぞれのテーブル毎の列名の一覧を取得したくなったのでその方法のメモです。

対象のテーブルが少なければ、
DESCRIBE table_name
を順番に実行すれば十分ですが、対象テーブルが多くなるとこれでは大変です。

この場合、Presotのメタデータにアクセスすると手軽に列名の一覧を得ることができます。

FAQの次の質問が参考になります。
23. How do I access TD table metadata using Presto?

クエリをそのまま引用します。

# List TD Databases
SELECT * from information_schema.schemata

# List TD Tables
SELECT * from information_schema.tables

# List all column metadata
SELECT * from information_schema.columns

このうち、3番目の
SELECT * from information_schema.columns
を使うと、DB、テーブル、列を含む情報を取得できます。
不要な情報もあるので、自分は次の形で使うことが多いです。

SELECT
    table_schema,
    table_name,
    column_name
FROM
    information_schema.columns

通常のSELECTと同じように、WHERE句で特定のDB(schema)のみなどの条件をつけることもできます。

PrestoのINSERT文で、別のテーブルからSELECTした結果を挿入する書き方があります。
こういうの。

INSERT INTO
    new_table (
        col1,
        col2,
        col3
    )
SELECT
   col1,
   col2,
   col3
FROM
   old_table

このとき、SELECT文がそこそこ複雑になると、以前の記事で紹介したWITH句を使いたくなるのですが、うまく動かず困っていました。
それはどうやら、 INSERT INTO より先に WITH句を書いてしまっていたのが原因のようです。

WITH は INSERT INTO と SELECT の間に 書くのが正解のようです。
例としてはこんな感じ。

INSERT INTO
    new_table (
        col1,
        col2,
        col3
    )
WITH
    tmp_table AS (
        SELECT
            col1,
            col2,
            col3
        FROM
            old_table
    )
SELECT
   col1,
   col2,
   col3
FROM
   tmp_table