There are 8,760 hours in a year! (That’s all…)

There is a climate chart that stores temperature data for the area from 1 to 8,760 hours. Now, average daily temperatures throughout the year.

CREATE TABLE #Climate(h INT ,t DECIMAL(18.4));--h: hour t: temperature value
-- there are 8,760 records, corresponding to the temperature of every hour of the year

-- Now, you want to save the average temperature of the whole year by day in table #tD
CREATE TABLE #tD(d INT ,avgt DECIMAL(18.4));-- D: day AVgt: average temperature

The simplest is to use loops
DECLARE @d INT;
DECLARE @h1 INT.@h2 INT;
SET @d = 1;
WHILE @d < = 365
BEGIN
	SET @h1 = 24 * (@d - 1);
	SET @h2 = @h1 + 24; 

	Caches calculated values to table variables
	INSERT INTO #tD(d,avgt)
		SELECT @d
			,AVG(t) 
		FROM [#Climate]
		WHERE h BETWEEN @h1 AND @h2;

	SET @d = @d + 1;
END --end of @d <= 365
Copy the code

However, there are no advantages to using loops in stored procedures. We can do this with an SQL statement.

The first step is to set up a day table. There are 365 days in a year, and there should be 365 records

CREATE TABLE #days(d INT);
DECLARE @i INT = 1;
WHILE @i < = 365
BEGIN
	INSERT INTO #days(d) VALUES(@i);
	SET @i = @i + 1;
END
Copy the code

Using this table, we can aggregate

INSERT INTO #tD(d,avgt)
		SELECT ds.d
			,AVG(c.t) 
		FROM [#Climate] c,[#days] AS ds
		WHERE c.h BETWEEN 24 * (ds.d - 1) AND ds.d * 24
		GROUP BY ds.d;
Copy the code


\

\

\