概要

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 |
+------+-------+------------+---------------------+

参考資料