如何计算一个月24小时数据的总和 [英] How to calculate sum of 24 hours data of a month

问题描述

表结构有一个时间戳（日期时间）和一个类似于此的值。

The table structure has a timestamp(datetime), and a value, similar to this..

-----timestamp-----             --value--
2009-08-13 08:12:00              200.40
2009-08-13 07:47:00               70.00
2009-08-13 07:33:00               84.00
2009-08-13 07:12:00               350.00
2009-08-12 06:12:00                10.70
2009-08-12 05:12:00                50.86

我想要输出如下一个月的格式：

I want output as following format for a month:

------------Time-------------                 -----Sum_Value------

28-AUG-2014 06 AM - 29-AUG-2014 06 AM	       553.212
29-AUG-2014 06 AM - 30-AUG-2014 06 AM	       553.212
30-AUG-2014 06 AM - 31-AUG-2014 06 AM	      1053.212

请帮帮我写一个SQL查询

please help me to write a sql query

推荐答案

我没有访问Oracle数据库的权限。我使用SQL Server 2012做了这个解决方案。也许您可以用它来帮助您使用Oracle解决方案。



要求查询的日期来自第二天早上6点到第二天早上6点提出了一些挑战。为了解决这个问题，我首先使用公用表表达式将值与日期相加。我使用CASE语句来计算日期。如果时间小于6AM，则将日期设置为前一天。创建CTE后，我选择结果并将日期列格式化为您的规格，包括日期中的破折号。





创建表

I don't have access to an Oracle database. I did this solution using SQL Server 2012. Maybe you can use it to help you with your Oracle solution.

The requirement that a "day" for your query is from 6:00AM on one day until 6:00AM on the next day presents a bit of a challenge. To solve that, I first used a Common Table Expression to sum the values by a date. I use the CASE statement to compute the date. If the time is less than 6AM, then the date is set to the day before. After creating the CTE, I select the results and format the "Date" column to your specification including the dashes in the date.


Create the table

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblLog](
    [TimeStamp] [datetime] not NULL,
    [Value] [decimal](18, 3) not NULL
) ON [PRIMARY]
GO

将一些数据放入表中

Put some data into the table

insert into tblLog values('2014-09-01 07:00:00',100.1);
insert into tblLog values('2014-09-01 08:00:00',100.1);
insert into tblLog values('2014-09-02 07:00:00',100.1);
insert into tblLog values('2014-09-02 08:00:00',100.1);
insert into tblLog values('2014-09-03 07:00:00',100.1);
insert into tblLog values('2014-09-03 08:00:00',100.1);
insert into tblLog values('2014-09-01 05:00:00',100.1);
insert into tblLog values('2014-09-01 06:00:00',100.1);
insert into tblLog values('2014-09-02 05:00:00',100.1);
insert into tblLog values('2014-09-02 06:00:00',100.1);
insert into tblLog values('2014-09-03 05:00:00',100.1);
insert into tblLog values('2014-09-03 06:00:00',100.1);

执行query

Execute the query

With Sums (LogDate,Sum)
AS
(
SELECT
 CASE
 WHEN CAST(timestamp AS time)<'06:00 am' THEN DATEADD(day,-1,CAST(timestamp AS date)) ELSE CAST(timestamp AS date)
 END AS LogDate,
 SUM(value) AS Sum
 FROM tblLog
 GROUP BY
 CASE
 WHEN CAST(timestamp AS time)<'06:00 am' THEN DATEADD(day,-1,CAST(timestamp AS date)) ELSE CAST(timestamp AS date)
 END
 )
 SELECT CONCAT(REPLACE(CONVERT(NVARCHAR,LogDate,113),' ','-'),' 06 AM - ',REPLACE(CONVERT(NVARCHAR,DATEADD(day,1,LogDate),113),' ','-'),' 06 AM') AS LogDate,Sum from Sums
 ORDER BY CAST(LogDate AS DATE)

结果

The results

LogDate                                 Sum
31-Aug-2014 06 AM - 01-Sep-2014 06 AM	100.100
01-Sep-2014 06 AM - 02-Sep-2014 06 AM	400.400
02-Sep-2014 06 AM - 03-Sep-2014 06 AM	400.400
03-Sep-2014 06 AM - 04-Sep-2014 06 AM	300.300

请chechk这个

Please chechk this

select trunc(datecolumn) - 1 || 'AM - ' || trunc(datecolumn) || 'AM' as RecordDate,sum(valuecolumn) as AverageValue  from Yourtable group by trunc(datecolumn);

这篇关于如何计算一个月24小时数据的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！