分析客戶資料以偵測潛在詐欺
在最後這組練習中，你會分析日間 Spa 的資料，以找出潛在的詐欺行為。我們的公司為每位客戶提供 1 張個人使用的通行證，以及 1 張訪客通行證。我們有每位客戶的報到與離場資料，且訪客通行證會連回該客戶的基礎客戶 ID。這表示當客戶與訪客同時報到時，時段可能會重疊。我們想檢查是否有單一客戶出現至少 3 個重疊的條目，因為那會違反公司的營運規範。
思考重疊條目的關鍵，是將資料反向樞紐（unpivot），把它視為一連串的進出事件。我們會先這麼做。
本練習屬於課程
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