T-SQL | Stored Procedures | "Triangle Report" | Пример реализации

Пример запроса к базе данных, применимого для построения аналитического отчета о лояльности клиентов:

Loyalty Triangle Report

 

См. также What is a Claims Triangle Report? - Insurance IT blog

 

Реализация запроса вручную на базе минимального фрагмента данных

Реализация запроса с помощью хранимых процедур и циклов – для обработки большого объема данных (n млн. записей)

 

Суть запроса и его реализация вручную

 

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

 

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

 

Другой пример: есть магазин. При покупке товара более, чем на n рублей, выдаётся  дисконтная карта, регистрируется ID карты в базе данных. Если клиент заглянет в магазин снова и решит что-нибудь купить, предъявив карту, по нему зарегистрируется вторая продажа. И так далее.

Есть возможность ответить на вопрос: сколько из новых покупателей в n-м году или месяце пришли снова? Сколько из них пришли снова не один раз? Как покупатели отреагировали на акцию?

 

Анализ лояльности клиентов на примере оказания услуг по образованию.

Стандартные условия:

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

Понятное дело, что есть слушатели, которые расторгают договор и перестают посещать курсы.

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

 

Информация о первых и очередных оплатах договора поступает в таблицы базы данных. Пусть это БД, состоящая из двух справочников (CLIENTS и SERVICES) и одной таблицы – с фактами оплаты услуг (SERVICE_FACTS).

 

Таблица CLIENTS (список клиентов):

 

ID,

Имя

 

 

Таблица SERVICES (перечень предоставляемых услуг):

 

ID,

Наименование,

Длительность (в месяцах),

Стоимость за курс

 

 

 

SERVICE_FACTS (факты оплаты услуг).

Фрагмент данных по услуге "Китайский для начинающих", с началом курса в 1999-ом году:

 

ID услуги,

ID клиента,

Дата начала оказания услуги

Дата оплаты услуги

Сумма оплаты

 

 

Согласно таблице фактов, клиент 11001 (Петров Василий) купил курсы китайского, с началом 01-го января 1999-го; проплатил из них первый месяц, второй, третий и четвертый, дальше фактов нет. Клиент 11002 купил тот же курс, но с 15-го января и проплатил три месяца. В общем виде, наблюдаем, что только клиент 11004, купивший курсы с февраля, полностью проплатил и прослушал договорной год.

 

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

 

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

 

Поименуем шапки осей координат, согласно известным данным: начало оказания услуги – январь 1999, длительность оказания услуги – 12 месяцев:

 

 

Заполним вручную вспомагательную таблицу с координатами данных по фактам оплаты услуги: по оси X будет координата от 1 до 12 как месяц начала оказания услуги; по оси Y будет координата от 1 до 12 как порядковый номер месяца  оплаты:

 

 

Просуммируем таблицу по количеству клиентов, приходящихся на каждую координату Х и на каждую Y.

Например:

 

1:1

– 3 клиента

1:2

– 3 клиента

1:3

– 3 клиента

1:4

– 1 клиент

1:5–1:12

– никого (0)

2:1–2:11

– 1 клиент

3:1

– 3 клиента

3:2

– 2 клиента

3:3

– 1 клиент

3:4-3:10

– никого (0)

 

 

Заполним полученными результатами таблицу координат:

 

 

3 продажи января 1999-го не увенчались успехом: плательщики продержались только 3 месяца (неоправданные ожидания или плохой сервис оказания услуг?).

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

Продажи марта 1999-го неудачные – 3 ученика не продержались и трех месяцев.

 

Вот такой была бы идеальная картина лояльности на протяжении календарного года (январь 1999 – декабрь 1999):

 

 

 

Реализация подобного запроса с помощью циклов

 

План реализации состоит из построения рабочей таблицы, содержащей количество записей таблицы фактов (SERVICE_FACTS) по координатам X, Y и построения итогового отчета (куба) на базе данных рабочей таблицы и связанных с ней справочников.

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

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

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

 

Формат рабочей таблицы:

 

CREATE TABLE [dbo].[LOYALTY_REPORT_POSITION_DATA](

 [row] [int] NULL,

 [col] [int] NULL,

 [items] [int] NULL,

 [amount] [float] NULL

) ON [PRIMARY]

 

Заполним рабочую таблицу LOYALTY_REPORT_POSITION_DATA данными о колонках отчета (деления по оси X - количество месяцев в рассматриваемом периоде) и о строках (деления по оси Y – создадим 12 строк, чтобы понаблюдать лояльность по оплатам всего года от оплат первого месяца). Макет таблицы выглядит так:

col:row - координата, где row - строка (ось Y); col - колонка (ось X); items - количество, приходящееся на координату col:row по факту оплаты услуги; amount - сумма оплаты, приходящаяся на координату col:row по факту оплаты услуги:

 

(процедуру заполнения таблицы см. ниже)

 

Процедура первичного заполнения рабочей таблицы LOYALTY_REPORT_POSITION_DATA:

 

Create procedure dbo.initLoyaltyReport(

@startDate as DateTime,

@endDateExcluding as DateTime,

@rows as int) as

