Analyser les données des clients pour détecter une fraude potentielle

Dans ce dernier ensemble d'exercices, nous allons analyser les données d'un spa de jour afin de repérer une fraude potentielle. Notre entreprise accorde à chaque client un laissez-passer personnel et un seul laissez-passer pour un invité. Nous disposons des données d'arrivée et de départ pour chaque client, et les laissez-passer des invités sont associés à l'ID du client principal. Cela signifie qu'il peut y avoir chevauchement lorsque le client et son invité s'enregistrent en même temps. Nous voulons vérifier s'il y a au moins trois occurrences qui se chevauchent pour un même client, car cela contrevient à notre règle d'affaires.

La clé pour raisonner sur des occurrences qui se chevauchent consiste à « dé-pivoter » nos données et à considérer le flux des entrées et des sorties. Nous allons commencer par cela.

Cette activité fait partie du cours

Analyse de séries chronologiques dans SQL Server

Voir le cours

Instructions de l’exercice

Séparez les événements de début et de fin.

Renseignez la date de début de visite du client (dsv.CustomerVisitStart) comme TimeUTC dans la partie « entrées » de la requête.
Renseignez la fonction de fenêtre que nous appelons StartStopPoints pour obtenir le flux des enregistrements d'arrivée pour chaque client, ordonné par leur date de début de visite.
Renseignez la date de fin de visite du client (dsv.CustomerVisitEnd) comme TimeUTC dans la partie « sorties » de la requête.

Exercice interactif pratique

Essayez cet exercice en complétant ce code d’exemple.

-- 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

Modifier et exécuter le code