实现sqlserver数据根据不同时间周期分组

以下代码实现了根据不同时间周期要求返回sqlserver数据分组求平均数据，可以取得每年、每月、每天、每星期的分组平均数据，按星期分组GroupFlag字段存的时该星期周一最早的时间。

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
 SET ANSI_NULLS ON
 GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
alter PROCEDURE DxoilData_GroupData_Get
    -- Add the parameters for the stored procedure here
    @GroupType int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT off;

    -- Insert statements for procedure here
    create table #GroupData(
        QtyTD decimal(18,4),
        DensityLoad decimal(18,4),
        DensityArrival decimal(18,4),
        WaterLoad decimal(18,4),
        WaterArrival decimal(18,3),
        SulfurLoad decimal(18,3),
        SulfurArrival decimal(18,3),
        GroupFlag varchar(50))


    if( @GroupType=0)--按年分组
    begin
        insert into #GroupData
        SELECT  avg(qtytd),avg(DensityLoad),avg(DensityArrival),avg(WaterLoad),avg(WaterArrival),avg(SulfurLoad),avg(SulfurArrival),year(DateofArrival) from dxoildata group by year(DateofArrival)
    end

    if( @GroupType=1)--按月分组
    begin
        insert into #GroupData
        SELECT  avg(qtytd),avg(DensityLoad),avg(DensityArrival),avg(WaterLoad),avg(WaterArrival),avg(SulfurLoad),avg(SulfurArrival),cast(year(DateofArrival) as varchar(50))+'-'+cast(month(DateofArrival) as varchar(50)) from dxoildata group by year(DateofArrival),month(DateofArrival)
    end

    if( @GroupType=2)--按日分组
    begin
        insert into #GroupData
        SELECT  avg(qtytd),avg(DensityLoad),avg(DensityArrival),avg(WaterLoad),avg(WaterArrival),avg(SulfurLoad),avg(SulfurArrival),cast(year(DateofArrival) as varchar(50))+'-'+cast(month(DateofArrival) as varchar(50))+'-'+cast(day(DateofArrival) as varchar(50)) from dxoildata group by year(DateofArrival),month(DateofArrival),day(DateofArrival)
    end

    if( @GroupType=3)--按星期分组
    begin
        set datefirst 1
        create table #Week(MondayDate datetime,DateIndex int identity(1,1))
        --将是星期一的日期存入临时表#Week
        insert into #Week(MondayDate)
            select distinct DateofArrival from dxoildata where datepart(weekday, DateofArrival)=1
        --将分组数据存入临时表#GroupData
        declare @Index int
        declare @MaxIndex int
        select @MaxIndex=max(DateIndex) from #Week
        --循环插入数据
        set @Index=1
        while(@Index<@MaxIndex)
        begin
            declare @DateMonday datetime
            select @DateMonday=MondayDate from #Week where DateIndex=@Index
            
            insert into #GroupData
                select avg(qtytd),avg(DensityLoad),avg(DensityArrival),avg(WaterLoad),avg(WaterArrival),avg(SulfurLoad),avg(SulfurArrival),min(convert(varchar(50),DateofArrival,20)) from dxoildata where DateofArrival>=@DateMonday and DateofArrival<=DateAdd(dd,7,@DateMonday)

            set @Index=@Index+1
        end
        --删除临时表#Week
        drop table #Week
    end
    
    select * from #GroupData
    drop table #GroupData

END
GO

效果如下：

1.以年为周期；

2.以月为周期；

3.以日为周期；

4.以星期为周期；