窗口函数分组求累加值的差值

 1.源数据如下，需求：求每天每小时，yield、energy 的差值，speed,dspeed,kw的均值，当insertType=1时，下一行的yield、energy值会重新变化

 2.实现步骤

①这段代码使用了SQL的窗口函数和CASE表达式，主要是计算一个重启组标识。

1. 首先，使用CASE表达式判断[insertType]的值，如果等于1，则返回1，否则返回0。
2. 接下来，使用SUM函数计算该CASE表达式的累加和，这个SUM函数是通过窗口函数的方式计算的。
3. 窗口函数使用OVER子句指定了分区，分区的规则是根据[datetimes]的日期和小时进行分区。
4. 窗口函数还指定了排序规则，按照[datetimes]的升序进行排序。
5. 窗口函数还指定了窗口大小，ROW BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING表示当前行的前一行为窗口的结束行，而窗口的开始行则没有限制。
6. 最后，对于窗口函数的结果再次使用CASE表达式，如果结果为NULL，则返回0，否则保持原值。

整体来说，这段代码的作用是计算一个重启组标识，即对于相同日期和小时的数据，如果前一行的[insertType]等于1，则当前行的重启组标识为1，否则为0。

② 在①的基础上

窗口函数是用于在查询结果中的某个窗口（即一组行）上进行计算的函数。它可以用于在窗口内进行聚合、排序、分组等操作。在这段代码中，窗口函数使用了LEAD函数来获取下一行的某个特定列的值，并且通过PARTITION BY子句将窗口按照RestartGroup和HourlyDate进行分组。

COALESCE函数是用于检查并返回它的参数列表中的第一个非空表达式的函数。如果所有参数都为空，则返回NULL。在这段代码中，COALESCE函数用于将下一行的值与当前行的值进行比较，如果下一行的值为空，则返回当前行的值。

下面是查询结果中各个字段的含义：

• [datetimes]：日期时间
• HourlyDate：每小时日期
• [energy]：能源
• [speed]：速度
• [dspeed]：差速
• [kw]：千瓦
• [yield]：产量
• RestartGroup：重启分组
• [yield1]：下一行的产量值
• [energy1]：下一行的能源值

 ③ 在②的基础上，对差值进行求和，即可求出结果

 

 附代码及测试数据

CREATE TABLE [dbo].[produce_record3] (
  [id] int  IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
  [datetimes] datetime  NULL,
  [date] char(10) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [time] char(8) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [engineNum] varchar(10) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [yield] int  NOT NULL,
  [energy] float(53)  NOT NULL,
  [speed] int  NULL,
  [dspeed] int  NULL,
  [kw] int  NULL,
  [insertType] int  NOT NULL
)
GO

ALTER TABLE [dbo].[produce_record3] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of produce_record3
-- ----------------------------
SET IDENTITY_INSERT [dbo].[produce_record3] ON
GO

INSERT INTO [dbo].[produce_record3] ([id], [datetimes], [date], [time], [engineNum], [yield], [energy], [speed], [dspeed], [kw], [insertType]) VALUES (N'221402129', N'2022-04-01 12:21:45.000', N'2022-04-01', N'12:21:45', N'856', N'2661', N'41', N'100', N'8206', N'1', N'0')
GO

INSERT INTO [dbo].[produce_record3] ([id], [datetimes], [date], [time], [engineNum], [yield], [energy], [speed], [dspeed], [kw], [insertType]) VALUES (N'221402276', N'2022-04-01 12:23:22.000', N'2022-04-01', N'12:23:22', N'856', N'2661', N'41', N'0', N'0', N'1', N'0')
GO

INSERT INTO [dbo].[produce_record3] ([id], [datetimes], [date], [time], [engineNum], [yield], [energy], [speed], [dspeed], [kw], [insertType]) VALUES (N'221402573', N'2022-04-01 12:23:32.000', N'2022-04-01', N'12:23:32', N'856', N'2661', N'41', N'0', N'0', N'1', N'1')
GO

INSERT INTO [dbo].[produce_record3] ([id], [datetimes], [date], [time], [engineNum], [yield], [energy], [speed], [dspeed], [kw], [insertType]) VALUES (N'221402724', N'2022-04-01 12:27:33.000', N'2022-04-01', N'12:27:33', N'856', N'32', N'1', N'153', N'12609', N'373', N'0')
GO

