Привет, Хабр! Некоторые функции DAX из Power BI могут выглядеть интуитивно понятными, но при детальном рассмотрении ведут себя не совсем ожидаемо. Например, SUMMARIZE не агрегирует «сразу», в месте использования, но SUMMARIZE реализуется через «виртуальную», «отложенную» агрегацию за счет сохранения состояния. Для суммы, т.е. SUM, промежуточное состояние и есть сумма и особенности SUMMARIZE не проявляются, но для любой другой агрегации (например, среднего AVERAGE) становится понятно, что в Power BI уже учитывается так называемое состояние для корректного расчета SUMMARIZE, т.е. данные по всем записям сохраняются в состоянии. В других СУБД единственный аналог — только State и Merge комбинаторы из ClickHouse, поэтому для иллюстрации состояний будет рассмотрен пример из ClickHouse, соответствующий DAX с SUMMARIZE и AVERAGE. Интересующимся особенностями SUMMARIZE в Power BI — добро пожаловать под кат :)

Рассмотрим на примере одной таблицы sales с полями class, color и amount с данными:

Вначале рассмотрим простой DAX с суммированием SUM:

EVALUATE
    SUMMARIZECOLUMNS(
        'sales'[class],
		"sum",
            CALCULATE(
				SUM (sales[amount]),
				SUMMARIZE(sales, sales[color])
			)
        )    

Для суммирования не существует отдельного состояния, так как сумма и есть само состояние. Понятно, что даже если добавить агрегацию (сумму) по sales[color], и затем агрегировать (суммировать) по sales[class], из-за суммирования результат не меняется. Можно проиллюстрировать это по шагам для агрегации SUM, что «интуитивно понятный» SUMMARIZE в виде «немедленной агрегации» для суммирования SUM работает корректно, хотя на самом деле Power BI так не выполняет расчеты.

Вначале агрегируем (суммируем) по sales[color]:

Далее агрегируем (суммируем) по sales[class]:

Получаем, что результат совпадает с Power BI:

Видно, что для суммирования как вида агрегации проблем нет и результат такого способа вычисления соответствует результату Power BI.

Но что будет для других видов агрегаций, например, для среднего AVERAGE?

EVALUATE
    SUMMARIZECOLUMNS(
        'sales'[class],
		"average",
            CALCULATE(
				AVERAGE (sales[amount]),
				SUMMARIZE(sales, sales[color])
			)
        )

Попробуем проверить корректность предыдущего «интуитивно понятного» подхода для AVERAGE агрегации, точнее, покажем, почему он некорректен.

Понятно, что при расчете среднего корректный ответ должен быть 1 для deluxe и 3 для economy:

Проиллюстрируем на примере, что «немедленная» агрегация (по аналогии с SUM(sales[amount])) не работает для AVERAGE(sales[amount]).

Агрегируем и считаем среднее по sales[class], sales[color] (как мы раньше пробовали с SUM), вначале агрегируем по sales[color]:

Видим, что для economy и black среднее равно (3+4)/2 = 3.5.

Далее агрегируем со средним значением ещё раз, но теперь по sales[class], и видим, что результат неверный, т.к. для economy (2+3.5/2) = 2.75 вместо 3.

Таким образом, для SUM всё корректно, т.к. это «кумулятивная мера», и состояние сохранять дополнительно не нужно, сумма и есть состояние, но для AVERAGE — все сложнее, и SUMMARIZE «виртуальный», то есть по сути сохраняется состояние для всех записей таблицы, чтобы можно было агрегировать в нужный момент по нужным полям.

Способ вычисления, который подходил для SUM, не подходит для AVERAGE, т.к. нужно хранить состояние.

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

И в общем и целом, такие агрегации нежелательны (дважды расчет среднего, «среднее от среднего»), и это может быть одной из причин, что SUMMARIZE в Power BI работает именно так, с состояниями, и не искажает результат «средними от средних».

Сложно сказать, какая реализация SUMMARIZE используется в Power BI — возможно, через флаги колонок (и агрегация в нужный момент) или через механизмы вида State Merge.

К слову, такой механизм сохранения состояния в чистом виде, доступный для разработчика, есть только в ClickHouse в комбинаторах State Merge, в других СУБД в лучшем случае может быть реализация сохранения состояния внутри движка.

Для ClickHouse можно написать SQL, соответсвующий рассмотренному DAX c AVERAGE, с использованием комбинаторов State и Merge:

CREATE OR REPLACE TABLE sales
(
    class  String,
    color  String,
    amount Int32
)
    ENGINE = MergeTree
        ORDER BY (class, color);

INSERT INTO sales (class, color, amount)
VALUES ('deluxe', 'red', 1),
       ('economy', 'red', 2),
       ('economy', 'black', 3),
       ('economy', 'black', 4);

SELECT class,
       avgMerge(avg_state) AS average
FROM (
         SELECT class,
                color,
                avgState(amount) AS avg_state
         FROM sales
         GROUP BY class, color
         )
GROUP BY class;

Такой запрос возвращает корректный результат, совпадающий с Power BI:

В других СУБД нет возможности писать запросы с колонками состояний (avgState), объединять их (avgMerge) и использовать эти колонки в запросах.

Получается, механизм сохранения состояния работает не во всех СУБД, как же ведет себя Power BI в рассмотренных DAX запросах для SUM и AVERAGE? Поведение Power BI не является открытым, но из анализа SQL в DAX Studio видно, что Power BI игнорирует SUMMARIZE, анализирует структуру запроса, колонки, возможно флаги, и т.д. и не делает агрегацию из SUMMARIZE как для AVERAGE, так и для SUM.

Как видно из псевдо-SQL в DAX Studio, в обоих случаях агрегации по sales[color] нет, и есть только агрегация по sales[class].

Надеюсь, такая привязка SUMMARIZE к реалиям СУБД может быть интересна, успешных запросов и дашбордов :)