mysql 按小时查询

需求1：给定时间范围，查询每小时的平均值
方案：
django orm

 select = {"hour": "concat(date_format(create_time, '%%Y-%%m-%%d %%H'),'-',hour(create_time)+1)"}

        objs = IndoorTemp.objects.filter(create_time__gte=start_date, create_time__lte=end_date,
                                         location_code=room).extra(select=select).values(
            'location_code', 'hour').annotate(indoor_temp=Round(Avg('indoor_temp'), 2),
                                              outdoor_temp=Round(Avg('outdoor_temp'), 2),
                                              humidity=Round(Avg('humidity'), 2),
                                              pm1=Round(Avg('pm1'), 2),
                                              pm25=Round(Avg('pm25'), 2),
                                              co2=Round(Avg('co2'), 2),
                                              tvoc=Round(Avg('tvoc'), 2),
                                              ).order_by('hour')

结果：

小结：select相当于sql语句中的select，create_time为数据库字段,取出小时格式化时间作为分组条件，并设置别名为hour，annotate为聚合函数，round保留2位小数。

需求2：给定时间范围，查询整点或接近整点的一条数据。
方案：
原生sql：

select a.* from indoor_temp a,( select min(id)as id,hour(create_time) AS time  FROM indoor_temp 
        where location_code=%s and create_time >= %s and create_time <= %s  GROUP BY time) b where b.id=a.id

结果：

小结：使用hour函数算出小时，同时取最小id联合作为分组函数，注意只能查询24小时数据，查询更多时间范围使用date_format。

需求3：查询每个设备最新一条数据。
原生sql：

select a.* from indoor_temp a,( select equip_code,max(create_time) time  from indoor_temp 
        GROUP BY equip_code ORDER BY equip_code) b 
        where a.equip_code=b.equip_code and a.create_time=b.time ORDER BY location_code

结果：

需求4：查询每小时耗电量
背景：每分钟存一次累计电量
方案：
原生sql：

select 1 as id, max(equip_data) as ma,min(equip_data) as mi,round(max(equip_data)-min(equip_data),2)as t, 
                    DATE_FORMAT(create_time,%s)as time  FROM equip_data  where type="electricity" 
                     and equip_code="elect_a" and create_time >= %s and create_time <= %s  GROUP BY tim

结果：

小结：
注意在django要加入1 as id ，不然会报错：Raw query must include the primary key。
同理可查询每天，每月，每年用量，只需改下date_format格式即可