如何在 SQL Qry 中获取平均日期 [英] How to get Avg Date in SQL Qry

问题描述

我正在尝试获取 sql 中的平均日期，但不确定如何执行此操作.我有以下字段:

I am trying to get the Average Date in sql and am not sure how to do this. I have the following fields:

ID   Date1             Date2                   Date3
1    05/04/2012        08/09/2012             07/02/2012

我想添加另一列，显示 ID 号 1 的平均日期.我只做过平均数，但从未做过平均日期.谢谢

i want to add another column that shows the Average Date for the ID number 1. I have done only average number but never done average Date. thanks

推荐答案

您可以使用类似这样的方法，将日期转换为 float，然后取平均值:

You could use something like this, which casts the date to a float and then takes the average:

select t.id, t.date1, t.date2, t.date3, src.AvgDate
from yourtable t
inner join 
(
  select id, cast(avg(date) as datetime) AvgDate
  from
  (
    select id, cast(cast(date1 as datetime) as float) date
    from yourtable
    union all
    select id, cast(cast(date2 as datetime) as float) date
    from yourtable
    union all
    select id, cast(cast(date3 as datetime) as float) date
    from yourtable
  ) x
  group by id
) src
  on t.id = src.id;

参见SQL Fiddle with Demo

由于您的日期值跨多列，我执行了 unpivot 或 union all 以先获取单列中的值，然后取该值的平均值.

Since your date values are across multiple columns, I performed an unpivot or union all to get the values in a single column first then take the average of that value.

这是使用 UNPIVOT 函数的另一个示例:

Here is another example of using the UNPIVOT function:

select t.id, t.date1, t.date2, t.date3, src.AvgDate
from yourtable t
inner join 
(
  select id, cast(avg(date) as datetime) AvgDate
  from
  (
    select id, cast(cast(date as datetime) as float) date
    from yourtable
    unpivot
    (
      date
      for col in (date1, date2, date3)
    ) unpiv
  ) x
  group by id
) src
  on t.id = src.id;

参见SQL Fiddle with Demo

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