sqlserver按时间区间分组汇总（峰平谷电量统计）

一.需求

公司要统计日周月的峰平谷用电量和电费（第一天八点到第二天八点算第一天的数据）

 二.思路解析

1.首先查一次表数据，按小时将尖峰平谷四个区间筛选出来

select 
		iif( datepart(hour,DATEADD(DD,0,time)) in (20,21),COALESCE(hour_value,0),0) as top_count,
		iif( datepart(hour,DATEADD(DD,0,time)) >= 9 and datepart(hour,DATEADD(DD,0,time))< 15,COALESCE(hour_value,0),0) as high_count,
		iif( datepart(hour,DATEADD(DD,0,time)) in (7,8,15,16,17,18,19,22),COALESCE(hour_value,0),0) as flat_count,
		iif( datepart(hour,DATEADD(DD,0,time)) in (0,1,2,3,4,5,6,23),COALESCE(hour_value,0),0) as valley_count,
		time
		from electric_detail

 2.按八点到八点可以先将所有日期往前推八小时，然后再分组汇总

group by CONVERT(CHAR(10), DATEADD(hh,-8,time), 23)

3.查尖峰平谷对应的电价

select date, top_cost,high_cost,flat_cost,valley_cost from
		(select distinct CONVERT(CHAR(10),time,23) as date from  electric_detail  where time >=  '2023-04-01' and time <  '2023-04-04') x
		left join
		(select * from electrovalence_details
		)   y
		on (start_time is null or start_time <= date)
		and (end_time is null or end_time >= DATEADD(DD,1,date))

 4.left join 将电价和用电量相乘获取电费