Phân tích dữ liệu khách hàng để phát hiện gian lận tiềm ẩn
Trong bộ bài tập cuối cùng này, bạn sẽ phân tích dữ liệu day spa để tìm các dấu hiệu gian lận. Công ty cấp cho mỗi khách một vé sử dụng cá nhân và một vé cho khách đi cùng. Chúng ta có dữ liệu check-in và check-out cho từng khách, và các vé khách đi cùng được liên kết với ID khách hàng gốc. Điều này có nghĩa là có thể có chồng lấn khi cả khách chính và khách đi cùng cùng check-in. Chúng ta muốn kiểm tra xem có ít nhất ba lượt chồng lấn đối với một khách hay không, vì điều đó sẽ vi phạm quy tắc kinh doanh của chúng ta.
Mấu chốt khi suy nghĩ về các lượt chồng lấn là “unpivot” dữ liệu và xem xét dòng sự kiện vào và ra. Trước hết, chúng ta sẽ thực hiện bước đó.
Bài tập này là một phần của khóa học
Phân tích chuỗi thời gian trong SQL Server
Hướng dẫn bài tập
Tách riêng các sự kiện bắt đầu và kết thúc.
- Điền ngày bắt đầu lượt ghé của khách (
dsv.CustomerVisitStart) làmTimeUTCtrong phần "entrances" của truy vấn. - Điền hàm cửa sổ mà chúng ta đặt bí danh là
StartStopPointsđể tạo dòng sự kiện check-in cho từng khách, sắp xếp theo ngày bắt đầu lượt ghé. - Điền ngày kết thúc lượt ghé của khách (
dsv.CustomerVisitEnd) làmTimeUTCtrong phần "departures" của truy vấn.
Bài tập tương tác thực hành trực tiếp
Hãy thử làm bài tập này bằng cách hoàn thành đoạn mã mẫu này.
-- 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