Аналитик

Некоторое время назад перед нами встал вопрос выбора 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:

GreenPlum:

Видим, что скорость инициализирующей загрузки как в 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

Получается такая картина:

Видим, что 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 и др.

В Сбере есть несколько практик 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;

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

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

Опишем вариант решения достаточно типовой банковской задачи по протягиванию остатков. Допустим, имеется таблица фактов изменения остатков по счетам. Требуется на каждый день календаря указать актуальный остаток по счёту (последний за день). Такая информация часто бывает нужна в хранилищах данных. Если в какой-то день не было движений по счёту, то нужно повторить последний известный остаток. Если объёмы данных и вычислительные мощности сервера позволяют, то можно решить такую задачу с помощью 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;

Результат запроса соответствует ожиданиям.

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

При загрузке данных в хранилища часто решается задача, когда нужно выстроить единую историю по сущности, имея отдельные истории атрибутов этой сущности, пришедшие из различных источников. Допустим, имеется некоторая сущность с первичным ключом 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;

Результат получается такой:

Нормалайзер

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

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;

Результат получается такой:

Визуализация в формате 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 и др.

Нужно ли покупать автомобиль за 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 месячных):

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

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

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



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

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

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

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

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



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

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



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

Посмотрим, какой же был коэффициент роста индекса MCFTR за последние 1,2,3,,11 лет. Т.е. какова же была бы наша доходность, если бы мы купили акции в пропорциях этого индекса и регулярно реинвестировали бы полученные дивиденды в те же самые акции:

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

Составим еще одну табличку не прибыльность за каждые последние N лет, а прибыльность за каждый из последних N одногодовых периодов:

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

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

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

Задача. Найти акцию, стабильно дающую доход выше недвижимости (среднегодовой коэффициент роста 1.045 свыше инфляции) максимальное число раз по каждому из последних 10 одногодовых периодов, когда акция торговалась.
В этой и следующих задачах имеется в виду вышеописанная модель с реинвестицией дивидендов и учётом инфляции.
Вот победители в этой номинации согласно нашему анализу данных. Акции в верхней части таблицы из года в год стабильно показывают высокую доходность без провалов. Здесь Год 1 это 30.07.2019-30.07.2020, Год 2 это 30.07.2018-30.07.2019 и т.д.:

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

Теперь сформулируем и решим такую задачу на анализ данных. Целесообразно ли немножко спекулировать, всякий раз покупая акции за 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% в годовом исчислении выше уровня инфляции, если входить в акцию за 34 дня до и выходить через 25 дней после даты выплаты дивидендов.

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

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

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

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

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

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

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

1. Языки программирования

Не будем касаться исключительных, уникальных или редких случаев. Рассмотрим все только самое популярное. И конечно же, в первую очередь, это язык python.

Python
Python служит главным инструментом в руках data scientists, не имеет строгой типизации и предназначен для быстрой разработки прототипов или написания коротких сценариев или скриптов. Люди разбирающиеся в программировании и computer science его часто критикуют за то, что алгоритмы написанные на чистом python оказываются не оптимальными в отношении своей производительности и требованиям к памяти.
Но тем не менее у данного языка программирования есть много плюсов. Среди них я бы отметил то, что python преподают уже практически везде, в связи с чем сравнительно легко найти аналитика знающего python. Второе преимущество это библиотеки для работы с данными и машинного обучения, имеющие удобный интерфейс. Например, на основе библиотеки sklearn легко собирать конвейеры предварительной обработки данных и построения моделей. Все алгоритмы и настройки машинного обучения инкапсулированы внутри классов и объектов, что делает код очень простым.

R
До недавнего времени основным конкурентом python был язык R. Пожелания к знанию R и сейчас изредка встречаются в описаниях вакансий по крайней мере в разделе преимущества. До середины 2018-го года я и сам программировал на R. И при попытке автоматизировать часть своей работы по машинному обучению чуть не изобрел велосипед, пытаясь на R создать конвейеры подготовки данных и обучения моделей. Чуть позже узнал, что такие конвейеры уже давно существуют в библиотеке sklearn и называются pipeline.

