Анализ данных клиентов на предмет мошенничества
В этом финальном блоке упражнений мы проанализируем данные дневного спа-салона, чтобы выявить возможные случаи мошенничества. Компания предоставляет каждому клиенту один личный пропуск и один гостевой. У нас есть данные о заселении и выезде для каждого клиента, а гостевые пропуска привязаны к идентификатору основного клиента. Это означает, что при одновременном посещении клиента и его гостя может возникать пересечение. Нас интересует ситуация, когда для одного клиента зафиксировано не менее трёх пересекающихся записей — это нарушение бизнес-правила.
Ключ к анализу пересечений — депивотировать данные и рассмотреть поток входов и выходов. Именно с этого мы и начнём.
Это упражнение является частью курса
Анализ временных рядов в SQL Server
Инструкции к упражнению
Разделите события на входы и выходы.
- Укажите дату начала визита клиента (
dsv.CustomerVisitStart) в качествеTimeUTCв части запроса, отвечающей за «входы». - Укажите оконную функцию, которой присваивается псевдоним
StartStopPoints, чтобы получить поток отметок о заселении для каждого клиента с сортировкой по дате начала визита. - Укажите дату окончания визита клиента (
dsv.CustomerVisitEnd) в качествеTimeUTCв части запроса, отвечающей за «выходы».
Интерактивное практическое упражнение
Попробуйте выполнить это упражнение, дополнив этот пример кода.
-- 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