Совет №12
Точный подсчёт с многомерным дополнением

Материал опубликован с разрешения компании Ralph Kimball Associates
Автор оригинала: Warren Thornthwaite (все статьи)
Перевод на русский язык: Константин Лисянский
Оригинальный документ располагается здесь.

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

Недавно мы загрузили простой пример такой дополнительной таблицы, которая отображает почтовые индексы на Маркетинговые Регионы (Media Market Area). Нашим ребятам из Управления Маркетинга было интересно посмотреть, каково распределение доли наших клиентов по маркетинговым регионам в сравнении со всем остальным населением.

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

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

Если мы хотим увидеть, сколько клиентов у нас в каждом маркетинговом регионе, внутреннее соединение даст нам следующий результат:

Внутреннее соединение является соединением "по равенству". Поскольку для почтового индекса 24166 нет эквивалентного маркетингового региона, запрос недосчитывает наших подписчиков, возвращая четырёх клиентов, вместо пяти. Мы также теряем информацию с другой стороны соединения, поскольку в результате запроса отсутствуют данные о регионах, в которых у нас нет клиентов (например, Gloucester). Переписав запрос с использованием полного внешнего соединения, мы получим следующий результат:

Теперь мы посчитали всех пятерых клиентов и увидели, что в регионе Gloucester они отсутствуют. Мы могли бы использовать функцию IFNULL для замены значения NULL на более дружественные значения, например, "неизвестный регион". Заметьте, то, что вы считаете, сильно влияет на результаты. В нашем случае мы считали значения столбца Customer_Key. Если бы мы заменили это на count(*), мы могли бы в результате получить 7, поскольку * означает подсчёт всех строк, а полный результат состоит из семи строк. Если бы мы стали считать количество значений столбца MMA_to_Zipcode.Zip_Code, мы насчитали бы 6 значений, поскольку значение 94025 встречается два раза.

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

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

Sum(case when Media_Market_Area.Zip IS NULL then 1 else 0 end) AS Customer_Count_with_No_MMA,

Sum(case when count(customer_key)=0 then 1 else 0 end) as MMA_Count_with_No_Customers,

Sum(case when not(Media_Market_Area.Zip IS NULL or count(customer_key)=0) then 1 else 0 end as Count_MMAs_with_Customers)

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

• Следующий совет
• Предыдущий совет
• Другие советы

По этой теме можно также почитать:

• Совет №11. Точный подсчёт количества элементов измерения
  

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

Если вы нашли в сети интересные ссылки на ресурсы по технологиям хранилищ данных, OLAP, CRM или data mining, и хотите поделиться ими с другими, присылайте их. Я с удовольствием размещу их на этом сайте.