MS Access SQL获取两条记录的平均值 [英] MS Access SQL to get the average of two records

问题描述

当前，我正在使用MS Excel来执行此操作，但我想知道在MS Access中是否可行.

Currently, I am using MS Excel to do this but I would like to know if it is possible in MS Access.

我想获取前一个小时和当前小时的平均值，然后将该平均值放入当前小时记录中.一个限制是不要查询第一条记录，因为它没有前一个小时.

I would like to get the average of a value from the previous hour and the current hour, and put that average value in the current hour record. One restriction is not to query the first record since it has no previous hour.

应该如何实现下面的伪sql代码?

How should go about implementing the pseudo-sql code below?

SELECT Date
      ,Hour
      ,Node
      ,Average (Value,"Value from previous hour, e.g (Hour-1) with the same date and node")
FROM tblInput
WHERE Hour = 2,3,4

tblInput:

+----------+------+------+------+
|   Date   | Hour | Node |Value |
+----------+------+------+------+
|   ...    | ...  | ...  | ...  |
| 1/1/2-18 |   1  | AAA  | 5    |
| 1/1/2-18 |   2  | AAA  | 10   |
| 1/1/2-18 |   3  | AAA  | 15   |
| 1/1/2-18 |   4  | AAA  | 20   |
| 1/1/2-18 |   1  | BBB  | 4    |
| 1/1/2-18 |   2  | BBB  | 8    |
| 1/1/2-18 |   3  | BBB  | 12   |
| 1/1/2-18 |   4  | BBB  | 16   |
|   ...    | ...  | ...  | ...  |
+----------+------+------+------+

输出:

+----------+------+------+------+
|   Date   | Hour | Node | Ave  |
+----------+------+------+------+
| 1/1/2-18 |   2  | AAA  | 7.5  |
| 1/1/2-18 |   3  | AAA  | 12.5 |
| 1/1/2-18 |   4  | AAA  | 17.5 |
| 1/1/2-18 |   2  | BBB  | 6    |
| 1/1/2-18 |   3  | BBB  | 10   |
| 1/1/2-18 |   4  | BBB  | 14   |
+----------+------+------+------+

推荐答案

它是:

SELECT 
    [Date],
    Hour,
    Node,
    (Value + 
        (Select Value
        From tblInput As T
        Where 
            T.Node = tblInput.Node And
            T.Date = tblInput.Date And 
            T.Hour = tblInput.Hour - 1)) / 2
FROM 
    tblInput
WHERE 
    Hour In (2,3,4)

这篇关于MS Access SQL获取两条记录的平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！