Русский
Русский
English
Статистика
Реклама

Таблица

Адаптация таблиц под мобильные устройства

15.03.2021 10:06:02 | Автор: admin

Для кого эта статья

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

Проблема

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

Данная проблема встречается повсеместно, и усугубить ее может пожалуй лишь контент ячейки, который не переносится построчно, увеличивая тем самым ширину колонки.

Подопытный набор данных

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

Колонки:

  1. Номер

  2. Фото

  3. ФИО

  4. Телефон

  5. Email

  6. Дата

  7. Текст описание

  8. Статус

  9. Действия

Результатом станет довольно обширная таблица, которая имеет все нужные нам виды колонок. Признаться даже список сверху вызывает у меня вопросы справа тратится слишком много места.

  1. С фиксированной шириной и переносом строк

  2. С шириной по контенту

Анонс следующей статьи О списках в интерфейсах и как их применять по феншую.

Десктоп

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

Варианты адаптации

Проблема наша талица по ширине не влезает в телефон.

Ошибочные решения

  1. Уменьшать шрифт

  2. Убирать колонки

  3. Делать растровую картинку с таблицей и вставлять ее в макет

Возможные верные решения по убыванию

  1. Каждую строку таблицы делать блоком

  2. Горизонтальный скроллинг

Первый вариант адаптации - сделать каждую строку таблицу отдельным блоком, и вывести ее на экране телефона.

Проблема с данным методом: Удлиняется вертикальный скроллинг, данные повторяются. (частично решается добавлением фильтров для поиска)

Возьмём за пример таблицу, которая отображена сверху. В таком виде она не поместится по ширине в мобильный экран. Для решения этой проблемы мы будем каждую строку таблицы преобразовывать в блок.

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

Второй вариант - горизонтальный скролинг таблицы.

Для реализации данного подхода - нам нужно взять таблицу из декстопной версии сайта, и поместить ее в мобильный экран, а весь тот контент, который не помещается по ширине вынести за границы экрана, добавив при этом горизонтальную прокрутку к скрытым столбцам.

Субъективно для меня - это наименее подходящий вариант отображения таблиц в мобильных устройствах - так, как он скрывает большую часть контента за пределами окна устройства - что нарушает визуальную целостность данных контекста каждой строки таблицы (мы не видим строку целиком).

Вывод

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

Если вы заметили ошибки, или вам есть что дополнить - дайте мне знать, я обязательно это сделаю.Спасибо за внимание!

Подробнее..

Когда у вас сберовские масштабы. Использование Ab Initio при работе с Hive и GreenPlum

07.07.2020 18:11:11 | Автор: admin
Некоторое время назад перед нами встал вопрос выбора ETL-средства для работы с BigData. Ранее использовавшееся решение Informatica BDM не устраивало нас из-за ограниченной функциональности. Её использование свелось к фреймворку по запуску команд spark-submit. На рынке имелось не так много аналогов, в принципе способных работать с тем объёмом данных, с которым мы имеем дело каждый день. В итоге мы выбрали Ab Initio. В ходе пилотных демонстраций продукт показал очень высокую скорость обработки данных. Информации об Ab Initio на русском языке почти нет, поэтому мы решили рассказать о своём опыте на Хабре.

Ab Initio обладает множеством классических и необычных трансформаций, код которых может быть расширен с помощью собственного языка PDL. Для мелкого бизнеса такой мощный инструмент, вероятно, будет избыточным, и большинство его возможностей могут оказаться дорогими и невостребованными. Но если ваши масштабы приближаются к сберовским, то вам Ab Initio может быть интересен.

Он помогает бизнесу глобально копить знания и развивать экосистему, а разработчику прокачивать свои навыки в ETL, подтягивать знания в shell, предоставляет возможность освоения языка PDL, даёт визуальную картину процессов загрузки, упрощает разработку благодаря обилию функциональных компонентов.

В посте я расскажу о возможностях Ab Initio и приведу сравнительные характеристики по его работе с Hive и GreenPlum.

  • Описание фреймворка MDW и работ по его донастройке под GreenPlum
  • Сравнительные характеристики производительности Ab Initio по работе с Hive и GreenPlum
  • Работа Ab Initio с GreenPlum в режиме Near Real Time


Функционал этого продукта очень широк и требует немало времени на своё изучение. Однако, при должных навыках работы и правильных настройках производительности результаты обработки данных получаются весьма впечатляющие. Использование Ab Initio для разработчика может дать ему интересный опыт. Это новый взгляд на ETL-разработку, гибрид между визуальной средой и разработкой загрузок на скрипто-подобном языке.
Бизнес развивает свои экосистемы и этот инструмент оказывается ему как никогда кстати. С помощью Ab Initio можно копить знания о текущем бизнесе и использовать эти знания для расширения старых и открытия новых бизнесов. Альтернативами Ab Initio можно назвать из визуальных сред разработки Informatica BDM и из невизуальных сред Apache Spark.

Описание Ab Initio


Ab Initio, как и другие ETL-средства, представляет собой набор продуктов.

Ab Initio GDE (Graphical Development Environment) это среда для разработчика, в которой он настраивает трансформации данных и соединяет их потоками данных в виде стрелочек. При этом такой набор трансформаций называется графом:

Входные и выходные соединения функциональных компонентов являются портами и содержат поля, вычисленные внутри преобразований. Несколько графов, соединённых потоками в виде стрелочек в порядке их выполнения называются планом.
Имеется несколько сотен функциональных компонентов, что очень много. Многие из них узкоспециализированные. Возможности классических трансформаций в Ab Initio шире, чем в других ETL-средствах. Например, Join имеет несколько выходов. Помимо результата соединения датасетов можно получить на выходе записи входных датасетов, по ключам которых не удалось соединиться. Также можно получить rejects, errors и лог работы трансформации, который можно в этом же графе прочитать как текстовый файл и обработать другими трансформациями:

Или, например, можно материализовать приёмник данных в виде таблицы и в этом же графе считать из него данные.
Есть оригинальные трансформации. Например, трансформация Scan имеет функционал, как у аналитических функций. Есть трансформации с говорящими названиями: Create Data, Read Excel, Normalize, Sort within Groups, Run Program, Run SQL, Join with DB и др. Графы могут использовать параметры времени выполнения, в том числе возможна передача параметров из операционной системы или в операционную систему. Файлы с готовым набором передаваемых графу параметров называются parameter sets (psets).
Как и полагается, Ab Initio GDE имеет свой репозиторий, именуемый EME (Enterprise Meta Environment). Разработчики имеют возможность работать с локальными версиями кода и делать check in своих разработок в центральный репозиторий.
Имеется возможность во время выполнения или после выполнения графа кликнуть по любому соединяющему трансформации потоку и посмотреть на данные, прошедшие между этими трансформациями:

Также есть возможность кликнуть по любому потоку и посмотреть tracking details в сколько параллелей работала трансформация, сколько строк и байт в какой из параллелей загрузилось:

Есть возможность разбить выполнение графа на фазы и пометить, что одни трансформации нужно выполнять первым делом (в нулевой фазе), следующие в первой фазе, следующие во второй фазе и т.д.
У каждой трансформации можно выбрать так называемый layout (где она будет выполняться): без параллелей или в параллельных потоках, число которых можно задать. При этом временные файлы, которые создаёт Ab Initio при работе трансформаций, можно размещать как в файловой системе сервера, так и в HDFS.
В каждой трансформации на базе шаблона по умолчанию можно создать свой скрипт на языке PDL, который немного напоминает shell.
С помощью языка PDL вы можете расширять функционал трансформаций и, в частности, вы можете динамически (во время выполнения) генерировать произвольные фрагменты кода в зависимости от параметров времени выполнения.
Также в Ab Initio хорошо развита интеграция с ОС через shell. Конкретно в Сбербанке используется linux ksh. Можно обмениваться с shell переменными и использовать их в качестве параметров графов. Можно из shell вызывать выполнение графов Ab Initio и администрировать Ab Initio.
Помимо Ab Initio GDE в поставку входит много других продуктов. Есть своя Co>Operation System с претензией называться операционной системой. Есть Control>Center, в котором можно ставить на расписание и мониторить потоки загрузки. Есть продукты для осуществления разработки на более примитивном уровне, чем позволяет Ab Initio GDE.

Описание фреймворка MDW и работ по его донастройке под GreenPlum


Вместе со своими продуктами вендор поставляет продукт MDW (Metadata Driven Warehouse), который представляет собой конфигуратор графов, предназначенный для помощи в типичных задачах по наполнению хранилищ данных или data vaults.
Он содержит пользовательские (специфичные для проекта) парсеры метаданных и готовые генераторы кода из коробки.

На входе MDW получает модель данных, конфигурационный файл по настройке соединения с базой данных (Oracle, Teradata или Hive) и некоторые другие настройки. Специфическая для проекта часть, например, разворачивает модель в базе данных. Коробочная часть продукта генерирует графы и настроечные файлы к ним по загрузке данных в таблицы модели. При этом создаются графы (и psets) для нескольких режимов инициализирующей и инкрементальной работы по обновлению сущностей.
В случаях Hive и RDBMS генерируются различающиеся графы по инициализирующему и инкрементальному обновлению данных.
В случае Hive поступившие данные дельты соединяется посредством Ab Initio Join с данными, которые были в таблице до обновления. Загрузчики данных в MDW (как в Hive, так и в RDBMS) не только вставляют новые данные из дельты, но и закрывают периоды актуальности данных, по первичным ключам которых поступила дельта. Кроме того, приходится переписать заново неизменившуюся часть данных. Но так приходится делать, поскольку в Hive нет операций delete или update.

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

Поступившая дельта загружается в промежуточную таблицу в базу данных. После этого происходит соединение дельты с данными, которые были в таблице до обновления. И делается это силами SQL посредством сгенерированного SQL-запроса. Далее с помощью SQL-команд delete+insert в целевую таблицу происходит вставка новых данных из дельты и закрытие периодов актуальности данных, по первичным ключам которых поступила дельта. Неизменившиеся данные переписывать нет нужды.
Таким образом, мы пришли к выводу, что в случае Hive MDW должен пойти на переписывание всей таблицы, потому что Hive не имеет функции обновления. И ничего лучше полного переписывания данных при обновлении не придумано. В случае же RDBMS, наоборот, создатели продукта сочли нужным доверить соединение и обновление таблиц использованию SQL.
Для проекта в Сбербанке мы создали новую многократно используемую реализацию загрузчика базы данных для GreenPlum. Сделано это было на основе версии, которую MDW генерирует для Teradata. Именно Teradata, а не Oracle подошла для этого лучше и ближе всего, т.к. тоже является MPP-системой. Способы работы, а также синтаксис Teradata и GreenPlum оказались близки.
Примеры критичных для MDW различий между разными RDBMS таковы. В GreenPlum в отличии от Teradata при создании таблиц нужно писать клаузу
distributed by

В Teradata пишут
delete <table> all

, а в GreеnPlum пишут
delete from <table>

В Oracle в целях оптимизации пишут
delete from t where rowid in (<соединение t с дельтой>)

, а в Teradata и GreenPlum пишут
delete from t where exists (select * from delta where delta.pk=t.pk)

Ещё отметим, что для работы Ab Initio с GreenPlum потребовалось установить клиент GreenPlum на все ноды кластера Ab Initio. Это потому, что мы подключились к GreenPlum одновременно со всех узлов нашего кластера. А для того, чтобы чтение из GreenPlum было параллельным и каждый параллельный поток Ab Initio читал свою порцию данных из GreenPlum, пришлось в секцию where SQL-запросов поместить понимаемую Ab Initio конструкцию
where ABLOCAL()

и определить значение этой конструкции, указав читающей из БД трансформации параметр
ablocal_expr=string_concat("mod(t.", string_filter_out("{$TABLE_KEY}","{}"), ",", (decimal(3))(number_of_partitions()),")=", (decimal(3))(this_partition()))

, которая компилируется в что-то типа
mod(sk,10)=3

, т.е. приходится подсказывать GreenPlum явный фильтр для каждой партиции. Для других баз данных (Teradata, Oracle) Ab Initio может выполнить это распараллеливание автоматически.

Сравнительные характеристики производительности Ab Initio по работе с Hive и GreenPlum


В Сбербанке был проведён эксперимент по сравнению производительности сгенерированных MDW графов применительно к Hive и применительно к GreenPlum. В рамках эксперимента в случае Hive имелось 5 нод на том же кластере, что и Ab Initio, а в случае GreenPlum имелось 4 ноды на отдельном кластере. Т.е. Hive имел некоторое преимущество над GreenPlum по железу.
Было рассмотрено две пары графов, выполняющих одну и ту же задачу обновления данных в Hive и в GreenPlum. При этом запускали графы, сгенерированные конфигуратором MDW:

  • инициализирующая загрузка + инкрементальная загрузка случайно сгенерированных данных в таблицу Hive
  • инициализирующая загрузка + инкрементальная загрузка случайно сгенерированных данных в такую же таблицу GreenPlum

