Время прочтения: 2 мин.
Нам в работу поступила задача: проанализировать имеющиеся и придумать новые факторы, повышающие точность работы модели. Для анализа был дан массив в сотни тысяч записей. Собрав гипотезы, настал черед проверки их влияния на модель. Анализ массива в Excel привычными формулами/фильтрами привел бы к тому, что на проверку каждой гипотезы уходило бы по несколько часов.
Проконсультировавшись с IT-специалистами, пришли к варианту ускорения путем обработки этих массивов через SQL, но так как работа все же аналитическая, хотелось самим запускать и проверять гипотезы. Пройдя экспресс-обучение по SQL, мы по почерпнули необходимые нам методы анализа, в частности функцию PIVOT, которые и применили в работе.
PIVOT – это оператор Transact-SQL, применяющимся в Microsoft SQL Server, схожий по функционалу со сводными таблицами в Excel. В нем указывается какие параметры нас интересуют по вертикали и горизонтали, указываем способ заполнения таблицы – одну из агрегатных функций, – и получаем готовую таблицу для анализа. На выходе функция может производить группировку по нескольким показателям, выводя их иерархично.
Для наглядности же приведем пример применения функции в простом виде, схожим со сводной таблицей.
Пример:
Сотрудник должен проанализировать корректность расчета кредитного риска или кредитной истории клиентов на основании данных кредитных заявок.
Выведем сколько одобрено кредитных заявок в зависимости от кредитной истории и способа рассмотрения заявки. По горизонтали отразим кредитную историю, а по вертикали – способ рассмотрения. Агрегирующей функцией будет count, так как нам необходимо посчитать количество заявок.
Синтаксис оператора в описанном примере выглядит следующим образом:
With table as (
select Способ_рассмотрения, Кр_история, Заявки
from [base].[table_test]
)
Select *
from table
pivot (count (Заявки) for Кр_история in ([Положительная], [Средняя], [Отрицательная], [Нулевая])) pvt
С помощью этого алгоритма даже массив в сотни тысяч строк отработает за несколько секунд, выдав на выходе следующую таблицу:
Способ рассмотрения | Положительная | Средняя | Отрицательная | Нулевая |
Маршрут 0 | 53000 | 12000 | 10000 | 27000 |
Маршрут 1 | 34000 | 9000 | 17000 | 30000 |
Маршрут 2 | 23000 | 12000 | 8000 | 16000 |
Маршрут 3 | 5000 | 2000 | 1000 | 6000 |
Маршрут 4 | 8000 | 2000 | 3000 | 15000 |
Маршрут 5 | 800 | 200 | 100 | 500 |
В дальнейшем подобный анализ можно проводить уже с выводом значений гипотез в зависимости от разных факторов, которые требуется проверить. С ростом иерархичности в выводе таблица теряет визуальную простоту, но зато увеличивает информативность.
Затратив немного времени на обучение и изучив всего один инструмент, вы можете в разы сократить время на обработку массивов данных, высвободив время для других задач. Ну а мы надеемся, сегодняшний мини-экскурс поможет вам в ваших задачах.