Продвинутая аналитика на SQL
2026-02-26 17:16 Diff
https://ru.hexlet.io/courses/analytics-with-sql/lessons/candlestick-chart/theory_unit

В этом уроке мы рассмотрим свечной график или Candlestick chart. Он широко используется для изображения котировок валют. Сегодня мы агрегируем данные валютной пары за 19 лет и построим график в Google Sheets.

Знания и навыки об агрегации данных для свечного графика позволят работать аналитиком в финансовой сфере.

Candlestick chart

Представим, что у нас есть котировки валюты австралийского доллара относительно американского доллара. Мы хотим узнать, как они меняются со временем. Мы уже умеем строить линейный график, поэтому сначала изобразим его:

На этом рисунке мы видим линейный график котировок валют австралийского доллара относительно американского доллара.

Но такой график для отображения котировок неудобен и неинформативен. Мы видим только изменение текущей цены со временем. Торги на рынке происходят в каком-то окне: есть цена открытия и цена закрытия. Цена открытия — это цена, с которой начинаются торги, а по цене закрытия они заканчиваются. Поэтому таблицы с котировками обычно выглядят более сложно:

currency_exchange

В этой таблице представлены котировки валюты с шагом в один месяц. Она содержит такие столбцы:

  1. date — дата
  2. low — минимальное значение цены за этот месяц
  3. open — цена открытия. Это первая цена за месяц
  4. high — максимальная цена за месяц
  5. close — цена закрытия. Это последняя цена за месяц

Из этой таблицы мы понимаем, как торги начинались и заканчивались. Также знаем минимальное и максимальное значение котировок. Для визуального представления таких таблиц используют свечной график или Candlestick chart. Он удобнее линейного графика, потому что позволяет визуализировать все параметры: цену открытия, закрытия, минимальную и максимальную цены.

Посмотрим, из чего он состоит, и где на нем находятся данные из таблицы:

На свечном графике каждый промежуток времени мы изображаем в виде «свечи». У свечи есть тело и фитиль. Нижний и верхний фитили на картинке обозначены как Lower Wick и Upper Wick. Тело свечи отмечено как Real Body. Open и Close — это цены открытия и закрытия.

Тело — это прямоугольник, у которого одна из границ — цена открытия, а вторая — закрытия. Цена открытия бывает меньше или больше цены закрытия — это зависит от трендов на рынке.

Фитиль рисуется узкой вертикальной линией. Верхняя граница фитиля — это максимальная цена за период торгов, а нижняя — минимальная. Если фитиль длинный, а тело нет — это говорит о том, что рынок очень волатильный, то есть быстро меняется.

По таблице CurrencyExchange мы можем построить свечной график котировок. Он будет выглядеть так:

Здесь мы видим свечной график котировок валют. Он содержит в себе не информацию о текущей стоимости валюты, а цену открытия, закрытия, минимальную и максимальную во временном окне, когда идут торги.

Чтобы получить из таблицы, в которой есть только текущая стоимость, таблицу с ценами открытия, закрытия, минимальной и максимальной, мы агрегируем данные. По агрегированной таблице мы уже построим свечной график.

Агрегация курсов валют

Мы будем агрегировать таблицу курса валют помесячно. Для агрегации мы проделаем такие шаги:

  1. Создадим вспомогательную таблицу с первыми и последними значениями цены за месяц
  2. Создадим вспомогательную таблицу для цен открытия и закрытия
  3. Создадим вспомогательные таблицы для минимальных и максимальных значений за месяц
  4. Объединим эти таблицы с помощью SQL-функции join и проведем итоговую агрегацию

Первые и последние значения цены за месяц

Откроем базу australian_currency.

В базе данных есть таблица exchange с двумя полями:

  • date — дата, в которую валюта имела определенную стоимость
  • currency — значение котировки валюты

Посмотрим на саму таблицу:

Currency

В этой таблице 5019 строк. Мы видим, что в таблице есть данные за 2000-2019 года.

Нам нужно создать новую таблицу с минимумом, максимумом, первой ценой и последней ценой за определенный период времени. Мы будем агрегировать данные помесячно.

Чтобы получить первую и последнюю цены за месяц, мы создадим вспомогательную таблицу monthly_dates. В нее мы агрегируем первую и последнюю дату за месяц, само значение месяца и уникальный ключ, по которому мы будем джойнить таблицы:

