非日次時系列データから日次レポートを作成する方法：MySQLでカレンダー表を使わない3つのアプローチ
2024-07-27
MySQL を使用して非日次時系列データから日次レポートを作成する方法（カレンダー表なし）
データセット
このチュートリアルでは、次の構造のテーブルを使用します。
CREATE TABLE sensor_data (
id INT PRIMARY KEY AUTO_INCREMENT,
sensor_id INT,
timestamp DATETIME,
value FLOAT
);
このテーブルには、センサーからの読み取り値が格納されています。各行には、センサー ID、タイムスタンプ、および値が含まれます。
アプローチ
以下の手順で、日次レポートを作成します。
各日の最大値を計算する
次のクエリを使用して、各日の最大値を
daily_maxテーブルに計算します。INSERT INTO daily_max (sensor_id, date, value) SELECT sensor_id, DATE(timestamp), MAX(value) FROM sensor_data GROUP BY sensor_id, DATE(timestamp);日次レポートを作成する
次のクエリを使用して、日次レポートを
daily_reportテーブルに作成します。INSERT INTO daily_report (sensor_id, date, min_value, max_value, average_value) SELECT sensor_id, date, MIN(value), MAX(value), AVG(value) FROM daily_max GROUP BY sensor_id, date;
このクエリは、daily_max テーブルの各行に対して、以下の処理を実行します。
* センサー ID を `sensor_id` 列に格納します。
* 日付を `date` 列に格納します。
* 最小値を `min_value` 列に格納します。
* 最大値を `max_value` 列に格納します。
* 平均値を `average_value` 列に格納します。
結果
daily_report テーブルには、次の列が含まれます。
sensor_id: センサー IDdate: 日付min_value: 最小値average_value: 平均値
このテーブルを使用して、センサーからの日々のデータを集計したレポートを作成できます。
- この方法は、センサーデータが比較的密に記録されている場合にのみ適しています。データがまばらな場合は、レポートに誤った情報が反映される可能性があります。
- より複雑なレポートを作成するには、集計関数を組み合わせて使用できます。たとえば、標準偏差や中央値を計算できます。
- レポートを可視化するには、MySQL からデータをクエリして、グラフやチャートを作成するツールを使用できます。
INSERT INTO daily_max (sensor_id, date, value)
SELECT sensor_id, DATE(timestamp), MAX(value)
FROM sensor_data
GROUP BY sensor_id, DATE(timestamp);
INSERT INTO daily_report (sensor_id, date, min_value, max_value, average_value)
SELECT sensor_id, date, MIN(value), MAX(value), AVG(value)
FROM daily_max
GROUP BY sensor_id, date;
説明
このコードは、次のことを行います。
sensor_dataテーブルからデータを選択します。DATE(timestamp)関数を使用して、各行のタイムスタンプから日付を抽出します。GROUP BY句を使用して、センサー ID と日付ごとにデータをグループ化します。MAX(value)関数を使用して、各グループの最大値を計算します。daily_maxテーブルに結果を挿入します。MIN(value),MAX(value), およびAVG(value)関数を使用して、各グループの最小値、最大値、および平均値を計算します。
方法 1: ウィンドウ関数を使用する
この方法では、ウィンドウ関数を使用して、各行の周辺の行に基づいて値を計算します。
INSERT INTO daily_report (sensor_id, date, min_value, max_value, average_value)
SELECT sensor_id, DATE(timestamp),
MIN(value) OVER (PARTITION BY sensor_id, DATE(timestamp) ORDER BY timestamp ROWS BETWEEN PRECEDING 24 HOUR AND CURRENT ROW),
MAX(value) OVER (PARTITION BY sensor_id, DATE(timestamp) ORDER BY timestamp ROWS BETWEEN PRECEDING 24 HOUR AND CURRENT ROW),
AVG(value) OVER (PARTITION BY sensor_id, DATE(timestamp) ORDER BY timestamp ROWS BETWEEN PRECEDING 24 HOUR AND CURRENT ROW)
FROM sensor_data;
ROWS BETWEEN PRECEDING 24 HOUR AND CURRENT ROW句を使用して、各行の周辺の24時間分の行を含むウィンドウを定義します。MIN(value) OVER関数を使用して、各ウィンドウ内の最小値を計算します。
方法 2: サブクエリを使用する
この方法では、サブクエリを使用して、各行の日付と同じ日付の最大値、最小値、および平均値を取得します。
INSERT INTO daily_report (sensor_id, date, min_value, max_value, average_value)
SELECT sensor_id, date,
(SELECT MIN(value) FROM sensor_data WHERE sensor_id = s.sensor_id AND DATE(timestamp) = s.date),
(SELECT MAX(value) FROM sensor_data WHERE sensor_id = s.sensor_id AND DATE(timestamp) = s.date),
(SELECT AVG(value) FROM sensor_data WHERE sensor_id = s.sensor_id AND DATE(timestamp) = s.date)
FROM sensor_data AS s;
sensor_idとDATE(timestamp)列を使用して、各行をsというエイリアス付きのサブテーブルに格納します。
どの方法を選択するべきか
どの方法を選択するかは、ニーズと好みによって異なります。
- 方法 1 は、ウィンドウ関数に慣れている場合に適しています。より簡潔なコードで済みますが、ウィンドウ関数の概念を理解する必要があります。
- 方法 2 は、サブクエリに慣れている場合に適しています。コードが冗長になる可能性がありますが、ウィンドウ関数の概念を理解する必要はありません。
- [MySQL ウィンドウ
mysql sql calendar
