Интерактивен учебник по информатика

Задачата на днешния час

Имаме таблица със събития — по няколко реда на клиент. Моделът иска един ред на клиент. Превръщането се нарича агрегиране и е същината на подготовката на данни.

Признак от агрегация

Число, обобщаващо цялата история на един обект: брой, сума, средно, максимум, дни от последното събитие.

Четирите вида признаци

Вид	Пример	SQL
Броене	брой поръчки	`COUNT(*)`
Сума и средно	общо похарчено, средна поръчка	`SUM, AVG`
Крайности	най-голяма поръчка, първа дата	`MAX, MIN`
Време	дни от последната поръчка, възраст на профила	`DATEDIFF`

Класическата тройка

В търговията се използва RFM: колко скоро (дни от последната покупка), колко често (брой покупки) и колко много (обща сума). Три числа, които обясняват изненадващо голяма част от поведението.

Признаци по прозорци от време

Едно число за цялата история крие динамиката. Клиент с 20 поръчки отпреди три години и клиент с 20 поръчки за последния месец са съвършено различни, а COUNT(*) ги изравнява.

SELECT
    CustomerID,
    COUNT(*)                                                  AS vsichki,
    SUM(CASE WHEN OrderDate >= DATEADD(day, -30, @kray)
             THEN 1 ELSE 0 END)                               AS posledni_30_dni,
    SUM(CASE WHEN OrderDate >= DATEADD(day, -90, @kray)
             THEN 1 ELSE 0 END)                               AS posledni_90_dni
FROM Orders
WHERE OrderDate < @kray
GROUP BY CustomerID;

Съотношението между два прозореца (последните 30 спрямо средното за 90) е още по-силен признак — то показва посоката на промяната.

Прозоречни функции

Кога са незаменими

Когато трябва признак „предишната поръчка на същия клиент“ или „кой пореден е този запис“. С обикновено групиране не се получава, защото резултатът трябва да остане на ниво ред.

SELECT
    OrderID, CustomerID, OrderDate, Total,
    LAG(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS predishna,
    DATEDIFF(day,
        LAG(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate),
        OrderDate)                                                   AS dni_mezhdu,
    ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate)    AS poreden
FROM Orders;

Средно от последните N

Плъзгащото средно изглажда шума и е стандартен признак при предсказване по време:

AVG(Total) OVER (
    PARTITION BY CustomerID ORDER BY OrderDate
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS sredno_posledni_3

Категорийни признаци направо в SQL

One-hot кодирането от 11. клас може да се направи още в заявката — удобно е, когато категориите са малко и известни:

SELECT
    CustomerID,
    SUM(CASE WHEN p.Category = 'Техника'  THEN 1 ELSE 0 END) AS tehnika,
    SUM(CASE WHEN p.Category = 'Книги'    THEN 1 ELSE 0 END) AS knigi,
    SUM(CASE WHEN p.Category = 'Облекло'  THEN 1 ELSE 0 END) AS obleklo
FROM OrderItems oi
JOIN Products p ON p.ProductID = oi.ProductID
JOIN Orders   o ON o.OrderID   = oi.OrderID
GROUP BY CustomerID;

Внимавайте с новите категории

Ако утре се появи категория „Градина“, заявката мълчаливо ще я игнорира. Затова при много и променливи категории кодирането се прави в Python, където липсващата колона се забелязва.

Правила за заявката, която прави набор

Една заявка, един резултат — набор, който може да се запише като CSV или таблица.
Именувайте колоните смислено: те стават имена на признаци в модела.
Никакви NULL в числовите колони — заменяйте с 0 или с изрична стойност.
Фиксирайте крайната дата като параметър, а не GETDATE() — иначе резултатът се променя всеки ден.
Запишете заявката в проекта. Тя е част от него като всеки друг код.