利用sql中的窗口函数lead和lag统计上料皮带的峰谷平运行时间占比

众所周知，工业企业在不同时间段的用电价格是不同的，尖峰电价是平电的2倍，峰电是评价的1.7倍，而谷电只有平电价格的30%。尽量多用谷电少用峰电，是降低成本的有效手段。要想有效管理，首先得做到有效监控，怎样快速统计上料皮带在峰谷平各个时间段的运行时间和占比，成了摆在能源主管眼前的一个大问题。

一筹莫展之下， 他找到了我。我经过一番研究，还真给他实现了。

我们单位的上料皮带的运行数据存储在数据库的一个表里，表结构如下：

只有两个字段：时间和皮带。时间字段作为主键，皮带字段记录皮带的运行状态，开启记录为“1”，停止记录为“0”。下面是表内容：

首先，要依据给定的峰谷平时间短，将tb_上料皮带表内容做一下梳理。生成一个视图vw_上料皮带运行情况

create view [dbo].[vw_上料皮带运行情况] as
SELECT cast([时间] as DATE) as 日期, --提取出日期
  时间,
case
when DATEPART(hour,时间) between 0 and 6 then '谷'
when DATEPART(hour,时间) between 7 and 7 then '谷'
when DATEPART(hour,时间) between 8 and 14 then '平'
when DATEPART(hour,时间) between 15 and 18 then '峰'
when DATEPART(hour,时间) between 19 and 21 then '尖'
when DATEPART(hour,时间) between 22 and 22 then '峰'
when DATEPART(hour,时间) between 23 and 23 then '平'
end as 峰谷平
,皮带
FROM [db_LG].[dbo].tb_上料皮带

视图查询效果是这样的：

这时候，就要找到所有皮带状态变化时候的哪一行记录，打上一个标记“1”。这时候就要用到窗口函数lead和lag了。lead，lag，它们可以将数据进行位移。因为涉及到位移，所以会有数据会被挪位而消失。

lag:形象的理解就是把数据从上向下推，上端出现空格

lead:形象的理解就是把数据从下向上推，下端出现空格

lag 和lead 有三个参数，第一个参数是列名，第二个参数是偏移的offset，第三个参数是超出记录窗口时的默认值。

知道了lead和lag的原理，就可以利用lag函数来找到皮带运行状态改变时的每一行记录了。查询代码如下：

SELECT 日期,
        时间,
        皮带,峰谷平,
        CASE 
            WHEN 峰谷平!=LAG(峰谷平) 
over(order by 时间)  or  皮带 != LAG(皮带) OVER (ORDER BY 时间) THEN 1 
            ELSE 0 
        END AS status_changed
    FROM [vw_上料皮带运行情况]

相当于使用lag函数，把皮带字段的数据整体往下推了一行，然后再横向比较皮带字段和lag后的数据是否相等，相等标记成“0”，不相等标记成“1”。

然后筛选出所有状态改变标记（status_changed）为“1”的数据行。下面在创建一个视图vw_上料皮带开停持续时间。

视图中用到了LEAD函数，找到每一个状态的开始时间和结束时间，以及持续时间。

在这里并没有直接使用lead后的时间作为结束时间，而是用datediff函数，在lead后的时间基础上减了一秒。因为lead把数据整体网上推了一行，最后一行就会出现空白，这时候用getdate()函数获取当前时间作为默认值。代码如下：

Create view [dbo].[vw_上料皮带开停持续时间] as
select A.日期,峰谷平,时间 as 开始时间,
dateadd(second,-1,LEAD(时间, 1, getdate()) OVER (ORDER BY 时间)) AS 结束时间,
datediff(minute,时间,LEAD(时间, 1, getdate()) OVER (ORDER BY 时间)) as 持续时间,
皮带
from
(SELECT 日期,峰谷平,
时间,
皮带
FROM (
SELECT 日期,
时间,
皮带,峰谷平,
CASE
WHEN 峰谷平!=LAG(峰谷平) 
over(order by 时间) or 皮带 != LAG(皮带) OVER (ORDER BY 时间) THEN 1
ELSE 0
END AS status_changed
FROM vw_上料皮带运行情况
) AS subquery
WHERE status_changed = 1
) A

查询效果如下：

这时候，距离成品只有一步之遥。下面就是使用普通的sql语句，分类汇总一下即可得到每天的峰谷平用电时间和百分比，这里就不赘述了。