【XSIOT】电表能耗统计sql

SELECT 
  su.deviceId,
  DATE_FORMAT( su.avgDatetime ,'%Y-%m-%d') AS dtTime,
  CAST(SUM(su.sum41) AS DECIMAL (15, 2)) AS sum41,
  CAST(SUM(su.sum42) AS DECIMAL (15, 2)) AS sum42,
  CAST(SUM(su.sum53) AS DECIMAL (15, 2)) AS sum53,
  CAST(SUM(su.sum54) AS DECIMAL (15, 2)) AS sum54,
  CAST(SUM(su.sum61) AS DECIMAL (15, 2)) AS sum61,
  CAST(SUM(su.sum62) AS DECIMAL (15, 2)) AS sum62,
  CAST(
    SUM(
      sum41 + sum42 + sum53 + sum54 + sum61 + sum62
    ) AS DECIMAL (15, 2)
  ) AS totalSum 
FROM
  (SELECT 
    device_id AS deviceId,
    avg_datetime AS avgDatetime,
    MAX(
      CASE
        data_type 
        WHEN 41 
        THEN changev 
        ELSE 0 
      END
    ) AS sum41,
    MAX(
      CASE
        data_type 
        WHEN 42 
        THEN changev 
        ELSE 0 
      END
    ) AS sum42,
    MAX(
      CASE
        data_type 
        WHEN 53 
        THEN changev 
        ELSE 0 
      END
    ) AS sum53,
    MAX(
      CASE
        data_type 
        WHEN 54 
        THEN changev 
        ELSE 0 
      END
    ) AS sum54,
    MAX(
      CASE
        data_type 
        WHEN 61 
        THEN changev 
        ELSE 0 
      END
    ) AS sum61,
    MAX(
      CASE
        data_type 
        WHEN 62 
        THEN changev 
        ELSE 0 
      END
    ) AS sum62 
  FROM
    `sui_data_wzwl0002_201803_day` 
  WHERE data_type IN (41, 42, 53, 54, 61, 62) 
    AND avg_datetime > '2017-09-07 00:00:00' 
    AND avg_datetime < '2020-12-01 00:00:00' 
  GROUP BY avg_datetime,
    device_id) su 
GROUP BY su.deviceId ,su.avgDatetime
ORDER BY totalSum DESC ;

结果：