복붙노트

[SQL] 어떻게 두 행을 결합와 MySQL에서 두 개의 타임 스탬프 값 사이의 시간 차이를 계산?

SQL

어떻게 두 행을 결합와 MySQL에서 두 개의 타임 스탬프 값 사이의 시간 차이를 계산?

나는 확신 매우 일반적이다 나는 상황을 가지고 정말 내가 그것을 할 또는 관련 예 / 해결책을 찾기 위해 무엇을 검색하는 방법을 알아낼 수없는 날 귀찮게입니다. 나는 상대적으로 MySQL의 새로운 내가 MySQL의 부족한 일부 기능에 의해 차단 생각할 수있는 모든 방법 (MSSQL 및 PostgreSQL의 이전 버전을 사용하고 있습니다)입니다.

나는 단순히 (날짜 형식으로 저장) 자신의 타임 스탬프와 함께 다양한 이벤트를 나열하는 "로그"테이블이 있습니다. 이 문제와 관련이없는 테이블의 데이터와 열을 많이 그래서 우리는이 같은 간단한 테이블이 말할 수,있다 :

CREATE TABLE log (  
  id INT NOT NULL AUTO_INCREMENT,  
  name VARCHAR(16),  
  ts DATETIME NOT NULL,  
  eventtype VARCHAR(25),  
  PRIMARY KEY  (id)  
)

하자 말 일부 행이 EVENTTYPE을 가지고 = '시작'등이 EVENTTYPE = '정지'를 가지고있다. 내가 뭘 원하는 것은 각 "stoprow"각 "startrow"어떻게 든 부부과 사이의 시간 차이를 알려면 두 (다음 각 이름마다 기간을 합산,하지만 그건하지 어디에 문제가 거짓말이다). 각각의 "시작"이벤트는 나중에 후 "시작"이벤트를 어떤 단계에 해당하는 "STOP"이벤트 발생하는이 있지만 문제 / 버그 / 데이터와 충돌하기 때문에 일부가 누락되어있을 수 콜렉터한다. 이 경우 나는 "파트너"하지 않고도 이벤트를 무시하고 싶습니다. 데이터가 주어진 그 수단은

foo, 2010-06-10 19:45, start  
foo, 2010-06-10 19:47, start  
foo, 2010-06-10 20:13, stop

.. 난 그냥 19시 45분 시작 이벤트를 무시하고 바로 정지 시간으로 20시 13분 중지 이벤트를 사용하여 두 개의 결과 행을하지 싶습니다.

나는 다른 방법으로 자신과 테이블을 결합하려고했지만, 나를 위해 중요한 문제는 제대로 주어진 "이름"의 "시작"이벤트에 해당하는 "STOP"이벤트를 식별 할 수있는 방법을 찾을 것 같다. 당신이 직원 및 직장에서 스탬프와 테이블이 있고 실제로 직장에서 얼마나 많이 알아 원한다면 당신이하는 것처럼 문제는 정확히 동일합니다. 나는이에 있는지 잘가 알고 있어야합니다 솔루션을 해요,하지만 난 그들을 찾을 수 없습니다 ...

해결법

  1. ==============================

    1.나는이 목표에 도달 할 수있는 간단한 방법이 될 수 있으리라 생각합니다 :

    나는이 목표에 도달 할 수있는 간단한 방법이 될 수 있으리라 생각합니다 :

    SELECT
    start_log.name,
    MAX(start_log.ts) AS start_time,
    end_log.ts AS end_time,
    TIMEDIFF(MAX(start_log.ts), end_log.ts)
FROM
    log AS start_log
INNER JOIN
    log AS end_log ON (
            start_log.name = end_log.name
        AND
            end_log.ts > start_log.ts)
WHERE start_log.eventtype = 'start'
AND end_log.eventtype = 'stop'
GROUP BY start_log.name

    그것은 하나 개의 하위 쿼리를 제거로 더 빠른 상당히 실행해야합니다.

  2. ==============================

    2.임시 테이블 *을 생성 괜찮다면, 나는 다음은 잘 작동한다고 생각합니다. 나는 12 만 개 레코드를 테스트하고, 모든 과정이 완료 6 초 미만했다. 1,048,576 기록으로 그냥 아래 66초 완료 - 그리고 메모리가 128Mb와 오래 된 펜티엄 III에 그의 :

    임시 테이블 *을 생성 괜찮다면, 나는 다음은 잘 작동한다고 생각합니다. 나는 12 만 개 레코드를 테스트하고, 모든 과정이 완료 6 초 미만했다. 1,048,576 기록으로 그냥 아래 66초 완료 - 그리고 메모리가 128Mb와 오래 된 펜티엄 III에 그의 :

    같은 쿼리에 한 번 이상 임시 테이블을 참조 할 수 *에서 5.0 (그리고 아마도 다른 버전) 임시 테이블은 진정한 MySQL의 임시 테이블이 될 수 없습니다. 여기를 보아라:

    http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html

    대신 다음과 같이 바로 삭제 / 일반 테이블을 만들 :

    DROP TABLE IF EXISTS `tmp_log`;
