Как самостоятельно писать вьюхи независимо от сложности проекта

Зачем нужно знать как писать вьюхи?

— Можно быстро править вьюхи самому без ожидания разработчиков

— Нужно знать и разбираться в своей вьюхе, чтобы знать возможности для создания новых отчетов.

Зачем нужна вьюха? Почему без нее нельзя?

— Чтобы готовить данные для отчета об эффективности, онлайн-отчета. 

Без нее отчет будет на порядок дольше собирать и обрабатывать данные БД.

С чего начать изучение SQL

Теория — Мартин Граббер «Введение в SQL», Электронный учебник sql-tutorial.ru

Практика — http://sql-ex.ru/

Как написать вьюху «до целей»

Как писать вьюхи разберем, начиная с самой простой версии до целей.

Чтобы видеть данные в отчете об эффективности, нужно отдать сводной таблице данные из БД (пример ниже)

Дата	Город	Источник	Клики	Цель 60 сек	Калькулятор вкладов
2015-07-13	Питер	Яндекс.Директ	100	30	25

Выглядит просто, но чтобы подготовить такую таблицу нужно склеить несколько разрозненных таблиц в БД

Источники, Клики, Цели и Продажи хранятся в отдельных таблицах.

Алгоритм сбора вьюхи до целей

Microsoft SQL Management Studio — Программа для windows. Удобна для написания вьюх.

Базовые вьюхи почти везде одинаковые, отличаются только количеством и названием целей. Тут цепочка простая Dates -> Goals -> PivotRow -> Pivot. 

Их мы обычно создаем при разворачивании базы, после заполнения таблицы Goals.

1. Начать нужно с вьюхи с датами Dates. В ней хранятся все даты из GA, площадок.
   Суть: собрать все задействованные даты и не брать лишние, в которых не было открутки рекламы или отработки целей.
   После подключения к серверу базами данных,  жмем на кнопку New Query
   
   Выбираем нужную БД с проектом в Studio
   
   
   Далее в открывшемся окне
   
   пишем код ниже.
   CREATE view [core].[Dates]
   as
   select distinct [Date]
   from (select [Date] from core.SourceData
   union all
   select [Date] from core.GaData)
   as dates
   Жмем Execute
   
   Если все прошло успешно, то вы увидите сообщение ниже
   
   Ищем в проводнике созданную вьюху
   
   Скорее всего, созданная вьюха появится не сразу. Чтобы увидеть ее нажмите на обновление в окне проводника
   
2. Создаем новую вьюху Core.Goals. Зачем нужна отдельная вьюха для целей?
   Так быстрее.
   
   Разница Cross и Left Join рассмотрены на примере написания базовой вьюхи ниже.
   Отметим способ подключения целей в две отдельные строки.
   Coalesce — выбирает первое ненулевое значение
   CREATE VIEW [core].[Goals] AS
   SELECT
   AtomId = A.Id, A.[Version], D.[Date],
   Clicks = Coalesce(Ga1.Clicks, Ga2.Clicks),
   Goals1 = Ga1.Goals,
   Goals2 = Ga2.Goals
   FROM [core].[Dates] as D
   CROSS JOIN [core].[Atoms] as A
   LEFT JOIN [core].[GaData] as Ga1 on D.[Date] = Ga1.[Date] and Ga1.AtomId = A.Id
   and Ga1.[Version] = A.[Version] and Ga1.GaGoalId = 1
   LEFT JOIN [core].[GaData] as Ga2 on D.[Date] = Ga2.[Date] and Ga2.AtomId = A.Id
   and Ga2.[Version] = A.[Version] and Ga2.GaGoalId = 2
3. Создаем новую базовую вьюху PivotRaw
   Самая важная вьюха. В ней вся магия. Она большая, ее код разберем отдельно ниже.
4. Создаем новую финальную вьюху Pivot, которую подключим к автоотчету
   В ней мы заменяем числа на текст, чтобы видеть в качестве источника не цифру 3, а «Гугл поиск».
5. Подключаем вьюху в Excel, смотрим как работает в сводной таблице.

Создание базовой вьюхи Raw 

Первый шаг
Прописываем команду «Создать вьюху с названием Core.PivotRaw»
CREATE VIEW [core].[PivotRaw] AS

Далее команда SELECT с запросом желаемых полей вьюхи на выходе.

SELECT 

SourceId,

CampaignId,

CityId,

D.[Date],

Ga.Goals1,

Sd.Clicks,

Sd.Shows,

Sd.Budget

Теперь указываем таблицы из которых хотим получить поля выше.

Начинаем с таблицы с датами, она есть во вьюхе CORE.DATES.

FROM [core].[Dates] AS D

Результат:

Date

2015-05-01

2015-05-02

Это основа. К ней будем подключать клики, города, источники и т.д.

Второй шаг

Далее подключаем таблицу Core.Atoms с помощью команды CROSS JOIN.

Join сообщает что нужно две таблицы объединить, а CROSS — как это сделать, а именно создать таблицу со всеми возможными сочетаниями строк двух таблиц.
Т.е. если в одной таблице 2 даты, а во второй 4 атома. Получим 8 строк.

