bugfix> mysql > 投稿

私はこれに固執しており、それを行う方法がわかりません。

私はこのテーブルを持っています

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がなくても開始時間と終了時間を表示するように機能しますが、時間の計算に取り組む必要があります。継続時間はまだ機能せず、各行にヌルが表示されます。

回答 1 件
  • IDフィールド(キーとして)およびコードの後のイベント識別フィールドが理想的です。

    それが不可能な場合、関数が役立ちます:

    DELIMITER $$
    CREATE FUNCTION event_stoptime (start_time datetime)
    RETURNS datetime
    BEGIN
        DECLARE stop_time datetime;
        SET stop_time = (SELECT MIN(event_datetime) FROM events WHERE 
    event_datetime > start_time);
        RETURN stop_time;
    END $$
    DELIMITER ;
    
    

    関数が構築されたら、MySQL日付関数と組み合わせて必要なデータを取得できます。

    SELECT  event_datetime as event_start, 
            event_stoptime(event_datetime) as event_stop, 
            TIMEDIFF(event_stoptime(event_datetime), event_datetime) as event_duration,
            WEEK(event_datetime) as event_week,
            MONTH(event_datetime) as event_month
    from events 
    where event_code = 50;
    
    

    私が得た結果は

    | event_start        | event_stop          | duration  | week| month|
    '2018-01-01 06:00:00', '2018-01-01 07:00:00', '01:00:00', '0', '1'
    '2018-01-01 11:00:00', '2018-01-01 13:00:00', '02:00:00', '0', '1'
    '2018-01-02 07:00:00', '2018-01-02 23:00:00', '16:00:00', '0', '1'
    '2018-01-24 07:00:00', '2018-01-25 23:00:00', '40:00:00', '3', '1'
    '2018-02-02 10:00:00', '2018-02-02 12:00:00', '02:00:00', '4', '2'
    
    

    重大な警告:特定の50に対応する54のイベントがない場合(エラーなどにより)、時間が大幅にずれます。

    これがより良い解決策です IDフィールドを使用します。テストを容易にするために、挿入ステートメントにIDを含めました。

    CREATE TABLE `events` (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `event_datetime` datetime DEFAULT NULL,
    `event_code` varchar(40) DEFAULT NULL,
    `val` varchar(40) DEFAULT NULL,
    `event_id` int(11) DEFAULT NULL,
    PRIMARY KEY (`ID`)
    );
    INSERT INTO `events` 
        VALUES  (1,'2018-01-01 06:00:00','50','1',NULL),
                (2,'2018-01-01 07:00:00','54',NULL,1),
                (3,'2018-01-01 11:00:00','50','2',NULL),
                (4,'2018-01-01 13:00:00','54',NULL,3),
                (5,'2018-01-01 09:00:00','50',NULL,NULL),
                (6,'2018-01-02 23:00:00','54',NULL,5),
                (7,'2018-01-24 07:00:00','50','1',NULL),
                (8,'2018-01-25 23:00:00','54','3',7),
                (9,'2018-02-02 10:00:00','50','1',NULL),
                (10,'2018-02-02 12:00:00','54',NULL,9),
                (11,'2018-01-30 23:00:00','54','3',NULL);
    
    

    ビューはテーブルをそれ自体に結合し、正しい「54」から「50」を選択します。月の最初の週を見つけるための日付の作成に注意してください。

    CREATE VIEW `event_list` AS 
    select  `events`.`ID` AS `ID`,`events`.`event_datetime` AS `event_start`,
            `end_events`.`event_datetime` AS `event_stop`,
            timediff(`end_events`.`event_datetime`,`events`.`event_datetime`) AS `event_duration`,
            week(`events`.`event_datetime`) AS `event_week`,
            month(`events`.`event_datetime`) AS `month_number`,
            monthname(`events`.`event_datetime`) AS `event_month`,
            WEEK(STR_TO_DATE(concat(year(events.event_datetime), '-', month(events.event_datetime), '-', '01'), '%Y-%m-%d')) as first_week
    from (`events` left join `events` `end_events` on((`events`.`ID` = `end_events`.`event_id`))) 
    where (`events`.`event_code` = '50');
    
    

    selectステートメントは、IFを使用して週の要約に適切なデータを選択します。時間は、時間を計算するために秒に分割され、表示用に再構成されます。

    select  event_month,
            sec_to_time(sum(time_to_sec(if(event_week = first_week, event_duration, 0)))) as week_one,
            sec_to_time(sum(time_to_sec(if(event_week = first_week + 1, event_duration, 0)))) as week_two,
            sec_to_time(sum(time_to_sec(if(event_week = first_week + 2, event_duration, 0)))) as week_three,
            sec_to_time(sum(time_to_sec(if(event_week = first_week + 3, event_duration, 0)))) as week_four,
            sec_to_time(sum(time_to_sec(if(event_week = first_week + 4, event_duration, 0)))) as week_five
    from    event_list
    group by event_month
    order by month_number
    
    

    エレガントではありませんが、機能します。

あなたの答え