Summarize data over a time frame
There are several useful aggregate functions in SQL Server which we can use to summarize our data over time frames and gain insights. In the following example, you will look at a set of incident reports at a fictional company. They have already rolled up their incidents to the daily grain, giving us a number of incidents per type and day. We would like to investigate further and review incidents over a three-month period, from August 1 through October 31st, and gain basic insights through aggregation.
The key aggregate functions we will use are COUNT(), SUM(), MIN(), and MAX(). In the next exercise, we will look at some of the statistical aggregate functions.
Questo esercizio fa parte del corso
Time Series Analysis in SQL Server
Istruzioni dell'esercizio
- Fill in the appropriate aggregate function based on the column name. Choose from
COUNT(),SUM(),MIN(), andMAX()for each.
Esercizio pratico interattivo
Prova a risolvere questo esercizio completando il codice di esempio.
-- Fill in the appropriate aggregate functions
SELECT
it.IncidentType,
___(1) AS NumberOfRows,
___(ir.NumberOfIncidents) AS TotalNumberOfIncidents,
___(ir.NumberOfIncidents) AS MinNumberOfIncidents,
___(ir.NumberOfIncidents) AS MaxNumberOfIncidents,
___(ir.IncidentDate) As MinIncidentDate,
___(ir.IncidentDate) AS MaxIncidentDate
FROM dbo.IncidentRollup ir
INNER JOIN dbo.IncidentType it
ON ir.IncidentTypeID = it.IncidentTypeID
WHERE
ir.IncidentDate BETWEEN '2019-08-01' AND '2019-10-31'
GROUP BY
it.IncidentType;