C++, C#
Если существующих библиотек на python недостаточно и требуется реализовать новый алгоритм с высокой производительностью, к вашим услугам компилируемый и статически типизированный язык C++ или похожий на него язык C#.

MatLab
Язык MatLab встроен в одноименный пакет программ и интерактивную среду инженерных расчетов. Правда предназначен данный язык в большей степени для решения технических задач, а не для выполнения финансового или бизнес-анализа. Например, мне посчастливилось применять MatLab дважды: в процессе исследования сигналов акустической эмиссии в конструкциях, а также при обработке человеческой речи.
Существует ряд библиотек машинного обучения с API для других языков программирования, таких как Java, JavaScript, Scala и т.д. Но останавливаться на них не буду поскольку цель статьи немного иная.

Прошу немного потерпеть. Обо всем вы узнаете в следующих разделах.

2. AutoML и визуальные конструкторы

AutoML согласно своей основной идее резко упрощает задачу исследователя и сводит несколько шагов по изучению и подготовке данных, конструированию признаков, выбору и сравнению алгоритма машинного обучения и настройке гиперпараметров к одному единственному шагу. И этот шаг заключается в выборе и настройке одного большого ящика под названием AutoML. Результатом запуска алгоритма AutoML является сконструированный и соответствующим образом настроенный и обученный pipeline. Остается только брать сырые данные, подсовывать их в pipeline и ждать на выходе результат в виде прогнозов.

Ящик под названием AutoML выглядит либо как библиотека машинного обучения, либо как веб-сервис куда заливаются данные.
Если это библиотека, то она отличается от sklearn тем, что наш привычный код в 20-30 строк сжимается до 5 строк. Известный пример такой библиотеки H2O.
Другой пример библиотека MLBox. Про нее в интернете можно найти истории, о том как применение MLBox позволило попасть в топовые 5% на соревнованиях kaggle.

Теперь несколько слов об облачных сервисах AutoML. Во первых, свои технические решения спешат представить все основные цифровые гиганты. Вот некоторые из них: Google AutoML Tables, Azure Machine Learning (Microsoft), SageMaker Autopilot (Amazon). Перечисленные сервисы должны быть интересны в первую очередь тем компаниям, которые разрабатывают аналитические системы на облачных платформах. Очень удобно, когда и инфраструктуру данных, и вычислительные ресурсы, и готовые алгоритмы машинного обучения предоставляет один и тот же провайдер. Интеграция получается поистине бесшовной.
Помимо цифровых гигантов на рынке AutoML появляются и игроки поменьше. Например, непосредственно в настоящий момент в компании Bell Integrator идет активная работа над платформой neuton.ai.

В этом же разделе стоит вспомнить про системы машинного обучения, занимающие промежуточные позиции между непосредственным программированием на R и Python и полностью упакованным в коробку AutoML. Это так называемые конструкторы workflow. Два типичных примера: конструктор машинного обучения Azure от Microsoft и платформа SberDS Сбербанка.
Конструктор представляет собой набор кубиков, из которых можно собрать весь конвейер машинного обучения, включая финальную проверку работоспособности модели. Это несомненно красивое решение для людей с визуальным типом мышления, которым удобно представлять процесс машинного обучения и тестирования моделей в виде схем.

3. Инструменты BI

Здесь бы я хотел рассмотреть несколько BI решений в области аналитики: Power BI, Tableau, Qlick Sense, Qlick View и Excel.

Power BI
Power BI это набор аналитических инструментов от Microsoft, которые доступны в виде десктопных приложений и облачных сервисов. Существуют корпоративные решения, работающие на закрытой it-инфраструктуре компании. Работа в Power BI Desktop или Power BI Services не требует навыков программирования. Предусмотрена возможность онлайн-интеграции с внешними источниками данных, а также загрузка данных в формате csv.
Power BI способен решать задачи машинного обучения посредством AutoML, то есть для построения модели классификации или регрессии писать программный код как на питоне не придется. Кроме стандартных задач анализа табличных данных в функционал встроены технологии анализа тональности, извлечения ключевых фраз, распознавания языка и добавления тегов к изображению.