Begin

 Delete from LOYALTY_REPORT_POSITION_DATA

 Declare @cols as int

 Set @cols = datediff(m, @startDate, @endDateExcluding)

 

 Declare @row as int

 Set @row = 1

 While @row < = @rows

 Begin

         Declare @col as int

         Set @col = 1

         While @col < =  @cols

         Begin

                 Insert into LOYALTY_REPORT_POSITION_DATA (row,  col, items, amount)

                 values (@row, @col,  0, 0)

                 Set @col = @col + 1

         end

         Set @row = @row + 1

 end

End

 

Запуск процедуры по датам рассматриваемого периода на 12 строк:

 

execute dbo.initLoyaltyReport

'1999-01-01',

'2000-01-01',

12

 

Следующая задача – разработать процедуру формирования данных к отчету, заполнить полученный макет рабочей таблицы количествами и денежными суммами по данным таблицы фактов оплат (SERVICE_FACTS), так чтобы каждая оплата проверялась к какой она относится координате (X - месяц начала оказания услуги; Y – месяц, за который услуга оплачена) и в соответствии с результатом проверки проставлялясь бы в соответствующую ячейку макета. Например, услуга с датой начала 01.01.1999, оплаченная за февраль 1999 запишется в виде единицы в колонку items по  колонке row = 2, т.к. февраль – 2-й месяц по порядку от начала оказания услуги и по колонке col = 1, т.к. январь – первый месяц в рассматриваемом периоде. Если еще один клиент оплатил услугу за февраль-99, начало которой январь-99, то в эту же ячейку items, где row = 2 и col = 1 запишется еще одна единица и в ячейке будет уже 2 item-а. И так далее.

Аналогичным образом заполнится и колонка по суммам оплат.

 

Фрагмент таблицы SERVICE_FACTS:

 

 

 

Процедура формирования данных к отчету – заполнение рабочей таблицы LOYALTY_REPORT_POSITION_DATA данными таблицы SERVICE_FACTS:

 

Create procedure dbo.fillLoyaltyReportRows(

@startDate as DateTime,

@endDateExcluding as dateTime)

as

Begin

 

DECLARE items CURSOR FOR

SELECT SERVICE_BEGIN_DATE, PAYMENT_DATE, PAYMENT_AMNT

From SERVICE_FACTS

Where SERVICE_BEGIN_DATE >= @startDate

And SERVICE_BEGIN_DATE < @endDateExcluding

 

OPEN items

 

Declare @SERVICE_BEGIN_DATE as datetime

Declare @PAYMENT_DATE as datetime

Declare @PAYMENT_AMNT as numeric(18,2)

 

-- выполнить первое считывание и сохранить указанные значения (выполненного Select запроса)

-- FETCH - достать

FETCH NEXT FROM items

 

-- переменные должны идти в том же порядке, в котором идут клонки Select запроса

INTO

@SERVICE_BEGIN_DATE,

@PAYMENT_DATE,

@PAYMENT_AMNT

 

-- проверить, считалась ли запись: если результат считывания успешный обработать полученные данные

WHILE @@FETCH_STATUS = 0

BEGIN

 

 Declare @col as int

 Set @col = datediff(month, @startDate, @SERVICE_BEGIN_DATE)

 

 Declare @row as int

 Set @row = 1 + datediff(month, @SERVICE_BEGIN_DATE, @PAYMENT_DATE)

 

print 'col='+convert(varchar,@col)+' row='+convert(varchar,@row)

 

 Update LOYALTY_REPORT_POSITION_DATA

 Set

items = items + 1,

amount = amount + @PAYMENT_AMNT

 where

col = @col

and row = @row

 

-- продолжить пока есть записи в рекордсете

FETCH NEXT FROM items

INTO

@SERVICE_BEGIN_DATE,

@PAYMENT_DATE,

@PAYMENT_AMNT

END

 

CLOSE items

DEALLOCATE items

 

 

End

 

Запуск процедуры dbo.fillLoyaltyReportRows:

 

execute dbo.fillLoyaltyReportRows

'1999-01-01',

'2000-01-01'

 

Результат работы dbo.fillLoyaltyReportRows – заполненная таблица LOYALTY_REPORT_POSITION_DATA:

 

 

 

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

 

Измерениями (Dimensions) куба будут поля Column Nbr и Column Name – таблицы dbo.LOYALTY_REPORT_DIM_X:

 

 

и поле Row Nbr таблицы – dbo.LOYALTY_REPORT_DIM_Y:

 

 

 

Аггрегированными фактами куба (Measures) будут

LOYALTY_REPORT_POSITION_DATA.items – количество оплаченных услуг

или

LOYALTY_REPORT_POSITION_DATA.amount – сумма оплаченных услуг

с типом аггрегации Sum.

 

Схема куба:

 

 

 

Результат разработки куба:

 

 

 

По циклам см. также: Работа с CURSOR (Recordset)

 

Если интересно как представлен этот куб в Excel, то см. тут

 



© 2018 | Анна Петросян | pashelp@yandex.ru