SQL查找多行之间的差异

I have a table containing multiple records for different transactions i.e.

我有一个包含不同交易的多个记录的表,即

ID  Date         REF
1   01/09/2008   A
1   11/09/2008   A
1   01/10/2008   A
2   01/09/2008   A
2   01/10/2008   A
2   01/11/2008   B
2   01/12/2008   B

and I'm looking to summarise the data so that I have the average days for each id and ref... i.e.

我想总结数据,以便我有每个id和ref的平均天数...即

ID  Ref    Avg_Days
1   A      15
2   A      30
2   B      30

Thanks in advance if anyone can help

如果有人可以提供帮助,请提前致谢

3 个解决方案

#1

Average day difference is a SUM of differences divided by COUNT(*)

平均日差是差异的和除以COUNT(*)

SUM of differences is in fact difference between MIN and MAX:

差异的总和实际上是MIN和MAX之间的差异:

SELECT  id, ref, DATEDIFF(day, MIN(date), MAX(date)) / NULLIF(COUNT(*) - 1, 0)
FROM    mytable
GROUP BY
        id, ref

#2

Something like this... not really sure how this info will help you with anything though.... need more info as to what your trying to average the days for.

这样的事情...不确定这些信息将如何帮助你做任何事情....需要更多关于你想要平均日期的信息。

SELECT ID, REF, AVG(DATEPART(day, [Date]))
FROM dbo.Table1
GROUP BY ID, REF

Reference: AVG, DATEPART

参考:AVG,DATEPART

#3

Using sql server 2005 try this.

使用sql server 2005试试这个。

DECLARE @Table TABLE(
        ID INT,
        Date DATETIME,
        Ref VARCHAR(MAX)
)

INSERT INTO @Table (ID,Date,Ref) SELECT 1, '01 Sep 2008', 'A'
INSERT INTO @Table (ID,Date,Ref) SELECT 1, '11 Sep 2008', 'A'
INSERT INTO @Table (ID,Date,Ref) SELECT 1, '01 Oct 2008', 'A'
INSERT INTO @Table (ID,Date,Ref) SELECT 2, '01 Sep 2008', 'A'
INSERT INTO @Table (ID,Date,Ref) SELECT 2, '01 Oct 2008', 'A'
INSERT INTO @Table (ID,Date,Ref) SELECT 2, '01 Nov 2008', 'B'
INSERT INTO @Table (ID,Date,Ref) SELECT 2, '01 Dec 2008', 'B'


;WITH Ordered AS (
    SELECT  ID,
            Ref,
            Date,
            ROW_NUMBER() OVER (PARTITION BY ID, Ref  ORDER BY Date) SubNumber
    FROM    @Table t
)
SELECT  Ordered.ID,
        Ordered.Ref,
        AVG(DATEDIFF(dd, Ordered.Date, OrderedNext.Date)) AVG_Days
FROM    Ordered INNER JOIN
        Ordered OrderedNext ON  Ordered.ID = OrderedNext.ID
                            AND Ordered.Ref = OrderedNext.Ref
                            AND Ordered.SubNumber + 1 = OrderedNext.SubNumber
GROUP BY Ordered.ID,
        Ordered.Ref

Also have a look at it mathematically:

还可以用数学方式看看它:

Let say

([X(1)-X(0)] + [X(2)-X(1)] + [X(3)-X(2)] + ... + [X(n-1)-X(n-2)] + [X(n)-X(n-1)]) / (n-1).

([X(1)-X(0)] + [X(2)-X(1)] + [X(3)-X(2)] + ... + [X(n-1)-X (n-2)] + [X(n)-X(n-1)])/(n-1)。

expand the top part as

将顶部扩展为

-X(0) + X(1) - X(1) + X(2) - X(2) + X(3) - ... - X(n-2) + X(n-1) - X(n-1) + X(n)

-X(0)+ X(1) - X(1)+ X(2) - X(2)+ X(3) - ...... - X(n-2)+ X(n-1) - X (n-1)+ X(n)

whcih end up as -X(0) + X(n)

最终为-X(0)+ X(n)

so we have [X(n) - X(0)] / (n - 1)

所以我们有[X(n) - X(0)] /(n - 1)

so take (MAX - MIN) / (Count - 1) for count > 1

所以取(MAX - MIN)/(Count - 1)计数> 1

#1