Tableau
Tableau также представляет собой целое семейство онлайн и десктопных приложений, как и Power BI. Данные приложения имеют простой визуальный интерфейс и позволяют работать методом перетаскивания drag-and-drop. Красивые графики строятся буквально за несколько кликов. Также данные можно анализировать в табличном виде и применять к ним различные фильтры.
Tableau позволяет решать и задачи машинного обучения, такие как регрессия, прогнозирование временных рядов, кластерный анализ. А главное, Tableau способен интегрироваться с внешними скриптами на R и Python. Получается легко расширяемый инструмент.

Qlick Sence и Qlick View
Qlick Sence и Qlick View по позиционированию и интерфейсу отличаются между собой, но по сути и по алгоритмам решения задач построены на одном движке. Qlick View корпоративная платформа, которой управляют it-специалисты, Qlick Sence инструмент для личного использования без необходимости обращаться за помощью в тех. поддержку.
При первом же знакомстве бросается в глаза красота и легкость визуализации. Это тот самый инструмент, если надо построить приятный глазу дашборд для руководства. С моей точки зрения особенно зрелищным выглядит возможность менять масштаб при анализе географических карт и кластеров на двухмерных графиках. Вспоминаются кадры из фильмов, где на фото со спутников пытаются разглядеть номер автомобиля или выделить человека из толпы на площади.
Еще одна интересная опция наличие мобильного приложения для выполнения анализа со смартфона. Так и представляется топ-менеджер сети ритейла, спешащий на очередной рейс в аэропорту и получивший неожиданное сообщение в мессенджере со ссылкой на дашборд.
Qlick Sence интегрируется с Python, а следовательно и с машинным обучением.

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

4. Изюминка на торте. Автоматическая генерация кода на основе AI.

Как-то раз при знакомстве в сети мне задали вопрос ты программируешь на python?. И когда я ответил Да, продолжение было совершенно неожиданным.
А ты знаешь про это и далее шла ссылка на ролик в Youtube
https://www.youtube.com/watch?v=fZSFNUT6iY8&t=4s&ab_channel=FazilBabu.
Речь идет о генеративной текстовой модели от OpenAI, обученной на репозитории GitHub. На конкретных примерах показана способность модели генерировать код на Python на основании заголовка функции и ее краткого описания.
А что будет, если такую модель удастся хорошо обучить на скриптах data scientists? Это вопрос для размышлений ))

Я в ИТ-сфере официально около 15 лет, и большую часть этого времени занимался проектированием систем. Очень часто в работе или при знакомстве с новыми коллегами возникают споры на профессиональные темы, одним из которых является ответ на вопрос Что такое система?. Каждый понимает это понятие по-своему, чаще всего опираясь на свой опыт и знания, полученные в ИТ-сфере; другие трактуют определения, взятые из интернета или учебников. И чаще всего эти споры не приводят к единому мнению, так и сейчас я не претендую на 100% принятия моих рассуждений.

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

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

Мы творцы, мы рисуем мир цифрами.

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

Я дам свое определение системы, которое мне больше всего нравится, и я везде использую его как основу. Оно, на мой взгляд, самое простое и понятное:

Система это набор объектов и правила взаимодействия между ними

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

Скорее всего, вы сейчас думаете а причем тут АИС, как это поможет нам? Разберем для начала, что такое информационная система. Рассмотрим наше определение и доработаем его:

Информационная (Система это набор объектов и правила взаимодействия между ними) => Информационная система это набор информационных объектов и правила информационного взаимодействия между ними

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

Разберем, что такое информационный объект и что такое информационное взаимодействие между этими объектами.

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

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

Чтобы стало понятнее, я выделю из вышеперечисленного примера 2 сущности:

