SQL在同一结果集上取平均值的行

问题描述：

我试图完成某种奇怪的事情，我有一个表在select *上有流动的select输出（我只是简化了4行）。桌子上的每一对行都是同一时间的信息，我想从每个“事件”取得某种平均值并将其输出到结果中。SQL在同一结果集上取平均值的行

当前结果集

VehicleId        ActivityDateTime   SensorValue SensorPilot 
66B828BE-6EB9-4B69-AB5C-125439F065DE 2017-02-17 17:52:54.010 750   0 
66B828BE-6EB9-4B69-AB5C-125439F065DE 2017-02-17 17:52:54.007 770   1 
66B828BE-6EB9-4B69-AB5C-125439F065DE 2017-02-17 17:51:54.010 800   0 
66B828BE-6EB9-4B69-AB5C-125439F065DE 2017-02-17 17:51:54.007 900   1

，我想获得具有查询，将得到我，ResultSet和在同一时间拥有的每一个“事件”是这样的平均水平。

VehicleId        ActivityDateTime   SensorValue SensorPilot 
66B828BE-6EB9-4B69-AB5C-125439F065DE 2017-02-17 17:52:54.010 750   0 
66B828BE-6EB9-4B69-AB5C-125439F065DE 2017-02-17 17:52:54.007 770   1 
**66B828BE-6EB9-4B69-AB5C-125439F065DE 2017-02-17 17:52:54.008 760   2** 
66B828BE-6EB9-4B69-AB5C-125439F065DE 2017-02-17 17:51:54.010 800   0 
66B828BE-6EB9-4B69-AB5C-125439F065DE 2017-02-17 17:51:54.007 900   1 
**66B828BE-6EB9-4B69-AB5C-125439F065DE 2017-02-17 17:51:54.008 850   2**

是这样的可能吗？或者我应该尝试以另一种方式接近这个儿子？

我试着做一个视图了这个查询的，如果由于某种原因，是相关

您可以将数据放在临时表中。查询临时表2次，1查找常规数据，1查找总结版本...然后合并它们。 – manderson

@manderson，我如何实现这样的平均组最近的行在一起？这意味着我在第一张桌子上的问题我如何使第1行第2行而不是第2行第3行 –

啊我的坏，我认为VehicleId是独一无二的。 – manderson

答

使用（Row_Number - 1）/ 2生成连续配对。然后将基本集合与集合集合。

Use TEMPDB; 
    IF OBJECT_ID('tempdb..test') IS NOT NULL 
     DROP TABLE dbo.Test; 
    GO 

CREATE TABLE dbo.Test (
         VehicleID VARCHAR(38), 
         ActivityDateTime DATETIME2(7), 
         SensorValue INT, 
         SensorPilot INT 
        ); 
INSERT INTO dbo.Test 
VALUES ('66B828BE-6EB9-4B69-AB5C-125439F065DE', '2017-02-17 17:52:54.010', 750, 0), 
('66B828BE-6EB9-4B69-AB5C-125439F065DE', '2017-02-17 17:52:54.007', 770, 1), 
('66B828BE-6EB9-4B69-AB5C-125439F065DE', '2017-02-17 17:51:54.010', 800, 0), 
('66B828BE-6EB9-4B69-AB5C-125439F065DE', '2017-02-17 17:51:54.007', 900, 1); 

WITH cte 
AS (SELECT *, 
      ConsecutivePairs = (ROW_NUMBER() OVER (PARTITION BY t.VehicleID ORDER BY t.ActivityDateTime) - 1)/2 
    FROM dbo.Test AS t 
    ) 
SELECT cte.VehicleID, 
     cte.ActivityDateTime, 
     cte.SensorValue, 
     cte.SensorPilot, 
     cte.ConsecutivePairs 
FROM cte 
UNION ALL 
SELECT cte.VehicleID, 
     ActivityDateTime = DATEADD(MILLISECOND, DATEDIFF(MILLISECOND, MIN(cte.ActivityDateTime), MAX(cte.ActivityDateTime))/2, MIN(cte.ActivityDateTime)), 
     SensorValue = AVG(cte.SensorValue), 
     SensorPilot = COUNT(cte.VehicleID), 
     cte.ConsecutivePairs 
FROM cte 
GROUP BY cte.VehicleID, 
     cte.ConsecutivePairs 
ORDER BY cte.ConsecutivePairs, cte.SensorPilot  ;

你的例子工作的很好,,没有额外的临时表需要这是好的，即时试图把它放在我的实际数据库，我得到的''datediff函数导致溢出。分隔两个日期/时间实例的日期部分数量太大。尝试使用更精确的日期部分使用datediff。' –

您希望看到平均时间戳，但它在毫秒级。如果你有更大的差距，你会溢出整数值。将微秒更改为毫秒。这仍然是一个问题，将其改为第二个。 –

@ pato.llaguno。我已更新查询以使用毫秒。 –

答

我没有很多的时间，但是这将让你开始...你将不得不做关于日期的事情。

CREATE TABLE #temptable (vehicleid VARCHAR(100), activitydate DATETIME, sensorvalue INT, sensorpilot INT) 


INSERT INTO #temptable (vehicleid, activitydate, sensorvalue, sensorpilot) 
VALUES ('66B828BE-6EB9-4B69-AB5C-125439F065DE', '2017-02-17 17:52:54.010', 750,   0) 
INSERT INTO #temptable (vehicleid, activitydate, sensorvalue, sensorpilot) 
VALUES ('66B828BE-6EB9-4B69-AB5C-125439F065DE', '2017-02-17 17:52:54.007', 770,   1) 
INSERT INTO #temptable (vehicleid, activitydate, sensorvalue, sensorpilot) 
VALUES ('66B828BE-6EB9-4B69-AB5C-125439F065DE', '2017-02-17 17:51:54.010', 800,   0) 
INSERT INTO #temptable (vehicleid, activitydate, sensorvalue, sensorpilot) 
VALUES ('66B828BE-6EB9-4B69-AB5C-125439F065DE', '2017-02-17 17:51:54.007', 900,   1) 


SELECT NTILE(2) OVER(ORDER BY vehicleid DESC) AS RankVehicle 
     , vehicleid 
     , activitydate 
     , sensorvalue 
     , sensorpilot 
FROM #temptable 


UNION ALL 


SELECT A.RankVehicle, A.vehicleid, '' AS ActivityDate, AVG(A.SensorValue) AS SensorValue, 2 AS SensorPilot 
FROM ( 
     SELECT NTILE(2) OVER(ORDER BY vehicleid DESC) AS RankVehicle 
       , vehicleid 
       , activitydate 
       , AVG(sensorvalue) AS SensorValue 
       , sensorpilot 
     FROM #temptable 
    GROUP BY vehicleid, activitydate, sensorpilot 
    ) A 
GROUP BY A.RankVehicle, A.vehicleid 





DROP TABLE #temptable

谢谢，看看你的例子，RankVehicle是什么？ –

也不应该OVER（）是orderby activitydate吗？ –

SQL在同一结果集上取平均值的行

相关推荐