В обоих случаях (Hive и GreenPlum) запускали загрузки в 10 параллельных потоков на одном и том же кластере Ab Initio. Промежуточные данные для расчётов Ab Initio сохранял в HDFS (в терминах Ab Initio был использован MFS layout using HDFS). Одна строка случайно сгенерированных данных занимала в обоих случаях по 200 байт.
Результат получился такой:

Hive:
Инициализирующая загрузка в Hive
Вставлено строк 6 000 000 60 000 000 600 000 000
Продолжительность инициализирующей
загрузки в секундах
41 203 1 601
Инкрементальная загрузка в Hive
Количество строк, имевшихся в
целевой таблице на начало эксперимента
6 000 000 60 000 000 600 000 000
Количество строк дельты, применённых к
целевой таблице в ходе эксперимента
6 000 000 6 000 000 6 000 000
Продолжительность инкрементальной
загрузки в секундах
88 299 2 541

GreenPlum:
Инициализирующая загрузка в GreenPlum
Вставлено строк 6 000 000 60 000 000 600 000 000
Продолжительность инициализирующей
загрузки в секундах
72 360 3 631
Инкрементальная загрузка в GreenPlum
Количество строк, имевшихся в
целевой таблице на начало эксперимента
6 000 000 60 000 000 600 000 000
Количество строк дельты, применённых к
целевой таблице в ходе эксперимента
6 000 000 6 000 000 6 000 000
Продолжительность инкрементальной
загрузки в секундах
159 199 321

Видим, что скорость инициализирующей загрузки как в Hive, так и в GreenPlum линейно зависит от объёма данных и по причинам лучшего железа она несколько быстрее для Hive, чем для GreenPlum.
Инкрементальная загрузка в Hive также линейно зависит от объёма имеющихся в целевой таблице ранее загруженных данных и проходит достаточно медленно с ростом объёма. Вызвано это необходимостью перезаписывать целевую таблицу полностью. Это означает, что применение маленьких изменений к огромным таблицам не очень хороший вариант использования для Hive.
Инкрементальная же загрузка в GreenPlum слабо зависит от объёма имеющихся в целевой таблице ранее загруженных данных и проходит достаточно быстро. Получилось это благодаря SQL Joins и архитектуре GreenPlum, допускающей операцию delete.

Итак, GreenPlum вливает дельту методом delete+insert, а в Hive нету операций delete либо update, поэтому весь массив данных при инкрементальном обновлении были вынуждены переписывать целиком. Наиболее показательно сравнение выделенных жирным ячеек, так как оно соответствует наиболее частому варианту эксплуатации ресурсоёмких загрузок. Видим, что GreenPlum выиграл у Hive в этом тесте в 8 раз.

Работа Ab Initio с GreenPlum в режиме Near Real Time


В этом эксперименте проверим возможность Ab Initio производить обновление таблицы GreenPlum случайно формируемыми порциями данных в режиме, близком к реальному времени. Рассмотрим таблицу GreenPlum dev42_1_db_usl.TESTING_SUBJ_org_finval, с которой будет вестись работа.
Будем использовать три графа Ab Initio по работе с ней:

1) Граф Create_test_data.mp создаёт в 10 параллельных потоков файлы с данными в HDFS на 6 000 000 строк. Данные случайные, структура их организована для вставки в нашу таблицу



2) Граф mdw_load.day_one.current.dev42_1_db_usl_testing_subj_org_finval.pset сгенерированный MDW граф по инициализирующей вставке данных в нашу таблицу в 10 параллельных потоков (используются тестовые данные, сгенерированные графом (1))


3) Граф mdw_load.regular.current.dev42_1_db_usl_testing_subj_org_finval.pset сгенерированный MDW граф по инкрементальному обновлению нашей таблицы в 10 параллельных потоков с использованием порции свежих поступивших данных (дельты), сгенерированных графом (1)


Выполним нижеприведённый сценарий в режиме NRT:

  • сгенерировать 6 000 000 тестовых строк
  • произвести инициализирующую загрузку вставить 6 000 000 тестовых строк в пустую таблицу
  • повторить 5 раз инкрементальную загрузку
    • сгенерировать 6 000 000 тестовых строк
    • произвести инкрементальную вставку 6 000 000 тестовых строк в таблицу (при этом старым данным проставляется время истечения актуальности valid_to_ts и вставляются более свежие данные с тем же первичным ключом)

Такой сценарий эмулирует режим реальной работы некой бизнес-системы в режиме реального времени появляется достаточно объёмная порция новых данных и тут же вливается в GreenPlum.

Теперь посмотрим лог работы сценария:
Start Create_test_data.input.pset at 2020-06-04 11:49:11
Finish Create_test_data.input.pset at 2020-06-04 11:49:37
Start mdw_load.day_one.current.dev42_1_db_usl_testing_subj_org_finval.pset at 2020-06-04 11:49:37
Finish mdw_load.day_one.current.dev42_1_db_usl_testing_subj_org_finval.pset at 2020-06-04 11:50:42
Start Create_test_data.input.pset at 2020-06-04 11:50:42
Finish Create_test_data.input.pset at 2020-06-04 11:51:06
Start mdw_load.regular.current.dev42_1_db_usl_testing_subj_org_finval.pset at 2020-06-04 11:51:06
Finish mdw_load.regular.current.dev42_1_db_usl_testing_subj_org_finval.pset at 2020-06-04 11:53:41
Start Create_test_data.input.pset at 2020-06-04 11:53:41
Finish Create_test_data.input.pset at 2020-06-04 11:54:04
Start mdw_load.regular.current.dev42_1_db_usl_testing_subj_org_finval.pset at 2020-06-04 11:54:04
Finish mdw_load.regular.current.dev42_1_db_usl_testing_subj_org_finval.pset at 2020-06-04 11:56:51
Start Create_test_data.input.pset at 2020-06-04 11:56:51
Finish Create_test_data.input.pset at 2020-06-04 11:57:14
Start mdw_load.regular.current.dev42_1_db_usl_testing_subj_org_finval.pset at 2020-06-04 11:57:14
Finish mdw_load.regular.current.dev42_1_db_usl_testing_subj_org_finval.pset at 2020-06-04 11:59:55
Start Create_test_data.input.pset at 2020-06-04 11:59:55
Finish Create_test_data.input.pset at 2020-06-04 12:00:23
Start mdw_load.regular.current.dev42_1_db_usl_testing_subj_org_finval.pset at 2020-06-04 12:00:23
Finish mdw_load.regular.current.dev42_1_db_usl_testing_subj_org_finval.pset at 2020-06-04 12:03:23
Start Create_test_data.input.pset at 2020-06-04 12:03:23
Finish Create_test_data.input.pset at 2020-06-04 12:03:49
Start mdw_load.regular.current.dev42_1_db_usl_testing_subj_org_finval.pset at 2020-06-04 12:03:49
Finish mdw_load.regular.current.dev42_1_db_usl_testing_subj_org_finval.pset at 2020-06-04 12:06:46


Получается такая картина:
Graph Start time Finish time Length
Create_test_data.input.pset 04.06.2020 11:49:11 04.06.2020 11:49:37 00:00:26
mdw_load.day_one.current.
dev42_1_db_usl_testing_subj_org_finval.pset
04.06.2020 11:49:37 04.06.2020 11:50:42 00:01:05
Create_test_data.input.pset 04.06.2020 11:50:42 04.06.2020 11:51:06 00:00:24
mdw_load.regular.current.
dev42_1_db_usl_testing_subj_org_finval.pset
04.06.2020 11:51:06 04.06.2020 11:53:41 00:02:35
Create_test_data.input.pset 04.06.2020 11:53:41 04.06.2020 11:54:04 00:00:23
mdw_load.regular.current.
dev42_1_db_usl_testing_subj_org_finval.pset
04.06.2020 11:54:04 04.06.2020 11:56:51 00:02:47
Create_test_data.input.pset 04.06.2020 11:56:51 04.06.2020 11:57:14 00:00:23
mdw_load.regular.current.
dev42_1_db_usl_testing_subj_org_finval.pset
04.06.2020 11:57:14 04.06.2020 11:59:55 00:02:41
Create_test_data.input.pset 04.06.2020 11:59:55 04.06.2020 12:00:23 00:00:28
mdw_load.regular.current.
dev42_1_db_usl_testing_subj_org_finval.pset
04.06.2020 12:00:23 04.06.2020 12:03:23 00:03:00
Create_test_data.input.pset 04.06.2020 12:03:23 04.06.2020 12:03:49 00:00:26
mdw_load.regular.current.
dev42_1_db_usl_testing_subj_org_finval.pset
04.06.2020 12:03:49 04.06.2020 12:06:46 00:02:57

Видим, что 6 000 000 строк инкремента обрабатываются за 3 минуты, что достаточно быстро.
Данные в целевой таблице получились распределёнными следующим образом:
select valid_from_ts, valid_to_ts, count(1), min(sk), max(sk) from dev42_1_db_usl.TESTING_SUBJ_org_finval group by valid_from_ts, valid_to_ts order by 1,2;


Можно разглядеть соответствие вставленных данных моментам запуска графов.
Значит можно запускать в Ab Initio инкрементальную загрузку данных в GreenPlum с очень высокой частотой и наблюдать высокую скорость вставки этих данных в GreenPlum. Конечно, раз в секунду запускаться не получится, так как Ab Initio, как и любое ETL-средство, при запуске требует времени на раскачку.

Заключение


Сейчас Ab Initio используется в Сбербанке для построения Единого семантического слоя данных (ЕСС). Этот проект подразумевает построение единой версии состояния различных банковских бизнес-сущностей. Информация приходит из различных источников, реплики которых готовятся на Hadoop. Исходя из потребностей бизнеса, готовится модель данных и описываются трансформации данных. Ab Initio загружает информацию в ЕСС и загруженные данные не только представляют интерес для бизнеса сами по себе, но и служат источником для построения витрин данных. При этом функционал продукта позволяет использовать в качестве приёмника различные системы (Hive, Greenplum, Teradata, Oracle), что даёт возможность без особых усилий подготавливать данные для бизнеса в различных требуемых ему форматах.

Возможности Ab Initio широки, например, прилагающийся фреймворк MDW даёт возможность строить техническую и бизнес-историчность данных из коробки. Для разработчиков Ab Initio даёт возможность не изобретать велосипед, а пользоваться множеством имеющихся функциональных компонентов, по сути являющихся библиотеками, нужными при работе с данными.

Автор эксперт профессионального сообщества Сбербанка SberProfi DWH/BigData. Профессиональное сообщество SberProfi DWH/BigData отвечает за развитие компетенций в таких направлениях, как экосистема Hadoop, Teradata, Oracle DB, GreenPlum, а также BI инструментах Qlik, SAP BO, Tableau и др.
Подробнее..

10 приёмов работы с Oracle

01.10.2020 10:13:11 | Автор: admin
В Сбере есть несколько практик Oracle, которые могут оказаться вам полезны. Думаю, часть вам знакома, но мы используем для загрузки не только ETL-средства, но и хранимые процедуры Oracle. На Oracle PL/SQL реализованы наиболее сложные алгоритмы загрузки данных в хранилища, где требуется прочувствовать каждый байт.

  • Автоматическое журналирование компиляций
  • Как быть, если хочется сделать вьюшку с параметрами
  • Использование динамической статистики в запросах
  • Как сохранить план запроса при вставке данных через database link
  • Запуск процедур в параллельных сессиях
  • Протягивание остатков
  • Объединение нескольких историй в одну
  • Нормалайзер
  • Визуализация в формате SVG
  • Приложение поиска по метаданным Oracle


Автоматическое журналирование компиляций


На некоторых базах данных Oracle в Сбере стоит триггер на компиляцию, который запоминает, кто, когда и что менял в коде серверных объектов. Тем самым из таблицы журнала компиляций можно установить автора изменений. Также автоматически реализуется система контроля версий. Во всяком случае, если программист забыл сдать изменения в Git, то этот механизм его подстрахует. Опишем пример реализации такой системы автоматического журналирования компиляций. Один из упрощённых вариантов триггера на компиляцию, пишущего в журнал в виде таблицы ddl_changes_log, выглядит так:

create table DDL_CHANGES_LOG(  id               INTEGER,  change_date      DATE,  sid              VARCHAR2(100),  schemaname       VARCHAR2(30),  machine          VARCHAR2(100),  program          VARCHAR2(100),  osuser           VARCHAR2(100),  obj_owner        VARCHAR2(30),  obj_type         VARCHAR2(30),  obj_name         VARCHAR2(30),  previous_version CLOB,  changes_script   CLOB);create or replace trigger trig_audit_ddl_trg  before ddl on databasedeclare  v_sysdate              date;  v_valid                number;  v_previous_obj_owner   varchar2(30) := '';  v_previous_obj_type    varchar2(30) := '';  v_previous_obj_name    varchar2(30) := '';  v_previous_change_date date;  v_lob_loc_old          clob := '';  v_lob_loc_new          clob := '';  v_n                    number;  v_sql_text             ora_name_list_t;  v_sid                  varchar2(100) := '';  v_schemaname           varchar2(30) := '';  v_machine              varchar2(100) := '';  v_program              varchar2(100) := '';  v_osuser               varchar2(100) := '';begin  v_sysdate := sysdate;  -- find whether compiled object already presents and is valid  select count(*)    into v_valid    from sys.dba_objects   where owner = ora_dict_obj_owner     and object_type = ora_dict_obj_type     and object_name = ora_dict_obj_name     and status = 'VALID'     and owner not in ('SYS', 'SPOT', 'WMSYS', 'XDB', 'SYSTEM')     and object_type in ('TRIGGER', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'VIEW');  -- find information about previous compiled object  select max(obj_owner) keep(dense_rank last order by id),         max(obj_type) keep(dense_rank last order by id),         max(obj_name) keep(dense_rank last order by id),         max(change_date) keep(dense_rank last order by id)    into v_previous_obj_owner, v_previous_obj_type, v_previous_obj_name, v_previous_change_date    from ddl_changes_log;  -- if compile valid object or compile invalid package body broken by previous compilation of package then log it  if (v_valid = 1 or v_previous_obj_owner = ora_dict_obj_owner and     (v_previous_obj_type = 'PACKAGE' and ora_dict_obj_type = 'PACKAGE BODY' or     v_previous_obj_type = 'PACKAGE BODY' and ora_dict_obj_type = 'PACKAGE') and     v_previous_obj_name = ora_dict_obj_name and     v_sysdate - v_previous_change_date <= 1 / 24 / 60 / 2) and     ora_sysevent in ('CREATE', 'ALTER') then    -- store previous version of object (before compilation) from dba_source or dba_views in v_lob_loc_old    if ora_dict_obj_type <> 'VIEW' then      for z in (select substr(text, 1, length(text) - 1) || chr(13) || chr(10) as text                  from sys.dba_source                 where owner = ora_dict_obj_owner                   and type = ora_dict_obj_type                   and name = ora_dict_obj_name                 order by line) loop        v_lob_loc_old := v_lob_loc_old || z.text;      end loop;    else      select sys.dbms_metadata_util.long2clob(v.textlength, 'SYS.VIEW$', 'TEXT', v.rowid) into v_lob_loc_old        from sys."_CURRENT_EDITION_OBJ" o, sys.view$ v, sys.user$ u       where o.obj# = v.obj#         and o.owner# = u.user#         and u.name = ora_dict_obj_owner         and o.name = ora_dict_obj_name;    end if;    -- store new version of object (after compilation) from v_sql_text in v_lob_loc_new    v_n := ora_sql_txt(v_sql_text);    for i in 1 .. v_n loop      v_lob_loc_new := v_lob_loc_new || replace(v_sql_text(i), chr(10), chr(13) || chr(10));    end loop;    -- find information about session that changed this object    select max(to_char(sid)), max(schemaname), max(machine), max(program), max(osuser)      into v_sid, v_schemaname, v_machine, v_program, v_osuser      from v$session     where audsid = userenv('sessionid');    -- store changes in ddl_changes_log    insert into ddl_changes_log      (id, change_date, sid, schemaname, machine, program, osuser,       obj_owner, obj_type, obj_name, previous_version, changes_script)    values      (seq_ddl_changes_log.nextval, v_sysdate, v_sid, v_schemaname, v_machine, v_program, v_osuser,       ora_dict_obj_owner, ora_dict_obj_type, ora_dict_obj_name, v_lob_loc_old, v_lob_loc_new);  end if;exception  when others then    null;end;

В этом триггере получают название и новое содержимое компилируемого объекта, дополняют его предыдущим содержимым из словаря данных и записывают в лог изменений.

Как быть, если хочется сделать вьюшку с параметрами


Такое желание может часто посещать разработчика на Oracle. Почему можно сделать процедуру или функцию с параметрами, но не бывает вьюшек с входными параметрами, которые можно использовать при вычислениях? В Oracle есть чем заменить это недостающее, на наш взгляд, понятие.
Рассмотрим пример. Пусть есть таблица с продажами по подразделениям за каждый день.

create table DIVISION_SALES(  division_id INTEGER,  dt          DATE,  sales_amt   NUMBER);

Такой запрос сравнивает продажи по подразделениям за два дня. В данном случае, 30.04.2020 и 11.09.2020.

select t1.division_id,       t1.dt          dt1,       t2.dt          dt2,       t1.sales_amt   sales_amt1,       t2.sales_amt   sales_amt2  from (select dt, division_id, sales_amt          from division_sales         where dt = to_date('30.04.2020', 'dd.mm.yyyy')) t1,       (select dt, division_id, sales_amt          from division_sales         where dt = to_date('11.09.2020', 'dd.mm.yyyy')) t2 where t1.division_id = t2.division_id;

Вот вьюшка, которую хочется написать для обобщения такого запроса. Хочется передавать даты в качестве параметров. Однако синтаксис не позволяет такое сделать.

create or replace view vw_division_sales_report(in_dt1 date, in_dt2 date) asselect t1.division_id,       t1.dt          dt1,       t2.dt          dt2,       t1.sales_amt   sales_amt1,       t2.sales_amt   sales_amt2  from (select dt, division_id, sales_amt          from division_sales         where dt = in_dt1) t1,       (select dt, division_id, sales_amt          from division_sales         where dt = in_dt2) t2 where t1.division_id = t2.division_id;

Предлагается такое обходное решение. Создадим тип под строку из этой вьюшки.

create type t_division_sales_report as object(  division_id INTEGER,  dt1         DATE,  dt2         DATE,  sales_amt1  NUMBER,  sales_amt2  NUMBER);

И создадим тип под таблицу из таких строк.

create type t_division_sales_report_table as table of t_division_sales_report;

Вместо вьюшки напишем pipelined функцию с входными параметрами-датами.

create or replace function func_division_sales(in_dt1 date, in_dt2 date)  return t_division_sales_report_table  pipelined asbegin  for z in (select t1.division_id,                   t1.dt          dt1,                   t2.dt          dt2,                   t1.sales_amt   sales_amt1,                   t2.sales_amt   sales_amt2              from (select dt, division_id, sales_amt                      from division_sales                     where dt = in_dt1) t1,                   (select dt, division_id, sales_amt                      from division_sales                     where dt = in_dt2) t2             where t1.division_id = t2.division_id) loop    pipe row(t_division_sales_report(z.division_id,                                     z.dt1,                                     z.dt2,                                     z.sales_amt1,                                     z.sales_amt2));  end loop;end;

Обращаться к ней можно так:

select *  from table(func_division_sales(to_date('30.04.2020', 'dd.mm.yyyy'),                                 to_date('11.09.2020', 'dd.mm.yyyy')));

Этот запрос и выдаст нам такой же результат, как запрос в начале этой заметки с явным образом подставленными датами.
Pipelined функции могут быть также полезны, если нужно передать параметр внутрь сложного запроса.
Например, рассмотрим сложную вьюшку, в которой поле field1, по которому хочется отфильтровать данные, запрятано где-то глубоко во вьюшке.

create or replace view complex_view as select field1, ...   from (select field1, ...           from (select field1, ... from deep_table), table1          where ...),        table2  where ...;

И запрос из вьюшки с фиксированным значением field1 может иметь плохой план выполнения.

select field1, ... from complex_view where field1 = 'myvalue';

Т.е. вместо того, чтобы сначала отфильтровать deep_table по условию field1 = 'myvalue', запрос может сначала соединить все таблицы, обработав излишне большой объём данных, а потом уже фильтровать результат по условию field1 = 'myvalue'. Такой сложности можно избежать, если сделать вместо вьюшки pipelined функцию с параметром, значение которого присваивается полю field1.

Использование динамической статистики в запросах


Бывает, что один и тот же запрос в БД Oracle обрабатывает всякий раз различный объём данных в использующихся в нём таблицах и подзапросах. Как заставить оптимизатор всякий раз понимать, какой из способов соединения таблиц на этот раз лучше и какие индексы использовать? Рассмотрим, например, запрос, который соединяет порцию изменившихся с последней загрузки остатков по счетам со справочником счетов. Порция изменившихся остатков по счетам сильно меняется от загрузки к загрузке, составляя то сотни строк, то миллионы строк. В зависимости от размера этой порции требуется соединять изменившиеся остатки со счетами то способом /*+ use_nl*/, то способом /*+ use_hash*/. Всякий раз повторно собирать статистику неудобно, особенно, если от загрузки к загрузке изменяется количество строк не в соединяемой таблице, а в соединяемом подзапросе. На помощь тут может прийти хинт /*+ dynamic_sampling()*/. Покажем, как он влияет, на примере запроса. Пусть таблица change_balances содержит изменения остатков, а accounts справочник счетов. Соединяем эти таблицы по полям account_id, имеющимся в каждой из таблиц. В начале эксперимента запишем в эти таблицы побольше строк и не будем менять их содержимое.
Сначала возьмём 10% изменений остатков в таблице change_balances и посмотрим, какой план будет с использованием dynamic_sampling:

SQL> EXPLAIN PLAN  2   SET statement_id = 'test1'  3   INTO plan_table  4  FOR  with c as  5   (select /*+ dynamic_sampling(change_balances 2)*/  6     account_id, balance_amount  7      from change_balances  8     where mod(account_id, 10) = 0)  9  select a.account_id, a.account_number, c.balance_amount 10    from c, accounts a 11   where c.account_id = a.account_id;Explained.SQL>SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY);Plan hash value: 874320301----------------------------------------------------------------------------------------------| Id  | Operation          | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |----------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT   |                 |  9951K|   493M|       |   140K  (1)| 00:28:10 ||*  1 |  HASH JOIN         |                 |  9951K|   493M|  3240K|   140K  (1)| 00:28:10 ||*  2 |   TABLE ACCESS FULL| CHANGE_BALANCES |   100K|  2057K|       |  7172   (1)| 00:01:27 ||   3 |   TABLE ACCESS FULL| ACCOUNTS        |    10M|   295M|       |   113K  (1)| 00:22:37 |----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("ACCOUNT_ID"="A"."ACCOUNT_ID")   2 - filter(MOD("ACCOUNT_ID",10)=0)Note-----   - dynamic sampling used for this statement (level=2)20 rows selected.

Итак, видим, что предлагается пройти таблицы change_balances и accounts с помощью full scan и соединить их посредством hash join.
Теперь резко уменьшим выборку из change_balances. Возьмём 0.1% изменений остатков и посмотрим, какой план будет с использованием dynamic_sampling:

SQL> EXPLAIN PLAN  2   SET statement_id = 'test2'  3   INTO plan_table  4  FOR  with c as  5   (select /*+ dynamic_sampling(change_balances 2)*/  6     account_id, balance_amount  7      from change_balances  8     where mod(account_id, 1000) = 0)  9  select a.account_id, a.account_number, c.balance_amount 10    from c, accounts a 11   where c.account_id = a.account_id;Explained.SQL>SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY);Plan hash value: 2360715730-------------------------------------------------------------------------------------------------------| Id  | Operation                    | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT             |                        | 73714 |  3743K| 16452   (1)| 00:03:18 ||   1 |  NESTED LOOPS                |                        |       |       |            |          ||   2 |   NESTED LOOPS               |                        | 73714 |  3743K| 16452   (1)| 00:03:18 ||*  3 |    TABLE ACCESS FULL         | CHANGE_BALANCES        |   743 | 15603 |  7172   (1)| 00:01:27 ||*  4 |    INDEX RANGE SCAN          | IX_ACCOUNTS_ACCOUNT_ID |   104 |       |     2   (0)| 00:00:01 ||   5 |   TABLE ACCESS BY INDEX ROWID| ACCOUNTS               |    99 |  3069 |   106   (0)| 00:00:02 |-------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   3 - filter(MOD("ACCOUNT_ID",1000)=0)   4 - access("ACCOUNT_ID"="A"."ACCOUNT_ID")Note-----   - dynamic sampling used for this statement (level=2)22 rows selected.

На этот раз к таблице change_balances таблица accounts присоединяется посредством nested loops и используется индекс для чтения строк из accounts.
Если же хинт dynamic_sampling убрать, то во втором случае план останется такой же, как в первом случае, и это не оптимально.
Подробности о хинте dynamic_sampling и возможных значениях его числового аргумента можно найти в документации.

Как сохранить план запроса при вставке данных через database link


Решаем такую задачу. На сервере-источнике данных имеются таблицы, которые нужно соединить и загрузить в хранилище данных. Допустим, на сервере-источнике написана вьюшка, которая содержит в себе всю нужную ETL-логику преобразований. Вьюшка написана оптимально, в ней указаны хинты оптимизатору, подсказывающие, как соединять таблицы и какие индексы использовать. На стороне сервера хранилища данных нужно сделать несложную вещь вставить данные из вьюшки в целевую таблицу. И тут могут возникнуть сложности. Если вставку в целевую таблицу осуществить командой вида

insert into dwh_table  (field1, field2)  select field1, field2 from vw_for_dwh_table@xe_link;

, то вся логика плана запроса, содержащаяся во вьюшке, из которой читаем данные по database link, может быть проигнорирована. Все хинты, зашитые в этой вьюшке могут быть не учтены.

SQL> EXPLAIN PLAN  2   SET statement_id = 'test'  3   INTO plan_table  4  FOR  insert into dwh_table  5    (field1, field2)  6    select field1, field2 from vw_for_dwh_table@xe_link;Explained.SQL>SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY);Plan hash value: 1788691278-------------------------------------------------------------------------------------------------------------| Id  | Operation                | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|-------------------------------------------------------------------------------------------------------------|   0 | INSERT STATEMENT         |                  |     1 |  2015 |     2   (0)| 00:00:01 |        |      ||   1 |  LOAD TABLE CONVENTIONAL | DWH_TABLE        |       |       |            |          |        |      ||   2 |   REMOTE                 | VW_FOR_DWH_TABLE |     1 |  2015 |     2   (0)| 00:00:01 | XE_LI~ | R->S |-------------------------------------------------------------------------------------------------------------Remote SQL Information (identified by operation id):----------------------------------------------------   2 - SELECT /*+ OPAQUE_TRANSFORM */ "FIELD1","FIELD2" FROM "VW_FOR_DWH_TABLE" "VW_FOR_DWH_TABLE"       (accessing 'XE_LINK' )16 rows selected.

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

declare  cursor cr is    select field1, field2 from vw_for_dwh_table@xe_link;  cr_row cr%rowtype;begin  open cr;  loop    fetch cr      into cr_row;    insert into dwh_table      (field1, field2)    values      (cr_row.field1, cr_row.field2);    exit when cr%notfound;  end loop;  close cr;end;

Запрос из курсора

select field1, field2 from vw_for_dwh_table@xe_link;

в отличие от вставки

insert into dwh_table  (field1, field2)  select field1, field2 from vw_for_dwh_table@xe_link;

сохранит план запроса, заложенный во вьюшку на сервере-источнике.

Запуск процедур в параллельных сессиях


Часто стоит задача запустить из некоторой родительской процедуры несколько параллельных расчётов и, дождавшись завершения каждого из них, продолжить выполнение родительской процедуры. Это может быть полезно при параллельных вычислениях, если ресурсы сервера позволяют это. Есть много способов сделать это.
Опишем очень простой вариант реализации такого механизма. Параллельные процедуры будем выполнять в параллельных одноразовых джобах, родительская же процедура в цикле будет ожидать завершения всех этих джобов.
Создадим таблицы с метаданными для этого механизма. Для начала сделаем таблицу с группами параллельно запускаемых процедур:

create table PARALLEL_PROC_GROUP_LIST(  group_id   INTEGER,  group_name VARCHAR2(4000));comment on column PARALLEL_PROC_GROUP_LIST.group_id  is 'Номер группы параллельно запускаемых процедур';comment on column PARALLEL_PROC_GROUP_LIST.group_name  is 'Название группы параллельно запускаемых процедур';

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

create table PARALLEL_PROC_LIST(  group_id    INTEGER,  proc_script VARCHAR2(4000),  is_active   CHAR(1) default 'Y');comment on column PARALLEL_PROC_LIST.group_id  is 'Номер группы параллельно запускаемых процедур';comment on column PARALLEL_PROC_LIST.proc_script  is 'Pl/sql блок с кодом процедуры';comment on column PARALLEL_PROC_LIST.is_active  is 'Y - active, N - inactive. С помощью этого поля можно временно отключать процедуру из группы';

И сделаем таблицу журнала, где будем собирать лог того, какая процедура когда в каком джобе запускалась:

create table PARALLEL_PROC_LOG(  run_id      INTEGER,  group_id    INTEGER,  proc_script VARCHAR2(4000),  job_id      INTEGER,  start_time  DATE,  end_time    DATE);comment on column PARALLEL_PROC_LOG.run_id  is 'Номер запуска процедуры run_in_parallel';comment on column PARALLEL_PROC_LOG.group_id  is 'Номер группы параллельно запускаемых процедур';comment on column PARALLEL_PROC_LOG.proc_script  is 'Pl/sql блок с кодом процедуры';comment on column PARALLEL_PROC_LOG.job_id  is 'Job_id джоба, в котором была запущена эта процедура';comment on column PARALLEL_PROC_LOG.start_time  is 'Время начала работы';comment on column PARALLEL_PROC_LOG.end_time  is 'Время окончания работы';create sequence Seq_Parallel_Proc_Log;

Теперь приведём код процедуры по запуску параллельных потоков:

create or replace procedure run_in_parallel(in_group_id integer) as  -- Процедура по параллельному запуску процедур из таблицы parallel_proc_list.  -- Параметр - номер группы из parallel_proc_list  v_run_id             integer;  v_job_id             integer;  v_job_id_list        varchar2(32767);  v_job_id_list_ext    varchar2(32767);  v_running_jobs_count integer;begin  select seq_parallel_proc_log.nextval into v_run_id from dual;  -- submit jobs with the same parallel_proc_list.in_group_id  -- store seperated with ',' JOB_IDs in v_job_id_list  v_job_id_list     := null;  v_job_id_list_ext := null;  for z in (select pt.proc_script              from parallel_proc_list pt             where pt.group_id = in_group_id               and pt.is_active = 'Y') loop    dbms_job.submit(v_job_id, z.proc_script);    insert into parallel_proc_log      (run_id, group_id, proc_script, job_id, start_time, end_time)    values      (v_run_id, in_group_id, z.proc_script, v_job_id, sysdate, null);    v_job_id_list     := v_job_id_list || ',' || to_char(v_job_id);    v_job_id_list_ext := v_job_id_list_ext || ' union all select ' ||                         to_char(v_job_id) || ' job_id from dual';  end loop;  commit;  v_job_id_list     := substr(v_job_id_list, 2);  v_job_id_list_ext := substr(v_job_id_list_ext, 12);  -- loop while not all jobs finished  loop    -- set parallel_proc_log.end_time for finished jobs    execute immediate 'update parallel_proc_log set end_time = sysdate where job_id in (' ||                      v_job_id_list_ext ||                      ' minus select job from user_jobs where job in (' ||                      v_job_id_list ||                      ') minus select job_id from parallel_proc_log where job_id in (' ||                      v_job_id_list || ') and end_time is not null)';    commit;    -- check whether all jobs finished    execute immediate 'select count(1) from user_jobs where job in (' ||                      v_job_id_list || ')'      into v_running_jobs_count;    -- if all jobs finished then exit    exit when v_running_jobs_count = 0;    -- sleep a little    sys.dbms_lock.sleep(0.1);  end loop;end;

Проверим, как работает процедура run_in_parallel. Создадим тестовую процедуру, которую будем вызывать в параллельных сессиях.

create or replace procedure sleep(in_seconds integer) asbegin  sys.Dbms_Lock.Sleep(in_seconds);end;

Заполним название группы и таблицу со cкриптами, которые будут выполняться параллельно.

insert into PARALLEL_PROC_GROUP_LIST(group_id, group_name) values(1, 'Тестовая группа');insert into PARALLEL_PROC_LIST(group_id, proc_script, is_active) values(1, 'begin sleep(5); end;', 'Y');insert into PARALLEL_PROC_LIST(group_id, proc_script, is_active) values(1, 'begin sleep(10); end;', 'Y');

Запустим группу параллельных процедур.

begin  run_in_parallel(1);end;

По завершении посмотрим лог.

select * from PARALLEL_PROC_LOG;

RUN_ID GROUP_ID PROC_SCRIPT JOB_ID START_TIME END_TIME
1 1 begin sleep(5); end; 1 11.09.2020 15:00:51 11.09.2020 15:00:56
1 1 begin sleep(10); end; 2 11.09.2020 15:00:51 11.09.2020 15:01:01

Видим, что время выполнения экземпляров тестовой процедуры соответствует ожиданиям.

Протягивание остатков


Опишем вариант решения достаточно типовой банковской задачи по протягиванию остатков. Допустим, имеется таблица фактов изменения остатков по счетам. Требуется на каждый день календаря указать актуальный остаток по счёту (последний за день). Такая информация часто бывает нужна в хранилищах данных. Если в какой-то день не было движений по счёту, то нужно повторить последний известный остаток. Если объёмы данных и вычислительные мощности сервера позволяют, то можно решить такую задачу с помощью SQL-запроса, даже не прибегая к PL/SQL. Поможет нам в этом функция last_value(* ignore nulls) over(partition by * order by *), которая протянет последний известный остаток на последующие даты, в которых не было изменений.
Создадим таблицу и заполним её тестовыми данными.

create table ACCOUNT_BALANCE(  dt           DATE,  account_id   INTEGER,  balance_amt  NUMBER,  turnover_amt NUMBER);comment on column ACCOUNT_BALANCE.dt  is 'Дата и время остатка по счёту';comment on column ACCOUNT_BALANCE.account_id  is 'Номер счёта';comment on column ACCOUNT_BALANCE.balance_amt  is 'Остаток по счёту';comment on column ACCOUNT_BALANCE.turnover_amt  is 'Оборот по счёту';insert into account_balance(dt, account_id, balance_amt, turnover_amt) values(to_date('01.01.2020 00:00:00','dd.mm.yyyy hh24.mi.ss'), 1, 23, 23);insert into account_balance(dt, account_id, balance_amt, turnover_amt) values(to_date('05.01.2020 01:00:00','dd.mm.yyyy hh24.mi.ss'), 1, 45, 22);insert into account_balance(dt, account_id, balance_amt, turnover_amt) values(to_date('05.01.2020 20:00:00','dd.mm.yyyy hh24.mi.ss'), 1, 44, -1);insert into account_balance(dt, account_id, balance_amt, turnover_amt) values(to_date('05.01.2020 00:00:00','dd.mm.yyyy hh24.mi.ss'), 2, 67, 67);insert into account_balance(dt, account_id, balance_amt, turnover_amt) values(to_date('05.01.2020 20:00:00','dd.mm.yyyy hh24.mi.ss'), 2, 77, 10);insert into account_balance(dt, account_id, balance_amt, turnover_amt) values(to_date('07.01.2020 00:00:00','dd.mm.yyyy hh24.mi.ss'), 2, 72, -5);

Нижеприведённый запрос решает нашу задачу. Подзапрос cld содержит календарь дат, в подзапросе ab группируем остатки за каждый день, в подзапросе a запоминаем перечень всех счетов и дату начала истории по каждому счёту, в подзапросе pre для каждого счёта составляем календарь дней с начала его истории. Финальный запрос присоединяет к календарю дней активности каждого счёта последние остатки на каждый день и протягивает их на дни, в которых не было изменений.

with cld as (select /*+ materialize*/   to_date('01.01.2020', 'dd.mm.yyyy') + level - 1 dt    from dual  connect by level <= 10),ab as (select trunc(dt) dt,         account_id,         max(balance_amt) keep(dense_rank last order by dt) balance_amt,         sum(turnover_amt) turnover_amt    from account_balance   group by trunc(dt), account_id),a as (select min(dt) min_dt, account_id from ab group by account_id),pre as (select cld.dt, a.account_id from cld left join a on cld.dt >= a.min_dt)select pre.dt,       pre.account_id,       last_value(ab.balance_amt ignore nulls) over(partition by pre.account_id order by pre.dt) balance_amt,       nvl(ab.turnover_amt, 0) turnover_amt  from pre  left join ab    on pre.dt = ab.dt   and pre.account_id = ab.account_id order by 2, 1;

Результат запроса соответствует ожиданиям.
DT ACCOUNT_ID BALANCE_AMT TURNOVER_AMT
01.01.2020 1 23 23
02.01.2020 1 23 0
03.01.2020 1 23 0
04.01.2020 1 23 0
05.01.2020 1 44 21
06.01.2020 1 44 0
07.01.2020 1 44 0
08.01.2020 1 44 0
09.01.2020 1 44 0
10.01.2020 1 44 0
05.01.2020 2 77 77
06.01.2020 2 77 0
07.01.2020 2 72 -5
08.01.2020 2 72 0
09.01.2020 2 72 0
10.01.2020 2 72 0

Объединение нескольких историй в одну


При загрузке данных в хранилища часто решается задача, когда нужно выстроить единую историю по сущности, имея отдельные истории атрибутов этой сущности, пришедшие из различных источников. Допустим, имеется некоторая сущность с первичным ключом primary_key_id, о которой известна история (start_dt end_dt) трёх её различных атрибутов, расположенная в трёх различных таблицах.

create table HIST1(  primary_key_id INTEGER,  start_dt       DATE,  attribute1     NUMBER);insert into HIST1(primary_key_id, start_dt, attribute1) values(1, to_date('2014-01-01','yyyy-mm-dd'), 7);insert into HIST1(primary_key_id, start_dt, attribute1) values(1, to_date('2015-01-01','yyyy-mm-dd'), 8);insert into HIST1(primary_key_id, start_dt, attribute1) values(1, to_date('2016-01-01','yyyy-mm-dd'), 9);insert into HIST1(primary_key_id, start_dt, attribute1) values(2, to_date('2014-01-01','yyyy-mm-dd'), 17);insert into HIST1(primary_key_id, start_dt, attribute1) values(2, to_date('2015-01-01','yyyy-mm-dd'), 18);insert into HIST1(primary_key_id, start_dt, attribute1) values(2, to_date('2016-01-01','yyyy-mm-dd'), 19);create table HIST2(  primary_key_id INTEGER,  start_dt       DATE,  attribute2     NUMBER); insert into HIST2(primary_key_id, start_dt, attribute2) values(1, to_date('2015-01-01','yyyy-mm-dd'), 4);insert into HIST2(primary_key_id, start_dt, attribute2) values(1, to_date('2016-01-01','yyyy-mm-dd'), 5);insert into HIST2(primary_key_id, start_dt, attribute2) values(1, to_date('2017-01-01','yyyy-mm-dd'), 6);insert into HIST2(primary_key_id, start_dt, attribute2) values(2, to_date('2015-01-01','yyyy-mm-dd'), 14);insert into HIST2(primary_key_id, start_dt, attribute2) values(2, to_date('2016-01-01','yyyy-mm-dd'), 15);insert into HIST2(primary_key_id, start_dt, attribute2) values(2, to_date('2017-01-01','yyyy-mm-dd'), 16);create table HIST3(  primary_key_id INTEGER,  start_dt       DATE,  attribute3     NUMBER); insert into HIST3(primary_key_id, start_dt, attribute3) values(1, to_date('2016-01-01','yyyy-mm-dd'), 10);insert into HIST3(primary_key_id, start_dt, attribute3) values(1, to_date('2017-01-01','yyyy-mm-dd'), 20);insert into HIST3(primary_key_id, start_dt, attribute3) values(1, to_date('2018-01-01','yyyy-mm-dd'), 30);insert into HIST3(primary_key_id, start_dt, attribute3) values(2, to_date('2016-01-01','yyyy-mm-dd'), 110);insert into HIST3(primary_key_id, start_dt, attribute3) values(2, to_date('2017-01-01','yyyy-mm-dd'), 120);insert into HIST3(primary_key_id, start_dt, attribute3) values(2, to_date('2018-01-01','yyyy-mm-dd'), 130);

Целью является загрузка единой истории изменения трёх атрибутов в одну таблицу.
Ниже приведён запрос, решающий такую задачу. В нём сначала формируется диагональная таблица q1 с данными из разных источников по разным атрибутам (отсутствующие в источнике атрибуты заполняются null-ами). Затем с помощью функции last_value(* ignore nulls) диагональная таблица схлопывается в единую историю, а последние известные значения атрибутов протягиваются вперёд на те даты, в которые изменений по ним не было:

select primary_key_id,       start_dt,       nvl(lead(start_dt - 1)           over(partition by primary_key_id order by start_dt),           to_date('9999-12-31', 'yyyy-mm-dd')) as end_dt,       last_value(attribute1 ignore nulls) over(partition by primary_key_id order by start_dt) as attribute1,       last_value(attribute2 ignore nulls) over(partition by primary_key_id order by start_dt) as attribute2,       last_value(attribute3 ignore nulls) over(partition by primary_key_id order by start_dt) as attribute3  from (select primary_key_id,               start_dt,               max(attribute1) as attribute1,               max(attribute2) as attribute2,               max(attribute3) as attribute3          from (select primary_key_id,                       start_dt,                       attribute1,                       cast(null as number) attribute2,                       cast(null as number) attribute3                  from hist1                union all                select primary_key_id,                       start_dt,                       cast(null as number) attribute1,                       attribute2,                       cast(null as number) attribute3                  from hist2                union all                select primary_key_id,                       start_dt,                       cast(null as number) attribute1,                       cast(null as number) attribute2,                       attribute3                  from hist3) q1         group by primary_key_id, start_dt) q2 order by primary_key_id, start_dt;

Результат получается такой:
PRIMARY_KEY_ID START_DT END_DT ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3
1 01.01.2014 31.12.2014 7 NULL NULL
1 01.01.2015 31.12.2015 8 4 NULL
1 01.01.2016 31.12.2016 9 5 10
1 01.01.2017 31.12.2017 9 6 20
1 01.01.2018 31.12.9999 9 6 30
2 01.01.2014 31.12.2014 17 NULL NULL
2 01.01.2015 31.12.2015 18 14 NULL
2 01.01.2016 31.12.2016 19 15 110
2 01.01.2017 31.12.2017 19 16 120
2 01.01.2018 31.12.9999 19 16 130

Нормалайзер


Иногда встаёт задача о нормализации данных, пришедших в формате поля с разделителями. Например, в виде такой таблицы:

create table DENORMALIZED_TABLE(  id  INTEGER,  val VARCHAR2(4000));insert into DENORMALIZED_TABLE(id, val) values(1, 'aaa,cccc,bb');insert into DENORMALIZED_TABLE(id, val) values(2, 'ddd');insert into DENORMALIZED_TABLE(id, val) values(3, 'fffff,e');

Такой запрос нормализует данные, расклеив соединённые запятой поля в виде нескольких строк:

select id, regexp_substr(val, '[^,]+', 1, column_value) val, column_value  from denormalized_table,       table(cast(multiset                  (select level                     from dual                   connect by regexp_instr(val, '[^,]+', 1, level) > 0) as                  sys.odcinumberlist)) order by id, column_value;

Результат получается такой:
ID VAL COLUMN_VALUE
1 aaa 1
1 cccc 2
1 bb 3
2 ddd 1
3 fffff 1
3 e 2

Визуализация в формате SVG


Часто возникает желание как-то визуализировать числовые показатели, хранящиеся в базе данных. Например, построить графики, гистограммы, диаграммы. В этом могут помочь специализированные средства, например, Oracle BI. Но лицензии на эти средства могут стоить денег, а настройка их может занять больше времени, чем написание на коленке SQL-запроса к Oracle, который выдаст готовую картинку. Продемонстрируем на примере, как с помощью запроса быстро нарисовать такую картинку в формате SVG.
Предположим, у нас есть таблица с данными

create table graph_data(dt date, val number, radius number);insert into graph_data(dt, val, radius) values (to_date('01.01.2020','dd.mm.yyyy'), 12, 3);insert into graph_data(dt, val, radius) values (to_date('02.01.2020','dd.mm.yyyy'), 15, 4);insert into graph_data(dt, val, radius) values (to_date('05.01.2020','dd.mm.yyyy'), 17, 5);insert into graph_data(dt, val, radius) values (to_date('06.01.2020','dd.mm.yyyy'), 13, 6);insert into graph_data(dt, val, radius) values (to_date('08.01.2020','dd.mm.yyyy'),  3, 7);insert into graph_data(dt, val, radius) values (to_date('10.01.2020','dd.mm.yyyy'), 20, 8);insert into graph_data(dt, val, radius) values (to_date('11.01.2020','dd.mm.yyyy'), 18, 9);

dt это дата актуальности,
val это числовой показатель, динамику которого по времени мы визуализируем,
radius это ещё один числовой показатель, который будем рисовать в виде кружка с таким радиусом.
Скажем пару слов о формате SVG. Это формат векторной графики, который можно смотреть в современных браузерах и конвертировать в другие графические форматы. В нём, среди прочего, можно рисовать линии, кружки и писать текст:

<line x1="94" x2="94" y1="15" y2="675" style="stroke:rgb(150,255,255); stroke-width:1px"/><circle cx="30" cy="279" r="3" style="fill:rgb(255,0,0)"/><text x="7" y="688" font-size="10" fill="rgb(0,150,255)">2020-01-01</text>

Ниже SQL-запрос к Oracle, который строит график из данных в этой таблице. Здесь подзапрос const содержит различные константные настройки размеры картинки, количество меток на осях графика, цвета линий и кружочков, размеры шрифта и т.д. В подзапросе gd1 мы приводим данные из таблицы graph_data к координатам x и y на рисунке. Подзапрос gd2 запоминает предыдущие по времени точки, из которых нужно вести линии к новым точкам. Блок header это заголовок картинки с белым фоном. Блок vertical lines рисует вертикальные линии. Блок dates under vertical lines подписывает даты на оси x. Блок horizontal lines рисует горизонтальные линии. Блок values near horizontal lines подписывает значения на оси y. Блок circles рисует кружочки указанного в таблице graph_data радиуса. Блок graph data строит из линий график динамики показателя val из таблицы graph_data. Блок footer добавляет замыкающий тэг.

with const as (select 700 viewbox_width,         700 viewbox_height,         30 left_margin,         30 right_margin,         15 top_margin,         25 bottom_margin,         max(dt) - min(dt) + 1 num_vertical_lines,         11 num_horizontal_lines,         'rgb(150,255,255)' stroke_vertical_lines,         '1px' stroke_width_vertical_lines,         10 font_size_dates,         'rgb(0,150,255)' fill_dates,         23 x_dates_pad,         13 y_dates_pad,         'rgb(150,255,255)' stroke_horizontal_lines,         '1px' stroke_width_horizontal_lines,         10 font_size_values,         'rgb(0,150,255)' fill_values,         4 x_values_pad,         2 y_values_pad,         'rgb(255,0,0)' fill_circles,         'rgb(51,102,0)' stroke_graph,         '1px' stroke_width_graph,         min(dt) min_dt,         max(dt) max_dt,         max(val) max_val    from graph_data),gd1 as (select graph_data.dt,         const.left_margin +         (const.viewbox_width - const.left_margin - const.right_margin) *         (graph_data.dt - const.min_dt) / (const.max_dt - const.min_dt) x,         const.viewbox_height - const.bottom_margin -         (const.viewbox_height - const.top_margin - const.bottom_margin) *         graph_data.val / const.max_val y,         graph_data.radius    from graph_data, const),gd2 as (select dt,         round(nvl(lag(x) over(order by dt), x)) prev_x,         round(x) x,         round(nvl(lag(y) over(order by dt), y)) prev_y,         round(y) y,         radius    from gd1)/* header */select '<?xml version="1.0" encoding="UTF-8" standalone="no"?>' txt  from dualunion allselect '<svg version="1.1" width="' || viewbox_width || '" height="' ||       viewbox_height || '" viewBox="0 0 ' || viewbox_width || ' ' ||       viewbox_height ||       '" style="background:yellow" baseProfile="full" xmlns="http://personeltest.ru/away/www.w3.org/2000/svg" xmlns:xlink="http://personeltest.ru/away/www.w3.org/1999/xlink" xmlns:ev="http://personeltest.ru/away/www.w3.org/2001/xml-events">'  from constunion allselect '<title>Test graph</title>'  from dualunion allselect '<desc>Test graph</desc>'  from dualunion allselect '<rect width="' || viewbox_width || '" height="' || viewbox_height ||       '" style="fill:white" />'  from constunion all/* vertical lines */select '<line x1="' ||       to_char(round(left_margin +                     (viewbox_width - left_margin - right_margin) *                     (level - 1) / (num_vertical_lines - 1))) || '" x2="' ||       to_char(round(left_margin +                     (viewbox_width - left_margin - right_margin) *                     (level - 1) / (num_vertical_lines - 1))) || '" y1="' ||       to_char(round(top_margin)) || '" y2="' ||       to_char(round(viewbox_height - bottom_margin)) || '" style="stroke:' ||       const.stroke_vertical_lines || '; stroke-width:' ||       const.stroke_width_vertical_lines || '"/>'  from constconnect by level <= num_vertical_linesunion all/* dates under vertical lines */select '<text x="' ||       to_char(round(left_margin +                     (viewbox_width - left_margin - right_margin) *                     (level - 1) / (num_vertical_lines - 1) - x_dates_pad)) ||       '" y="' ||       to_char(round(viewbox_height - bottom_margin + y_dates_pad)) ||       '" font-size="' || font_size_dates || '" fill="' || fill_dates || '">' ||       to_char(min_dt + level - 1, 'yyyy-mm-dd') || '</text>'  from constconnect by level <= num_vertical_linesunion all/* horizontal lines */select '<line x1="' || to_char(round(left_margin)) || '" x2="' ||       to_char(round(viewbox_width - right_margin)) || '" y1="' ||       to_char(round(top_margin +                     (viewbox_height - top_margin - bottom_margin) *                     (level - 1) / (num_horizontal_lines - 1))) || '" y2="' ||       to_char(round(top_margin +                     (viewbox_height - top_margin - bottom_margin) *                     (level - 1) / (num_horizontal_lines - 1))) ||       '" style="stroke:' || const.stroke_horizontal_lines ||       '; stroke-width:' || const.stroke_width_horizontal_lines || '"/>'  from constconnect by level <= num_horizontal_linesunion all/* values near horizontal lines */select '<text text-anchor="end" x="' ||       to_char(round(left_margin - x_values_pad)) || '" y="' ||       to_char(round(viewbox_height - bottom_margin -                     (viewbox_height - top_margin - bottom_margin) *                     (level - 1) / (num_horizontal_lines - 1) +                     y_values_pad)) || '" font-size="' || font_size_values ||       '" fill="' || fill_values || '">' ||       to_char(round(max_val / (num_horizontal_lines - 1) * (level - 1), 2)) ||       '</text>'  from constconnect by level <= num_horizontal_linesunion all/* circles */select '<circle cx="' || to_char(gd2.x) || '" cy="' || to_char(gd2.y) ||       '" r="' || gd2.radius || '" style="fill:' || const.fill_circles ||       '"/>'  from gd2, constunion all/* graph data */select '<line x1="' || to_char(gd2.prev_x) || '" x2="' || to_char(gd2.x) ||       '" y1="' || to_char(gd2.prev_y) || '" y2="' || to_char(gd2.y) ||       '" style="stroke:' || const.stroke_graph || '; stroke-width:' ||       const.stroke_width_graph || '"/>'  from gd2, constunion all/* footer */select '</svg>' from dual;

Результат запроса можно сохранить в файл с расширением *.svg и посмотреть в браузере. При желании можно с помощью какой-либо из утилит конвертировать его в другие графические форматы, размещать на веб-страницах своего приложения и т.д.
Получилась такая картинка:



Приложение поиска по метаданным Oracle


Представьте себе, что стоит задача найти что-либо в исходном коде на Oracle, поискав информацию сразу на нескольких серверах. Речь идёт о поиске по объектам словаря данных Oracle. Рабочим местом для поиска является веб-интерфейс, куда пользователь-программист вводит искомую строку и выбирает галочками, на каких серверах Oracle осуществить этот поиск.
Веб-поисковик умеет искать строку по серверным объектам Oracle одновременно в нескольких различных базах данных банка. Например, можно поискать:
  • Где в коде Oracle зашита константа 61209, представляющая собой номер счёта второго порядка?
  • Где в коде и на каких серверах используется таблица accounts (в т.ч. через database link)?
  • С какого сервера из какой хранимой процедуры или триггера приходит сгенерированная программистом ошибка, например, ORA-20001 Курс валюты не найден?
  • Прописан ли планируемый к удалению индекс IX_CLIENTID где-либо явным образом в хинтах оптимизатора в SQL-запросах?
  • Используются ли где-либо (в т.ч. через database link) планируемые к удалению таблица, поле, процедура, функция и т.д.?
  • Где в коде явно зашит чей-то е-мэйл или номер телефона? Такие вещи лучше выносить из серверных объектов в настроечные таблицы.
  • Где в коде на серверах используется зависящий от версии Oracle функционал? Например, функция wm_concat выдаёт различный тип данных на выходе в зависимости от версии Oracle. Это может быть критично и требует внимания при миграции на более новую версию.
  • Где в коде используется какой-либо редкий приём, на который программисту хочется посмотреть, как на образец? Например, поискать в коде Oracle примеры использования функций sys_connect_by_path, regexp_instr или хинта push_subq.

По результатам поиска пользователю выдаётся информация, на каком сервере в коде каких функций, процедур, пакетов, триггеров, вьюшек и т.п. найдены требуемые результаты.
Опишем, как реализован такой поисковик.
Клиентская часть не сложная. Веб-интерфейс получает введённую пользователем поисковую строку, список серверов для поиска и логин пользователя. Веб-страница передаёт их в хранимую процедуру Oracle на сервере-обработчике. История обращений к поисковику, т.е. кто какой запрос выполнял, на всякий случай журналируется.
Получив поисковый запрос, серверная часть на поисковом сервере Oracle запускает в параллельных джобах несколько процедур, которые по database links на выбранных серверах Oracle сканируют следующие представления словаря данных в поисках искомой строки: dba_col_comments, dba_jobs, dba_mviews, dba_objects, dba_scheduler_jobs, dba_source, dba_tab_cols, dba_tab_comments, dba_views. Каждая из процедур, если что-то обнаружила, записывает найденное в таблицу результатов поиска (с соответствующим ID поискового запроса). Когда все поисковые процедуры завершили работу, клиентская часть выдаёт пользователю всё, что записалось в таблицу результатов поиска с соответствующим ID поискового запроса.
Но это ещё не всё. Помимо поиска по словарю данных Oracle в описанный механизм прикрутили ещё и поиск по репозиторию Informatica PowerCenter. Informatica PowerCenter является популярным ETL-средством, использующимся в Сбербанке при загрузке различной информации в хранилища данных. Informatica PowerCenter имеет открытую хорошо задокументированную структуру репозитория. По этому репозиторию есть возможность искать информацию так же, как и по словарю данных Oracle. Какие таблицы и поля используются в коде загрузок, разработанном на Informatica PowerCenter? Что можно найти в трансформациях портов и явных SQL-запросах? Вся эта информация имеется в структурах репозитория и может быть найдена. Для знатоков PowerCenter напишу, что наш поисковик сканирует следующие места репозитория в поисках маппингов, сессий или воркфловов, содержащих в себе где-то искомую строку: sql override, mapplet attributes, ports, source definitions in mappings, source definitions, target definitions in mappings, target_definitions, mappings, mapplets, workflows, worklets, sessions, commands, expression ports, session instances, source definition fields, target definition fields, email tasks.

Автор: Михаил Гричик, эксперт профессионального сообщества Сбербанка SberProfi DWH/BigData.

Профессиональное сообщество SberProfi DWH/BigData отвечает за развитие компетенций в таких направлениях, как экосистема Hadoop, Teradata, Oracle DB, GreenPlum, а также BI инструментах Qlik, SAP BO, Tableau и др.
Подробнее..

Как мы, сотрудники Сбера, считаем и инвестируем свои деньги

19.10.2020 10:13:52 | Автор: admin


Нужно ли покупать автомобиль за 750 тысяч рублей при том, что вы ездите 18 раз в месяц или дешевле пользоваться такси? Если вы работаете на заднем сидении или слушаете музыку как это меняет оценку? Как правильнее покупать квартиру в какой момент оптимально заканчивать копить на депозите и делать первый взнос по ипотеке? Или даже тривиальный вопрос: выгоднее положить деньги на депозит под 6% с ежемесячной капитализацией или под 6,2% с ежегодной капитализацией? Большинство людей даже не пытается производить такие подсчёты и даже не хотят собирать детальную информацию о своих деньгах. Вместо подсчётов подключают чувства и эмоции. Либо делают какую-то узкую оценку, например, детально подсчитывают годовую стоимость владения автомобилем, в то время как все эти расходы могут составлять лишь 5% от общих трат (а траты на другие стороны жизни при этом не подсчитывают). Мозг человека подвержен когнитивным искажениям. Например, сложно бросить, несмотря на неокупаемость, дело, в которое вложены масса времени и денег. Люди обычно излишне оптимистичны и недооценивают риски, а также легко внушаемы и могут купить дорогую безделушку или вложиться в финансовую пирамиду.
Понятное дело, в случае банка эмоциональная оценка не работает. Поэтому я хочу сначала рассказать о том, как оценивает деньги обычное физлицо (я, в том числе), и как это делает банк. Ниже будет немного финансового ликбеза и много про аналитику данных в Сбербанке для всего банка в целом.
Полученные выводы приведены только в качестве примера и не могут расцениваться как рекомендации для частных инвесторов, поскольку не учитывают множества факторов, оставшихся за рамками данной статьи.
Например, любое событие типа черный лебедь в макроэкономике, в корпоративном управлении любой из компаний и пр., может привести к кардинальным изменениям.

Предположим, что вы уже расплатились с ипотекой и у вас есть накопления. Эта статья может быть вам полезна, если вам:

  • не безразлично, сколько у вас накопилось имущества и как вести его учёт
  • интересно, как заставить своё имущество приносить вам дополнительный доход
  • хочется понять, какой же из способов вложения денег лучший: недвижимость, депозиты или акции
  • любопытно, что посоветует по этому вопросу анализ данных Сбербанка

Часто люди принимают финансовые решения, не располагая полной информацией о динамике своих собственных доходов и расходов, не имея оценки стоимости собственного имущества, не учитывая в расчётах инфляцию и т.п.
Иногда люди допускают ошибки, например, берут кредит, думая, что смогут его выплачивать, а затем не справляются. При этом ответ на вопрос, осилит ли человек обслуживание кредита, зачастую известен заранее. Нужно просто знать, сколько зарабатываешь, сколько тратишь, какова динамика изменений этих показателей.
Или, например, человек получает какую-то зарплату на работе, её периодически повышают, преподнося, как оценку заслуг. А в действительности по сравнению с инфляцией заработок этого человека может падать, и он может этого не осознавать, если не ведёт учёт доходов.
Некоторые люди не могут оценить, какой выбор выгоднее в сложившейся у них ситуации: снимать квартиру или взять ипотеку под такую-то ставку.
И вместо того, чтобы произвести подсчёт, каковы будут расходы в том и ином случае, как-то монетизировав в расчётах нефинансовые показатели (выгоду от московской прописки оцениваю в M рублей в месяц, удобство от проживания в снимаемой возле работы квартире оцениваю в N рублей в месяц), люди бегут в интернет дискутировать с собеседниками, у которых может быть иная финансовая ситуация и другие приоритеты в оценке нефинансовых показателей.
Я за ответственное финансовое планирование. Прежде всего, предлагается осуществлять сбор следующих данных о собственном финансовом положении:

  • учёт и оценка всего имеющегося в наличии имущества
  • учёт доходов и расходов, а также разницы между доходами и расходами, т.е. динамики накопления имущества

Учёт и оценка всего имеющегося в наличии имущества


Для начала приведем картинку, на которой, вероятно, неверно трактуется финансовое положение людей. На картинке отображены только лишь денежные составляющие имущества, которыми располагают изображённые люди. В реальности всё-таки у подающих милостыню людей, вероятно, кроме кредитов есть какое-то имущество, в результате чего баланс денег у них отрицательный, но итоговая стоимость их имущества всё-таки больше, чем у нищего.

Оцените, что у вас есть:

  • недвижимость
  • земельные участки
  • транспортные средства
  • вклады в банках
  • кредитные обязательства (с минусом)
  • инвестиции (акции, облигации, ...)
  • стоимость собственного бизнеса
  • прочее имущество

Среди имущества можно отметить ликвидную долю, которую возможно достаточно быстро отозвать и конвертировать в другие формы. Например, долю в квартире, которой владеешь совместно с проживающими в ней родственниками, можно отнести к неликвидному имуществу. Долгосрочные вложения во вклады или акции, которые нельзя отозвать без потерь, также можно считать неликвидными. В свою очередь, недвижимость, которой владеешь, но не проживаешь, транспортные средства, краткосрочные и отзываемые вклады можно отнести к ликвидному имуществу. Например, если понадобятся деньги на срочное лечение, то польза от каких-то инструментов примерно нулевая, поэтому ликвидная доля более ценна.
Далее, среди имущества можно выделить неприбыльное и прибыльное. Например, недвижимость, которая не сдаётся в аренду, а также транспортные средства можно расценить как неприбыльные. А сдаваемые в аренду объекты недвижимости, вложенные под ставку выше инфляции депозиты и акции прибыльное имущество.
Получится, например, такая картинка (данные сгенерированы случайным образом):



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

Учёт доходов, расходов и динамики накопления имущества


Предлагается регулярно записывать в электронной форме свои доходы и расходы. В эпоху интернет-банков это не требует особых усилий. При этом доходы и расходы можно делить по категориям. Далее, агрегируя их по годам, можно делать выводы об их динамике. Важно учитывать инфляцию, чтобы иметь представление о том, как выглядят суммы за прошлые годы в современных ценах. У каждого своя потребительская корзина. Бензин и еда дорожают с разной скоростью. Но посчитать свою личную инфляцию достаточно сложно. Поэтому, с некоторой погрешностью, можно пользоваться данными об официальном уровне инфляции.
Данные по ежемесячной инфляции имеются во многих открытых источниках, в том числе загружены и в озеро данных Сбербанка.
Пример визуализации динамики доходов-расходов (данные сгенерированы случайным образом, динамика инфляции настоящая):



Имея такую полную картину, можно делать выводы о своём реальном росте/снижении доходов и реальном росте/снижении накоплений, анализировать динамику расходов по категориям и принимать обоснованные финансовые решения.
Какой способ вложения свободных денежных средств обыгрывает инфляцию и приносит наибольший пассивный доход?
В озере данных Сбербанка есть ценные данные на эту тему:

  • динамика стоимости квадратного метра в Москве
  • база предложений по продаже и аренде недвижимости в Москве и ближнем Подмосковье
  • динамика средней годовой процентной ставки по депозитам
  • динамика уровня рублёвой инфляции
  • динамика индекса Мосбиржи полной доходности брутто (MCFTR)
  • котировки акций Мосбиржи и данные о выплаченных дивидендах

Эти данные позволят нам сравнить доходность и риски от вложений в сдаваемую в аренду недвижимость, в банковские депозиты и в рынок акций. При этом не забудем учесть инфляцию.
Сразу скажу, что в этом посте мы занимаемся исключительно анализом данных и не прибегаем к использованию каких-либо экономических теорий. Просто посмотрим, что говорят наши данные какой способ сохранить и преумножить сбережения в России за последние годы дал наилучший результат.
Кратко расскажем, о том, как собираются и анализируются данные, использующиеся в этой статье, и прочие данные в Сбербанке. Имеется слой реплик источников, которые хранятся в формате parquet на hadoop. Используются как внутренние источники (различные АС банка), так и внешние. Реплики источников собираются разными способами. Есть продукт stork, в основе которого лежит spark, набирает обороты и второй продукт Ab Initio AIR. Реплики источников загружаются на различные кластеры hadoop под управлением Cloudera, в том числе могут быть прилинкованы с одного кластера на другой. Кластеры разделены преимущественно по бизнес-блокам, имеются также и кластеры Лаборатории данных. На базе реплик источников строятся различные витрины данных, доступные бизнес-пользователям и data scientist-ам. Для написания этой статьи были использованы различные приложения spark, запросы к hive, приложения по анализу данных и визуализации результатов в формате графики SVG.

Исторический анализ рынка недвижимости


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

График цен в рублях без учёта инфляции:



График цен в рублях с учётом инфляции (в современных ценах):



Видим, что исторически цена колебалась около 200 000 руб./кв.м. в современных ценах и изменчивость была достаточно низкая.

Сколько же процентов годовых сверх инфляции приносят инвестиции в жилую недвижимость? Как доходность зависит от количества комнат в квартире? Проанализируем имеющуюся у Сбербанка базу объявлений о продаже и аренде квартир в Москве и ближнем Подмосковье.
В нашей базе нашлось достаточно много многоквартирных домов, в которых имеются одновременно объявления о продаже квартир и объявления об аренде квартир, причём количество комнат в продаваемых и сдаваемых в аренду квартирах одинаково. Мы сопоставили такие случаи, сгруппировав по домам и количеству комнат в квартире. Если предложений в такой группе имелось несколько, вычисляли среднюю цену. Если площадь продаваемой и сдаваемой в аренду квартиры отличалась, то пропорционально изменяли цену предложения, чтобы площади сравниваемых квартир соответствовали. В результате, предложения нанесли на график. Каждый кружок это фактически квартира, которую одновременно предлагают приобрести и снять. На горизонтальной оси видим стоимость приобретения квартиры, а на вертикальной оси стоимость снятия этой же квартиры. Количество комнат в квартире понятно из цвета кружка, а чем больше площадь квартиры, тем больше радиус кружка. С учётом сверхдорогих предложений график получился такой:



Если убрать дорогие предложения, то можно детальнее разглядеть цены в эконом-сегменте:



Корреляционный анализ показывает, что зависимость между стоимостью аренды квартиры и стоимостью её покупки близка к линейной.
Получилось такое соотношение между стоимостью годовой аренды квартиры и стоимостью приобретения квартиры (не забудем, что годовая стоимость это 12 месячных):
Количество комнат: Отношение стоимости годовой аренды квартиры к стоимости приобретения квартиры:
1-комнатные 5,11%
2-комнатные 4,80%
3-комнатные 4,94%
Всего 4,93%

Получили среднюю оценку в 4,93% годовых доходности от сдачи квартиры в аренду сверх инфляции. Также интересен момент, что дешёвые 1-комнатные квартиры сдавать в аренду немного выгоднее. Мы сравнивали цену предложения, которая в обоих случаях (аренды и покупки) немного завышена, поэтому корректировка не требуется. Однако требуются другие корректировки: сдаваемые в аренду квартиры нужно иногда хотя бы косметически ремонтировать, некоторое время занимает поиск арендатора и квартиры пустуют, иногда в цену аренды не заложены коммунальные платежи частично или полностью, также имеет место крайне незначительное обесценивание квартир с годами.
С учётом корректировок, от сдачи жилой недвижимости в аренду можно иметь доход до 4,5% годовых (сверх того, что сама недвижимость не обесценивается). Если такая доходность впечатляет, у Сбербанка есть множество предложений на ДомКлик.

Исторический анализ ставок по депозитам


Рублёвые депозиты в России в последние несколько лет в основном обыгрывают инфляцию. Но не на 4,5%, как сдаваемая недвижимость, а, в среднем, на 2%.
На графике ниже видим динамику сравнения ставок по депозитам и уровня инфляции.



Отмечу такой момент, что доход от депозитов обыгрывает инфляцию несколько сильнее, чем на вышеприведённом графике по следующим причинам:

  • Можно фиксировать ставку по пополняемым вкладам в благоприятное время на несколько месяцев вперёд
  • Ежемесячная капитализация, свойственная многим учтённым в этих усреднённых данных вкладам, за счёт сложных процентов добавляет прибыли
  • Выше были учтены ставки по топ-10 банкам по информации от Банка России, вне топ-10 можно найти ставки несколько выше

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

Исторический анализ рынка акций


Теперь посмотрим на более разнообразный и рискованный рынок российских акций. Доходность от вложения в акции не фиксирована и может сильно меняться. Однако, если диверсифицировать активы и заниматься инвестированием длительный период, то можно проследить среднюю годовую процентную ставку, характеризующую успешность инвестирования в портфель акций.
Для далёких от темы читателей скажу пару слов об индексах акций. В России есть индекс Мосбиржи, который показывает, динамику рублевой стоимости портфеля, состоящего из 50 крупнейших российских акций. Состав индекса и доля акций каждой компании зависит от объема торговых операций, объема бизнеса, количества находящихся в обращении акций. График ниже показывает, как рос индекс Мосбиржи (т.е. такой усреднённый портфель) в последние годы.



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

Поэтому нам будет интереснее индекс Мосбиржи полной доходности брутто (MCFTR), который учитывает полученные дивиденды и списанный с этих дивидендов налог. Покажем на графике ниже, как менялся этот индекс в последние годы. Кроме того, учтём инфляцию и посмотрим, как рос этот индекс в современных ценах:



Зелёный график это и есть реальная стоимость портфеля в современных ценах, если вложиться в индекс Мосбиржи, регулярно реинвестировать дивиденды и платить налоги.

Посмотрим, какой же был коэффициент роста индекса MCFTR за последние 1,2,3,,11 лет. Т.е. какова же была бы наша доходность, если бы мы купили акции в пропорциях этого индекса и регулярно реинвестировали бы полученные дивиденды в те же самые акции:
Лет Начало Конец MCFTR
нач. с
учётом
инфл.
MCFTR
кон. с
учётом
инфл.
Коэфф.
роста
Годовой
коэфф.
роста
1 30.07.2019 30.07.2020 4697,47 5095,54 1,084741 1,084741
2 30.07.2018 30.07.2020 3835,52 5095,54 1,328513 1,152612
3 30.07.2017 30.07.2020 3113,38 5095,54 1,636659 1,178472
4 30.07.2016 30.07.2020 3115,30 5095,54 1,635650 1,130896
5 30.07.2015 30.07.2020 2682,35 5095,54 1,899655 1,136933
6 30.07.2014 30.07.2020 2488,07 5095,54 2,047989 1,126907
7 30.07.2013 30.07.2020 2497,47 5095,54 2,040281 1,107239
8 30.07.2012 30.07.2020 2634,99 5095,54 1,933799 1,085929
9 30.07.2011 30.07.2020 3245,76 5095,54 1,569907 1,051390
10 30.07.2010 30.07.2020 2847,81 5095,54 1,789284 1,059907
11 30.07.2009 30.07.2020 2223,17 5095,54 2,292015 1,078318

Видим, что, вложившись любое количество лет назад, мы получили бы победу над инфляцией в 5-18% ежегодно в зависимости от удачности точки входа.

Составим еще одну табличку не прибыльность за каждые последние N лет, а прибыльность за каждый из последних N одногодовых периодов:
Год Начало Конец MCFTR
нач. с
учётом
инфл.
MCFTR
кон. с
учётом
инфл.
Годовой
коэфф.
роста
1 30.07.2019 30.07.2020 4697,47 5095,54 1,084741
2 30.07.2018 30.07.2019 3835,52 4697,47 1,224728
3 30.07.2017 30.07.2018 3113,38 3835,52 1,231947
4 30.07.2016 30.07.2017 3115,30 3113,38 0,999384
5 30.07.2015 30.07.2016 2682,35 3115,30 1,161407
6 30.07.2014 30.07.2015 2488,07 2682,35 1,078085
7 30.07.2013 30.07.2014 2497,47 2488,07 0,996236
8 30.07.2012 30.07.2013 2634,99 2497,47 0,947810
9 30.07.2011 30.07.2012 3245,76 2634,99 0,811825
10 30.07.2010 30.07.2011 2847,81 3245,76 1,139739
11 30.07.2009 30.07.2010 2223,17 2847,81 1,280968

Видим, что не каждый из годов был удачным, но за неудачными годами следовали со временем удачные года, которые всё починили.

Теперь, для лучшего понимания, давайте абстрагируемся от этого индекса и посмотрим на примере конкретной акции, какой бы получился результат, если вложиться в эту акцию 15 лет назад, повторно вкладывать дивиденды и платить налоги. Результат посмотрим с учётом инфляции, т.е. в современных ценах. Ниже показан пример обыкновенной акции Сбербанка. Зелёный график показывает динамику стоимости портфеля, изначально состоявшего из одной акции Сбербанка в современных ценах с учётом реинвестиции дивидендов. За 15 лет инфляция обесценила рубли в 3.014135 раз. Акция Сбербанка за эти годы подорожала с 21.861 руб. до 218.15 руб., т.е. цена выросла в 9.978958 раз без учёта инфляции. За эти годы владельцу одной акции было выплачено в разное время дивидендов за вычетом налогов в сумме 40.811613 руб. Суммы выплаченных дивидендов показаны на графике красными вертикальными палочками и не относятся к самому графику, в котором дивиденды и их реинвестиция также учтены. Если всякий раз на эти дивиденды вновь покупались акции Сбербанка, то в конце периода акционер уже владел не одной, а 1.309361 акциями. С учётом реинвестиции дивидендов и инфляции исходный портфель подорожал в 4.334927 раз за 15 лет, т.е. дорожал в 1.102721 раз ежегодно. Итого, обыкновенная акция Сбербанка приносила владельцу в среднем 10,27% годовых сверх инфляции каждый из 15 последних лет:



В качестве ещё одного примера приведём аналогичную картинку с динамикой по привилегированной акции Сбербанка. Привилегированная акция Сбербанка приносила владельцу в среднем ещё больше, 13,59% годовых сверх инфляции каждый из 15 последних лет:



Эти результаты на практике будут чуть ниже, потому что при покупке акций нужно платить небольшую брокерскую комиссию. В то же время результат может быть ещё улучшен, если использовать Индивидуальный Инвестиционный Счёт, позволяющий получить от государства налоговый вычет в некотором ограниченном объёме. Если вы не слышали о таком, предлагается поискать по аббревиатуре ИИС. Не забудем также упомянуть, что ИИС можно открыть в Сбербанке.

