Analiza danych klientów pod kątem potencjalnych nadużyć
W tym ostatnim zestawie ćwiczeń przeanalizujemy dane spa, aby wykryć potencjalne nadużycia. Każdy klient otrzymuje jeden karnet na własny użytek oraz jeden karnet dla gościa. Dysponujemy danymi o zameldowaniach i wymeldowaniach dla każdego klienta, a karnety gościnne są powiązane z identyfikatorem klienta bazowego. Oznacza to, że może dochodzić do nakładania się wpisów, gdy klient i jego gość zameldują się jednocześnie. Chcemy sprawdzić, czy dla jednego klienta istnieją co najmniej trzy nakładające się wpisy – byłoby to naruszenie naszych zasad biznesowych.
Kluczem do analizy nakładających się wpisów jest przestawienie danych i spojrzenie na strumień wejść oraz wyjść. Od tego właśnie zaczniemy.
To ćwiczenie jest częścią kursu
Analiza szeregów czasowych w SQL Server
Instrukcje do ćwiczenia
Rozdziel zdarzenia wejść i wyjść.
- Wstaw datę rozpoczęcia wizyty klienta (
dsv.CustomerVisitStart) jakoTimeUTCw części zapytania dotyczącej „wejść". - Wstaw funkcję okna, którą aliasujemy jako
StartStopPoints, aby uzyskać strumień zameldowań dla każdego klienta z sortowaniem według daty rozpoczęcia wizyty. - Wstaw datę zakończenia wizyty klienta (
dsv.CustomerVisitEnd) jakoTimeUTCw części zapytania dotyczącej „wyjść".
Interaktywne ćwiczenie praktyczne
Spróbuj tego ćwiczenia, uzupełniając ten przykładowy kod.
-- This section focuses on entrances: CustomerVisitStart
SELECT
dsv.CustomerID,
dsv.___ AS TimeUTC,
1 AS EntryCount,
-- We want to know each customer's entrance stream
-- Get a unique, ascending row number
___() OVER (
-- Break this out by customer ID
PARTITION BY dsv.___
-- Ordered by the customer visit start date
ORDER BY dsv.___
) AS StartOrdinal
FROM dbo.DaySpaVisit dsv
UNION ALL
-- This section focuses on departures: CustomerVisitEnd
SELECT
dsv.CustomerID,
dsv.___ AS TimeUTC,
-1 AS EntryCount,
NULL AS StartOrdinal
FROM dbo.DaySpaVisit dsv