• стулья объекты, определяющиеся наличием 4 ножек, основы, чтобы сидеть на нем, и спинки, на которую можно облокотиться. Возможно определение не точное, но оно подходит для понимания.
• предметы интерьера, на которые можно садиться это объекты, имеющие плоскую опору, на которую можно сесть и которые стоят на полу и возвышаются на высоту не менее 40 см и не более 1,5 метров.

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

Идем дальше Правила информационного взаимодействия. Рассмотрим для примера цветовую индикацию светофора как возможную систему, и выделим объекты рассматриваемой нами системы. Объектами системы являются три элемента света: красный, желтый, зеленый. Если человек не понимает предназначения светофора, то, наблюдая за ним, он все равно может увидеть связи между этими объектами. Красный свет загорается после зеленого, желтый после красного, зеленый после желтого; иногда встречаются светофоры, на которых зеленый загорается после одновременно горящих красного цвета и желтого и т.д. Добавляя в эту систему новые объекты автомобили, он выявит, что на зеленый все едут, на красный останавливаются перед светофором и т.д.

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

Еще один пример. Возьмем сущность Контрагент некоторое физическое или юридическое лицо, с которым осуществляется какая-либо деятельность; добавим к системе новую сущность Сделка. Если сущность Сделка связать с Контрагентом, то мы можем получить 2 новые сущности Покупатель и Поставщик.

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

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

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

Поэтому я бы определил автоматизированную информационную систему как проекцию информационной системы на технические средства

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

Аналитика рынка аналитиков:

• Сколько денег хотят аналитики, которые не ищут работу и как можно повлиять на их мотивацию. Отдельно рассказали про продуктовых аналитиков, маркетинговых и data scientists.
• Основные компетенции аналитиков разного уровня, от junior до head of analytics. Какие типовые задачи решают аналитики разных специализаций и какими инструментами пользуются.
• Критичные факторы, которые могут снижать стоимость аналитика без учета его специализации, а также, что может повышать ценность аналитика разной специализации.
• 7 причин, с помощью которых вы можете заинтересовать опытного эксперта, и факторы, снижающие привлекательность вашей вакансии.
• Могут ли кандидаты стоить для вас дешевле. Что делать, если у вас проблемы с наймом. Как кризис и пандемия повлияли на хантинг. Общие рекомендации современного хантинга.

Мы в New.HR подготовили очередную Аналитику для хантинга. В этот раз она получилась настолько объемной, что мы решили разделить ее на три статьи: про продакт менеджеров, про аналитиков (продуктовых, маркетинговых и data science) и про разработчиков.

Если хотите прочитать сразу всю, полную аналитику для хантинга, по всем профессиям сразу, скачивайте нашу презентацию (осторожнее, там 83 слайда!)

Про каких аналитиков мы тут говорим:

• Большинство аналитиков, про которых мы расскажем ниже, работает в средних, крупных или известных проектах, а также в успешных стартапах из IT и Диджитал-отрасли.
• Для этого обзора мы не оценивали кандидатов, которые сейчас работают в банках или крупных финансовых организациях. Отчасти потому что зарплаты в таких компаниях могут быть выше, чем в среднем в интернет-отрасли, и будут сильно смещать вилки. Также мы не учитывали аналитиков, работающих в не-дидижитал индустриях, несмотря на то что там могут быть уже сформированные крупные аналитические практики (промышленность, нефтедобыча, логистика, фарма, производство и т.д.).
• Мы не учитывали данные по аналитикам из маленьких проектов или стартапов, где такой специалист один и часто выполняет задачи из разных направлений аналитики. Либо, бывает, что функция аналитики размазана по другим профессиям/должностям.
• В основном мы оцениваем кандидатов из Москвы, либо тех, кто работает удаленно на московские компании.
• Наши респонденты готовы рассматривать предложения преимущественно в российских проектах.
• Большинство опытных аналитиков, которых мы оценивали, не находятся в активном поиске работы, но готовы рассмотреть интересные предложения. Такие кандидаты обычно готовы к работе, интересной с точки зрения профессионального роста и других перспектив.
• Мы не включали в нашу оценку тех кандидатов, которые ищут работу срочно, а значит, потенциально готовы соглашаться на то, что предлагает рынок, а не ждать действительно интересного им предложения.