INSERT INTO [dbo].[produce_record3] ([id], [datetimes], [date], [time], [engineNum], [yield], [energy], [speed], [dspeed], [kw], [insertType]) VALUES (N'221402882', N'2022-04-01 12:30:12.000', N'2022-04-01', N'12:30:12', N'856', N'34', N'1', N'155', N'12681', N'377', N'0')
GO

INSERT INTO [dbo].[produce_record3] ([id], [datetimes], [date], [time], [engineNum], [yield], [energy], [speed], [dspeed], [kw], [insertType]) VALUES (N'221403039', N'2022-04-01 12:31:52.000', N'2022-04-01', N'12:31:52', N'856', N'56', N'1', N'156', N'12753', N'365', N'0')
GO

INSERT INTO [dbo].[produce_record3] ([id], [datetimes], [date], [time], [engineNum], [yield], [energy], [speed], [dspeed], [kw], [insertType]) VALUES (N'221403182', N'2022-04-01 12:33:33.000', N'2022-04-01', N'12:33:33', N'856', N'79', N'1', N'157', N'12836', N'376', N'0')
GO

INSERT INTO [dbo].[produce_record3] ([id], [datetimes], [date], [time], [engineNum], [yield], [energy], [speed], [dspeed], [kw], [insertType]) VALUES (N'221403330', N'2022-04-01 12:35:14.000', N'2022-04-01', N'12:35:14', N'856', N'101', N'1', N'157', N'12884', N'377', N'0')
GO

INSERT INTO [dbo].[produce_record3] ([id], [datetimes], [date], [time], [engineNum], [yield], [energy], [speed], [dspeed], [kw], [insertType]) VALUES (N'221403473', N'2022-04-01 12:36:53.000', N'2022-04-01', N'12:36:53', N'856', N'124', N'2', N'158', N'12968', N'382', N'0')
GO

INSERT INTO [dbo].[produce_record3] ([id], [datetimes], [date], [time], [engineNum], [yield], [energy], [speed], [dspeed], [kw], [insertType]) VALUES (N'221403618', N'2022-04-01 12:38:30.000', N'2022-04-01', N'12:38:30', N'856', N'145', N'2', N'160', N'13052', N'369', N'0')
GO

INSERT INTO [dbo].[produce_record3] ([id], [datetimes], [date], [time], [engineNum], [yield], [energy], [speed], [dspeed], [kw], [insertType]) VALUES (N'221403772', N'2022-04-01 12:40:09.000', N'2022-04-01', N'12:40:09', N'856', N'168', N'2', N'160', N'13099', N'379', N'0')
GO

SET IDENTITY_INSERT [dbo].[produce_record3] OFF
GO

WITH RankedRecords AS (  
    SELECT  
        [id],  
        [datetimes],  
        FORMAT([datetimes], 'yyyy-MM-dd HH') AS HourlyDate,  
        [date],  
        [time],  
        [engineNum],  
        [yield],  
        [energy],  
        [speed],  
        [dspeed],  
        [kw],  
        [insertType],  
        isnull(SUM(CASE WHEN [insertType] = 1 THEN 1 ELSE 0 END) OVER (  
            PARTITION BY CAST([datetimes] AS DATE), FORMAT([datetimes], 'HH')  
            ORDER BY [datetimes]  
            ROWS BETWEEN UNBOUNDED  PRECEDING AND 1 PRECEDING
        ),0) AS RestartGroup  
    FROM  
        [dbo].[produce_record3]  
),  
CumulativeYield AS (  
    SELECT  
	    [datetimes], 
        HourlyDate,  
        [energy],  
        [speed],  
        [dspeed],  
        [kw],  
		[yield],
		RestartGroup,
        coalesce(lead(([yield]),1) over(partition by  RestartGroup,HourlyDate order by [datetimes]),[yield]) as [yield1],
				coalesce(lead(([energy]),1) over(partition by  RestartGroup,HourlyDate order by [datetimes]),[energy]) as [energy1]
    FROM  
        RankedRecords  
)  
select 
HourlyDate,
sum([yield1]-[yield]) as [yield],
sum([energy1]-[energy]) as energy ,
avg([dspeed]) as dspeed,
avg([speed]) as speed,
avg([kw]) as kw
from CumulativeYield
group by HourlyDate