Rivalry2

HTML Diff

0 added 0 removed

Original 2026-01-01

Modified 2026-02-26

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

2 Сегодня мы научимся создавать витрины и работать с ними. Эти навыки помогут построить аналитические отчеты и выявить причины проблем в работе сервисов компании. Например, построить отчет о выручке магазинов сети за период времени и найти самые прибыльные магазины.

3 <h2>Базы данных систем</h2>

4 Представим, что мы ведем заметки о своей жизни и работе в разных местах. Для учета доходов и расходов мы используем Google-таблицы или специальные приложения. Трекинг рабочих задач производим в Trello. А в Notion записываем мысли о событиях из жизни и ведем дневник. Каждый из этих инструментов удобен для определенных задач.

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

6 Обычно базы данных подсистем компании хранятся в нормализованном виде. Покажем, как это выглядит на примере.

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

8 Purchases

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

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

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

12 Чтобы не столкнуться с этой проблемой, базы данных таких систем нормализуют - разносят разные объекты по разным таблицам. В таком случае предстоит изменять минимальное количество строк.

13 Так выглядят нормализованные таблицы:

14 Shops

15 Customers

16 Products

17 Discounts

18 CustomersDiscounts

19 Purchases

20 Датасет доступен на DB Fiddle по<a>этой</a>ссылке.

21 Здесь представлены следующие таблицы:

22 <ul><li>Shops - информация о магазинах</li>

23 <li>Customers - информация о клиентах</li>

24 <li>Products - информация о товарах</li>

25 <li>Discounts - информация о скидках</li>

26 <li>CustomersDiscounts - таблица, которая связывает клиентов и их персональные скидки</li>

27 <li>Purchases - таблица покупок</li>

28 </ul>У каждой из этих таблиц есть поле ID - это уникальный ключ. Также одна таблица содержит в себе информацию только об одной сущности, например: Shops - о магазинах, Customers - о клиентах.

29 Каждый объект, например, магазин, клиент, представлены одной строкой без дублирования. При этом таблица Purchases может содержать одинаковые ID клиентов, товаров или магазинов, так как клиенты покупают товары в разных магазинах в разное время или одни и те же товары.

30 Если заменить адрес магазина, мы затронем одну строчку в таблице с магазинами. Остальные таблицы останутся без изменений.

31 CustomersDiscounts - это отдельный вид таблиц. Они содержат в себе только свой ID и ID других таблиц, что позволяет связать эти таблицы. Покупателей и их индивидуальные скидки разносят по разным таблицам.

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

33 Такие базы данных называютсяOLTPилиOnline Transaction Processing. Online - потому, что базы данных обновляются в реальном времени. Транзакции идут большим потоком, поэтому, чтобы достичь минимального времени отклика, важно вносить изменения в малое количество строк.

34 <h2>Витрины данных</h2>

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

36 Пример витрины данных - это таблица Purchases. Посмотрим на нее еще раз:

37 Purchases

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

39 Напомним, что обычно в компаниях базы данных для каждого отдела разные. Аналитики обычно не анализируют все данные сразу, поэтому витрины - это срез во всех данных компании. Их еще называютData Mart.

40 Представим общую схему потока данных между базами разных отделов и витринами:

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

42 Витрины данных могут быть базами данных или таблицами в Google Sheets, это не меняет их сути.

43 Теперь разберемся, как работать с витриной. Для этого создадим ее.

44 <h2>Создаем витрину</h2>

45 Откроем базу данных Purchases. Мы увидим в ней таблицы, которые были в примерах урока:

46 <ul><li>Customers</li>

47 <li>CustomersDiscounts</li>

48 <li>Discounts</li>

49 <li>Products</li>

50 <li>Purchases</li>

51 <li>Shops</li>

52 </ul>Посмотрим на таблицу Purchases:

53 purchases.purchases

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

55 <ul><li>date - дата покупки</li>

56 <li>full_name - ФИО покупателя</li>

57 <li>shop_address - адрес магазина</li>

58 <li>product_name - наименование товара</li>

59 <li>price - исходная цена товара в рублях</li>

60 <li>discount - скидка в долях от единицы</li>

61 <li>discounted_price - цена товара после индивидуальной скидки покупателю</li>

62 </ul>Таблица Purchases будет основной связующей с другими таблицами. Друг с другом связаны только таблицы customers и discounts через промежуточную таблицу customersdiscounts.

63 Чтобы создать витрину, нужно объединить нормализованные таблицы джойнами. В нашем случае в таблицах нет NULL-значений, поэтому мы можем использовать любой вид джойнов, например, INNER JOIN. INNER JOIN - это разновидность джойна, которая связывает таблицы по ненулевым ключам. Если ключ какой-то строки имеет значение NULL, эта строка не попадает в итоговую таблицу.

64 Если в ключах есть NULL-значения, а мы все равно хотим включить эти строки в итоговую таблицу, мы воспользуемся LEFT JOIN, RIGHT JOIN или FULL OUTER JOIN. В таком случае мы выберем джойн исходя из того, включать ли нулевые ключи только левой таблицы, правой или обеих.

65 Теперь нужно понять, откуда взять нужные нам поля. Например, date - это дата покупки, поэтому возьмем ее из таблицы Purchases. А full_name есть в таблице customers. Попробуем составить селект, чтобы получить два этих поля:

66 В customers id покупателя называется id, так как эта таблица про покупателей, и id покупателя в ней - это уникальный ключ. А таблица Purchases о покупках. Покупатели в ней могут повторяться, поэтому поле называется customer_id.

67 shop_address - это адрес магазина, то есть поле address в таблице Shops. Добавим джойн с этой таблицей:

68 product_name и price мы возьмем из таблицы Products:

69 Остались поля discount и discounted_price. Мы помним, что таблицы Discount и Customers связаны через CustomersDiscounts. Поэтому мы объединим скидки с покупателями через дополнительную таблицу. В итоге посчитаем discounted_price по следующей формуле:

70 Итоговый запрос будет выглядеть так:

71 data_mart

72 <a>Ссылка на таблицы</a>

73 <h2>Работаем с витриной</h2>

74 Допустим, нам нужно посчитать суммарную выручку по каждому из магазинов. Для этого используем агрегирующую функцию SUM с группировкой по магазинам. Функция SUM позволяет посчитать сумму всех значений покупок в каждой из групп. Группы формируются с помощью GROUP BY, и наши группы - это каждый отдельный магазин.

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

76 В запросе выше мы объединили все таблицы в одну и записали нашу витрину в новую таблицу.

77 Теперь мы можем составить запрос к таблице витрины:

78 Итоговая таблица выглядит так:

79 purchases.purchasesDataMart

80 <a>Ссылка на таблицу</a>

81 В этой таблице мы видим суммарную выручку по каждому из магазинов в колонке total_earnings.

82 Мы создали витрину данных и записали ее в таблицу purchasesDataMart, а также создали свой первый отчет по витрине.

83 <h2>Выводы</h2>

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

85 Мы научились создавать витрину. Чтобы превратить таблицы в базе в витрину, нужно объединить информативные поля из разных таблиц по ключам. Главный ключ в каждой из таблиц - это уникальное поле. Обычно он называется id.

86 Когда у нас уже есть витрина, мы можем строить по ней отчеты и выявлять закономерности.

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