MySQL - 이전 행에서 값 빼기, 그룹화 기준

MySQL - 이전 행에서 값 빼기, 그룹화 기준

SN 번호로 이전의 소비액을 기준으로 해야 합니다.이것이 제 데이터입니다.

TABLE 에너지 로그

SN     Date                 Value
2380   2012-10-30 00:15:51  21.01
2380   2012-10-31 00:31:03  22.04
2380   2012-11-01 00:16:02  22.65
2380   2012-11-02 00:15:32  23.11
20100  2012-10-30 00:15:38  35.21
20100  2012-10-31 00:15:48  37.07
20100  2012-11-01 00:15:49  38.17
20100  2012-11-02 00:15:19  38.97
20103  2012-10-30 10:27:34  57.98
20103  2012-10-31 12:24:42  60.83

이것이 제가 필요로 하는 결과입니다.

SN      Date                 Value  consumption
2380    2012-10-30 00:15:51  21.01  0
2380    2012-10-31 00:31:03  22.04  1.03
2380    2012-11-01 00:16:02  22.65  0.61
2380    2012-11-02 00:15:32  23.11  0.46
20100   2012-10-30 00:15:38  35.21  0
20100   2012-10-31 00:15:48  37.07  1.86
20100   2012-11-01 00:15:49  38.17  1.1
20100   2012-11-02 00:15:19  38.97  0.8
20103   2012-10-30 10:27:34  57.98  0
20103   2012-10-31 12:24:42  60.83  2.85

MySQL 변수를 사용하는 작업은 인라인 프로그램 변수 할당과 마찬가지로 훌륭합니다.먼저 FROM 절은 @ 변수를 "선언"하며 기본값은 공백으로 설정합니다.그런 다음 원하는 순서대로 레코드를 조회합니다.시간이 많이 걸릴 수 있는 반복적인 하위 쿼리 대신 데이터를 한 번 통과합니다.

각 행 읽기에 대해 @last를 비교합니다.현재 레코드의 SN이 있는 SN.다를 경우 항상 0을 반환합니다.동일한 경우 단순 차이를 계산합니다.비교가 완료된 후에만 @last를 설정합니다.SN 및 @lastValue는 다음 레코드 비교를 위한 현재 레코드와 동일합니다.

select
      EL.SN,
      EL.Date,
      EL.Value, --remove duplicate alias
      if( @lastSN = EL.SN, EL.Value - @lastValue, 0000.00 ) as Consumption,
      @lastSN := EL.SN,
      @lastValue := EL.Value
   from
      EnergyLog EL,
      ( select @lastSN := 0,
               @lastValue := 0 ) SQLVars
   order by
      EL.SN,
      EL.Date

이렇게 하면 효과가 있을 것입니다.

SELECT l.sn,
       l.date, 
       l.value,
       l.value - (SELECT value 
                  FROM energylog x
                  WHERE x.date < l.date
                  AND x.sn = l.sn
                  ORDER BY date DESC
                  LIMIT 1) consumption
FROM energylog l;

SQLFidle: http://sqlfiddle.com/ #!2/b9eb1/8 참조

거의 보편적인 해결책은 데이터를 자신에게 결합하고, 결합 조건에 상관된 하위 쿼리를 포함함으로써 이전 기록을 찾는 것입니다.

SELECT
  ThisLog.*,
  COALESCE(ThisLog.Value - PrevLog.Value, 0) AS consumption
FROM
  EnergyLog    AS ThisLog
LEFT JOIN
  EnergyLog    AS PrevLog
    ON  PrevLog.SN   = ThisLog.SN
    AND PrevLog.Date = (SELECT MAX(Date)
                          FROM EnergyLog
                         WHERE SN   = ThisLog.SN
                           AND Date < ThisLog.Date)

이는 두 가지를 모두 포함하는 하나의 인덱스에서 가장 잘 수행됩니다.(SN, Date).

다음과 같이 동일한 테이블의 두 행을 결합할 수 있습니다.

    SELECT this.*, prev.*
      FROM tbl this
INNER JOIN tbl prev ON prev.id =
           ( 
               SELECT max(t.id) 
               FROM tbl t  
               WHERE t.id < this.id
           )     
     WHERE ...

당신의 경우는 다음과 같습니다.

    SELECT this.SN, this.Date, this.Value, (this.Value - prev.Value) AS consumption
      FROM EnergyLog this
INNER JOIN EnergyLog prev ON prev.Date =
           ( 
               SELECT max(t.Date) 
               FROM EnergyLog t  
               WHERE t.Date < this.Date
           )     

아래 쿼리를 한 번 시도해 보시기 바랍니다.

SELECT e1.*,
   (SELECT Value
    FROM EnergyLog e2
    WHERE e2.sn = e1.sn AND e2.date < e1.date
    ORDER BY date DESC
    LIMIT 1)-l.Value consumption
FROM EnergyLog e1;

언급URL : https://stackoverflow.com/questions/13196190/mysql-subtracting-value-from-previous-row-group-by