一定間隔の時間に該当するレコード、もしくはNULLを取得する方法

対象テーブル：
不定期なタイミングで保存された”時刻／ID”のテーブル log_table
（実際は下記サンプルよりもランダムな時刻となります）

mysql> SELECT log_date, log_id FROM `log_table` WHERE log_date BETWEEN '2022-04-30 23:50' AND '2022-05-02 00:20';
+---------------------+--------+
| log_date            | log_id |
+---------------------+--------+
| 2022-04-30 23:50:00 | 284201 |
| 2022-04-30 23:52:00 | 284203 |
| 2022-04-30 23:54:00 | 284205 |
| 2022-04-30 23:56:00 | 284207 |
| 2022-04-30 23:58:00 | 284209 |
| 2022-05-02 00:00:00 | 284211 |
| 2022-05-02 00:02:00 | 284213 |
| 2022-05-02 00:04:00 | 284215 |
| 2022-05-02 00:06:00 | 284217 |
| 2022-05-02 00:08:00 | 284219 |
| 2022-05-02 00:10:00 | 284221 |
| 2022-05-02 00:12:00 | 284223 |
| 2022-05-02 00:14:00 | 284225 |
| 2022-05-02 00:16:00 | 284227 |
| 2022-05-02 00:18:00 | 284229 |
| 2022-05-02 00:20:00 | 284231 |
+---------------------+--------+

このテーブルから任意の周期でレコードを取得したく、
周期でグループ化して、その中の一番古い時刻のlog_idを取得しようとしています。また、単位は分で良いので、秒の値は気にしていません

mysql> SELECT A.log_date, A.log_id
    -> FROM log_table AS A
    ->     INNER JOIN
    ->     ( SELECT log_date, log_id,
    ->         ROW_NUMBER() OVER
    ->         ( PARTITION BY TRUNCATE((TIMESTAMPDIFF(MINUTE,'2022:04:30 00:00:00',log_date) -1) / 4, 0) ORDER BY log_date DESC ) seq
    ->       FROM log_table
    ->       WHERE log_date BETWEEN '2022:04:30 23:50:00' AND '2022:05:02 00:20:00'
    ->     ) B
    ->     ON A.log_id = B.log_id
    -> WHERE B.seq = 1
    -> ORDER BY A.log_date;
+---------------------+--------+
| log_date            | log_id |
+---------------------+--------+
| 2022-04-30 23:52:02 | 284203 |
| 2022-04-30 23:56:04 | 284207 |
| 2022-04-30 23:58:05 | 284209 |
| 2022-05-02 00:00:06 | 284211 |
| 2022-05-02 00:04:08 | 284215 |
| 2022-05-02 00:08:01 | 284219 |
| 2022-05-02 00:12:03 | 284223 |
| 2022-05-02 00:16:05 | 284227 |
| 2022-05-02 00:20:07 | 284231 |
+---------------------+--------+

＜期待する出力＞
+---------------------+--------+
| log_date            | log_id |
+---------------------+--------+
| 2022-04-30 23:52:02 | 284203 |
| 2022-04-30 23:56:04 | 284207 |
| 2022-04-30 23:58:05 | 284209 |
| 2022-05-01 00:00:00 | null   |
| 2022-05-01 00:04:00 | null   |
| 2022-05-01 00:08:00 | null   |
～　　　　　　　　　　　　　　～
| 2022-05-01 23:52:00 | null   |
| 2022-05-01 23:56:00 | null   |
| 2022-05-02 00:00:06 | 284211 |
| 2022-05-02 00:04:08 | 284215 |
| 2022-05-02 00:08:01 | 284219 |
| 2022-05-02 00:12:03 | 284223 |
| 2022-05-02 00:16:05 | 284227 |
| 2022-05-02 00:20:07 | 284231 |
+---------------------+--------+

上の例では４分間隔で該当するレコードを出力していますが、
5/1 のデータが無いので、4/30 の次は 5/2 に飛んでしまいます。

＜質問＞
データが無い場合でも一定間隔の log_date で log_id=null を出力することはできますか。

事前に一定間隔の時間テーブルを作成しておいて、それと連動させれば出来そうですが、良い方法が見つかりません。

よろしくお願いします。

行動規範の内容に同意します

回答1件

ベストアンサー

2022-04-30 23:50から4分間隔で最小値を選ぶなら
2022-04-30 23:50:01 284201が対象では？

こんな感じのダミーテーブル処理が有効だと思います

SQL
1select * from (
2SELECT @dt := '2022-04-30 23:50' AS start,
3@dt +interval 239 second AS end
4UNION
5SELECT @dt := @dt + interval 4 minute,
6@dt +interval 239 second AS start
7FROM information_schema.COLUMNS
8WHERE @dt<='2022-05-02 00:20'
9) as t1