Что может влиять на стоимость аналитика

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

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

3 ключевых причины, которые могут снижать стоимость аналитика вне зависимости от специализации

1. Если основной результат работы аналитика отчёты ради отчётов, а не ради развития продукта или бизнеса.
2. Большая часть работы аналитика не привязана к конкретным бизнес-метрикам, например, к деньгам, пользователям и проч.
3. Невладение критичным инструментарием. Когда незнание той или иной технологии, либо инструмента становится барьером для решения задачи.

4 фактора, которые могут повышать ценность аналитика вне зависимости от специализации

1. Общая универсальность специалиста. Например, аналитик владеет большим арсеналом аналитических инструментов, может решать широкий спектр задач, понимает их на уровне пользы для бизнеса.
2. Личный бренд. Статус эксперта в сообществе, преподавание, выступление на конференциях, хакатонах, профильные статьи.
3. Дополнительную ценность аналитикам может дать опыт работы в собственном стартапе. Такой опыт дает чёткий фокус на бизнес-целях, учит видеть физический смысл за цифрами, а не просто делать отчёты.
4. Способность генерить множество гипотез, в том числе неочевидных. Умение оперировать не только аналитическими факторами, но и экономическими, логистическими, политическими, эмоциональными и прочими. Некоторые руководители оценивают умение генерить гипотезы на собеседовании. Пример вопроса, который проверяет вашу способность генерить неочевидные гипотезы: Не работает интернет. Назовите все возможные причины, почему это случилось. Другой пример вопроса: Придумаете продуктовую метрику, объясните, чем она хороша. А теперь, расскажите, чем она плоха.

Специализации аналитиков и факторы, повышающие их ценность

Что может повышать ценность продуктового аналитика

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

Что может повышать ценность data scientists

• Опыт применения или уверенное знание классических алгоритмов машинного обучения (например: Линейная регрессия, Логистическая регрессия, LDA, Деревья решений, Бустинг, Байес, KNN и т.д.)
• Опыт работы с нейросетями
• Узкая специализация. Например, аналитик специализируется на применении NLP для оптимизации работы колл-центров или на построении рекомендательных систем и т.п.
• Способность выполнять роль Data Engineer для решения задачи. Например, самостоятельно собрать, очистить и подготовить данные для дальнейшей работы. А также опыт выкатки моделей в продакшен.
• Высокие результаты в профессиональных соревнованиях.

Что может повышать ценность маркетингового аналитика

• Опыт работы с мобайлом и, как следствие, знание систем мобильной аналитики (например: Mixpanel, Amplitude, Flurry)
• Знание математики, статистики, SQL
• Опыт проведения АБ тестов
• Наличие технического образования часто оценивается как плюс

Что может повышать ценность руководителя аналитики

• Наличие в портфолио завершённых проектов, которые принесли очевидную пользу для бизнеса.
• Опыт управления командами (от 15-30 человек и более).
• Управление различными направлениями аналитики (продуктовая, бизнес, операционная и проч.).
• Умение напрямую монетизировать данные. Например, продавать собственную аналитику сторонним компаниям. А также выстраивать партнёрские проекты с другими компаниями с использованием собственной аналитики. В этом случае мотивация руководителя может состоять не только из фиксированной части, но и иметь переменную часть (опционы, проценты от прибыли, бонусы и проч.).
• Известность руководителя в экспертном сообществе. Это даёт возможность проще и быстрее нанимать аналитиков в команду к такому руководителю.

Как схантить опытного аналитика?

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

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

Факторы, снижающие привлекательность вакансии

1. Noname компания, которая может и делает классный продукт, но об этом мало кто знает.
2. Отсутствие амбициозных планов по развитию и/или сформулированной бизнес-стратегии.
3. Микроменеджмент руководителя/фаундера. Например, может прийти и воткнуть в роадмап свои хотелки без аргументов и объяснений.
4. Отсутствие профессионального окружения, не у кого учиться.
5. Устаревший стек технологий и отсутствие возможности внедрения актуального стека.
6. Аналитика в компании не влияет на принятие бизнес-решений.
7. Отсутствие возможности вертикального и/или профессионального роста.
8. Отсутствие ресурсов, а также возможности их получить.

