Вопрос по sql-server, sql-server-2008 – Разделение результатов в запросе промежуточных итогов

2

Я ищу быстрый способ создания кумулятивных итогов в большом наборе данных SQL Server 2008, который разделен по определенному столбцу, возможно, с помощьюmultiple assignment variable solution, В качестве очень простого примера я хотел бы создать "cumulative_total" столбец ниже:

user_id | month | total | cumulative_total

1       | 1     | 2.0   | 2.0
1       | 2     | 1.0   | 3.0
1       | 3     | 3.5   | 8.5

2       | 1     | 0.5   | 0.5
2       | 2     | 1.5   | 2.0
2       | 3     | 2.0   | 4.0

Мы традиционно делали это с помощью коррелированных подзапросов, но при больших объемах данных (более 200 000 строк и нескольких различных категорий промежуточного итога) это не дает нам идеальной производительности.

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

http://sqlblog.com/blogs/paul_nielsen/archive/2007/12/06/cumulative-totals-screencast.aspx

В примере из этого блога решение для кумулятивных переменных выглядит следующим образом:

UPDATE my_table
SET @[email protected]+ISNULL(total, 0)

Это решение кажется чрезвычайно быстрым для суммирования для одного пользователя в приведенном выше примере (пользователь 1 или пользователь 2). Тем не менее, мне нужно эффективно разделить по пользователям - дать мне совокупный итог по пользователям по месяцам.

Кто-нибудь знает способ расширения концепции множественных присваивающих переменных для решения этой проблемы или какие-либо другие идеи, кроме коррелированных подзапросов или курсоров?

Большое спасибо за любые советы.

Я думаю, что ваш третий ряд должен иметьcumulative_total = 6.5не8.5. Aaron Bertrand
У вас есть большое количество пользователей или большое количество месяцев или оба? И какая версия SQL Server? Aaron Bertrand
Привет, Аарон. Большое количество пользователей, но всего несколько месяцев (не более 24). SQL Server 2008. Andy W

Ваш Ответ

2   ответа
2

Ваши возможности в SQL Server 2008 достаточно ограничены: вы можете сделать что-то на основе метода, описанного выше (который называется «причудливое обновление»), или вы можете сделать что-то в CLR.

Лично я бы пошел с CLR, потому что он гарантированно работает, в то время как необычный синтаксис обновления не является чем-то, что формально поддерживается (поэтому может произойти сбой в будущих версиях).

Вариант необычного синтаксиса обновления, который вы ищете, будет выглядеть примерно так:

UPDATE my_table
SET @CumulativeTotal=cumulative_total=ISNULL(total, 0) + 
        CASE WHEN @[email protected] THEN @CumulativeTotal ELSE 0 END, 
    @user=lastUser

Стоит отметить, что в SQL Server 2012 вводитсяRANGE поддержка оконных функций, и это выражается наиболее эффективным способом, при этом поддерживается на 100%.

@AaronBertrand - Хорошая работа там. Не поймите меня неправильно, я не рекомендую причудливое обновление - просто пытаюсь ответить на заданный вопрос.
@AaronBertrand - Да, я должен был явно сказать RANGE / ROWS, так как я склонен рассматривать все расширение синтаксиса под одной крышей. Бит CLR, о котором я говорю, выполняет вычисления на катушке, что отлично. У меня были очень хорошие результаты, когда я делал подобные вещи с CLR раньше. Это было бы интересным упражнением, чтобы попытаться получить хорошие результаты.
Отлично, это прекрасно - спасибо, Мэтт. Заботы о поддержке и записка о RANGE отмечены и оценены. Andy W
@ Мэтт я на самом делеdid some testing on this и подход CLR конкурировал с новым методом SQL Server 2012 (все другие методы работали хуже). Курсор, однако, превзошел даже причудливое обновление.
@Matt, как я указал в конце моего ответа, если вы уже используетеRANGE для этого вы должны проверить этоROWS дает одинаковые результаты (это не во всех случаях), так как онshould быть более эффективным. Кроме того, знаете ли вы какие-либо опубликованные решения CLR, которые работают быстрее, чем подходы на этой странице? Я знаю, что CLR отлично подходит для некоторых вещей (например, расщепление строки), но что, в частности, делает его лучше в этой проблеме? Чуть быстрее математические расчеты? Я бы ожидал, что накладные расходы CLR перевесят какие-либо преимущества (но, к счастью, они окажутся неправильными).
6

