>

온도 조절 장치의 테이블이 있습니다. 다음과 같이 데이터를 기록합니다. 따라서 스위치를 켜면 상태 1이 켜지고 상태 0은 가열 스위치가 꺼집니다. 또한 매일 총 난방 량을 설정/해제 할 수 있습니다.

Date                | Status | Total_heatings
2019-01-20 10:00:00 | 1      | 1
2019-01-20 10:10:00 | 0      | 1
2019-01-20 14:00:00 | 1      | 2
2019-01-20 14:25:00 | 0      | 2
2019-01-20 18:00:00 | 1      | 3
2019-01-20 18:15:00 | 0      | 3
2019-01-21 01:00:00 | 1      | 1
2019-01-21 01:30:00 | 0      | 1
2019-01-21 06:00:00 | 1      | 2
2019-01-21 06:15:00 | 0      | 2

일별 총 기간을 얻으려고합니다. 아래 스크립트를 사용해 보았습니다.이 스크립트는 매일 여러 가열 세션의 지속 시간을 제공합니다. SUM(TIMESTAMPDIFF(Minute,Min(Date),MAX(Date))) 를 사용할 때  그룹을 잘못 사용하여 오류가 발생합니다.

SELECT 
   DATE_FORMAT(Date, '%d.%m') AS 'day',
   TIMESTAMPDIFF(MINUTE,MIN(Date),MAX(Date)) AS 'Duration'
FROM thermostat 
   WHERE (Date BETWEEN '2019-01-21 00:00:00' + INTERVAL -7 DAY AND '2019-01-21 00:00:00') 
   GROUP BY DAY(Date),Total_heatings;

필요한 것은 매일 이러한 다양한 가열 세션을 통해 SUM을 얻는 것입니다. 따라서 결과는 다음과 같아야합니다.

Day   | Duration
20.01 | 50
21.01 | 45

이제 매일 총 지속 시간과 같이 하루에 모든 난방 세션을 더 이상 계산할 수 없게되었습니다. 조언과 도움을 주셔서 감사합니다.

  • 답변 # 1

    이 쿼리는 8.0 이전의 MySQL 버전에서 작동합니다. 그것은 SELF JOIN 를 사용합니다  일치하는 heater off 를 찾으려면  주어진 heater on 에 대한 행  열. 일치하는 행이 존재하지 않는 경우 하루의 끝 또는 현재 시간 중 더 낮은 시간을 사용합니다.

    SELECT DATE_FORMAT(t1.Date, '%d.%m') AS `day`,
           SUM(TIMESTAMPDIFF(MINUTE, t1.Date, COALESCE(t2.Date, LEAST(NOW(), DATE(t1.Date) + INTERVAL 1 DAY)))) AS Duration,
           MAX(t1.Total_heatings) AS Total_heatings
    FROM thermostat t1
    LEFT JOIN thermostat t2 ON t2.Status = 0 AND t2.Total_heatings = t1.Total_heatings AND DATE(t2.Date) = DATE(t1.Date)
    WHERE t1.Status = 1 AND DATE(t1.Date) BETWEEN '2019-01-21' - INTERVAL 7 DAY AND '2019-01-21' 
    GROUP BY `day`
    
    

    출력 :

    day     Duration    Total_heatings
    20.01   50          3
    21.01   45          2
    
    

    dbfiddle의 데모

  • 답변 # 2

    MySQL 8을 사용하는 경우 창 함수 LAG 를 사용할 수 있습니다  이전 스위치에 액세스합니다. 외부 쿼리에서 이전 상태가 설정된 간격을 필터링 할 수 있습니다.

    SELECT
        DATE_FORMAT(x.date, '%d.%m'),
        SUM(TIMESTAMPDIFF( minute, x.date, x.last_date) duration
    FROM (
        SELECT 
            t.*,
            LAG(t.date) OVER (PARTITION BY DATE_FORMAT(t.date, '%d.%m') ORDER BY t.date) last_date,
            LAG(t.status) OVER (PARTITION BY DATE_FORMAT(t.date, '%d.%m') ORDER BY t.date) last_status
        FROM mytable t
    ) x
    WHERE x.last_status = 1
    GROUP BY DATE_FORMAT(x.date, '%d.%m')
    ORDER BY 1
    
    

    이 db 바이올린에서 이것은 예상 된 결과와 일치합니다.

  • 답변 # 3

    MySQL-8.0 및 MariaDB-10.2에서 사용할 수있는 창 기능 사용 :

    select DATE(ts) as 'day', sum(ontime) as 'on time'
    from (
      select status, lead(ts,1,ts) over w - ts as 'ontime'
      from (
        select unix_timestamp(ts) as ts, status
        from t
        order by ts
      ) x
      window w as (order by ts)
    ) y
    where status=1
    group by 'day';
    
    

  • 이전 angular - Jasmine, Custom userAgent가 CORS 오리진 오류를 리턴 함
  • 다음 mongodb - 몽고가 내 자바 스크립트를 올바르게 실행하도록 만드는 방법