【XSIOT】电表能耗统计sql

最新推荐文章于 2024-05-28 19:46:27 发布

原创最新推荐文章于 2024-05-28 19:46:27 发布 · 406 阅读

0 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

4 篇文章

订阅专栏

本文详细介绍了一种使用SQL进行数据汇总的高级技巧，通过具体案例展示了如何利用CASE语句和GROUP BY从大型数据集中高效地提取特定类型的数据并进行求和运算。文章涵盖了日期格式化、数据类型筛选、时间范围限制等实用功能。

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 ;

结果：