7 причин, которые повышают интерес опытного аналитика к вашей вакансии

1. HR-бренд компании/продукта. Хорошо, если ваша компания известна на рынке своей аналитической культурой, дата-дривен подходом, вы рассказываете о том, как аналитика в вашей компании влияет на бизнес-решения и т.д.
2. Сфера применения вашего продукта. Многие аналитики сейчас ориентированы на социально-значимые и/или наукоёмкие проекты. Часть аналитиков даже готова снижать свои зарплатные ожидания, если есть возможность поучаствовать в проекте, оказывающим реальное влияние на жизнь людей. Например, это могут быть проекты, связанные с медициной, биотехнологиями, агротехом, обучением.
3. Возможность профессионального роста. Есть общее правило: вам может быть непросто заинтересовать опытного профессионала, если вы предлагаете те же задачи, которые он успешно решает прямо сейчас. Поэтому мы рекомендуем рассматривать кандидатов из другого продукта или сферы, а также тех кандидатов, для которых ваши задачи будут вызовом и профессиональным ростом.
4. Международный проект. Работа на других рынках интересна многим кандидатам, так как даёт возможность получить уникальный опыт.
5. Работа в команде опытных и/или известных рынку аналитиков. Для многих аналитиков важна работа в профессиональной среде, возможность учиться у команды. Поэтому если у вас есть в команде опытные аналитики, обязательно рассказывайте про них кандидату. Большим плюсом будет наличие в команде аналитиков-амбассадоров, известных на рынке персоналий, чьим именем легко нанимать людей в команду.
6. Удалённая работа. Мы видим растущий интерес к удалённой работе. Уже сейчас на рынке есть кандидаты, которые готовы рассматривать только remote-предложения.
7. Деньги. Важно отметить, что этот фактор практически никогда не является первоочерёдным критерием выбора нового места. Аналитики предпочитают выбирать новые проекты по совокупности вышеперечисленных причин. Но деньги это гигиенический фактор. И если ваш бюджет ниже рынка, то, скорее всего, вы не сможете заинтересовать опытных и востребованных профессионалов.

Что у нас есть еще интересного?

• Канал с вакансиями для аналитиков и дата сайнтистов. Общая аудитория 3 500 человек. И еще 24 канала с вакансиями для разных профессий в ИТ и Диджитал (общая аудитория 65+ тыс. человек).
• Geekjob.ru сервис анонимного поиска работы для экспертов из ИТ и Диджитал индустрии.
• Podcast.New.HR подкаст про карьеру и найм в ИТ и Диджитал.
• Repository.New.HR статьи про карьеру и найм в ИТ и Диджитал

Откуда мы берем данные?

Мы рассказываем только о том, в чём сами хорошо разбираемся:

• New.HR хантинговое агентство, мы работаем на узком рынке: только IT и Интернет-компании.
• Наша специализация: поиск специалистов конкретных профессий разработчики, тестировщики, девопсы, продакты, проджекты, аналитики, дата сайнтисты, перфоманс маркетологи и продуктовые дизайнеры
• Такой узкий фокус позволяет нам постоянно работать с кандидатами из конкретных профессий, а значит, во всех подробностях знать, чего они хотят, сколько стоят, и какие тренды появляются в каждой из специальностей.
• Каждый год мы готовим Аналитику для хантинга, основываясь на тех данных, которые мы напрямую получаем у кандидатов, с которыми работаем.

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

Какую информацию мы используем

