March 19, 2020 at 3:45 pm
I have a table that captures rainfall data approx every 10 minutes. I want to calculate a sort of a running total of the Last Hour's Rainfall and the Last 8 Hours Rainfall by Time.
Last Hour's Rainfall = Sum(PrecipitationAmount) for the previous 60 minutes (PrecipitationMinutes is the number of minutes over which the PrecipitationAmount was recorded from the weather station. This will usually be the last 6 values
Last 8 Hour's Rainfall = Sum(PrecipitationAmount) for the previous 480 minutes. This will usually be the last 48 values
Is there a way to do this using OVER (partition by ...)?
Here is the table:
Here is the table:
CREATE TABLE [aData].[tblWeather](
[WeatherID] [int] IDENTITY(1,1) NOT NULL,
[MessageID] [varchar](50) NOT NULL,
[FK_BaseUnitID] [int] NOT NULL,
[PrecipitationAmount] [decimal](6, 2) NULL,
[PrecipitationMinutes] [int] NOT NULL,
[DataTime] [datetime] NOT NULL,
CONSTRAINT [PK_tblWeather] PRIMARY KEY CLUSTERED
(
[WeatherID] ASC
)
)
and here is some sample data:
INSERT INTO tblWeather(MessageID, PrecipitationAmount, PrecipitationMinutes, DataTime) VALUES ('6c4f5129-ddf5-5aa5-a8eb-07c36388dc2d', 0.72, 10, '17-Mar-2020 12:02:30')
INSERT INTO tblWeather(MessageID, PrecipitationAmount, PrecipitationMinutes, DataTime) VALUES ('e16aa602-a153-5f11-8477-f0e861b740b6', 0.48, 10, '17-Mar-2020 12:12:44')
INSERT INTO tblWeather(MessageID, PrecipitationAmount, PrecipitationMinutes, DataTime) VALUES ('b81b36f8-8c62-5923-9593-60124f286fc5', 0.23, 10, '17-Mar-2020 12:22:59')
INSERT INTO tblWeather(MessageID, PrecipitationAmount, PrecipitationMinutes, DataTime) VALUES ('c4c64555-17ac-5e25-98f6-13bfd21df182', 0.17, 11, '17-Mar-2020 12:33:13')
INSERT INTO tblWeather(MessageID, PrecipitationAmount, PrecipitationMinutes, DataTime) VALUES ('c8b99d6c-45b5-57ba-abe1-cb868da297fe', 0.16, 10, '17-Mar-2020 12:43:27')
INSERT INTO tblWeather(MessageID, PrecipitationAmount, PrecipitationMinutes, DataTime) VALUES ('34d4730f-4c01-5f91-becf-25e5867fe008', 0.53, 10, '17-Mar-2020 12:53:42')
INSERT INTO tblWeather(MessageID, PrecipitationAmount, PrecipitationMinutes, DataTime) VALUES ('71d18557-fc82-51f6-9eb4-d3bd306181b2', 0.54, 10, '17-Mar-2020 13:03:56')
INSERT INTO tblWeather(MessageID, PrecipitationAmount, PrecipitationMinutes, DataTime) VALUES ('70e3e9f7-707b-5375-9de7-bf1dbf315593', 0.41, 11, '17-Mar-2020 13:14:11')
INSERT INTO tblWeather(MessageID, PrecipitationAmount, PrecipitationMinutes, DataTime) VALUES ('7e96fd3e-1dda-5a5b-a86a-abd4cc6b465d', 0.07, 10, '17-Mar-2020 13:24:25')
INSERT INTO tblWeather(MessageID, PrecipitationAmount, PrecipitationMinutes, DataTime) VALUES ('8aaf45a9-5eda-5f80-985e-13438acde1a5', 0.63, 10, '17-Mar-2020 13:34:40')
March 19, 2020 at 4:50 pm
I think you've figured it out, you just haven't tried it yet. Google the usage of SUM() OVER (PARTITION BY ORDER BY ) and use ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
March 19, 2020 at 6:05 pm
Be careful of having exact numbers in the ROWS clause if your data isn't clean. You can partition by some datepart(hour), but be aware this might not run quickly. If you're running this often, that could be an issue.
March 20, 2020 at 11:57 am
Thanks Guys,
I think I can get it from that. My data isn't exact but I can get a good approximation from it (as weather data is anyway). I've made a CTE to put together the summary data for 1 hour and 8 hour data. By summing the minutes for each data point, I can see whether the data is valid or not: for 1 hour data if the precipitationminutes is not between 50 & 70 minutes, disregard. For 8 hour data, if the precipitationminutes > 460 and 500 minutes, disregard. It seems to work pretty well. Does it look efficient to the experts?
With RainfallAccumulated (WeatherID, FK_BaseUnitID, OneHourRainfall, OneHourDuration, EightHourRainfall, EightHourDuration)
AS
(
selectWeatherID
,FK_BaseUnitID
,Sum(PrecipitationAmount) OVER (PARTITION BY FK_BaseUnitID ORDER BY DataTime DESC ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
,Sum(PrecipitationMinutes) OVER (PARTITION BY FK_BaseUnitID ORDER BY DataTime DESC ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
,Sum(PrecipitationAmount) OVER (PARTITION BY FK_BaseUnitID ORDER BY DataTime DESC ROWS BETWEEN 46 PRECEDING AND CURRENT ROW)
,Sum(PrecipitationMinutes) OVER (PARTITION BY FK_BaseUnitID ORDER BY DataTime DESC ROWS BETWEEN 46 PRECEDING AND CURRENT ROW)
FROMadata.tblWeather
)
selectW.WeatherID
,MessageID
,DataTime
,OneHourDuration
,EightHourDuration
,CASEWHEN R.OneHourDuration between 50 and 70 THEN OneHourRainfall
ELSE NULL
END AS OneHourRainfall
,CASEWHEN R.EightHourDuration between 460 and 500 THEN EightHourRainfall
ELSE NULL
END AS EightHourRainfall
fromadata.tblweather W INNER JOIN RainfallAccumulated R On W.WeatherID = R.WeatherID
March 26, 2020 at 4:16 pm
A possible solution!
March 26, 2020 at 4:17 pm
USE master;
GO
DROP DATABASE IF EXISTS TestDB;
GO
CREATE DATABASE TestDB;
GO
USE TestDB;
CREATE TABLE dbo.tblWeather
(
WeatherID INT IDENTITY(1,1) NOT NULL
CONSTRAINT PK_tblWeather PRIMARY KEY,
FK_BaseUnitID INT NOT NULL,
PrecipitationAmount DECIMAL(6, 2) NULL,
DataTime DATETIME NOT NULL
);
GO
INSERT INTO dbo.tblWeather (FK_BaseUnitID, PrecipitationAmount, DataTime) VALUES
(27, 0.72, '17-Mar-2020 12:02:30'),
(27, 0.48, '17-Mar-2020 12:12:44'),
(27, 0.23, '17-Mar-2020 12:22:59'),
(27, 0.17, '17-Mar-2020 12:33:13'),
(27, 0.16, '17-Mar-2020 12:43:27'),
(27, 0.53, '17-Mar-2020 12:53:42'),
(27, 0.54, '17-Mar-2020 13:03:56'),
(27, 0.41, '17-Mar-2020 13:14:11'),
(27, 0.07, '17-Mar-2020 13:24:25'),
(27, 0.63, '17-Mar-2020 13:34:40');
GO
WITH MaxTime
AS
(
SELECT MAX(DataTime) AS Endtime
FROM dbo.tblWeather
),
Times
AS
(
SELECT DATETIMEFROMPARTS(YEAR(MIN(DataTime)), MONTH(MIN(DataTime)), DAY(MIN(DataTime)),
DATEPART(Hour, MIN(DataTime)), 0, 0, 0) AS AggTime
FROM dbo.tblWeather
UNION ALL
SELECT DATEADD(HOUR, 1, AggTime)
FROM Times
WHERE AggTime < (SELECT EndTime FROM MaxTime)
),
SumHour
AS
(
SELECT FK_BaseUnitID,
Times.AggTime,
SUM(PrecipitationAmount) AS SumPrecipitationAmount
FROM Times LEFT JOIN dbo.tblWeather ON tblWeather.DataTime >= Times.AggTime AND
tblWeather.DataTime < DATEADD(HOUR, 1, Times.AggTime)
GROUP BY FK_BaseUnitID, Times.AggTime
)
SELECT FK_BaseUnitID,
AggTime AS TimeFromOneHour,
DATEADD(SECOND, 3599, AggTime) TimeToOneHour,
SumPrecipitationAmount AS SumOneHour,
MIN(AggTime) OVER(PARTITION BY FK_BaseUnitID ORDER BY AggTime ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS TimeFromEightHour,
DATEADD(SECOND, 3599, AggTime) AS TimeToEightHour,
SUM(SumPrecipitationAmount) OVER(PARTITION BY FK_BaseUnitID ORDER BY AggTime ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS SumEightHour
FROM SumHour
WHERE FK_BaseUnitID = 27
ORDER BY FK_BaseUnitID, AggTime
OPTION (MAXRECURSION 0);
GO
-- Many Data
INSERT INTO dbo.tblWeather (FK_BaseUnitID, PrecipitationAmount, DataTime)
SELECT FK_BaseUnitID,
PrecipitationAmount,
DATEADD(MINUTE,
DATEDIFF(MINUTE,
(SELECT MIN(DataTime) FROM dbo.tblWeather),
(SELECT MAX(DataTime) FROM dbo.tblWeather)) + 10,
DataTime)
FROM dbo.tblWeather;
GO 8
-- More data
SELECT (SELECT MIN(DataTime) FROM dbo.tblWeather),
(SELECT MAX(DataTime) FROM dbo.tblWeather);
UPDATE dbo.tblWeather
SET PrecipitationAmount = NULL
WHERE Datatime BETWEEN DATEADD(HOUR, 4, (SELECT MIN(DataTime) FROM dbo.tblWeather)) AND
DATEADD(HOUR, 7, (SELECT MIN(DataTime) FROM dbo.tblWeather));
UPDATE dbo.tblWeather
SET PrecipitationAmount = NULL
WHERE Datatime BETWEEN DATEADD(HOUR, 201, (SELECT MIN(DataTime) FROM dbo.tblWeather)) AND
DATEADD(HOUR, 338, (SELECT MIN(DataTime) FROM dbo.tblWeather));
UPDATE dbo.tblWeather
SET PrecipitationAmount = PrecipitationAmount + 0.3
WHERE Datatime BETWEEN DATEADD(HOUR, 47, (SELECT MIN(DataTime) FROM dbo.tblWeather)) AND
DATEADD(HOUR, 211, (SELECT MIN(DataTime) FROM dbo.tblWeather));
March 26, 2020 at 4:54 pm
Thanks, I'll have a play and report back on how it goes over the weekend.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply