每个月应用一个减法器 [英] Apply a substracton for each month

问题描述

我有一个可以捕获数据的 Wonderware Historian 服务器.我必须用SQL查询它才能找到一个月的最后一个值和一个月的第一个值之间的差异(该值不断增长).

I have a Wonderware Historian server that captures data. I have to query it with SQL to find difference between the last value of a month and the first value of a month (the value is constantly growing).

为了选择我的数据，我这样做了:

To select my data, I did this :

SELECT Annees=YEAR(DateTime),Mois=MONTH(DateTime),Value=()
FROM AnalogHistory where TagName = 'OBJECT_TAG_NAME'
AND DateTime >= '01/01/2016 00:00:00' 
AND DateTime <= '24/07/2017 13:53:31';

我想我需要使用 group 和 order by，但是现在，我被困在如何获得 a 的最后一个和第一个值上月，减去它.

I supposed I will need to use group and order by, but for now, I'm stuck on how I will get the last and the first value of a month, to subtract it.

您知道应用查询的方法吗?

Do you know a way to apply the query ?

实际上，我想通过创建一个带有每月消耗量"(煤气、电、水……)的图表来执行统计.该指数不断增长，因此我需要每个月从最后一个值中减去第一个值.

Actually, I want to perform stats by creating a chart with the "Monthly consumption" (of gas, electricity, water...). The index is constantly growing so I need, for each month, to subtract the first value from the last value.

例如，假设我有这样的数据:

For example, supposing I have this kind of data :

Date                | Index
------              | ------
2017-05-01 00:00:00 | 12
2017-05-01 03:57:00 | 14
2017-05-29 15:00:00 | 97
2017-05-31 22:54:00 | 104
2017-06-01 03:54:00 | 107
2017-06-30 21:54:00 | 211

2017 年 5 月的结果必须是 104-12 ==> 922017 年 6 月的结果必须是 211-104 ==> 107

The result for the month of May 2017 must be 104-12 ==> 92 The result for the month of June 2017 must be 211-104 ==> 107

然后，这将显示在条形图中.

And then, this will be displayed in a bar chart.

推荐答案

使用 FIRST_VALUE 窗口函数

;with cte as
(
SELECT distinct 
   Annees=YEAR(DateTime),
   Mois=MONTH(DateTime),
   firstRecord = first_value(value)over(partition by YEAR(DateTime),MONTH(DateTime) order by DateTime asc),
   lastRecord = first_value(value)over(partition by YEAR(DateTime),MONTH(DateTime) order by DateTime desc)
FROM AnalogHistory 
where TagName = 'OBJECT_TAG_NAME'
AND DateTime >= '01/01/2016 00:00:00' 
AND DateTime <= '24/07/2017 13:53:31'
)
select 
    Annees,
    Mois, 
    value = isnull(lastRecord,0) - isnull(firstRecord ,0)
from cte

<小时>

Row_Number 旧版本的方法

;with cte as
(
SELECT 
   Annees=YEAR(DateTime),
   Mois=MONTH(DateTime),
   firstRecord = Row_Number(value)over(partition by YEAR(DateTime),MONTH(DateTime) order by DateTime asc),
   lastRecord = Row_Number(value)over(partition by YEAR(DateTime),MONTH(DateTime) order by DateTime desc)
FROM AnalogHistory 
where TagName = 'OBJECT_TAG_NAME'
AND DateTime >= '01/01/2016 00:00:00' 
AND DateTime <= '24/07/2017 13:53:31'
)
select Annees,Mois,
       min(case when lastRecord = 1 then value end) - 
       min(case when firstRecord = 1 then value end)
From Cte 
Where 1 in (lastRecord,firstRecord )
Group by Annees,Mois

这篇关于每个月应用一个减法器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！