不正の可能性を分析する（クライアントデータ）
この最終セットの演習では、デイスパのデータを分析して不正の可能性を探ります。弊社では、各顧客に個人用のパスを1枚とゲストパスを1枚付与しています。各クライアントのチェックイン／チェックアウトのデータがあり、ゲストパスは元の顧客IDにひも付きます。つまり、クライアントとゲストが一緒にチェックインすると、滞在時間が重なる可能性があります。1人のクライアントについて、少なくとも3つの重複エントリがあるかを確認したいのですが、これはビジネスルール違反にあたります。
重複エントリを考えるカギは、データをアンピボットして、入場と退場のイベントストリームとして捉えることです。まずはそこから始めます。
この演習はコースの一部です
SQL Serverで学ぶ時系列分析
演習の手順
開始イベントと終了イベントに分割します。
- クエリの「entrances」パートでは、顧客の訪問開始日時（
dsv.CustomerVisitStart）をTimeUTCに入力します。 - 各顧客について訪問開始日時順にチェックインの流れを作るため、
StartStopPointsというエイリアスを付けるウィンドウ関数を入力します。 - クエリの「departures」パートでは、顧客の訪問終了日時（
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