Сейчас мы рассмотрим возможность создания различных вычисляемых полей на базе сервиса Analytic Workspace
Мы будем работать с данными, содержащими информацию о самых продаваемых книгах. Таблица будет содержать поля: название книги, автор, язык публикации, год публикации, объем продаж в миллионах, жанр.
Осмотрев данные, мы видим, что у нас есть целый ряд строковых полей и 2 числовых. Одно из числовых полей отвечает за год. С этим знанием перейдем к созданию полей.
Какие вычисляемые поля добавить?
1. Самый простой вариант — математическая операция.
Иногда нужно, чтобы новые данные были умножены на некий коэффициент или значение другого поля. В этом случае в вычисляемое поле необходимо без лишних дополнений вписать то выражение, которое вам необходимо.
Пример: получение результата продаж в единицах из миллионов.
2. Редакция строк
В вычисляемом поле может быть выражение, позволяющее форматировать текст внутри поля. Для нашего случая добавим поле, которое будет оставлять от имени автора только фамилию. Пример:
substr(author_s, instr(author_s, ' '))
Данный запрос состоит из нескольких функций:
instr(author_s, ' ') — возвращает индекс первого встретившегося в строке символа (символ указывается вторым параметром, в данном случае — пробел)
substr(author_s, instr(author_s, ' ')) — возвращает подстроку из строки author_s. Вторым параметром подается номер символа, с которого читаем до конца строки.
3. Добавление оконной функции
В поле “продажи в миллионах” хранится число продаж по каждой книге. Но важно отметить, что это число характеризует не только конкретную книгу.
Корректно было бы сказать, что число “200” характеризует в том числе какую-то часть некоторых других показателей.
А именно, часть:
- продаж автора Чарльза Дикенса,
- книг, написанных в 1859 году,
- книг, жанра “историческая фантастика”
С помощью оконных функций мы можем создать агрегацию параметров, и полученный по группе результат записать в соседнем поле числом, при этом не изменяя внешнего вида таблицы.
Рассмотрим, как выглядит результат нахождения агрегации с использованием функции group by.
select author_s, sum(approximate_sales_in_millions)
from child
group by author_s
group by не допускает добавления в таблицу полей без агрегирующей функции. Поля, которые невозможно агрегировать — например, название книги — мы потеряем. При использовании оконных функций потерь не происходит, и в этом состоит их преимущество.
Как выглядят случаи, для которых полезна оконная функция?
- Например, мы хотим получить поле, которое показывало бы, сколько всего было продано книг за конкретным автором. И при этом не хотим группировать по автору, как на картинках выше.
Воспользуемся формулой:
sum(approximate_sales_in_millions) over (partition by author_s)
Новая часть для нас —
over (partition by author_s). Она позволяет произвести агрегацию (в нашем случае суммирование) не всех значений вообще, а разделяя по авторам.
- Другой случай — итог с накоплением.
Попробуем проанализировать, как менялась динамика популярности автора с годами его публикаций. Для этого введем запрос, с помощью которого поле “накопление по автору” будет содержать не просто сумму всего, а только сумму продаж книг, написанных до определенного года.
Это будет выглядеть так.
sum(approximate_sales_in_millions) over (partition by author_s order by first_published)
Таким образом, имеем для 1942 — 10 млн, для 1947 — 10+12=22. Это и есть итог с накоплением. В оконной функции за такую функциональность будет отвечать параметр order by first_published
- Предыдущие два пункта можно расширить, если использовать не только агрегирующую функцию суммирования, но и другие.
avg() — нахождение среднего
min() — минимум
max() — максимум
count() — подсчет количества
И это только их небольшая часть.
Таким образом,
- Вычисляемые поля позволяют добавлять новые данные в таблицу, переиспользуя и преобразуя данные из исходных полей
- В вычисляемое поле можно записать математическое выражение или функцию, которая преобразует значение, хранящееся в поле.
- Оконные функции позволяют находить агрегаты данных, не меняя исходной структуры таблицы. Также только с помощью них возможно добавить итоги с накоплением.