CROSS JOIN [core].[Atoms] AS A

Результат:

Date	Id (атома)	SourceId	CityId
2015-03-02	1101	1	2

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

Трейтий шаг
Далее подключаем таблицу Core.SourceData командой LEFT JOIN.

При LEFT JOIN нет эффекта перемножения строк таблиц, тут остаются в полном составе все строки таблицы слева от оператора LEFT и к ним добавляются строки из таблицы справа от LEFT, если они удовлетворяют условиям оператора ON.

SourceData добавит инфу о кликах, показах, бюджете атома за конкретную дату от площадки.

LEFT JOIN [core].[SourceData] AS Sd ON Sd.AtomId = A.Id and Sd.[Version] = A.[Version] and Sd.[Date] = D.[Date]

Результат:

Date	Id (атома)	SourceId	CityId	Clicks	Shows	Budget
2015-03-02	1101	1	2	100	1000	10000

Четвертый шаг

Далее с помощью команды LEFT JOIN подключаем таблицу с целями GA

LEFT JOIN [core].[Goals] AS Ga ON Ga.AtomId = A.Id and Ga.[Version] = A.[Version] and Ga.[Date] = D.[Date]

Результат

Date	Id (атома)	SourceId	CityId	Clicks	Shows	Budget	Goals1	Goals2
2015-03-02	1101	1	2	100	1000	10000	300	100

Пятый шаг

Осталось исправить источники и города c цифр на нормальные читаемые значения.

Поэтому пишем финальную вьюху Core.Pivot

CREATE VIEW [core].[Pivot]

AS

SELECT

Ca.Name AS [Кампания], 

So.Name AS [Площадка], 

Ci.Name AS [Город],

CAST([Date] AS DATETIME) AS [Дата],

Goals1 AS [60 секунд],

Clicks AS [Клики]

FROM [core].[PivotRaw]

INNER JOIN [core].[Campaigns] AS Ca ON CampaignId = Ca.Id

INNER JOIN [core].[Sources] AS So ON SourceId = So.Id

INNER JOIN [core].[Cities] AS Ci ON CityId = Ci.Id

Результат:

Дата	Площадка	Город	Клики	Показы	Бюджет	60 секунд	Калькулятор вкладов
2015-03-02	Яндекс.Директ	Питер	100	1000	10000	300	100

Как добавить во вьюху продажи/коммуникации

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

Таблица с продажами должна содержать id атома, версию атома, дату и сумму продажи.

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

В этом случае нужна доп. обработка в самой вьюхе

Обработать нужно, чтобы «схлопнуть» продажи по дате, атому и версии атома.

Поэтому сразу после команды создания вьюхи пишем след. код (на примере Аквафор)

CREATE VIEW [core].[PivotRaw]

AS

with MoneyAll AS

(

SELECT 

AtomId, [Version], [Date], 

[Sum] = Sum(Coalesce(SumGoods, 0)), 

[Count] = Count(*)

FROM client.Orders

GROUP BY AtomId, [Version], [Date]

)

В SELECT добавляем след. код

Ma.[Count] as OrderCount,

Ma.[Sum] as OrderSum,

В FROM в конце добавляем подключение обработанную таблицу с продажами

LEFT JOIN MoneyAll AS Ma on Ma .AtomId = A.Id and Ma.[Version] = A.[Version] and Ma.[Date] = D.[Date]

Важно не забыть в финальной вьюхе core.Pivot также добавить в SELECT обработку

OrderCount as [Счета],

OrderSum as [Доход],

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

Как добавить валюту (Банки)

Валюту вклада следует грузить в таблицу Orders (либо Deposits для банков) вместе с суммой вклада в числовом виде (id).

В базовой вьюхе PivotRaw нужно прописать в SELECT

Ma.CurrencyId as Currency

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

Т.е. добавляем в финальную вьюху в FROM код ниже

LEFT JOIN client.Currencies Cur ON Currency = Cur.Id;

Как добавить продажи с источниками из calltouch (Юсталь)

Неважно calltouch или промокоды, главное чтобы все правильно попадало в таблицу Orders.

Если есть желание видеть в отчете источник продажи (calltouch или форма) на сайте, то тут действовать можно по аналогии с валютами банка. Тот же алгоритм.

Как добавить подпроекты (отделы в Аквафоре/факультеты в Унике)

Тут похожая история как валютами банка, но не совсем.

Придется создать и поддерживать дополнительную таблицу с соответствием
«Идентификатор атома» — «идентификатор подпроекта».

Так нужно, чтобы на уровне атома делить ВСЁ, клики, показы, цели, транзакции, коммуникации по подпроектам.

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

В плане вьюхи все так же как с валютой банка.

Как добавить оффлайн продажи

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

Как добавить пополнения за 30 дней на дату оформления вклада (Евразийский/МЗ)

В Евразийском банке реализовали полезный отчет по пополнениям за первые 30 дней на дату оформления вклада.

Тут критично иметь в таблице с продажами две даты:
— дату оформления вклада (выставления счета/коммуникации) и
— дату продажи

Нужно создать две доп. вьюшки