CREATE TABLE `tmp_log` (
    `id` INT NOT NULL,
    `row` INT NOT NULL,
    `name` VARCHAR(16),
    `ts` DATETIME NOT NULL,
    `eventtype` VARCHAR(25),
    INDEX `row` (`row` ASC),
    INDEX `eventtype` (`eventtype` ASC)
);

    이 테이블은 다음과 같은 SELECT 쿼리에서 행의 분류 및 번호 매기기 목록을 저장하는 데 사용됩니다 :

    INSERT INTO `tmp_log` (
    `id`,
    `row`,
    `name`,
    `ts`,
    `eventtype`
)
SELECT
    `id`,
    @row:=@row+1,
    `name`,
    `ts`,
    `eventtype`
FROM log,
(SELECT @row:=0) row_count
ORDER BY `name`, `id`;

    SELECT 쿼리 위의 이름 다음 ID (방금 너무 오래 시작 이벤트가 정지 이벤트 전에 나타나는 대신 ID의 타임 스탬프를 사용할 수 있습니다)에 의해 행을 정렬합니다. 각 행은 넘버링된다. 이렇게함으로써, 이벤트의 일치 쌍은 서로 옆에 항상하고 시작 이벤트의 행 수는 적은 중지 이벤트의 행 ID보다 항상 하나입니다.

    이제 목록에서 일치하는 쌍을 선택합니다

    SELECT
    start_log.row AS start_row,
    stop_log.row AS stop_row,
    start_log.name AS name,
    start_log.eventtype AS start_event,
    start_log.ts AS start_time,
    stop_log.eventtype AS stop_event,
    stop_log.ts AS end_time,
    TIMEDIFF(stop_log.ts, start_log.ts) AS duration
FROM
    tmp_log AS start_log
INNER JOIN tmp_log AS stop_log
    ON start_log.row+1 = stop_log.row
    AND start_log.name = stop_log.name
    AND start_log.eventtype = 'start'
    AND stop_log.eventtype = 'stop'
ORDER BY start_log.id;

    작업이 완료되면, 임시 테이블을 삭제하는 것이 좋습니다 아마 :

    DROP TABLE IF EXISTS `tmp_log`;row

    최신 정보

    당신은 임시 테이블을 제거하고 이전 행에서 값을 저장하는 변수를 사용하여 완전히 조인 다음과 같은 아이디어를 시도 할 수 있습니다. 그것은 어떤 이름이 동일한 그룹의 모든 값을 함께 다음 타임 스탬프, 이름하여 열을 정렬하고, 시간 순서대로 각각의 그룹을 둔다. 나는이 해당되는 모든 시작 / 중지 이벤트가 서로 옆에 있는지 확인해야한다고 생각합니다.

    SELECT id, name, start, stop, TIMEDIFF(stop, start) AS duration FROM (
    SELECT
        id, ts, eventtype,
        (@name <> name) AS new_name,
        @start AS start,
        @start := IF(eventtype = 'start', ts, NULL) AS prev_start,
        @stop  := IF(eventtype = 'stop',  ts, NULL) AS stop,
        @name  := name AS name
    FROM table1 ORDER BY name, ts
) AS tmp, (SELECT @start:=NULL, @stop:=NULL, @name:=NULL) AS vars
WHERE new_name = 0 AND start IS NOT NULL AND stop IS NOT NULL;

    나는 바르 Bonsaksen의 방법을 비교하는 방법을 모르겠지만, 내 상자에 매우 빠르게 실행됩니다.

    저는 여기에 테스트 데이터를 생성하는 방법은 다음과 같습니다

    CREATE TABLE  `table1` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(5),
    `ts` DATETIME,
    `eventtype` VARCHAR(5),
    PRIMARY KEY (`id`),
    INDEX `name` (`name`),
    INDEX `ts` (`ts`)
) ENGINE=InnoDB;

