Автор: Mosha Pasumansky
Дата публикации оригинала: 2008-14-06
Источник: Блог Mosha Pasumansky

Сколько элементов набора данных имеют отличающиеся значения? Это кажется простым вопросом. Ясно, что эта проблема давно известна в Excel и для нее есть классическое решение. В Интернете есть много сайтов (например здесь и здесь), которые дают следующее решение:

=SUM(1/COUNTIF(A1:A6,A1:A6)))

(обратите внимание, что эту формулу необходимо вводить с помощью Ctrl-Shift-Enter, для указания того, что это матричная формула, в противном случае результат будет неправильным – это одна из хитростей в Excel).

Когда кто-то показал мне это решение, я посмотрел на него и не понял, как это работает. Только переписав это в MDX я понял в чем дело. Давайте возьмем следующий пример на основе Adventure Works – мы хотим сосчитать, со скольки различных букв начинаются названия продуктов (просто для того, чтобы подобрать что-то, для чего в измерении Продукт нет специального атрибута, потому что в противном случае это было бы слишком просто). MDX, создающий тот же алгоритм, что и вышеуказанная формула в Excel в данных условиях будет выглядеть следующим образом:

with
member measures.CountDistinctLetters as
Sum([Product].[Product].[Product].MEMBERS AS AllProducts,
1/Filter([Product].[Product].[Product].MEMBERS,
VBA!Left(AllProducts.Current.Name,1) =
VBA!Left([Product].[Product].CurrentMember.Name,1)
).Count)

select CountDistinctLetters on 0
from [Adventure Works]

Это более подробно, но вероятно не проще для понимания обычного разработчика, чем формула в Excel. Вкратце идея состоит в следующем: для каждого элемента в наборе данных мы подсчитываем сколько других элементов производят то же значение (функции COUNTIF в Excel и Filter(…).Count в MDX), а затем путем деления 1 на это число мы получаем дробное число текущего элемента.
(К сожалению, нет простого способа отладить такие формулы. MDX-отладчик в MDX Studio не в состоянии рассмотреть такие выражения. Я провел довольно много времени размышляя над тем, как построить соответствующее окружение для отладки MDX и после нескольких неудачных попыток я, кажется, нашел прорывную идею. Ранние эксперименты были очень обнадеживающими и если это работает, я скоро напишу об этом в блоге. Эта идея довольно сильно отличается от прочих попыток отладки в MDX.)

Ясно, что выполнить такое решение очень сложно. Каждый элемент в наборе данных мы снова и снова сравниваем со всеми прочими элементами для определения того, сколько элементов имеют одно и то же значение. Алгоритмическая сложность данного метода вложенного цикла выражается как O(n^2). Фактически вышеуказанный запрос осуществляется в течение 12 секунд. Можно несколько ускорить его специально запоминая результаты VBA!Left

with
member Prefix as VBA!Left([Product].[Product].CurrentMember.Name,1)
member measures.CountDistinctLetters as
Sum([Product].[Product].[Product].MEMBERS AS AllProducts,
1/Filter([Product].[Product].[Product].MEMBERS,
(AllProducts.Current,Prefix) = Prefix
).Count)

select CountDistinctLetters on 0
from [Adventure Works]

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

Другой вариант – вместо создания алгоритма вложенных циклов отсортировать элементы набора данных, а затем еще раз просмотреть отсортированный набор данных, подсчитывая количество отличающихся один от одного элементов. Единственной технической сложностью в этом случае при просмотре отсортированного набора данных является необходимость сравнивать текущий элемент с предыдущим. Получить текущий элемент легко, но как получить предыдущий? В MDX нет функции «предыдущий», есть только функция PrevMember, но она влияет только на иерархическое упорядочивание уровня и здесь мы имеем произвольный набор данных, отсортированных по произвольным критериям. Решением является использование функции CurrentOrdinal, которая возвращает индекс n-строки, повторяющийся в наборе данных, а затем, используя функцию CurrentOrdinal-1 в качестве нового индекса, посылает его в функцию Item. В языке MDX это выражается следующим образом:

with
member Prefix as VBA!Left([Product].[Product].CurrentMember.Name,1)
set ProductsSortedByPrefix as 
order([Product].[Product].[Product].MEMBERS, Prefix, BASC)
member measures.CountDistinctLetters  as 
Sum(ProductsSortedByPrefix as y,
iif(y.CurrentOrdinal = 1 OR Prefix <>
(Prefix, y.Item(y.CurrentOrdinal-2)), 1, 0))

select CountDistinctLetters  on 0
from [Adventure Works]

Этот запрос выполняется гораздо быстрее, всего за 0,234 секунды. Алгоритмическая сложность объясняется как O(n*log(n)). Однако, это не идеальное решение, так как его можно найти за одно сканирование исходного множества, то есть, со сложностью O(n). Для того чтобы сделать это мы должны написать хранимую процедуру.

public int CountDistinctValues(Set set, Expression exp)
{
System.Collections.Hashtable ht = new System.Collections.Hashtable();
foreach (Tuple t in set.Tuples)
{
string v = exp.Calculate(t).ToString();
if ( !ht.ContainsKey(v) )
ht.Add(v, null);
}

return ht.Count;
}

Теперь, если мы используем эту хранимую процедуру в запросе как указано ниже

with member CountDistinctLetters as 
ASSP.ASStoredProcs.Util.CountDistinctValues(
[Product].[Product].[Product].MEMBERS,
VBA!Left([Product].[Product].CurrentMember.Name,1))
select CountDistinctLetters on 0
from [Adventure Works]

мы сможем выполнить данную задачу всего за 0,062 секунды.


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

Читайте также: