私はこれに固執しており、それを行う方法がわかりません。
私はこのテーブルを持っています
create table events(event_datetime datetime,event_code varchar(40),val varchar(40));
insert into events (event_datetime, event_code, val)
values ('2018-01-01 06:00:00', '50', '1'),
('2018-01-01 07:00:00', '54', null),
('2018-01-01 11:00:00', '50', '2'),
('2018-01-01 13:00:00', '54', null),
('2018-01-02 07:00:00', '50', '1'),
('2018-01-02 23:00:00', '54', null),
('2018-01-24 07:00:00', '50', '1'),
('2018-01-25 23:00:00', '54', null),
('2018-02-02 10:00:00', '50', '1'),
('2018-02-02 12:00:00', '54',null)
event_code 50は、イベントの開始を意味します。 event_code 54は、イベントが停止することを意味します。
1日の54から50の時間を合計したい(1日に50-54が複数ある場合がある)
私は1週間のAVGを4回やりたいです。
たとえば、1週目の上記のデータの場合:
2018-01-01 07:00:00 - 2018-01-01 06:00:00 = 1hour
2018-01-01 13:00:00 - 2018-01-01 11:00:00 = 2hours
2018-01-02 23:00:00 - 2018-01-02 07:00:00 = 16hours
Day 1 = 3hours
Day 2 = 16hours
Day 3-7 = 0hours
Week 1 = AVG(days) = 19/7
出力は次のようになります。
| Month | Week 1 | Week 2 | Week 3 | Week 4 |
|---------|------------|------------|-------------|------------|
| Jan |avghours/day|avghours/day|avghours/day |avghours/day|
| Feb |avghours/day|avghours/day|avghours/day |avghours/day|
SQLフィドル
編集:
イベント50の直後には常に54が続きます。
イベントは1日以上続くことはありません。
編集2:
ストアドプロシージャを作成しました。
DELIMITER #
CREATE PROCEDURE tablediff()
BEGIN
DECLARE done int default false;
DECLARE v_day DATETIME;
DECLARE v_code VARCHAR(40);
DECLARE v_day_1 DATETIME;
DECLARE v_code_1 VARCHAR(40);
DECLARE start_time DATETIME;
DECLARE stop_time DATETIME;
DECLARE code VARCHAR(40);
DECLARE duration TIME;
DECLARE cur1 CURSOR FOR
SELECT event_datetime, event_code as code, event_param_1
FROM events
AND event_code =50
ORDER BY event_datetime ASC;
DECLARE cur2 CURSOR FOR
SELECT event_datetime, event_code as code, event_param_1
FROM events
AND event_code = 50
ORDER BY event_datetime ASC;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
OPEN cur2;
CREATE TEMPORARY TABLE tmp
(
start_time DATETIME;
stop_time DATETIME;
code VARCHAR(40);
duration TIME;
);
FETCH cur2 INTO v_day_1, v_code_1;
forLoop: LOOP
FETCH cur1 INTO v_day, v_code;
FETCH cur2 INTO v_day_1, v_code_1;
CASE
WHEN v_code = 50 THEN
IF v_code_1 = 54 THEN
SET start_time = v_day;
SET stop_time = v_day_1;
SET code = v_code;
SET duration = duration + TIMEDIFF(stop_time, start_time);
ELSE
SET start_time = null;
SET stop_time = null;
SET code = null;
SET duration = duration;
END IF;
END CASE;
INSERT INTO tmp VALUES (start_time, stop_time, code, duration);
IF done
THEN
LEAVE forLoop;
END IF;
END LOOP;
CLOSE cur1;
CLOSE cur2;
END#
54がなくても開始時間と終了時間を表示するように機能しますが、時間の計算に取り組む必要があります。継続時間はまだ機能せず、各行にヌルが表示されます。
関連した質問
- SQL UPDATEプロシージャに複数のテーブルを使用させることができず、エラー1054が発生します
- mySQLを使用して、現在のデータベーステーブルの情報で別のデータベーステーブルを更新するにはどうすればよいですか?
- mysql:複数のフィールド(重複の近く)に一致する1つを除くすべてのレコードを削除済みとしてマークします
- カテゴリに関連するSQLのカウントのパーセンテージを計算します
- Sum出力の順序が間違っています
- MySQLテーブルのさまざまな行の2つの最高値を見つけます
- SQLで前月を取得する方法は?
- zplidとコードタイプに基づいて、列zfeaturekeyのすべての行で行のNULLを取得できませんか?
- MySQL(Aurora)adddateまたはdate-addまたはdate-subを使用してタイムスタンプを更新すると構文エラーが発生する
- 同じテーブル内の2つのクエリからのMySQLOneの結果
IDフィールド(キーとして)およびコードの後のイベント識別フィールドが理想的です。
それが不可能な場合、関数が役立ちます:
関数が構築されたら、MySQL日付関数と組み合わせて必要なデータを取得できます。
私が得た結果は
重大な警告:特定の50に対応する54のイベントがない場合(エラーなどにより)、時間が大幅にずれます。
これがより良い解決策です IDフィールドを使用します。テストを容易にするために、挿入ステートメントにIDを含めました。
ビューはテーブルをそれ自体に結合し、正しい「54」から「50」を選択します。月の最初の週を見つけるための日付の作成に注意してください。
selectステートメントは、IFを使用して週の要約に適切なデータを選択します。時間は、時間を計算するために秒に分割され、表示用に再構成されます。
エレガントではありませんが、機能します。