一天每五分钟取一次平均值

最新推荐文章于 2021-07-27 20:04:14 发布

高级盘丝洞

最新推荐文章于 2021-07-27 20:04:14 发布

阅读量573

点赞数

本文链接：https://blog.csdn.net/weixin_42023748/article/details/100008130

版权

对于一分钟接入功率表求取五分钟内平均值

oracle

SELECT
HOUR (concat( GATHER_DATE,' ' , GATHER_TIME)) AS h,
FLOOR( MINUTE ( concat( GATHER_DATE,' ' , GATHER_TIME) ) / 5 ) AS v,
avg( num ),
sum( num ),
count( num )
FROM
rpps_data_power
WHERE
concat( GATHER_DATE,' ' , GATHER_TIME)>= '2019-08-05 00:00:00'
GROUP BY
h,
v;

实锤：

SELECT
GATHER_DATe,
GATHER_time,
power_value,
power_wattless
FROM
( SELECT GATHER_DATe, GATHER_time, power_value, power_wattless FROM gf_spps_power_real WHERE GATHER_DATE >= '2019-08-22' AND GATHER_DATE <= '2019-08-22' )
WHERE
GATHER_TIME LIKE '%:00:%'
OR GATHER_TIME LIKE '%:05:%'
OR GATHER_TIME LIKE '%:10:%'
OR GATHER_TIME LIKE '%:15:%'
OR GATHER_TIME LIKE '%:20:%'
OR GATHER_TIME LIKE '%:25:%'
OR GATHER_TIME LIKE '%:30:%'
OR GATHER_TIME LIKE '%:35:%'
OR GATHER_TIME LIKE '%:40:%'
OR GATHER_TIME LIKE '%:45:%'
OR GATHER_TIME LIKE '%:50:%'
OR GATHER_TIME LIKE '%:55:%'
ORDER BY
GATHER_date,
GATHER_TIME ASC

mysql：

SELECT DATA_TIME FROM、 DR_DC_RIPPLE_HOUR_201804
WHERE DATA_TIME BETWEEN '2018-04-17 00:00:00' AND '2018-04-17 23:59:59'
AND DATE_FORMAT(DATA_TIME, '%i') % 15 = 0
AND DATE_FORMAT(DATA_TIME, '%s') = 0
AND MP_ID = 100000000186