• Обобщенные данные. Для этого материала мы обобщали данные, которые напрямую получили от кандидатов из разных компаний. Да, они все работают в IT и в Диджитал проектах. Большая часть продактов работает в Москве или на московские компании. Но мы не сегментировали наших респондентов по отраслям или, например, по размеру компании. Если вам хочется получить больше конкретики для своей сферы, мы можем помочь вам собрать данные, которые наиболее релевантны именно вашему запросу. Пишите: contact@new.hr
• Кандидаты не в активном поиске работы. Мы основывали наш анализ на оценке тех респондентов, которые либо совсем не ищут работу, либо ищут, но пассивно. Такие кандидаты, как правило, не готовы снижать свои ЗП ожидания, а ищут проект, который будет интересен не только уровнем дохода, но и другими критериями, например, задачами.
• Бэкграунд. В основном мы оценивали кандидатов с бэкграундом из приличных продуктовых компаний, с хорошим опытом и подтверждённым трекшеном проектов. И не учитывали тех, кто работает в не в продуктовых IT и интернет-компаниях.
• Постоянство. Мы не брали в расчёт прыгунов, которые работают менее 1 года на одном месте.
• Российские проекты. Учтены ЗП ожидания только тех кандидатов, которые готовы рассматривать работу в российских проектах, а не нацелены на релокейт.
• Также мы не учитывали зарплатные ожидания тех кандидатов, которые активно и срочно ищут работу, а значит, потенциально готовы соглашаться на то, что предлагает рынок, а не ждать действительно интересного им предложения.

Могут ли кандидаты стоить для вас дешевле?

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

• У вас классный и прокачанный HR-бренд
• Ваш продукт крут и активно развивается
• В команде есть известные эксперты
• Вы предлагаете хорошие рыночные условия
• У вас выстроена адекватная система оценки
• Вы готовы нанимать на вырост
• Вы быстро принимаете решения о найме
• Даёте качественный фидбек при отказе
• Делаете много хорошего для сотрудников
• И много полезного для рынка
• Хорошо расстаётесь с теми, кого увольняете

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

Почему мне столько не платят?

Если вы смотрите на наши цифры, и думаете: Почему я получаю меньше? специально для вас мы описали, что именно может повысить вашу стоимость. Несмотря на то что каждый кейс стоит оценивать отдельно, мы попробовали выделить несколько типичных паттернов: что ценится в опыте, а что может снижать ценность.

Хантинг в период кризиса и пандемии

• Компании собираются выжить после карантина, а делать это лучше всего с сильной командой, поэтому опытные и крутые эксперты под сокращения не попадают.
• Кандидатов на рынке стало больше в основном за счёт джунов и миддлов.
• Удалёнка больше не конкурентный фактор. Сейчас на удалёнку перешли не только российские IT-компании, но и международные. А значит в ближайшее время рынок может ждать увеличение конкуренции за кандидатов.
• Никто не готов снижать свои зарплатные ожидания, и в будущем ждать этого не стоит. Есть предположение, что после пандемии конкуренция за опытных и сильных кандидатов только возрастёт (смотри предыдущий пункт).
• Пассивных кандидатов хантить стало труднее (это те, кто готовы рассмотреть вакансию, но только если она действительно им интересна). Некоторые кандидаты отказываются рассматривать новые предложения в общей ситуации неопределенности и не готовы рисковать переходом на новое место, если в текущей компании все хорошо.
• Часть кандидатов не готова рисковать и переходить в потенциально менее стабильные проекты. Так что если у вас стартап без подтверждённого финансирования, вам может потребоваться больше времени и сил, чтобы заинтересовать опытного кандидата.
• Если у вас серая зарплата, если вы оформляете по ИП или выплачиваете небольшой фиксированный оклад, а всё остальное оформляете премией, привлечь зрелых кандидатов вам будет сложнее. Они хотят работать с гарантией, что их не уволят в любой момент или перестанут платить часть денег.
• Сильно вырос интерес к релокейту по сравнению с прошлым годом. Опытные кандидаты все чаще интересуются возможностью поработать с зарубежными проектами. Все больше иностранных компаний предлагают удаленку, а часть релокейт после открытия границ. На рынке существует достаточно большое количество кандидатов, которые готовы рассматривать предложения только с учетом международного проекта и заработной платы в валюте.

Что делать, если у вас есть проблемы с наймом?

