sql - 在sql中加入具有不同时间戳间隔的数据

Question

我有两张彼此不同的桌子。第一个表有这种形式

        Date1                 NGVolA
2020-11-20 12:05:19.000        10461
2020-11-20 12:14:11.000        15692
2020-11-20 12:25:53.000        20627

第二张表有这种形式

          char_time               disch_time         FUR       char_temp         disch_temp
2020-11-20 12:06:56.600    2020-11-20 12:08:28.193    A           59               1150
2020-11-20 12:07:02.693    2020-11-20 12:09:12.177    B           61               1140
2020-11-20 12:12:18.350    2020-11-20 12:13:46.350    A           77               1160
2020-11-20 12:16:16.070    2020-11-20 12:20:38.333    A           49               1156
2020-11-20 12:19:15.520    2020-11-20 12:21:22.317    A           75               1180
2020-11-20 12:27:10.513    2020-11-20 12:30:41.287    B           147              1165
2020-11-20 12:30:11.593    2020-11-20 12:32:33.257    A           72               1195

我想要的是从第二个表中获取数据并根据第一个表的时间间隔聚合它们。例如，在第一个表的 12:05:19 和 12:10:19 之间的时间间隔中，找出发生了多少 char_time 事件并平均它们的 char_temp。同样适用于 disch_time 和 disch_temp。

我想要的是一张这样的桌子

           Date1       Avg_Char_Temp_A   Char_events_A  Disch_events_A  Avg_DisCh_Temp_A      NGVolA  FUR
2020-11-20 12:05:19.000    68               2.0            1.0               1155             10461    A
2020-11-20 12:14:11.000    62               2.0            2.0               1168             15692    A
2020-11-20 12:25:53.000    72               1.0            1.0               1195             20627    A

到目前为止我所做的是

DECLARE @StartDate nvarchar(20)
DECLARE @EndDate nvarchar(20)
     
SET @StartDate ='2020-11-20 12:00:00'
SET @EndDate =  '2020-11-20 23:59:59'
        
SELECT  
[Date1]=  a.[_TimeStamp],
[Avg_Charg_Temp_A]=avg(CASE WHEN b.[FURNACE] ='A' then convert(real,isnull (b.[charge_temperature],'0.0')) else null end),
[Char_events_A]=sum(case when b.[FURNACE] ='A' then 1.0 else null end),
[Disch_events_A]=sum(case when b.[FURNACE] ='A' then 1.0 else null end)
[Avg_DisCh_Temp_A]=avg(CASE WHEN b.[FURNACE] ='A' then convert(real,isnull (b.[ave_disch_temp],'0.0')) else null end),
[NGVolA]=sum(CASE WHEN a.[Furnace] ='A' then convert(real,isnull (a.[NG_AVG_MEAS_FLOW],'0.0')) else 0.0 end ) 
      
 FROM (select a.*, (select min(aa.[_TimeStamp])
            from fix.dbo.IBA_Data aa 
            where aa.[_TimeStamp] > a.[_TimeStamp])as next_time_2
    from fix.dbo.IBA_Data a) a 
    Left JOIN ADB.dbo.Temp_Aims b on b.[charge_time] >= a.[_TimeStamp] and b.[charge_time] < a.[next_time_2]    

WHERE 
     CONVERT(datetime, a.[_TimeStamp], 20)  BETWEEN CONVERT(datetime, @StartDate , 20) AND CONVERT(datetime, @EndDate , 20)     

GROUP BY
a.[_TimeStamp],

ORDER BY 
  a.[_TimeStamp]

上述查询的问题是 Char_events_A 和 Disch_events_A 被高估（我得到了我期望的两倍）并且我无法在 _TimeStamp 上聚合 Avg_DisCh_Temp_A，因此我无法计算 Avg_DisCh_Temp_A。另请记住，我获取数据的服务器具有 SQL Server 2000 的兼容性级别。任何建议将不胜感激。

@Chuma 我收到一个错误“'MasterDetailLink' 不是可识别的 GROUP BY”和“'WorkingData' 附近的语法不正确。”

