概要
- SQL ウィンドウ関数にて PARTITION BY 句で指定したカラムに NULL が入っているときの挙動を確認するためのサンプルを書く
- 環境: MySQL Ver 8.0.29 for macos11.6 on x86_64 (Homebrew)
サンプルデータを準備
my_users テーブルを作成して、レコードを追加する。
CREATE TABLE my_users (id INT, name VARCHAR(5), group_name VARCHAR(3));
INSERT INTO my_users VALUES (1, 'Alice', 'abc');
INSERT INTO my_users VALUES (2, 'Bob' , 'xyz');
INSERT INTO my_users VALUES (3, 'Carol', NULL);
INSERT INTO my_users VALUES (4, 'Dave' , 'abc');
INSERT INTO my_users VALUES (5, 'Ellen', 'xyz');
INSERT INTO my_users VALUES (6, 'Frank', NULL);
INSERT INTO my_users VALUES (7, NULL, NULL);
my_users テーブルのすべてのレコードを確認する。
mysql> SELECT * FROM my_users;
+------+-------+------------+
| id | name | group_name |
+------+-------+------------+
| 1 | Alice | abc |
| 2 | Bob | xyz |
| 3 | Carol | NULL |
| 4 | Dave | abc |
| 5 | Ellen | xyz |
| 6 | Frank | NULL |
| 7 | NULL | NULL |
+------+-------+------------+
PARTITION BY 句で NULL が含まれるカラムを指定
group_name カラムの値ごとに部分集合を作り、それぞれの部分集合内で番号を振る。
group_name カラムには NULL が含まれている。
SELECT
id,
name,
group_name,
ROW_NUMBER() OVER(PARTITION BY group_name ORDER BY name IS NULL ASC) AS number_in_the_group
FROM
my_users
ORDER BY
group_name IS NULL ASC;
結果を見ると、group_name カラムの値が NULL でも部分集合が作られている。
+------+-------+------------+---------------------+
| id | name | group_name | number_in_the_group |
+------+-------+------------+---------------------+
| 1 | Alice | abc | 1 |
| 4 | Dave | abc | 2 |
| 2 | Bob | xyz | 1 |
| 5 | Ellen | xyz | 2 |
| 3 | Carol | NULL | 1 |
| 6 | Frank | NULL | 2 |
| 7 | NULL | NULL | 3 |
+------+-------+------------+---------------------+
PARTITION BY 句で指定したカラムの NULL を WHERE 句で排除
WHERE 句で group_name カラムの値が NULL のレコードを排除する。
group_name カラムの値ごとに部分集合を作り、それぞれの部分集合内で番号を振る。
SELECT
id,
name,
group_name,
ROW_NUMBER() OVER(PARTITION BY group_name ORDER BY name IS NULL ASC) AS number_in_the_group
FROM
my_users
WHERE
group_name IS NOT NULL
ORDER BY
group_name;
結果を見ると、group_name カラムの値が NULL のものは抽出されないようになっている。
+------+-------+------------+---------------------+
| id | name | group_name | number_in_the_group |
+------+-------+------------+---------------------+
| 1 | Alice | abc | 1 |
| 4 | Dave | abc | 2 |
| 2 | Bob | xyz | 1 |
| 5 | Ellen | xyz | 2 |
+------+-------+------------+---------------------+