• Отталкивайтесь от предложений рынка, перестаньте мечтать об идеальном кандидате.
• Обязательно пробуйте смотреть кандидатов с экспертизой ниже, чем вы хотите в идеале. Очень сложно привлечь кандидата, который не ищет работу и занимается тем же, что вы ему предлагаете. Обычно такие люди интересуются профессиональным ростом, поэтому всегда имеет смысл подумать, чем ваша задача привлекательна для конкретного кандидата. Далее мы рассказываем общие вещи, которые могут заинтересовать специалистов, но лучше всего отталкиваться от личности и мотивации каждого конкретного человека.
• Удалёнка маст хэв для всё большего количества кандидатов. Многие IT и Интернет-бизнесы уже научились работать удалённо (спасибо, самоизоляция), а некоторые даже выстроили эффективный асинхронный процесс работы. Так что если у вас нет удалёнки, велик риск, что вы сможете привлечь значительно меньшее количество заинтересованных кандидатов.
• Учитесь нанимать джунов. Их приток в индустрию растёт (здесь спасибо буму онлайн-образования). К сожалению, многие работодатели всё ещё скептически относятся к найму джунов. Кстати, мы в New.HR планируем в июле серию лекций на тему "Как нанимать джунов и не облажаться". Спикерами будут нанимающие менеджеры, которые видят ценность в найме джунов, они расскажут про свой опыт и ответят на вопросы. Приходите!
• При работе с кандидатом в активном поиске крайне важна скорость принятия решения. Опытные и адекватные специалисты весьма востребованы, и если они начинают активно искать работу, то уходят быстрее, чем вы скажете Мы вам перезвоним. Так что если хотите отложить кандидата на недельку, а потом к нему вернуться, есть риск, что возвращаться уже будет не к кому, ведь он получит несколько интересных офферов.
• Сокращайте этапы оценки (постарайтесь уложиться в 2-3 этапа).
• Если вы претендуете на опытных экспертов, постарайтесь обойтись без тестового (особенно при найме опытных кандидатов senior уровня). Мотивация делать тестовое есть только у junior или тех, кто мечтает о работе в вашей компании.
• Учитесь быстро принимать решения о кандидате. Идеальный срок не более суток. Терпимо 2-3 суток. Особенно важна скорость, если кандидат рассматривает и другие предложения, кроме вашего.
• Постарайтесь формулировать развернутый фидбек. Это выделит вас на фоне множества собеседований, где кандидат не получил никакого фидбека вовсе.
• Анализируйте причины отказов на всех этапах. Обращайте внимание на типичные паттерны в отказах и корректируйтесь, корректируйтесь, корректируйтесь.
• Помните, что ваша задача решить задачу бизнеса с помощью найма подходящего кандидата, а не провести месяцы и годы в поиске того самого, идеально подходящего по всем фронтам единорога.

Общие рекомендации современного хантинга

• Из-за высокого спроса опытные кандидаты практически не успевают выходить на открытый рынок и большинство экспертов в лучшем случае находятся в пассивном поиске.
• Стоит быть готовым к долгим срокам закрытия вакансии, а также к долгому процессу найма.
• В случае если кандидат не находится в активном поиске, стоит быть готовым к тому, что вашу вакансию будут рассматривать долго. Уже на этапе знакомства вам потребуется снабжать кандидата большим количеством разноплановой информации о вас, вашем проекте, задачах и перспективах. Словом, всем тем, что может вам помочь заинтересовать пассивного кандидата.
• Востребованные эксперты, скорее всего, будут смотреть и другие предложения для оценки привлекательности вашего оффера.
• Также стоит учитывать потенциально длительный срок перехода на новое место. Например, кандидату может потребоваться время для передачи дел или завершения начатого проекта.

Дисклеймер

Эта статья часть большой аналитики для хантинга продактов, аналитиков (продуктовых, маркетинговых и data scientists), разработчиков и CTO.

Вы можете скачать полный файл исследования по ссылке.

Или прочитать еще две статьи:

• Аналитика для хантинга продакт менеджеров
• Аналитика для хантинга разработчиков и CTO