With MasterDetailLink as
(select 
a.[_TimeStamp], a.[NGVolA], b.[charge_time], b.[discharge_time] 
from fix.dbo.Fce_IBA_Data a inner join ALPHADB.dbo.Mill_Temp_Aims b on a.[_TimeStamp] between b.[charge_time] and b.[discharge_time] ),

WorkingData as
(select 
a.[_TimeStamp], a.[NGVolA], b.*

from MasterDetailLink a inner join ALPHADB.dbo.Mill_Temp_Aims b on a.[charge_time]=b.[charge_time] and a.[charge_time]=b.[discharge_time] )

select 
a.[_TimeStamp], 
[Avg_Charg_Temp_A]=avg(CASE WHEN b.[FURNACE] ='A' then convert(real,isnull (b.[charge_temperature],'0.0')) else null end),
[Char_events_A]=sum(case when b.[FURNACE] ='A' then 1.0 else null end),
[Disch_events_A]=sum(case when b.[FURNACE] ='A' then 1.0 else null end),
[Avg_DisCh_Temp_A]=avg(CASE WHEN b.[FURNACE] ='A' then convert(real,isnull (b.[ave_disch_temp],'0.0')) else null end),
[NGVolA]=sum(CASE WHEN a.[Furnace] ='A' then convert(real,isnull (a.[NG_AVG_MEAS_FLOW],'0.0')) else 0.0 end ) 
from WorkingData

group by a.[charge_time]

With MasterDetailLink as
(
select a.[charge_time], a.[NGVolA], b.[charge_time], b.[discharge_time] 
from fix.dbo.Fce_IBA_Data a inner join ALPHADB.dbo.Mill_Temp_Aims b on a.[_TimeStamp] between b.[charge_time] and b.[discharge_time] )
 
 WorkingData as
(
select 
a.[_TimeStamp], a.[NGVolA], b.*
from MasterDetailLink a inner join ALPHADB.dbo.Mill_Temp_Aims b on a.[charge_time]=b.[charge_time] and a.[charge_time]=b.[discharge_time]) 

select 
a.[_TimeStamp], 
[Avg_Charg_Temp_A]=avg(CASE WHEN b.[FURNACE] ='A' then convert(real,isnull (b.[charge_temperature],'0.0')) else null end),
[Char_events_A]=sum(case when b.[FURNACE] ='A' then 1.0 else null end),
[Disch_events_A]=sum(case when b.[FURNACE] ='A' then 1.0 else null end),
[Avg_DisCh_Temp_A]=avg(CASE WHEN b.[FURNACE] ='A' then convert(real,isnull (b.[ave_disch_temp],'0.0')) else null end),
[NGVolA]=sum(CASE WHEN a.[Furnace] ='A' then convert(real,isnull (a.[NG_AVG_MEAS_FLOW],'0.0')) else 0.0 end ) 
from WorkingData
group by a.[_TimeStamp]

标签： sqlsql-servertimestamp

Answer 1

为此，为简单起见，我们将逐步分解。我们最初的任务应该是链接父表和子表之间的时间。由于我没有他们的真实姓名，我将称他们为大师和细节。所以在主与细节中连接时代

With MasterDetailLink as
(
select a.Date1, a.NGVolA, b.char_time, b.disch_time 
from master a inner join detail b
on a.Date1 between b.char_time and b.disch_time 
) 

现在我们将其加入常规详细信息表并从那里开始

WorkingData as
(
select a.Date1, a.NGVolA, b.*
from MasterDetailLink a
 inner join detail b on a.char_time=b.char_time and a.char_time=b.disch_time 
)

然后从那里，您可以进行所需的任何聚合。

select Date1, <Aggregations here e.g. Sum(field1)>
from WorkingData
group by Date1

把它们放在一起

With MasterDetailLink as
(
select a.Date1, a.NGVolA, b.char_time, b.disch_time 
from master a inner join detail b
on a.Date1 between b.char_time and b.disch_time 
), WorkingData as
(
select a.Date1, a.NGVolA, b.*
from MasterDetailLink a
 inner join detail b on a.char_time=b.char_time and a.char_time=b.disch_time 
) 
select Date1, <Aggregations here e.g. Sum(field1)>
from WorkingData
group by Date1