如何在 SQL 中获得适当的加权平均日期 [英] How do I get a proper weighted average date in SQL

问题描述

我使用此查询来获取我的平均日期:

I use this query to get my average date:

CONVERT(DATETIME, AVG(CONVERT(FLOAT, ChargeOffDate))) as [Average C/O Date]

哪个正确地给了我日期'2003-08-12 14:17:22.103'.

Which correctly give me the date '2003-08-12 14:17:22.103'.

当我使用此查询获取加权平均日期时

When I use this query to get the weighted average date

CONVERT(DATETIME, SUM(CONVERT(FLOAT, ChargeOffDate) * FaceValue) / SUM(FaceValue))

我得到一个像1938-10-19 21:28:48.000"这样的日期，这与平均日期相差甚远.我觉得这是一件简单的事情，我错过了一些重要但很小的东西.

I get a date like '1938-10-19 21:28:48.000' which is way off from the average date. I feel like this is a simple thing and I'm missing something critical but small.

示例:

declare @temp table (value datetime, [weight] money)
insert into @temp values (NULL,8850.00)
insert into @temp values (NULL,137.91)
insert into @temp values ('2006-01-15',221.13)
insert into @temp values ('2006-10-15',127.40)
insert into @temp values ('2001-07-31',551.44)
insert into @temp values ('1997-10-12',4963.41)
insert into @temp values ('2006-03-15',130.36)
insert into @temp values ('2005-01-07',1306.31)

SELECT
    CONVERT(DATETIME, AVG(CONVERT(FLOAT, value))) as [Avg Date],
    CONVERT(DATETIME, SUM(CONVERT(FLOAT, value) * [weight]) / SUM([weight])) as [Weighted Avg Date]
FROM @temp

这给出了2003-11-25 20:00:00.000"作为平均日期和1944-10-13 10:52:10.573"作为加权平均值.如果没有空值，它会给出1999-12-02 17:10:51.087".我的问题可能是空值影响了我的计算.

This gives '2003-11-25 20:00:00.000' as an Average Date and '1944-10-13 10:52:10.573' as a weighted average. Without the nulls, it gives '1999-12-02 17:10:51.087'. It's possible my problem is the nulls are throwing off my calculations.

推荐答案

很可能，您的某些 ChargeOffDate 是 NULL，但它们的 FaceValues 不是.

Most probably, some of your ChargeOffDate's are NULL, but their FaceValues are not.

这些记录不会影响第一个总和，但会影响第二个总和.

These records do not contribute into the first sum but do contribute into the second one.

WITH    q (ChargeOffDate, FaceValue) AS
        (
        SELECT  CAST('20030101' AS DATETIME), 1.0
        UNION ALL
        SELECT  CAST('20030201' AS DATETIME), 2.0
        UNION ALL
        SELECT  CAST('20030301' AS DATETIME), 3.0
        UNION ALL
        SELECT  NULL, 4.0
        )
SELECT  CONVERT(DATETIME, SUM(CONVERT(FLOAT, ChargeOffDate) * FaceValue) / SUM(FaceValue))
FROM    q

----
1961-11-12 21:36:00.000

使用这个:

WITH    q (ChargeOffDate, FaceValue) AS
        (
        SELECT  CAST('20030101' AS DATETIME), 1.0
        UNION ALL
        SELECT  CAST('20030201' AS DATETIME), 2.0
        UNION ALL
        SELECT  CAST('20030301' AS DATETIME), 3.0
        UNION ALL
        SELECT  NULL, 4.0
        )
SELECT  CONVERT(DATETIME, SUM(CONVERT(FLOAT, ChargeOffDate) * FaceValue) / SUM(FaceValue))
FROM    q
WHERE   ChargeOffDate IS NOT NULL

----
2003-02-09 20:00:00.000

这篇关于如何在 SQL 中获得适当的加权平均日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！