Если вам не нужно СОХРАНЯТЬ данные (что не следует делать, потому что вам нужно обновлять промежуточные итоги каждый раз, когда какая-либо строка изменяется, добавляется или удаляется), и если вы не доверяете причудливому обновлению (которое вы не должен, поскольку он не гарантированно работает и его поведение может измениться с помощью исправления, пакета обновления, обновления или даже изменения базового индекса или статистики), вы можете попробовать этот тип запроса во время выполнения. Это методик MVP Хьюго Корнелис придумал «итерацию на основе множеств» (он опубликовал нечто подобное в одной из своих главSQL Server MVP Deep Dives). Поскольку для выполнения итогов обычно требуется курсор по всему набору, причудливое обновление по всему набору или одиночное нелинейное самосоединение, которое становится все дороже по мере увеличения числа строк, уловка заключается в том, чтобы перебрать некоторый конечный элемент в наборе (в данном случае «ранг» каждой строки в терминах месяца для каждого пользователя), и вы обрабатываете только каждый ранг один раз для всех комбинаций пользователь / месяц в этом ранге, поэтому вместо циклического обхода 200 000 строк Вы зациклились до 24 раз).

DECLARE @t TABLE
(
  [user_id] INT, 
  [month] TINYINT,
  total DECIMAL(10,1), 
  RunningTotal DECIMAL(10,1), 
  Rnk INT
);

INSERT @t SELECT [user_id], [month], total, total, 
  RANK() OVER (PARTITION BY [user_id] ORDER BY [month]) 
  FROM dbo.my_table;

DECLARE @rnk INT = 1, @rc INT = 1;

WHILE @rc > 0
BEGIN
  SET @rnk += 1;

  UPDATE c SET RunningTotal = p.RunningTotal + c.total
    FROM @t AS c INNER JOIN @t AS p
    ON c.[user_id] = p.[user_id]
    AND p.rnk = @rnk - 1
    AND c.rnk = @rnk;

  SET @rc = @@ROWCOUNT;
END

SELECT [user_id], [month], total, RunningTotal
FROM @t
ORDER BY [user_id], rnk;

Результаты:

user_id  month   total   RunningTotal
-------  -----   -----   ------------
1        1       2.0     2.0
1        2       1.0     3.0
1        3       3.5     6.5 -- I think your calculation is off
2        1       0.5     0.5
2        2       1.5     2.0
2        3       2.0     4.0

Конечно тыcan обновите базовую таблицу из этой табличной переменной, но зачем беспокоиться, так как эти сохраненные значения действительны только до следующего раза, когда к таблице прикасается какой-либо оператор DML?

UPDATE mt
  SET cumulative_total = t.RunningTotal
  FROM dbo.my_table AS mt
  INNER JOIN @t AS t
  ON mt.[user_id] = t.[user_id]
  AND mt.[month] = t.[month];

Поскольку мы не полагаемся на неявное упорядочение любого рода, это поддерживается на 100% и заслуживает сравнения производительности относительно неподдерживаемого необычного обновления. Даже если он не превосходит его, но подходит близко, вы все равно должны подумать об его использовании.

Что касается решения SQL Server 2012, Мэтт упоминаетRANGE но так как этот метод использует катушку на диске, вы должны также проверить сROWS вместо того, чтобы просто бегать сRANGE, Вот быстрый пример для вашего случая:

SELECT
  [user_id],
  [month],
  total,
  RunningTotal = SUM(total) OVER 
  (
    PARTITION BY [user_id] 
    ORDER BY [month] ROWS UNBOUNDED PRECEDING
  )
FROM dbo.my_table
ORDER BY [user_id], [month];

Сравните это сRANGE UNBOUNDED PRECEDING или нетROWS\RANGE на всех (который также будет использоватьRANGE на диске шпуля). Вышеуказанное будет иметь меньшую общую продолжительность иway меньше операций ввода-вывода, даже если план выглядит несколько более сложным (дополнительный оператор проекта последовательности).

Недавно я опубликовал сообщение в блоге, в котором изложены некоторые различия в производительности, которые я наблюдал для конкретного сценария промежуточных итогов:

http://www.sqlperformance.com/2012/07/t-sql-queries/running-totals

Спасибо Аарон, это очень полезно. Очень признателен. Andy W

Похожие вопросы