DELIMITER //
DROP PROCEDURE IF EXISTS autofill//
CREATE PROCEDURE autofill()
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < 1000000 DO
        INSERT INTO table1 (name, ts, eventtype) VALUES (
            CHAR(FLOOR(65 + RAND() * 26)),
            DATE_ADD(NOW(),
            INTERVAL FLOOR(RAND() * 365) DAY),
            IF(RAND() >= 0.5, 'start', 'stop')
        );
        SET i = i + 1;
    END WHILE;
END;
//
DELIMITER ;

CALL autofill();
  3. ==============================

    3.당신은 데이터 수집기를 변경할 수 있습니까? 그렇다면, 로그 테이블에 (인덱스 포함) GROUP_ID 필드를 추가합니다 (GROUP_ID의 시작과 끝을 위해 동일한 ID)가 그것으로 시작 이벤트의 ID를 작성합니다. 그럼 당신은 할 수있다

    당신은 데이터 수집기를 변경할 수 있습니까? 그렇다면, 로그 테이블에 (인덱스 포함) GROUP_ID 필드를 추가합니다 (GROUP_ID의 시작과 끝을 위해 동일한 ID)가 그것으로 시작 이벤트의 ID를 작성합니다. 그럼 당신은 할 수있다

    SELECT S.id, S.name, TIMEDIFF(E.ts, S.ts) `diff`
FROM `log` S
    JOIN `log` E ON S.id = E.group_id AND E.eventtype = 'end'
WHERE S.eventtype = 'start'
  4. ==============================

    4.이 시도.

    이 시도.

    select start.name, start.ts start, end.ts end, timediff(end.ts, start.ts) duration from (
    select *, (
        select id from log L2 where L2.ts>L1.ts and L2.name=L1.name order by ts limit 1
    ) stop_id from log L1
) start join log end on end.id=start.stop_id
where start.eventtype='start' and end.eventtype='stop';
  5. ==============================

    5.이건 어때요:

    이건 어때요:

    SELECT start_log.ts AS start_time, end_log.ts AS end_time
FROM log AS start_log
INNER JOIN log AS end_log ON (start_log.name = end_log.name AND end_log.ts > start_log.ts)
WHERE NOT EXISTS (SELECT 1 FROM log WHERE log.ts > start_log.ts AND log.ts < end_log.ts)
 AND start_log.eventtype = 'start'
 AND end_log.eventtype = 'stop'

    이것은 첫 번째는 항상 시작과 마지막은 항상 정지이고, 중간에 이벤트와 행의 각 쌍 (start_log 및 end_log로 별칭)를 찾을 수 있습니다. 우리는 중간 이벤트를 허용하기 때문에, 즉시 정지 다음 아니에요 시작 자연스럽게 제외됩니다.

  6. ==============================

    6.나는 두 솔루션을 결합하여 작업을 얻었으나, 쿼리는 매우 효과적이지 내가 원치 않는 행을 생략 할 수있는 현명한 방법이있을 것 같아.

    나는 두 솔루션을 결합하여 작업을 얻었으나, 쿼리는 매우 효과적이지 내가 원치 않는 행을 생략 할 수있는 현명한 방법이있을 것 같아.

    내가 지금 가지고하는 것입니다 : SELECT의 y.name, y.start, y.stop, TIMEDIFF (y.stop, y.start) (SELECT l.name FROM, MAX (x.ts) 시작으로, l.ts AS 정지 로그 l로부터 (가입 t.name을 선택, t.ts 로그 t FROM WHERE t.eventtype = '시작') × ON x.name = l.name 및 x.ts는 l.ts을 < WHERE l.eventtype의 = '스톱' GROUP BY의 l.name, l.ts) Y WHERE NOT EXISTS (선택 1 로그 AS의 D FROM WHERE d.ts> y.start AND d.ts

    7. from https://stackoverflow.com/questions/3017468/how-to-combine-two-rows-and-calculate-the-time-difference-between-two-timestamp by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 열 절에 하위 쿼리와 MS SQL 서버 피벗 테이블  (0) 2020.07.08
[SQL] 어떻게 내 기본 키를 선택?  (0) 2020.07.08
[SQL] 테이블이 몇 가지 고유 한 열이 있는지 여부를 알아내는 방법  (0) 2020.07.08
[SQL] 대량 삽입을 사용할 때 특정 열을 무시  (0) 2020.07.08
[SQL] 어떻게 변경 신원 사양에 대한 표는 정체성 SQL 서버입니다  (0) 2020.07.08
,

티스토리툴바