CREATE View [core].[NewDepositsHelp] AS

SELECT 

[AtomId],

[Version],

[Date],

[CurrencyCode],

[Sum],

CASE WHEN [Agreement] = N’0′ THEN [Date]

ELSE MIN([Date]) OVER (PARTITION BY Agreement) END AS [FirstDate]

FROM [client].[Deposits]

В этой вьюхе выделяется строк

CASE WHEN [Agreement] = N’0′ THEN [Date]

ELSE MIN([Date]) OVER (PARTITION BY Agreement) END AS [FirstDate]

Она означает, что в таблице NewDepositsHelp добавляем поле FirstDate с условием:

ЕСЛИ 

поле Agreement (идентификатор договора) равен 0

ТО
в колонке ставим дату пополнения из текущей строки

ИНАЧЕ

ставим минимальную дату среди всех строк с одинаковым значением Agreement

Далее создаем вьюху 

CREATE View [core].[DepositsAdditions30] AS

SELECT

[AtomId],

[Version],

[Date],

[CurrencyCode],

COUNT(*) [Count],

SUM([Sum]) [Sum]

FROM [core].[NewDepositsHelp]

WHERE DATEDIFF(DD, [FirstDate], [Date]) BETWEEN 1 AND 30

GROUP BY [AtomId], [Version], [Date], [CurrencyCode]

В этой вьюхе самое важное описано в строке

WHERE DATEDIFF(DD, [FirstDate], [Date]) BETWEEN 1 AND 30

В ней просто формируется таблица с пополнениями за 30 дней на дату оформления вклада.

Далее эту таблицу подключаем в базовой вьюхе так же как валюты вклада.

[Сергей Полшков]

Небольшое пояснение* (сложность одна звездочка)

Нестандартные задачи влекут отход от стандартных вьюх.

Нестандартные задачи включают в себя:

1. Добавление в отчет новых сущностей: вклады, звонки, факультеты, валюты и т.д. 
2. Фильтрацию и/или изменение данных: удаление дубликатов и различные костыли.

Два основных метода решения нестандартных задач:

1. Расширение старых вьюх, например, добавили туда звонки
2. Создание промежуточных вьюх, которые добавляют и/или изменяют данные

Часто оба метода работают, например, в MZ у нас все в одном представлении, но пришлость использовать CTE (Common table extensions — фактичекси временные таблицы для хранения промежуточных данных):

CREATE VIEW [core].[PivotRaw2]

AS

with InitialCommunications as ( 

select AtomId, [Version], [Date], [Count] = Count(*) 

from mz.Transactions  where [TransactionType] in (N’п’,N’п/п’) 

group by AtomId, Version, Date), 

UniqueCommunications as ( 

select AtomId, [Version], [Date], [Count] = Count(*), [Sum] = Sum([Sum]) 

from (select AtomId, [Version], [Date] = DateAdv, Client, Doctor, [Sum] = Sum([Sum]) 

from mz.Transactions 

group by AtomId, Version, DateAdv, Client, Doctor) as Comms 

group by AtomId, [Version], [Date])

…

Здесь InitialCommunications и UniqueCommunications фактически временные таблицы, которые хранять начальные и уникальные коммуникации, мы их используем в представлении потом так же как и обычные таблицы.

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

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

CREATE VIEW [client].[AtomsLookup]

AS

SELECT

SourceId = A1.SourceId, 

CampaignId = A1.CampaignId,

CityId = A1.CityId,

Id = A2.Id,

[Version] = A2.[Version]

FROM core.Atoms A1 INNER JOIN core.Atoms A2

ON A1.[Version] = A2.[Version] AND

(A1.Id = A2.Id OR 

A1.Id = 3113 AND A2.Id IN (1101, 1301, 2101, 2301) OR

A1.Id = 3114 AND A2.Id IN (1102, 1302, 2102, 2302) OR

A1.Id = 3115 AND A2.Id IN (1203, 2203, 1204, 1404, 2204, 2404, 2405, 2406))

GO

Здесь мы хотим чтобы к одним атомам были добавлены данные из других атомов (к 3113 данные из 1101, 1301, 2101, 2301). Для нашего атома 3113 в результате получим 5 строк с id атомов (его и 4 дополнительных). Дальше во вьюхах мы будем использовать AtomsLookup вместо исходной таблицы Atoms. Это пример “костыля”.

Что удобней: одна большая вьюха или много маленьких? Исторически делаем по-разному, если представлений много, то нужно понимать, как они связаны. Но каждое маленькое представление легче читать, и можно вытащить его данные отдельно и посмотреть. Если все в одной вьюхе — понятно, что откуда, но может быть тяжело читать. И сложнее отлаживать.

Есть одно отличие, когда вьюху пишут программисты и джедаи. Программисты используют систему контроля версий. Это когда можно увидеть кто, что, когда и как поменял, всю историю файла. SQL такого не поддерживает. Но в течение недели можно откатить базу до ежедневного бэкапа. Плохое решение, но лучше никакого. Откатить базу до бэкапа — это fail. Когда нельзя откатить, а надо — epic fail:)

Крайний случай** (сложность две звездочки)

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