HOWTO · MySQL
MySQL で Datetime 列を日付のみでグループ化する
MySQLで、DATETIME値をカレンダー日付単位にグループ化し、DATE()とGROUP BYを使って出席行数を数える方法を説明します。
このページの内容
MySQLでカレンダー日ごとのレコード数を数えるには、DATE(ATTENDANCE) を選択し、同じ式でグループ化します。たとえば、SELECT DATE(ATTENDANCE) AS attendance_date, COUNT(STUDENT_ID) AS attendance_count FROM student_attendance GROUP BY DATE(ATTENDANCE); のように記述します。DATETIME 型の値には日付と時刻が含まれますが、DATE() を使うと、クエリ内でカレンダー日付を取り出せます。この関数は保存済みの列を変更しません。
MySQLの DATETIME 型の列をカレンダー日付でグループ化する
ATTENDANCE の値をそのまま使ってグループ化すると、同じ日の異なる時刻は別々に扱われます。DATE() を適用すると、たとえば 2022-05-02 のように、それらの行は同じグループ値になります。そのため、結果はデータに含まれる日付ごとに1行ずつです。次のクエリは、日付ごとに NULL ではない学生 ID を数えます。
出席データの例を準備する
この例では、学生 ID と出席時刻を格納する DATETIME 型の列を持つテーブルを1つ使います。3人の学生がそれぞれ2日間にわたって異なる時刻に記録されているため、日時全体でグループ化しても、求める日ごとの合計にはなりません。
CREATE TABLE student_attendance (STUDENT_ID INT NOT NULL, ATTENDANCE DATETIME NOT NULL); でテーブルを作成します。次の文でサンプル行を追加します。INSERT INTO student_attendance (STUDENT_ID, ATTENDANCE) VALUES (1, '2022-05-02 08:15:10'), (2, '2022-05-02 09:00:00'), (3, '2022-05-02 09:20:00'), (1, '2022-05-03 08:10:00'), (2, '2022-05-03 08:45:00'), (3, '2022-05-03 09:05:00');
DATE() と GROUP BY で日付ごとに行をグループ化する
サンプルテーブルに対して、次のMySQLクエリを実行します。SELECT DATE(ATTENDANCE) AS attendance_date, COUNT(STUDENT_ID) AS attendance_count FROM student_attendance GROUP BY DATE(ATTENDANCE) ORDER BY DATE(ATTENDANCE); 日付の式は選択する列と GROUP BY の両方に記述します。エイリアスを付けると、結果の列見出しが分かりやすくなります。上記の6行を使った場合、結果は次のとおりです。
| attendance_date | attendance_count |
|---|---|
| 2022-05-02 | 3 |
| 2022-05-03 | 3 |
これはサンプルデータから計算されるMySQLの期待結果であり、MySQLサーバーでクエリを実行したという意味ではありません。
同じ入力に対して、次のPython例でも日付ごとの件数を独立して計算します。これはMySQLクライアントの例ではありません。
次のPython例を実行してください。
このセクションのコードと表示されるラベルや出力は英語のままです。
from collections import Counter
attendance = [
(1, "2022-05-02 08:15:10"),
(2, "2022-05-02 09:00:00"),
(3, "2022-05-02 09:20:00"),
(1, "2022-05-03 08:10:00"),
(2, "2022-05-03 08:45:00"),
(3, "2022-05-03 09:05:00"),
]
counts = Counter(timestamp.split(" ", 1)[0] for _, timestamp in attendance)
for day, count in sorted(counts.items()):
print(day, count)
2022-05-02 3
2022-05-03 3
DATE()、GROUP BY、COUNT() を理解する
DATE() は、日付または日時の値から日付部分を返すMySQL関数です。行を日時全体ではなくカレンダー日単位でグループ化するには、GROUP BY でこの関数を使います。列の定義を変えたり、保存済みの値を更新したりするものではありません。SELECT と GROUP BY の日付式は、同じグループ値を表す必要があります。グループ化句に式を記述しておけば、その句で SELECT リストのエイリアスに依存せずに済みます。
GROUP BY は、グループ化式の値が等しい行をまとめます。集計関数は、まとめられた各グループについて値を計算します。この例の COUNT(STUDENT_ID) は、各日のグループにある NULL ではない ID を数えます。サンプルの列は NOT NULL として定義されているため、各グループの出席行すべてが数えられます。
よく使われる3種類の件数指定は、それぞれ意味が異なります。COUNT(*) は、列に NULL が含まれる場合も含めて行数を数えます。COUNT(expression) は、その式が NULL ではない行を数えます。COUNT(DISTINCT expression) は、NULL ではない式の値から重複を除いて数えます。何を測定したいかに応じて使い分けてください。たとえば、ID が NULL になり得る場合、COUNT(STUDENT_ID) の結果は行数より小さくなることがありますが、COUNT(*) はすべての行を数えます。日付ごとの重複しない学生数を求めるには、同じ日付のグループ化式とともに COUNT(DISTINCT STUDENT_ID) を使います。