Продолжение статьи о сравнении возможностей оконных функций SQL Server и формул DAX
В прошлой статье были рассмотрены аспекты работы функций PowerPivot и на гипотетическом примере была продемонстрирована аналогия между оконными функциями sql server и DAX функциями.
Напомню, что исходный пример предполагал нахождение с помощью DAX некорректных данных, а именно:
поиск в исходных данных (детализированных до строк накладной) названий торговых точек, имеющих более одного адреса в городе.
В итоге эта цель была достигнута, но практической пользы от нее можно было бы получить гораздо больше, если заставить PowerPivot по данным случаям выполнять некие действия. Заодно это прекрасный повод показать еще немного полезных вещей из DAX арсенала.
Немного подумав, как лучше всего обрабатывать события нашего примера (а природа их кроется либо в переезде точки на новый адрес, либо в банальных опечатках при вводе адреса), первое, что приходит в голову — это как то стандартизировать подобные адреса добавив единый адрес для нескольких разных адресов в рамках кортежа «торговая точка-город». Самым логичным в данном случае будет присвоение единого адреса для всей истории продаж торговой точки в городе по последнему адресу отгрузки.
Для этого было бы неплохо сначала для каждого «окна»[=Имя торговой точки+город] получить последнюю дату активности (продажи).
Ну здесь все просто, на sql server запрос поиска даты для каждой атомарной записи будет выглядеть следующим образом:
Select 't1.Имя ТТ', 't1.Город', 't1.Адрес', 't1.Продукт', 't1.№ ТТН', 't1.Дата ТТН', 't1.Отгрузка, шт', 't1.Отгрузка с НДС', 't1.Цена за шт без НДС', max( 't1.Дата ТТН') OVER ( partition by 't1.Город', 't1.Имя ТТ' ) as maxdate from Table as t1
После прочтения прошлой статьи реализовать вычисляемое поле в DAX не составит труда:
MaxDatePosInTown:=CALCULATE(MAX('Таблица1'[Дата ТТН]);ALLEXCEPT('Таблица1';'Таблица1'[Имя ТТ];'Таблица1'[Город]))
Теперь обладая датой последней продажи ее можно использовать как вспомогательное поле для формирования соответствующего субсета.
Функционально, субсет должен быть сформирован следующим образом:
Для каждой записи в исходном наборе данных надо получить окно из имени торговой точки+города и в данном окне получить подмножество записей где дата соответствует последней дате продажи в данном окне.
Из перечня функций DAX наиболее подходящей для этого выступает функция FILTER.
Вот фрагмент справки:
На DAX теперь нужный нам субсет будет выглядеть так:
FILTER( ALLEXCEPT('Таблица1';'Таблица1'[Имя ТТ]; 'Таблица1'[Город]); 'Таблица1'[Дата ТТН]= 'Таблица1'[MaxDatePosInTown])
где в роли table выступает окно=точка+город, в роли условия фильтрации filter: 'Таблица1'[Дата ТТН]= 'Таблица1'[MaxDatePosInTown]
Обращаем внимание на условие из справки:
«Функция FILTER не используется независимо, а внедряется в другие функции, аргументом которых должна быть таблица. „
То есть проблема в том что бы из получившегося субсета выбрать любую одну запись (именно одну) и из нее взять нужный нам столбец.
Уже вырисовывается общая картина итоговой формулы:
=CALCULATE( ФормулаИзвлеченияОднойЗаписиИзСубсета('Таблица1'[Адрес]); FILTER( ALLEXCEPT('Таблица1';'Таблица1'[Имя ТТ]; 'Таблица1'[Город]); 'Таблица1'[Дата ТТН]= 'Таблица1'[MaxDatePosInTown]))
(Необходимость функции CALCULATE описана в прошлой статье)
Среди перечня имеющихся формул наиболее подходящей бросается в глаза LOOKUPVALUE
Но попытки ее запустить успеха не принесли. В эту же тему нашел подтверждение с заморского блога:
Поэтому обращаем внимание на FIRSTNONBLANK, принимающей следующие аргументы:
Единственный момент — функция требует выражение, но здесь можно подсунуть ей TRUE
В итоге формула будет иметь конечный вид:
=CALCULATE( FIRSTNONBLANK('Таблица1'[Адрес]; TRUE()); FILTER( ALLEXCEPT('Таблица1';'Таблица1'[Имя ТТ];'Таблица1'[Город]); 'Таблица1'[Дата ТТН]='Таблица1'[MaxDatePosInTown]))
Аналогом в SQL Server будет что то вроде:
with a1 as
( Select 't1.Имя ТТ', 't1.Город', 't1.Адрес', row_number() over (partition by 't1.Имя ТТ', 't1.Город' order by 't1.Дата ТТН' desc ) as rv from 'Таблица1' )
Select 't1.Имя ТТ', 't1.Город', 't1.Адрес', 't1.Продукт', 't1.№ ТТН', 't1.Дата ТТН', 't1.Отгрузка, шт', 't1.Отгрузка с НДС', 't1.Цена за шт без НДС', 'a1.Адрес' as [ПоследнийАдрес]
from Table as t1 inner join a1 on 't1.Имя ТТ'='a1.Имя ТТ' and 't1.Город'= 'a1.Город' where a1.rw=1
Конечный результат из PowerPivot на сквозном примере будет выглядеть так:
P.S Можно попробовать еще такую альтернативу, дающую тот же результат
=calculate(LASTNONBLANK('Таблица1'[Адрес];1);Filter('Таблица1';'Таблица1'[Дата ТТН]=earlier('Таблица1'[MaxDatePosInTown]) && 'Таблица1'[Имя ТТ]=earlier('Таблица1'[Имя ТТ])))
Но как мне кажется, для восприятия она несколько сложнее
Надеюсь было интересно.
Ананьев Генрих.