Мы создали таблицу с полями:

  • year_month — уникальный строковый ключ с годом и месяцем
  • month — значение месяца
  • min_date — первая дата за месяц
  • max_date — последняя дата за месяц

Агрегируем данные и запишем их в новую таблицу:

Мы записали в таблицу ключ year_month, поле month, минимальное значение даты за месяц min_date и максимальное значение даты max_date.

Функция concat применяется, чтобы объединить несколько строковых значений. date_trunc позволяет «схлопнуть» даты временного промежутка в один. А с помощью значения month в функции date_trunc мы использовали агрегацию в месяц.

Сортировка по дате нужна, чтобы избежать перемешивания дат. Уникальный ключ, в котором есть год и месяц, нам понадобится в дальнейшем для джойна таблиц.

Цены открытия и закрытия

Теперь мы создадим таблицу open, чтобы сохранить в нее первые значения котировок в месяце. В ней тоже будет уникальный ключ year_month, а также первая дата за месяц и значение open цены валюты в эту дату:

Теперь сджойним таблицу monthly_dates с таблицей open по минимальной дате. Мы не будем использовать агрегацию, а только возьмем значения котировок в минимальные даты каждого месяца:

По аналогии с таблицей open создадим таблицу close, в которой будет последнее значение валюты за месяц. Мы будем джойнить ее с таблицей monthly_dates по максимальной дате месяца:

Минимальные и максимальные цены за месяц

Теперь мы создадим таблицу min_max_currency. В ней будет значение месяца, потому что мы агрегируем минимальное и максимальное значения валюты за месяц, и сами минимальное и максимальное значения котировок:

Агрегируем таблицу exchange по минимальным и максимальным помесячным значениям валют и запишем в min_max_currency. Для помесячной агрегации мы используем функцию date_trunc:

Витрина данных и свечной график

Теперь осталось создать таблицу с витриной, в которой будут уникальный ключ, цена открытия и закрытия и минимальная и максимальная цены:

Для создания витрины мы сджойним таблицы monthly_dates, open, close и min_max_currency:

Мы создали витрину данных, по которой будем строить свечной график. Мы сделали джойн таблиц monthly_dates, open и close по ключу year_month и полученной таблицы с min_max_currency по значению месяца month.

Итоговая таблица содержит 240 строк и выглядит так:

data_mart

В ней есть поля:

  • year_month — уникальный строковый ключ
  • month — месяц, по которому производилась агрегация
  • min_currency — минимальное значение валюты за месяц
  • open — первое значение валюты за месяц
  • max_currency — максимальное значение валюты за месяц
  • close - последнее значение валюты за месяц

Поля идут в таком порядке, потому что так по ним удобнее строить Candlestick Chart в Google Sheets.

Построим график по следующим шагам:

  1. Скопируем витрину в Google Sheets
  2. Выделим все столбцы, кроме year_month
  3. Для столбца month выберем «Формат» -> «Числа» -> «Обычный текст»
  4. В столбцах с котировками заменим все точки на запятые
  5. Вставим диаграмму и типом диаграммы выберем «График "японские свечи"»

В итоге мы получим график, который видели выше:

Мы построили свечной график по помесячным котировкам. Видим, что он состоит из свечей. Но на нем слишком много данных и мы не можем посмотреть на фитили. Поэтому возьмем только данные за 2019 год и запишем их в таблицу data_mart_new_data:

Здесь мы отобрали все даты, которые больше или равны 1 января 2019 года.

Теперь построим свечной график по этим данным. В дополнительных настройках вертикальной оси установим минимальное значение 1.2, чтобы рассмотреть график на большем масштабе:

Мы построили свечной график котировок валют за 2018-2019 год. На этом графике отчетливо видно, что свечной график состоит из тела свечи и фитилей. Границы тела определяются ценой открытия и закрытия, а фитили — минимальным и максимальным значениями за месяц.

Выводы

В этом уроке мы научились агрегировать данные для свечного графика. Мы взяли котировки валютной пары австралийского доллара и доллара США, сделали помесячную агрегацию валют и построили свечной график в Google Sheets. Агрегация валют позволит работать в финансовой сфере и строить отчеты по валютным парам.