按时间段聚合SQL列值

I have some numerical data that comes in every 5 minutes (i.e. 288 values per day, and quite a few days worth of data). I need to write a query that can return the sums of all values for each day. So currently the table looks like this:

我每5分钟就有一些数字数据(即每天288个值,以及相当多的数据)。我需要编写一个可以返回每天所有值的总和的查询。所以目前这个表看起来像这样:

03/30/2010 00:01:00  --   553   
03/30/2010 00:06:00  --   558   
03/30/2010 00:11:00  --   565  
03/30/2010 00:16:00  --   565  
03/30/2010 00:21:00  --   558   
03/30/2010 00:26:00  --   566  
03/30/2010 00:31:00  --   553   
...

And this goes on for 'x' number of days, I'd like the query to return 'x' number of rows, each of which containing the sum of all the values on each day. Something like this:

这持续了'x'天,我希望查询返回'x'行数,每行包含每天所有值的总和。像这样的东西:

03/30/2010  --  <sum>
03/31/2010  --  <sum>
04/01/2010  --  <sum>

The query will go inside a Dundas webpart, so unfortunately I can't write custom user functions to assist it. All the logic needs to be in just the one big query. Any help would be appreciated, thanks. I'm trying to get it to work using GROUP BY and DATEPART at the moment, not sure if it's the right way to go about it.

查询将进入Dundas webpart,所以不幸的是我无法编写自定义用户函数来帮助它。所有逻辑都只需要在一个大查询中。任何帮助将不胜感激,谢谢。我正在努力让它在目前使用GROUP BY和DATEPART工作,不确定它是否是正确的方法。

3 个解决方案

#1

U can use CAST to date type

你可以使用CAST到日期类型

SELECT [ENTRY_DATE],CAST([ENTRY_DATE] AS date) AS 'date' 
FROM [PROFIT_LIST]

Now you can group by according to this.

现在你可以根据这个分组了。

SELECT CAST([ENTRY_DATE] AS date) AS 'date',SUM(PROFIT) 
FROM [PROFIT_LIST]
GROUP BY CAST([ENTRY_DATE] AS date) AS 'date'

#2

Here's a nice trick. If you cast a SQL DATETIME to a FLOAT it gives you the date as days.fractionofday

这是一个很好的技巧。如果将SQL DATETIME转换为FLOAT,则会将日期作为days.fractionofday

Therefore if you floor that, and turn it back to a DATETIME it gives you minight on the given date.

因此,如果您将其置之不理,并将其转回DATETIME,它会在给定日期为您提供最低价格。

CAST(FLOOR(CAST(MyDateTime  AS FLOAT)) AS DATETIME)

Therefore, my favourite way of doing this is.

因此,我最喜欢这样做的方法是。

select
    CAST(FLOOR(CAST(OrderDate AS FLOAT)) AS DATETIME)
    , sum(taxamt) as Amount
from
    Sales.SalesOrderHeader
group by
        CAST(FLOOR(CAST(OrderDate AS FLOAT)) AS DATETIME)

I have no idea if that is more/less eficient than any previous correct answers.

我不知道这是否比以前任何正确的答案更多/更少有效。

#3

I do not see how you could use DATEPART for this since it cannot return only the date part of a DATETIME value (correct me if I am mistaken). What does work is use DATEADD to "null" the time and group by a value of the form YYYY-MM-DD 00:00:00.000.

我不知道你怎么可以使用DATEPART,因为它不能只返回DATETIME值的日期部分(如果我弄错了,请纠正我)。什么工作是使用DATEADD将时间和组“空”为YYYY-MM-DD 00:00:00.000形式的值。

The following query works against the Adventure Works database in case you happen to have it (tested on SQL Server 2005). Other than using DATEADD it is very similar to @OMG Ponies suggestion:

以下查询适用于Adventure Works数据库,以防您碰巧拥有它(在SQL Server 2005上测试)。除了使用DATEADD之外,它与@OMG Ponies建议非常相似:

select
    dateadd(dd, 0, datediff(dd, 0, OrderDate)) as SaleDay
    , sum(taxamt) as Amount
from
    Sales.SalesOrderHeader
group by
    dateadd(dd, 0, datediff(dd, 0, OrderDate))
order by
    SaleDay

The idea of dateadd(dd, 0, datediff(dd, 0, OrderDate)) is to first get the "number of days passed from the beginning of time until your date" (the datediff-part) and then add this number of days to "the beginning of time". Which gives you the "start of your day". I hope this is understandable :)

dateadd(dd,0,datediff(dd,0,OrderDate))的想法是首先得到“从开始时间到你的日期的天数”(datediff-part),然后加上这个天数到“时间的开始”。这给了你“一天的开始”。我希望这是可以理解的:)

#1