Итак, мы предварительно получили, что инвестировать в акции исторически выгоднее, чем в недвижимость и депозиты. Для развлечения приведём полученный в результате анализа данных хит-парад из 20 наилучших акций, которые торгуются на рынке более 10 лет. В последнем столбце видим, во сколько раз в среднем каждый год рос портфель из акций с учётом инфляции и реинвестиции дивидендов. Видим, что многие акции обыгрывали инфляцию более, чем на 10%:
Акция Начало Конец Коэфф. инфляции Нач. цена Кон. цена Рост
числа
акций
за счёт
реинве-
стиции
диви-
дендов,
раз
Итоговый
средне-
годовой
рост, раз
Лензолото 30.07.2010 30.07.2020 1,872601 1267,02 17290 2,307198 1,326066
НКНХ ап 30.07.2010 30.07.2020 1,872601 5,99 79,18 2,319298 1,322544
МГТС-4ап 30.07.2010 30.07.2020 1,872601 339,99 1980 3,188323 1,257858
Татнфт 3ап 30.07.2010 30.07.2020 1,872601 72,77 538,8 2,037894 1,232030
МГТС-5ао 30.07.2010 30.07.2020 1,872601 380,7 2275 2,487047 1,230166
Акрон 30.07.2010 30.07.2020 1,872601 809,88 5800 2,015074 1,226550
Лензол. ап 30.07.2010 30.07.2020 1,872601 845 5260 2,214068 1,220921
НКНХ ао 30.07.2010 30.07.2020 1,872601 14,117 92,45 1,896548 1,208282
Ленэнерг-п 30.07.2010 30.07.2020 1,872601 25,253 149,5 1,904568 1,196652
ГМКНорНик 30.07.2010 30.07.2020 1,872601 4970 19620 2,134809 1,162320
Сургнфгз-п 30.07.2010 30.07.2020 1,872601 13,799 37,49 2,480427 1,136619
ИРКУТ-3 30.07.2010 30.07.2020 1,872601 8,127 35,08 1,543182 1,135299
Татнфт 3ао 30.07.2010 30.07.2020 1,872601 146,94 558,4 1,612350 1,125854
Новатэк ао 30.07.2010 30.07.2020 1,872601 218,5 1080,8 1,195976 1,121908
СевСт-ао 30.07.2010 30.07.2020 1,872601 358 908,4 2,163834 1,113569
Красэсб ао 30.07.2010 30.07.2020 1,872601 3,25 7,07 2,255269 1,101105
ЧТПЗ ао 30.07.2010 30.07.2020 1,872601 55,7 209,5 1,304175 1,101088
Сбербанк-п 30.07.2010 30.07.2020 1,872601 56,85 203,33 1,368277 1,100829
ПИК ао 30.07.2010 30.07.2020 1,872601 108,26 489,5 1,079537 1,100545
ЛУКОЙЛ 30.07.2010 30.07.2020 1,872601 1720 5115 1,639864 1,100444

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

Задача. Найти акцию, стабильно дающую доход выше недвижимости (среднегодовой коэффициент роста 1.045 свыше инфляции) максимальное число раз по каждому из последних 10 одногодовых периодов, когда акция торговалась.
В этой и следующих задачах имеется в виду вышеописанная модель с реинвестицией дивидендов и учётом инфляции.
Вот победители в этой номинации согласно нашему анализу данных. Акции в верхней части таблицы из года в год стабильно показывают высокую доходность без провалов. Здесь Год 1 это 30.07.2019-30.07.2020, Год 2 это 30.07.2018-30.07.2019 и т.д.:
Акция Число
побед
над
недви-
жимо-
стью
за
после-
дние
10 лет
Год 1 Год 2 Год 3 Год 4 Год 5 Год 6 Год 7 Год 8 Год 9 Год 10
Татнфт 3ап 8 0,8573 1,4934 1,9461 1,6092 1,0470 1,1035 1,2909 1,0705 1,0039 1,2540
МГТС-4ап 8 1,1020 1,0608 1,8637 1,5106 1,7244 0,9339 1,1632 0,9216 1,0655 1,6380
ЧТПЗ ао 7 1,5532 1,2003 1,2495 1,5011 1,5453 1,2926 0,9477 0,9399 0,3081 1,3666
СевСт-ао 7 0,9532 1,1056 1,3463 1,1089 1,1955 2,0003 1,2501 0,6734 0,6637 1,3948
НКНХ ао 7 1,3285 1,5916 1,0821 0,8403 1,7407 1,3632 0,8729 0,8678 1,0716 1,7910
МГТС-5ао 7 1,1969 1,0688 1,8572 1,3789 2,0274 0,8394 1,1685 0,8364 1,0073 1,4460
Газпрнефть 7 0,8119 1,3200 1,6868 1,2051 1,1751 0,9197 1,1126 0,7484 1,1131 1,0641
Татнфт 3ао 7 0,7933 1,0807 1,9714 1,2109 1,0728 1,1725 1,0192 0,9815 1,0783 1,1785
Ленэнерг-п 7 1,3941 1,1865 1,7697 2,4403 2,2441 0,6250 1,2045 0,7784 0,4562 1,4051
НКНХ ап 7 1,3057 2,4022 1,2896 0,8209 1,2356 1,6278 0,7508 0,8449 1,5820 2,4428
Сургнфгз-п 7 1,1897 1,0456 1,2413 0,8395 0,9643 1,4957 1,2140 1,1280 1,4013 1,0031

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

Теперь сформулируем и решим такую задачу на анализ данных. Целесообразно ли немножко спекулировать, всякий раз покупая акции за M дней до даты выплаты дивидендов и продавая акции через N дней после даты выплаты дивидендов? Лучше ли собирать урожай с дивидендов и выходить из акции, чем сидеть в акции круглый год? Допустим, что нет потерь на комиссии от такого входа-выхода. И анализ данных поможет нам найти границы коридора M и N, который был исторически наиболее удачен в деле сбора урожая дивидендов вместо долгого владения акциями.

Приведём анекдот 2008 года.
Джон Смит, выпрыгнувший из окна 75-го этажа на Уолл Стрит, после удара о землю подпрыгнул на 10 метров, чем немного отыграл свое утреннее падение.

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

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

Наша модель просчитала все варианты ширины окрестности вокруг дат выплаты дивидендов за всю историю. Были приняты следующие ограничения: M<=30, N>=20. Дело в том, что далеко не всегда ранее, чем за 30 дней до выплаты дивидендов заранее известна дата и суммы выплаты. Также дивиденды приходят на счёт далеко не сразу, а с задержкой. Считаем, что нужно от 20 дней, чтобы гарантированно получить на счёт и реинвестировать дивиденды. С такими ограничениями модель выдала следующий ответ. Наиболее оптимально покупать акции за 34 дня до даты выплаты дивидендов и продавать их через 25 дней после даты выплаты дивидендов. При таком сценарии в среднем получался рост в 3,11% за этот период, что даёт 20,9% годовых. Т.е. при рассматриваемой модели инвестирования (с реинвестицией дивидендов и учётом инфляции) если покупать акцию за 34 дня до даты выплаты дивидендов и продавать её через 25 дней после даты выплаты дивидендов, то имеем 20,9% годовых свыше уровня инфляции. Это проверено усреднением по всем случаям выплаты дивидендов из нашей базы.

Например, по привилегированной акции Сбербанка такой сценарий входа-выхода давал бы 11,72% роста свыше уровня инфляции за каждый вход-выход в окрестности даты выплаты дивидендов. Это составляет аж 98,6% годовых свыше уровня инфляции. Но это, конечно, пример случайного везения.
Акция Вход Дата выплаты дивидендов Выход Коэфф. роста
Сбербанк-п 10.05.2019 13.06.2019 08.07.2019 1,112942978
Сбербанк-п 23.05.2018 26.06.2018 21.07.2018 0,936437635
Сбербанк-п 11.05.2017 14.06.2017 09.07.2017 1,017492563
Сбербанк-п 11.05.2016 14.06.2016 09.07.2016 1,101864592
Сбербанк-п 12.05.2015 15.06.2015 10.07.2015 0,995812419
Сбербанк-п 14.05.2014 17.06.2014 12.07.2014 1,042997818
Сбербанк-п 08.03.2013 11.04.2013 06.05.2013 0,997301095
Сбербанк-п 09.03.2012 12.04.2012 07.05.2012 0,924053861
Сбербанк-п 12.03.2011 15.04.2011 10.05.2011 1,010644958
Сбербанк-п 13.03.2010 16.04.2010 11.05.2010 0,796937418
Сбербанк-п 04.04.2009 08.05.2009 02.06.2009 2,893620094
Сбербанк-п 04.04.2008 08.05.2008 02.06.2008 1,073578067
Сбербанк-п 08.04.2007 12.05.2007 06.06.2007 0,877649005
Сбербанк-п 25.03.2006 28.04.2006 23.05.2006 0,958642001
Сбербанк-п 03.04.2005 07.05.2005 01.06.2005 1,059276282
Сбербанк-п 28.03.2004 01.05.2004 26.05.2004 1,049810801
Сбербанк-п 06.04.2003 10.05.2003 04.06.2003 1,161792898
Сбербанк-п 02.04.2002 06.05.2002 31.05.2002 1,099316569

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

Поставим нашей модели ещё одну задачу по анализу данных:

Задача. Найти акцию с наирегулярнейшей возможностью заработка на входе-выходе в окрестности даты выплаты дивидендов. Будем оценивать сколько случаев из случаев выплаты дивидендов дали возможность заработка более 10% в годовом исчислении выше уровня инфляции, если входить в акцию за 34 дня до и выходить через 25 дней после даты выплаты дивидендов.

Будем рассматривать акции, по которым было хотя бы 5 случаев выплаты дивидендов. Получившийся хит-парад приведён ниже. Отметим, что результат имеет ценность скорее всего только с точки зрения задачи на анализ данных, но не как практическое руководство к инвестированию.
Акция Количество
случаев выигрыша
более 10% годовых
сверх инфляции
Количество
случаев
выплаты
дивидендов
Доля
случаев
победы
Средний коэфф. роста
Лензолото 5 5 1 1,320779017
МРСК СЗ 6 7 0,8571 1,070324870
Роллман-п 6 7 0,8571 1,029644533
Россети ап 4 5 0,8 1,279877637
Кубанэнр 4 5 0,8 1,248634960
ЛСР ао 8 10 0,8 1,085474828
АЛРОСА ао 8 10 0,8 1,042920287
ФСК ЕЭС ао 6 8 0,75 1,087420610
НМТП ао 10 14 0,7143 1,166690777
КузбТК ао 5 7 0,7143 1,029743667

Из проведённого анализа рынка акций можно сделать такие выводы:
1) Проверено, что заявленная в материалах брокеров, инвестиционных компаний и прочих заинтересованных лиц доходность акций выше депозитов и инвестиционной недвижимости имеет место быть.
2) Волатильность рынка акций очень высокая, но на долгий срок с существенной диверсификацией портфеля вкладываться можно. Ради добавочных 13% налогового вычета при инвестиции на ИИС открывать для себя рынок акций вполне целесообразно и сделать это можно, в том числе, в Сбербанке.
3) Исходя из анализа результатов за прошлые периоды найдены лидеры по стабильной высокой доходности и по выгодности входа-выхода в окрестности даты выплаты дивидендов. Однако результаты не такие уж однозначные и руководствоваться только ими в своём инвестировании не стоит. Это были примеры задач на анализ данных.

Итого:
Полезно вести учет своего имущества, а также доходов и расходов. Это помогает в финансовом планировании. Если удаётся копить деньги, то есть возможности инвестировать их под ставку выше инфляции. Анализ данных из озера данных Сбербанка показал, что депозиты ежегодно приносят 2%, сдача квартир в аренду 4,5%, а российские акции около 10% свыше инфляции при наличии существенно больших рисков.

Автор: Михаил Гричик, эксперт профессионального сообщества Сбербанка SberProfi DWH/BigData.

Профессиональное сообщество SberProfi DWH/BigData отвечает за развитие компетенций в таких направлениях, как экосистема Hadoop, Teradata, Oracle DB, GreenPlum, а также BI инструментах Qlik, SAP BO, Tableau и др.
Подробнее..

Категории

Последние комментарии

  • Имя: Макс
    24.08.2022 | 11:28
    Я разраб в IT компании, работаю на арбитражную команду. Мы работаем с приламы и сайтами, при работе замечаются постоянные баны и лаги. Пацаны посоветовали сервис по анализу исходного кода,https://app Подробнее..
  • Имя: 9055410337
    20.08.2022 | 17:41
    поможем пишите в телеграм Подробнее..
  • Имя: sabbat
    17.08.2022 | 20:42
    Охренеть.. это просто шикарная статья, феноменально круто. Большое спасибо за разбор! Надеюсь как-нибудь с тобой связаться для обсуждений чего-либо) Подробнее..
  • Имя: Мария
    09.08.2022 | 14:44
    Добрый день. Если обладаете такой информацией, то подскажите, пожалуйста, где можно найти много-много материала по Yggdrasil и его уязвимостях для написания диплома? Благодарю. Подробнее..
© 2006-2024, personeltest.ru