Агрегация для candlestick-диаграмм — Продвинутая аналитика на SQL

• Candlestick chart
• Агрегация курсов валют
  • Первые и последние значения цены за месяц
  • Цены открытия и закрытия
  • Минимальные и максимальные цены за месяц
  • Витрина данных и свечной график
• Выводы

В этом уроке мы рассмотрим свечной график или 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. В нее мы агрегируем первую и последнюю дату за месяц, само значение месяца и уникальный ключ, по которому мы будем джойнить таблицы:

CREATE TABLE monthly_dates (
    year_month VARCHAR,
    month DATE,
    min_date DATE,
    max_date DATE
);

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

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

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

INSERT INTO monthly_dates (
    SELECT
        TO_CHAR(month, 'YYYY-MM') AS year_month,
        month,
        min_date,
        max_date
    FROM (
            SELECT
                DATE_TRUNC('month', date) AS month,
                MIN(date) AS min_date,
                MAX(date) AS max_date
            FROM exchange
            GROUP BY month
        ) AS subquery
    ORDER BY min_date
);

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

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

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

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

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

CREATE TABLE open (
    year_month VARCHAR PRIMARY KEY,
    date DATE,
    open FLOAT
);

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

INSERT INTO open
SELECT
    year_month,
    exchange.date,
    currency AS open
FROM exchange
INNER JOIN monthly_dates ON exchange.date = monthly_dates.min_date
ORDER BY date;

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

CREATE TABLE close (
    year_month VARCHAR PRIMARY KEY,
    date DATE,
    close FLOAT
);

INSERT INTO close
SELECT
    year_month,
    exchange.date,
    currency as close
FROM exchange
INNER JOIN monthly_dates ON exchange.date = monthly_dates.max_date
ORDER BY date;

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

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

CREATE TABLE min_max_currency (
    month DATE,
    min_currency FLOAT,
    max_currency FLOAT
);

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

INSERT INTO min_max_currency
SELECT
    DATE_TRUNC('month', date) AS month,
    MIN(currency) AS min_currency,
    MAX(currency) AS max_currency
FROM exchange
GROUP BY month
ORDER BY month;

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

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

CREATE TABLE data_mart (
    year_month VARCHAR,
    month DATE,
    min_currency FLOAT,
    open FLOAT,
    max_currency FLOAT,
    close FLOAT
);

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

INSERT INTO data_mart
(
    SELECT
        monthly_dates.year_month,
        monthly_dates.month,
        min_currency,
        open,
        max_currency,
        close
    FROM monthly_dates
    INNER JOIN open ON monthly_dates.year_month = open.year_month
    INNER JOIN close ON open.year_month = close.year_month
    INNER JOIN min_max_currency ON monthly_dates.month = min_max_currency.month
    ORDER BY month
);

Мы создали витрину данных, по которой будем строить свечной график. Мы сделали джойн таблиц 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:

CREATE TABLE data_mart_new_data (
    year_month VARCHAR,
    month DATA,
    min_currency FLOAT,
    open FLOAT,
    max_currency FLOAT,
    close FLOAT
);

INSERT INTO data_mart_new_data (
    SELECT * FROM data_mart
    WHERE month >= '2019-01-01'
);

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

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

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

Выводы

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

Самостоятельная работа

Повторите шаги, описанные в уроке.