Build a stream of events
In the prior exercise, we broke out day spa data into a stream of entrances and exits. Unpivoting the data allows us to move to the next step, which is to order the entire stream.
The results from the prior exercise are now in a temporary table called #StartStopPoints. The columns in this table are CustomerID, TimeUTC, EntryCount, and StartOrdinal. These are the only columns you will need to use in this exercise. TimeUTC represents the event time, EntryCount indicates the net change for the event (+1 or -1), and StartOrdinal appears for entrance events and gives the order of entry.
Diese Übung ist Teil des Kurses
Time Series Analysis in SQL Server
Anleitung zur Übung
- Fill out the appropriate window function (
ROW_NUMBER()) to create a stream of check-ins and check-outs in chronological order. - Partition by the customer ID to calculate a result per user.
- Order by the event time and solve ties by using the start ordinal value.
Interaktive Übung
Vervollständige den Beispielcode, um diese Übung erfolgreich abzuschließen.
SELECT s.*,
-- Build a stream of all check-in and check-out events
___() OVER (
-- Break this out by customer ID
PARTITION BY s.___
-- Order by event time and then the start ordinal
-- value (in case of exact time matches)
ORDER BY s.___, s.___
) AS StartOrEndOrdinal
FROM #StartStopPoints s;