Question

我的表载于Skkk服务器快版:

Time Device Value
0:00 1      2
0:01 2      3
0:03 3      5
0:03 1      3
0:13 2      5
0:22 1      7
0:34 3      5
0:35 2      6
0:37 1      5

该表用于记录报告其最新价值的不同装置的事件。我想做的是,以我通过时间尺度提交平均数据的方式编制数据,并最终使用这些数据制作图表。我以以下方式操纵了Excel的这个实例数据:

Time Average value
0:03 3,666666667
0:13 4,333333333
0:22 5,666666667
0:34 5,666666667
0:35 6
0:37 5,333333333

因此,在0:03时,我需要掌握我在表格中掌握的最新数据并计算平均数。在本案中,它(3+3+5)/3=3,67。当时:13 步骤将重复,再一次在0:22.......。

As I d like to leave the everything within the SQL table (I wouldn t like to create any service with C# or similar which would grab the data and store it into some other table)

我很想知道:

is this the right approach or should I use some other concept of calculating the average for charting data preparation?
if yes, what s the best approach to implement it? Table view, function within the database, stored procedure (which would be called from the charting API)?
any suggestions on how to implement this?

Thank you in advance. Mark

Update 1

In the mean time I got one idea how to approach to this problem. I d kindly ask you for your comments on it and I d still need some help in getting the problem resolved. So, the idea is to crosstab the table like this:

Time    Device1Value    Device2Value    Device3Value
0:00    2           NULL            NULL
0:01    NULL            3           NULL
0:03    3           NULL            5
0:13    NULL            5           NULL
0:22    7           NULL            NULL
0:34    NULL            NULL            5
0:35    NULL            6           NULL
0:37    5           NULL            NULL

要做到这一点,就要:

SELECT Time,
(SELECT Stock FROM dbo.Event WHERE Time = S.Time AND Device = 1) AS Device1Value,
(SELECT Stock FROM dbo.Event WHERE Time = S.Time AND Device = 2) AS Device2Value,
(SELECT Stock FROM dbo.Event WHERE Time = S.Time AND Device = 3) AS Device3Value
FROM dbo.Event S GROUP BY Time

我仍需要做的是,在本问询中写出一个用户界定的职能,在本组织拥有最后可用价值的情况下,如果最后的现值没有的话,就会留下联合国利比里亚特派团的价值。通过这一职能,我取得了以下成果:

Time    Device1Value    Device2Value    Device3Value
0:00    2           NULL            NULL
0:01    2           3           NULL
0:03    3           3           5
0:13    3           5           5
0:22    7           5           5
0:34    7           5           5
0:35    7           6           5
0:37    5           6           5

And by having this results I d be able to calculate the average for each time by only SUMing up the 3 relevant columns and dividing it by count (in this case 3). For NULL I d use 0 value.

是否有任何人建议如何建立用户确定的职能,以替换具有最新价值的联合国扫盲十年价值观?

Update 2

Thanks Martin. This query worked but it took almost 21 minutes to go through the 13.576 lines which is far too much.

使用的最后一个问题是:

SELECT Time,
(SELECT TOP 1 Stock FROM dbo.Event e WHERE e.Time <= S.Time AND Device = 1 ORDER BY e.Time DESC) AS Device1Value,
(SELECT TOP 1 Stock FROM dbo.Event e WHERE e.Time <= S.Time AND Device = 2 ORDER BY e.Time DESC) AS Device2Value,
(SELECT TOP 1 Stock FROM dbo.Event e WHERE e.Time <= S.Time AND Device = 3 ORDER BY e.Time DESC) AS Device3Value
FROM dbo.Event S GROUP BY Time

但我将其扩大到10个装置。

我同意,这不是这样做的最佳方式。是否还有其他办法为平均计算编制数据,因为这只需要太多的处理。

Answer 1

这里有一条路。它采用“Quirky Update”方法填补空白。这取决于无证行为,因此你可能更愿意为此使用治疗器。

DECLARE @SourceData TABLE([Time] TIME, Device INT, value FLOAT) 

INSERT INTO @SourceData
SELECT  0:00 ,1,2 UNION ALL
SELECT  0:01 ,2,3 UNION ALL
SELECT  0:03 ,3,5 UNION ALL
SELECT  0:03 ,1,3 UNION ALL
SELECT  0:13 ,2,5 UNION ALL
SELECT  0:22 ,1,7 UNION ALL
SELECT  0:34 ,3,5 UNION ALL
SELECT  0:35 ,2,6 UNION ALL
SELECT  0:37 ,1,5 

CREATE TABLE #tmpResults
(
[Time] Time primary key,
[1] FLOAT,
[2] FLOAT,
[3] FLOAT
)

INSERT INTO #tmpResults
SELECT [Time],[1],[2],[3]
FROM @SourceData 
PIVOT ( MAX(value) FOR Device IN ([1],[2],[3])) AS pvt
ORDER BY [Time];

DECLARE @1 FLOAT, @2 FLOAT, @3 FLOAT

UPDATE #tmpResults
SET @1 = [1] = ISNULL([1],@1),
    @2 = [2] = ISNULL([2],@2),
    @3 = [3] = ISNULL([3],@3)


SELECT [Time],
       (SELECT AVG(device)
        FROM   (SELECT [1] AS device
                UNION ALL
                SELECT [2]
                UNION ALL
                SELECT [3]) t) AS [Average value]
FROM   #tmpResults  

DROP TABLE #tmpResults

Answer 2

So one of the possible solutions which I found is far more efficient (less than a second for 14.574 lines). I haven t yet had time to review the results in details but on the first hand it looks promising. This is the code for the 3 device example: SELECT Time,
SUM(CASE MAC WHEN 1 THEN Stock ELSE 0 END) Device1Value, SUM(CASE MAC WHEN 2 THEN Stock ELSE 0 END) Device1Value, SUM(CASE MAC WHEN 3 THEN Stock ELSE 0 END) Device1Value, FROM dbo.Event GROUP BY Time ORDER BY Time

无论如何,我对马丁提供的守则进行了测试,看看它是否对结果有任何影响。

Update 1

Update 2

友情链接