Сглаживание данных в SQL
Внедрите скользящее среднее, чтобы сгладить данные в SQL
Проблема, встречающаяся во всем мире данных, заключается в том, как отличить сигнал от шума. При работе с данными, которые поступают в последовательности, такими как данные временных рядов (наиболее знакомый, но ни в коем случае не единственный), частым методом решения проблемы является применение более гладкого.
Сглаживания могут варьироваться от очень простых до очень сложных, но мы сосредоточимся на более простом конце спектра.
Все эти сглаживающие устройства, независимо от того, насколько они просты или сложны, имеют общее то, что они полагаются на данные, появляющиеся в определенном порядке. Чтобы вычислить более плавное, вам нужно иметь возможность ссылаться на несколько строк вокруг строки в фокусе, что не всегда было хорошо настроено в SQL.
В последние годы основные реализации SQL, включая MySQL, SQLite, PostgreSQL и SQL Server, добавили функции Lead () и Lag (), позволяющие выполнять вычисления для строк, появляющихся до и после интересующей строки. Мы можем использовать эти функции для реализации простых средств сглаживания в SQL, таких как взвешенное скользящее среднее.
Как и все оконные функции, SQL дает вам гибкость в том, как указать данные, к которым функция применяет, с помощью ключевых слов «PARTITION» и «OVER». В семантическом плане
SELECT Index, Classifier, Value, LAG(Value) OVER (Partition By Classifier ORDER BY Index) FROM YourTable
Обратите внимание, что смещение по умолчанию - одно место, но вы можете указать смещение в функции Lag, например. Lag (Value, 2) находится на две строки позади исходной строки.
Однако мы не можем возвращать несколько переменных оконных функций в одном столбце, поэтому мы не можем выполнять арифметические действия, необходимые для вычисления самого взвешенного скользящего среднего. Поэтому нам нужно создать Common Table Expression с, чтобы предоставить лаговые столбцы, и выполнить последующий запрос для вычисления самого взвешенного скользящего среднего. Опять же, чисто семантически:
WITH Base_Table
AS
(SELECT Index, Classifier, Value
,LAG(Value) OVER (Partition By Classifier ORDER BY Index) as LagVal1
,LAG(Value,2) OVER (Partition By Classifier ORDER BY Index) as LagVal2
FROM YourTable)
SELECT Index, Classifier
, (Value + LagVal1 + LagVal2)/3 as MovingAverage
From Base_Table
Однако для правильной иллюстрации нам нужен хороший пример. Хорошо это или плохо, но я австралиец из Виктории, поэтому футбольный кодекс, которому я следую, - это Австралийские правила (с меньшей вероятностью, если бы я был австралийцем из Нового Южного Уэльса или Квинсленда). Моя команда - Essendon Bombers, которая недавно зафиксировала одно из самых больших поражений в своей истории против Western Bulldogs, сумев набрать только 33 очка против 137 очков Western Bulldogs.
В качестве самобичевания я подумал, что было бы поучительно рассчитать скользящее среднее очков обеих команд в последних матчах, чтобы лучше видеть, как каждая из них имеет тенденцию.
Во-первых, нам нужно создать таблицу, содержащую результаты нескольких последних матчей.
CREATE TABLE AFL_SCORES
( Team VarChar(30)
, RoundNum Int
, Score Int
)
;
INSERT INTO AFL_SCORES
VALUES
('Essendon', 14, 71)
, ('Essendon', 15, 77)
, ('Essendon', 16, 76)
, ('Essendon', 17, 86)
, ('Essendon', 18, 96)
, ('Essendon', 19, 106)
, ('Essendon', 20, 67)
, ('Essendon', 21, 33)
, ('Western Bulldogs', 14, 73)
, ('Western Bulldogs', 15, 66)
, ('Western Bulldogs', 16, 71)
, ('Western Bulldogs', 17, 74)
, ('Western Bulldogs', 18, 89)
, ('Western Bulldogs', 19, 113)
, ('Western Bulldogs', 20, 80)
, ('Western Bulldogs', 21, 137)
;
Затем у нас есть сам запрос, следуя представленному выше шаблону CTE для создания лаговых столбцов и последующего запроса для вычисления самого скользящего среднего. Обратите внимание, что это простая скользящая средняя, но во многих случаях используется взвешенная скользящая средняя с множеством различных схем взвешивания для удовлетворения разных вкусов.
WITH LagsTable
AS
(SELECT Team
, RoundNum
, Score
, Lag(Score) OVER (Partition By Team Order By RoundNum) AS ScoreLag1
, Lag(Score,2) OVER (Partition By Team Order By RoundNum) AS ScoreLag2
FROM AFL_SCORES)
SELECT Team
, RoundNum
, (Score+ScoreLag1+ScoreLag2)/3 As MovingAverage
FROM LagsTable
Даем результат:
Возможно, это немного утешит болельщиков Эссендона в том смысле, что, похоже, «Вестерн Бульдоги» улучшаются несколько раундов подряд - более сильная тенденция, чем недавнее падение результатов Эссендона.
В любом случае, это полезный, хотя и болезненный способ проиллюстрировать, как можно использовать SQL для вычисления скользящего среднего с оконными функциями.
Книга Роберта де Граафа Управление проектами в области науки о данных теперь распространяется через Apress.
