Сумма сальдо диапазона дат с использованием оконных функций

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

  SELECT
    TH.ProductID,
    TH.TransactionDate,
    TH.ActualCost,
    RollingSum45 = SUM (TH.ActualCost) OVER (
        РАЗДЕЛЕНИЕ TH.ProductID
        ЗАКАЗАТЬ НА TH.TransactionDate
        ДИАПАЗОН МЕЖДУ
            ИНТЕРВАЛ 45 ДНЕЙ ПРЕЦЕДЕНТ
            И ТЕКУЩАЯ РУКА)
FROM Production.TransactionHistory AS TH
СОРТИРОВАТЬ ПО
    TH.ProductID,
    TH.TransactionDate,
    TH.ReferenceOrderID;
 

К сожалению, размер кадра окна RANGE в настоящее время не позволяет интервал в SQL Server.

Я знаю, что могу написать решение, используя подзапрос и обычный (не оконный) агрегат:

  SELECT
    TH.ProductID,
    TH.TransactionDate,
    TH.ActualCost,
    RollingSum45 =
    (
        SELECT SUM (TH2.ActualCost)
        FROM Production.TransactionHistory AS TH2
        ГДЕ
            TH2.ProductID = TH.ProductID
            И TH2.TransactionDate <= TH.TransactionDate
            AND TH2.TransactionDate> = DATEADD (DAY, -45, TH.TransactionDate)
    )
FROM Production.TransactionHistory AS TH
СОРТИРОВАТЬ ПО
    TH.ProductID,
    TH.TransactionDate,
    TH.ReferenceOrderID;
 

Учитывая следующий индекс:

  СОЗДАТЬ УНИКАЛЬНЫЙ ИНДЕКС i
ON Production.TransactionHistory
    (ProductID, TransactionDate, ReferenceOrderID)
ВКЛЮЧАЮТ
    (Действительная цена);
 

План выполнения:

План выполнения

Несмотря на то, что он не ужасно неэффективен, кажется, что этот запрос должен быть доступен, используя только агрегированные и аналитические функции окна, поддерживаемые в SQL Server 2012, 2014 или 2016 (пока).

Для ясности я ищу решение, которое выполняет один проход по данным.

В T-SQL это может означать, что OVER будет выполнять эту работу, а в плане выполнения будут представлены Window Spools и Window Aggregates. Все языковые элементы, которые используют предложение OVER , являются честной игрой. Решение SQLCLR приемлемо, если оно гарантировано для получения правильных результатов.

Для решений T-SQL, чем меньше хешей, сортировок и оконных катушек /агрегатов в плане выполнения, тем лучше. Не стесняйтесь добавлять индексы, но отдельные структуры не допускаются (так, например, не предварительно подсчитанные таблицы не синхронизируются с триггерами). Разрешены справочные таблицы (таблицы чисел, даты и т. Д.).

В идеале решения будут давать точно такие же результаты в том же порядке, что и вышеприведенная версия подзапроса, но допустимо и правильное. Производительность всегда учитывается, поэтому решения должны быть, по крайней мере, разумно эффективными.

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

51 голос | спросил Paul White 7 thEurope/Moscowp30Europe/Moscow09bEurope/MoscowMon, 07 Sep 2015 23:13:41 +0300 2015, 23:13:41

6 ответов


34

Отличный вопрос, Пол! Я использовал несколько разных подходов, один в T-SQL и один в CLR.

краткое резюме T-SQL

Подход T-SQL можно суммировать как следующие шаги:

  • Возьмите кросс-продукт продуктов /дат
  • Объединить наблюдаемые данные о продажах
  • Совокупность данных для уровня продукта /даты
  • Вычислить скользящие суммы за последние 45 дней на основе этих совокупных данных (которые содержат любые «отсутствующие» заполненные дни)
  • Отфильтруйте эти результаты только для пар продуктов /дат, которые имели один или несколько продаж.

Используя SET STATISTICS IO ON , этот подход сообщает Table 'TransactionHistory'. Количество сканирования 1, логическое чтение 484 , что подтверждает «единый проход» по таблице. Для справки, исходный запрос поиска по циклу Таблица «TransactionHistory». Число сканирования 113444, логическое чтение 438366 .

Как сообщается в SET STATISTICS TIME ON , время процессора составляет 514ms . Это выгодно отличается от 2231ms для исходного запроса.

Краткая сводка CLR

Резюме CLR можно суммировать как следующие шаги:

  • Прочитайте данные в памяти, упорядоченные по продукту и дате.
  • При обработке каждой транзакции добавьте к общей сумме затрат. Всякий раз, когда транзакция является другим продуктом, чем предыдущая транзакция, сбросьте общее число до 0.
  • Сохранять указатель на первую транзакцию, которая имеет тот же (продукт, дата), что и текущая транзакция. Всякий раз, когда встречается последняя транзакция с этим (продукт, дата), вычисляет текущую сумму для этой транзакции и применяет ее ко всем транзакциям с тем же (продукт, дата)
  • Верните все результаты пользователю!

Используя SET STATISTICS IO ON , этот подход сообщает, что логического ввода-вывода не произошло! Ничего себе, идеальное решение! (На самом деле, кажется, что SET STATISTICS IO не сообщает о вводе-выводе, выполняемом в CLR. Но из кода легко видеть, что сделано ровно одно сканирование таблицы и извлечения данных в по предложению Павла.

Как сообщается SET STATISTICS TIME ON , время CPU теперь 187ms . Таким образом, это значительно улучшает подход T-SQL. К сожалению, общее истекшее время обоих подходов очень похоже примерно на полсекунды. Однако подход на основе CLR должен выводить на консоль 113K строк (вместо 52K для подхода T-SQL, который группируется по продукту /дате), поэтому я сосредоточил внимание на времени процессора.

Другим большим преимуществом такого подхода является то, что он дает точно такие же результаты, как и исходный подход loop /seek, включая строку для каждой транзакции, даже в тех случаях, когда продукт продается несколько раз в тот же день. (В AdventureWorks я специально сравнивал результаты по строкам и подтверждал, что они связаны с исходным запросом Павла.)

Недостатком такого подхода, по крайней мере, в его нынешнем виде является то, что он считывает все данные в памяти. Однако алгоритм, который был разработан, строго нуждается в текущем оконном кадре в памяти в любой момент времени и может быть обновлен для работы с наборами данных, которые превышают объем памяти. Пол проиллюстрировал этот момент в своем ответе, создав реализацию этого алгоритма, в котором хранится только скользящее окно в памяти. Это происходит за счет предоставления более высоких разрешений для сборки CLR, но определенно было бы целесообразно масштабировать это решение до произвольно больших наборов данных.


T-SQL - одно сканирование, сгруппированное по дате

Начальная настройка

  USE AdventureWorks2012
ИДТИ
- Создать индекс Павла
СОЗДАТЬ УНИКАЛЬНЫЙ ИНДЕКС i
ON Production.TransactionHistory (ProductID, TransactionDate, ReferenceOrderID)
ВКЛЮЧАТЬ (ActualCost);
ИДТИ
- Создание таблицы календаря на 2000 ~ 2020
CREATE TABLE dbo.calendar (d DATETIME NOT NULL CONSTRAINT PK_calendar ПЕРВИЧНЫЙ КЛЮЧ)
ИДТИ
DECLARE @d DATETIME = '1/1/2000'
WHILE (@d <'1/1/2021')
НАЧАТЬ
    INSERT INTO dbo.calendar (d) ЦЕННОСТИ (@d)
    SELECT @d = DATEADD (ДЕНЬ, 1, @d)
КОНЕЦ
ИДТИ
 

Запрос

  DECLARE @minAnalysisDate DATE = '2007-09-01', - Настраиваемая дата начала в зависимости от потребностей бизнеса
        @maxAnalysisDate DATE = '2008-09-03' - Настраиваемая дата окончания в зависимости от потребностей бизнеса
SELECT ProductID, TransactionDate, ActualCost, RollingSum45, NumOrders
ИЗ (
    SELECT ProductID, TransactionDate, NumOrders, ActualCost,
        SUM (ActualCost) OVER (
                РАЗДЕЛЕНИЕ ПО ТОВАРНЫМ ЗАКАЗАМ TransactionDate
                ROWS МЕЖДУ 45 ПРЕДВАРИТЕЛЬНОЙ И ТЕКУЩЕЙ РУКОЙ
            ) AS RollingSum45
    ИЗ (
        - Полный перекрестный продукт продуктов и дат в сочетании с фактической информацией о расходах для этого продукта /даты
        SELECT p.ProductID, c.d AS TransactionDate,
            COUNT (TH.ProductId) AS NumOrders, SUM (TH.ActualCost) ASДействительная цена
        FROM Production.Product p
        JOIN dbo.calendar c
            ON c.d BETWEEN @minAnalysisDate AND @maxAnalysisDate
        ЛЕВЫЙ ВНЕШНИЙ ПРИСОЕДИНЕНИЕ Production.TransactionHistory TH
            ON TH.ProductId = p.productId
            И TH.TransactionDate = c.d
        ГРУППА BY P.ProductID, c.d
    ) aggsByDay
) rollSums
WHERE NumOrders> 0
ORDER BY ProductID, TransactionDate
- MAXDOP 1, чтобы избежать параллельного сканирования, надувающего счетчик сканирования
ВАРИАНТ (MAXDOP 1)
 

План выполнения

Из плана выполнения мы видим, что исходный индекс, предложенный Павлом, достаточен для того, чтобы мы могли выполнить одно упорядоченное сканирование Production.TransactionHistory , используя объединение слияния для объединения истории транзакций с каждый возможный комбинация продукта /даты.

 введите описание изображения здесь>> </a> </p>

<p> <STRONG> Предположения </STRONG> </p>

<p> Есть несколько существенных предположений, испеченных в этом подходе. Я полагаю, что Пол будет решать, приемлемы ли они:) </p>

<ul>
<li> Я использую таблицу <code> Production.Product </code>. Эта таблица свободно доступна в <code> AdventureWorks2012 </code>, и связь выполняется внешним ключом из <code> Production.TransactionHistory </code>, поэтому я интерпретировал это как честную игру. </li>
<li> Этот подход основан на том факте, что транзакции не имеют компонент времени на <code> AdventureWorks2012 </code>; если бы они это сделали, создание полного набора комбинаций продуктов и дат было бы невозможным без предварительного прохода по истории транзакций. </li>
<li> Я создаю набор строк, содержащий только одну строку на пару продуктов /дат. Я думаю, что это «возможно правильно» и во многих случаях более желательный результат для возвращения. Для каждого продукта /даты я добавил столбец <code> NumOrders </code>, чтобы указать, сколько продаж произошло. См. Следующий снимок экрана для сравнения результатов исходного запроса с предлагаемым запросом в случаях, когда продукт был продан несколько раз в одну и ту же дату (например, <code> 319 </code> /<code> 2007-09 -05 00: 00: 00.000 </code>) </li>
</ul>
<p> <a href=  введите описание изображения здесь>> </a> </p>

<p> <br> </p>

<h1> CLR - одно сканирование, полный набор негруппированных результатов </h1>

<p> <strong> Основное тело функции </strong> </p>

<p> Здесь нет тонны; основной элемент функции объявляет входные данные (которые должны соответствовать соответствующей функции SQL), устанавливает соединение SQL и открывает SQLReader. </p>

<pre class = //Функция SQL CLR для свертывания SUM на AdventureWorks2012.Production.TransactionHistory [SqlFunction (DataAccess = DataAccessKind.Read,     FillRowMethodName = "RollingSum_Fill",     TableDefinition = "ProductId INT, TransactionDate DATETIME, ReferenceOrderID INT", +                       «ActualCost FLOAT, PrevCumulativeSum FLOAT, RollingSum FLOAT»)] public static IEnumerable RollingSumTvf (SqlInt32 rollPeriodDays) {     using (var connection = new SqlConnection ("context connection = true;")) {         connection.Open ();         Список & л; TrxnRollingSum & GT; trxns;         используя (var cmd = connection.CreateCommand ()) {             //Прочитайте историю транзакций (обратите внимание: порядок важен!)             cmd.CommandText = @ "SELECT ProductId, TransactionDate, ReferenceOrderID,                                     CAST (ActualCost AS FLOAT) AS ActualCost                                 FROM Production.TransactionHistory                                 ORDER BY ProductId, TransactionDate ";             используя (var reader = cmd.ExecuteReader ()) {                 trxns = ComputeRollingSums (читатель, rollPeriodDays.Value);             }         }         return trxns;     } }

Основная логика

Я выделил основную логику, так что легче сосредоточиться на:

  //Учитывая SqlReader с данными истории транзакций, вычисляет /возвращает скользящие суммы
частный статический список <TrxnRollingSum> ComputeRollingSums (считыватель SqlDataReader,
                                                        int rollingPeriodDays) {
    var startIndexOfRollingPeriod = 0;
    var rollingSumIndex = 0;
    var trxns = new List <TrxnRollingSum> ();

    //До цикла, инициализировать «next», чтобы быть первой транзакцией
    var nextTrxn = GetNextTrxn (читатель, null);
    while (nextTrxn! = null)
    {
        var currTrxn = nextTrxn;
        nextTrxn = GetNextTrxn (reader, currTrxn);
        trxns.Add (currTrxn);

        //Если следующая транзакция - это не тот же продукт /дата, что и текущая
        //транзакция, мы можем завершить расчетную сумму для текущей транзакции
        //и все предыдущие транзакции для одного продукта /даты
        варfinalizeRollingSum = nextTrxn == null || (nextTrxn! = null & amp; & amp;
                                (currTrxn.ProductId! = nextTrxn.ProductId ||
                                currTrxn.TransactionDate! = nextTrxn.TransactionDate));
        if (finalizeRollingSum)
        {
            //Переместите указатель на первую транзакцию (для одного и того же продукта)
            //что происходит в течение периода прокатки
            while (startIndexOfRollingPeriod <trxns.Count
                & Amp; & Amp; trxns [startIndexOfRollingPeriod] .TransactionDate & lt;
                    currTrxn.TransactionDate.AddDays (-1 * rollPeriodDays))
            {
                startIndexOfRollingPeriod ++;
            }

            //Вычислить скользящую сумму как совокупную сумму (для этого продукта),
            //минус кумулятивная сумма до начала поворотного окна
            var sumPriorToWindow = trxns [startIndexOfRollingPeriod] .PrevSum;
            var rollingSum = currTrxn.ActualCost + currTrxn.PrevSum - sumPriorToWindow;
            //Заполните скользящую сумму для всех транзакций, совместно использующих этот продукт /дату
            в то время как (rollSumIndex <trxns.Count)
            {
                trxns [rollSumIndex ++]. RollingSum = rollSum;
            }
        }

        //Если это последняя транзакция для этого продукта, сбросьте период прокачки
        if (nextTrxn! = null & amp; & quot; currTrxn.ProductId! = nextTrxn.ProductId)
        {
            startIndexOfRollingPeriod = trxns.Count;
        }
    }

    return trxns;
}
 

Помощники

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

  private static TrxnRollingSum GetNextTrxn (SqlDataReader r, TrxnRollingSum currTrxn) {
    TrxnRollingSum nextTrxn = null;
    если (r.Read ()) {
        nextTrxn = new TrxnRollingSum {
            ProductId = r.GetInt32 (0),
            TransactionDate = r.GetDateTime (1),
            ReferenceOrderId = r.GetInt32 (2),
            ActualCost = r.GetDouble (3),
            PrevSum = 0};
        if (currTrxn! = null) {
            nextTrxn.PrevSum = (nextTrxn.ProductId == currTrxn.ProductId)
                    ? currTrxn.PrevSum + currTrxn.ActualCost: 0;
        }
    }
    return nextTrxn;
}

//Представляет возвращаемый результат
//Обратите внимание, что поля ReferenceOrderId /PrevSum предназначены только для отладки
частный класс TrxnRollingSum {
    public int ProductId {get; задавать; }
    public DateTime TransactionDate {get; задавать; }
    public int ReferenceOrderId {get; задавать; }
    public double ActualCost {get; задавать; }
    public double PrevSum {get; задавать; }
    public double RollingSum {get; задавать; }
}

//Функция, которая генерирует данные результата для каждой строки
//(Такая функция обязательна для табличных функций SQL CLR)
public static void RollingSum_Fill (объект trxnWithRollingSumObj,
                                    out int productId,
                                    out DateTime transactionDate,
                                    out int referenceOrderId, вне double actualCost,
                                    out double prevCumulativeSum,
                                    out double rollingSum) {
    var trxn = (TrxnRollingSum) trxnWithRollingSumObj;
    productId = trxn.ProductId;
    transactionDate = trxn.TransactionDate;
    referenceOrderId = trxn.ReferenceOrderId;
    actualCost = trxn.ActualCost;
    prevCumulativeSum = trxn.PrevSum;
    rollSum = trxn.RollingSum;
}
 

Связывание всего этого в SQL

Все до этого момента было на C #, поэтому давайте посмотрим на фактический SQL. (В качестве альтернативы вы можете использовать этот сценарий развертывания для создания сборки непосредственно из битов моей сборки, а не для компиляции. )

  USE AdventureWorks2012; /* GPATTERSON2 \ SQL2014DEVELOPER * /
ИДТИ

- Включить CLR
EXEC sp_configure 'clr enabled', 1;
ИДТИ
перенастроить;
ИДТИ

- Создать сборку на основе DLL, сгенерированной путем компиляции проекта CLR
- Я также включил версию «сборных бит», которая может быть запущена без компиляции
СОЗДАТЬ СБОРКУ ClrPlayground
- См. Http://pastebin.com/dfbv1w3z для версии «from bits bits»
FROM 'C: \ FullPathGoesHere \ ClrPlayground \ bin \ Debug \ ClrPlayground.dll'
WITH PERMISSION_SET = безопасно;
ИДТИ

--Создать функцию из сборки
CREATE FUNCTION dbo.RollingSumTvf (@rollingPeriodDays INT)
RETURNS TABLE (ProductId INT, TransactionDate DATETIME, ReferenceOrderID INT,
                ActualCost FLOAT, PrevCumulativeSum FLOAT, RollingSum FLOAT)
- Функция дает строки по порядку, поэтому пусть SQL Server знает, чтобы избежать лишней сортировки
ORDER (ProductID, TransactionDate, ReferenceOrderID)
AS EXTERNAL NAME ClrPlayground.UserDefinedFunctions.RollingSumTvf;
ИДТИ

- Теперь мы можем использовать TVF!
ВЫБРАТЬ *
ИЗdbo.RollingSumTvf (45)
ORDER BY ProductId, TransactionDate, ReferenceOrderId
ИДТИ
 

<сильный> Предостережение

Подход CLR обеспечивает гораздо большую гибкость для оптимизации алгоритма, и его, возможно, еще больше настроит эксперт на C #. Тем не менее, есть и минусы стратегии CLR. Несколько вещей, которые нужно иметь в виду:

  • Этот подход CLR сохраняет копию набора данных в памяти. Можно использовать потоковый подход, но я столкнулся с первоначальными трудностями и обнаружил, что существует выдающийся Connect , жалуясь на то, что изменения в SQL 2008+ затрудняют использование такого подхода. Это все еще возможно (как показывает Павел), но требует более высокого уровня разрешений путем установки базы данных как TRUSTWORTHY и предоставления EXTERNAL_ACCESS для сборки CLR. Таким образом, есть некоторые проблемы и потенциальные последствия для безопасности, но выигрыш - это потоковый подход, который может лучше масштабироваться для гораздо больших наборов данных, чем на AdventureWorks.
  • CLR может быть менее доступным для некоторых администраторов баз данных, что делает такую ​​функцию более черной, которая не является такой прозрачной, не так легко модифицируется, не так легко развертывается и, возможно, не так легко отлаживается. Это довольно большой недостаток по сравнению с подходом T-SQL.


Бонус: T-SQL # 2 - практический подход, который я использовал бы

Попытавшись некоторое время подумать о проблеме творчески, я подумал, что также опубликую довольно простой и практичный способ, которым я, скорее всего, захочу решить эту проблему, если она возникла в моей повседневной работе. Он использует функциональные возможности окна SQL 2012+, но не в том виде, в котором этот вопрос был надежен:

  - вычислить все текущие затраты в таблице #temp; Обратите внимание, что этот запрос можно просто прочитать
- от Production.TransactionHistory, но CROSS APPLY по продукту позволяет окно
- функция, которая должна вычисляться независимо для каждого продукта, поддерживая параллельный план запроса
SELECT t. *
INTO #runningCosts
FROM Production.Product p
CROSS APPLY (
    SELECT t.ProductId, t.TransactionDate, t.ReferenceOrderId, t.ActualCost,
        - Запуск суммы стоимости этого продукта, включая все ссылки на TransactionDate
        SUM (t.ActualCost) OVER (
            ORDER BY t.TransactionDate
            ДИАПАЗОН НЕОБХОДИМО ПРЕДОСТАВЛЯЕТСЯ) AS RunningCost
    FROM Production.TransactionHistory t
    WHERE t.ProductId = p.ProductId
) t
ИДТИ

- Введите таблицу в нашем порядке вывода
ALTER TABLE #runningCosts
ДОБАВИТЬ ПЕРВИЧНЫЙ КЛЮЧ (ProductId, TransactionDate, ReferenceOrderId)
ИДТИ

SELECT r.ProductId, r.TransactionDate, r.ReferenceOrderId, r.ActualCost,
    - Совокупная эксплуатационная стоимость - текущая стоимость до раздвижного окна
    r.RunningCost - ISNULL (w.RunningCost, 0) AS RollingSum45
FROM #runningCosts r
ВНЕШНЕЕ ПРИМЕНЕНИЕ (
    - Для каждой транзакции найдите текущую стоимость непосредственно перед началом скользящего окна
    SELECT TOP 1 b.RunningCost
    FROM #runningCosts b
    WHERE b.ProductId = r.ProductId
        И b.TransactionDate <DATEADD (DAY, -45, r.TransactionDate)
    ORDER BY b.TransactionDate DESC
) w
ORDER BY r.ProductId, r.TransactionDate, r.ReferenceOrderId
ИДТИ
 

Это фактически дает довольно простой общий план запроса, даже если вы смотрите на оба из двух соответствующих планов запросов вместе:

 введите описание изображения здесь>> </a>
<a href= «введите

Несколько причин мне нравится такой подход:

  • Он дает полный набор результатов, запрошенный в описании проблемы (в отличие от большинства других решений T-SQL, которые возвращают сгруппированную версию результатов).
  • Легко объяснить, понять и отладить; Я не вернусь через год и задаюсь вопросом, как я могу сделать небольшое изменение, не нарушая правильности или производительности.
  • Он работает в 900ms в предоставленном наборе данных, а не в 2700ms исходного поиска цикла
  • Если данные были намного плотнее (больше транзакций в день), сложность вычислений не растет квадратично с количеством транзакций в скользящем окне (как и для исходного запроса); Я думаю, что это касается части заботы Павла о том, что нужно избегать многократного сканирования.
  • Это приводит к по существу отсутствию ввода /вывода tempdb в последних обновлениях SQL 2012+ из-за новая функция tempdb lazy write
  • Для очень больших наборов данных тривиально разбивать работу на отдельные партии для каждого продукта, если давление памяти должно стать проблемой.

Пара потенциальныхпредостережения:

  • Пока он технически сканирует Production.TransactionHistory только один раз, это не по-настоящему подход «одного сканирования», потому что таблица #temp аналогичного размера и должна также выполнять дополнительные логические операции ввода-вывода в этой таблице. Тем не менее, я не вижу, чтобы это слишком отличалось от рабочего стола, что мы имеем более ручное управление, поскольку мы определили его точную структуру.
  • В зависимости от вашей среды использование tempdb можно рассматривать как положительное (например, оно находится на отдельном наборе дисков SSD) или отрицательное (высокий уровень параллелизма на сервере, много конфликтов tempdb)
ответил Geoff Patterson 8 thEurope/Moscowp30Europe/Moscow09bEurope/MoscowTue, 08 Sep 2015 18:41:26 +0300 2015, 18:41:26
22

Это длинный ответ, поэтому я решил добавить здесь краткое описание.

  • Сначала я представляю решение, которое производит точно такой же результат в том же порядке, что и в вопросе. Он сканирует основную таблицу 3 раза: чтобы получить список ProductIDs с диапазоном дат для каждого Продукта, суммировать затраты на каждый день (поскольку существует несколько транзакций с одинаковыми датами), чтобы результат объединения с исходными строками.
  • Далее я сравниваю два подхода, упрощающих задачу, и избегаю последнего сканирования основной таблицы. Их результат - ежедневное резюме, т. Е. Если несколько транзакций на Продукте имеют одинаковую дату, они свертываются в одну строку. Мой подход с предыдущего шага дважды проверяет таблицу. Подход Джеффа Паттерсона просматривает таблицу один раз, потому что он использует внешние знания о диапазоне дат и списка Продуктов.
  • Наконец, я представляю однопроходное решение, которое снова возвращает ежедневное резюме, но оно не требует внешних знаний о диапазоне дат или списка ProductIDs .

Я буду использовать базу данных AdventureWorks2014 и SQL Server Express 2014.

Изменения в исходной базе данных:

  • Изменен тип [Production]. [TransactionHistory]. [TransactionDate] из datetime в date . В любом случае компонент времени был равен нулю.
  • Добавлен таблица календаря [DBO]. [Календарь]
  • Добавлен индекс в [Production]. [TransactionHistory]

.

  CREATE TABLE [dbo]. [Календарь]
(
    [dt] [date] NOT NULL,
    CONSTRAINT [PK_Calendar] ОСНОВНАЯ КЛАВИАТУРА
(
    [dt] ASC
))

СОЗДАТЬ УНИКАЛЬНЫЙ НЕПРЕРЫВНЫЙ ИНДЕКС [i] ВКЛ [Производство]. [TransactionHistory]
(
    [ProductID] ASC,
    [TransactionDate] ASC,
    [ReferenceOrderID] ASC
)
ВКЛЮЧИТЬ ([ActualCost])

- Запустить таблицу календаря
INSERT INTO dbo.Calendar (dt)
SELECT TOP (50000)
    DATEADD (день, ROW_NUMBER () OVER (ORDER BY s1. [Object_id]) - 1, '2000-01-01') AS dt
FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
ВАРИАНТ (MAXDOP 1);
 

Статья MSDN о OVER статье имеет ссылку на отличное сообщение в блоге о функциях окна от Itzik Ben-Gan. В этой статье он объясняет, как работает OVER , разница между параметрами ROWS и RANGE и упоминает эту самую проблему расчета текущей суммы за дату ассортимент. Он упоминает, что текущая версия SQL Server не реализует RANGE в полном объеме и не использует временные интервальные типы данных. Его объяснение разницы между ROWS и RANGE дало мне представление.

Даты без пробелов и дубликатов

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

  SELECT
    TH.ProductID,
    TH.TransactionDate,
    TH.ActualCost,
    RollingSum45 = SUM (TH.ActualCost) OVER (
        РАЗДЕЛЕНИЕ TH.ProductID
        ЗАКАЗАТЬ НА TH.TransactionDate
        ROWS МЕЖДУ
            45 ПРЕОБРАЗОВАНИЕ
            И ТЕКУЩАЯ РУКА)
FROM Production.TransactionHistory AS TH
СОРТИРОВАТЬ ПО
    TH.ProductID,
    TH.TransactionDate,
    TH.ReferenceOrderID;
 

Действительно, окно из 45 строк будет охватывать ровно 45 дней.

Даты с пробелами без дубликатов

К сожалению, наши данные имеют пробелы в датах. Чтобы решить эту проблему, мы можем использовать таблицу Calendar , чтобы сгенерировать набор дат без пробелов, затем LEFT JOIN исходные данные для этого набора и использовать тот же запрос с помощью ROWS BREWEEN 45 PRECEDING AND CURRENT ROW . Это даст правильные результаты, только если даты не повторяются (в пределах того же ProductID ).

Даты с пробелами с дубликатами

К сожалению, наши данные имеют как пробелы в датах, так и даты могут повторяться в пределах того же ProductID . Чтобы решить эту проблему, мы можем GROUP исходные данные с помощью ProductID, TransactionDate сгенерировать набор дат без дубликатов. Затем используйте таблицу Calendar , чтобы создать набор дат без пробелов. Затем мы можем использовать запрос с ROWS BETWEEN 45 PRECEDING AND CURRENT ROW , чтобы вычислить кастинг SUM . Это даст правильные результаты. См. Комментарии в запросе ниже.

  С

- рассчитать начальные и конечные даты для каждого продукта
CTE_Products
В ВИДЕ
(
    SELECT TH.ProductID
        , MIN (TH.TransactionDate) AS MinDate
        , MAX (TH.TransactionDate) AS MaxDate
    ИЗ[Производство]. [TransactionHistory] AS TH
    ГРУППА ПО TH.ProductID
)

- генерировать набор дат без пробелов для каждого продукта
, CTE_ProductsWithDates
В ВИДЕ
(
    SELECT CTE_Products.ProductID, C.dt
    ИЗ
        CTE_Products
        INNER JOIN dbo.Calendar AS C ON
            C.dt> = CTE_Products.MinDate AND
            C.dt <= CTE_Products.MaxDate
)

- генерировать набор дат без дубликатов для каждого продукта;
- также рассчитать суточную стоимость
, CTE_DailyCosts
В ВИДЕ
(
    SELECT TH.ProductID, TH.TransactionDate, SUM (ActualCost) AS DailyActualCost
    FROM [Производство]. [TransactionHistory] AS TH
    GROUP BY TH.ProductID, TH.TransactionDate
)

- рассчитать скользящую сумму за 45 дней
, CTE_Sum
В ВИДЕ
(
    ВЫБРАТЬ
        CTE_ProductsWithDates.ProductID
        , CTE_ProductsWithDates.dt
        , CTE_DailyCosts.DailyActualCost
        , SUM (CTE_DailyCosts.DailyActualCost) OVER (
            PARTITION by CTE_ProductsWithDates.ProductID
            ORDER BY CTE_ProductsWithDates.dt
            ROWS BREWEEN 45 PRECEDING AND CURRENT ROW) AS RollingSum45
    ИЗ
        CTE_ProductsWithDates
        LEFT JOIN CTE_DailyCosts ON
            CTE_DailyCosts.ProductID = CTE_ProductsWithDates.ProductID AND
            CTE_DailyCosts.TransactionDate = CTE_ProductsWithDates.dt
)

- удалить строки, которые были добавлены календарем, которые заполняют пробелы в датах
- добавить повторяющиеся дубликаты дат, которые были удалены GROUP BY
ВЫБРАТЬ
    TH.ProductID
    , TH.TransactionDate
    , TH.ActualCost
    , CTE_Sum.RollingSum45
ИЗ
    [Производство]. [TransactionHistory] AS TH
    INNER JOIN CTE_Sum ON
        CTE_Sum.ProductID = TH.ProductID AND
        CTE_Sum.dt = TH.TransactionDate
СОРТИРОВАТЬ ПО
    TH.ProductID
    , TH.TransactionDate
    , TH.ReferenceOrderID
;
 

Я подтвердил, что этот запрос дает те же результаты, что и подход из вопроса, который использует подзапрос.

Планы выполнения

 stats

Первый запрос использует подзапрос, второй - этот подход. Вы можете видеть, что продолжительность и количество чтений намного меньше в этом подходе. Большинство расчетных затрат в этом подходе является окончательным ORDER BY , см. Ниже.

 subquery

Подзапрос имеет простой план с вложенными циклами и сложностью O (n * n) .

 over

Планирование этого подхода несколько раз проверяет TransactionHistory , но нет циклов. Как вы видите, более 70% оценочной стоимости - это Sort для окончательного ORDER BY .

 io

Верхний результат - subquery , внизу - OVER .


Предотвращение дополнительных сканирований

Последнее сканирование индексов, объединение и сортировка в указанном выше плане вызвано окончательным INNER JOIN с исходной таблицей, чтобы сделать конечный результат точно таким же, как медленный подход с подзапросом. Количество возвращаемых строк такое же, как в таблице TransactionHistory . Существуют строки в TransactionHistory , когда в тот же день для одного и того же продукта произошло несколько транзакций. Если в результате будет показано только ежедневное резюме, то этот окончательный JOIN можно удалить, и запрос станет немного проще и немного быстрее. Последнее индексирование, объединение и сортировка по предыдущему плану заменяются фильтром, который удаляет строки, добавленные Calendar .

  С
- два сканирования
- рассчитать начальные и конечные даты для каждого продукта
CTE_Products
В ВИДЕ
(
    SELECT TH.ProductID
        , MIN (TH.TransactionDate) AS MinDate
        , MAX (TH.TransactionDate) AS MaxDate
    FROM [Производство]. [TransactionHistory] AS TH
    ГРУППА ПО TH.ProductID
)

- генерировать набор дат без пробелов для каждого продукта
, CTE_ProductsWithDates
В ВИДЕ
(
    SELECT CTE_Products.ProductID, C.dt
    ИЗ
        CTE_Products
        INNER JOIN dbo.Calendar AS C ON
            C.dt> = CTE_Products.MinDate AND
            C.dt <= CTE_Products.MaxDate
)

- генерировать набор дат без дубликатов для каждого продукта;
- также рассчитать суточную стоимость
, CTE_DailyCosts
В ВИДЕ
(
    SELECT TH.ProductID, TH.TransactionDate, SUM (ActualCost) AS DailyActualCost
    FROM [Производство]. [TransactionHistory] AS TH
    GROUP BY TH.ProductID, TH.TransactionDate
)

- рассчитать скользящую сумму за 45 дней
, CTE_Sum
В ВИДЕ
(
    ВЫБРАТЬ
        CTE_ProductsWithDates.ProductID
        , CTE_ProductsWithDates.dt
        , CTE_DailyCosts.DailyActualCost
        , SUM (CTE_DailyCosts.DailyActualCost) OVER (
            PARTITION by CTE_ProductsWithDates.ProductID
            СОРТИРОВАТЬ ПОCTE_ProductsWithDates.dt
            ROWS BREWEEN 45 PRECEDING AND CURRENT ROW) AS RollingSum45
    ИЗ
        CTE_ProductsWithDates
        LEFT JOIN CTE_DailyCosts ON
            CTE_DailyCosts.ProductID = CTE_ProductsWithDates.ProductID AND
            CTE_DailyCosts.TransactionDate = CTE_ProductsWithDates.dt
)

- удалить строки, которые были добавлены календарем, которые заполняют пробелы в датах
ВЫБРАТЬ
    CTE_Sum.ProductID
    , CTE_Sum.dt AS TransactionDate
    , CTE_Sum.DailyActualCost
    , CTE_Sum.RollingSum45
FROM CTE_Sum
WHERE CTE_Sum.DailyActualCost НЕ НУЛЛ
СОРТИРОВАТЬ ПО
    CTE_Sum.ProductID
    , CTE_Sum.dt
;
 

 two-scan

Тем не менее, TransactionHistory проверяется дважды. Для получения диапазона дат для каждого продукта требуется дополнительное сканирование. Мне было интересно посмотреть, как это сравнивается с другим подходом, где мы используем внешние знания о глобальном диапазоне дат в TransactionHistory , а также дополнительную таблицу Product , которая имеет все ProductIDs , чтобы избежать дополнительного сканирования. Я удалил расчет количества транзакций в день из этого запроса, чтобы сделать сравнение действительным. Он может быть добавлен в обоих запросах, но я хотел бы сохранить его простым для сравнения. Мне также пришлось использовать другие даты, потому что я использую версию базы данных 2014.

  DECLARE @minAnalysisDate DATE = '2013-07-31',
- Настраиваемая дата начала в зависимости от потребностей бизнеса
        @maxAnalysisDate DATE = '2014-08-03'
- Настраиваемая дата окончания в зависимости от потребностей бизнеса
ВЫБРАТЬ
    - одно сканирование
    ProductID, TransactionDate, ActualCost, RollingSum45
-, NumOrders
ИЗ (
    SELECT ProductID, TransactionDate,
    --NumOrders,
    Действительная цена,
        SUM (ActualCost) OVER (
                РАЗДЕЛЕНИЕ ПО ТОВАРНЫМ ЗАКАЗАМ TransactionDate
                ROWS МЕЖДУ 45 ПРЕДВАРИТЕЛЬНОЙ И ТЕКУЩЕЙ РУКОЙ
            ) AS RollingSum45
    ИЗ (
        - Полный перекрестный продукт продуктов и дат,
        - в сочетании с фактической информацией о расходах для этого продукта /даты
        SELECT p.ProductID, c.dt AS TransactionDate,
            --COUNT (TH.ProductId) AS NumOrders,
            SUM (TH.ActualCost) AS ActualCost
        FROM Production.Product p
        JOIN dbo.calendar c
            ON c.dt BETWEEN @minAnalysisDate AND @maxAnalysisDate
        ЛЕВЫЙ ВНЕШНИЙ ПРИСОЕДИНЕНИЕ Production.TransactionHistory TH
            ON TH.ProductId = p.productId
            И TH.TransactionDate = c.dt
        ГРУППА BY P.ProductID, c.dt
    ) aggsByDay
) rollSums
--WHERE NumOrders> 0
WHERE ActualCost NOT NULL
ORDER BY ProductID, TransactionDate
- MAXDOP 1, чтобы избежать параллельного сканирования, надувающего счетчик сканирования
ВАРИАНТ (MAXDOP 1);
 

 one-scan

Оба запроса возвращают один и тот же результат в том же порядке.

<сильный> Сравнение

Вот статистика времени и IO.

 stats2

 io2

Вариант с двумя сканированием немного быстрее и имеет меньшее количество чтений, поскольку вариант с одним сканированием должен много использовать Worktable. Кроме того, вариант с одним сканированием генерирует больше строк, чем нужно, как вы можете видеть в планах. Он генерирует даты для каждого ProductID , который находится в таблице Product , даже если ProductID не имеет транзакций. В таблице Product имеется 504 строки, но только 441 продукт имеет транзакции в TransactionHistory . Кроме того, он генерирует одинаковый диапазон дат для каждого продукта, что более чем необходимо. Если TransactionHistory имела более длинную общую историю, причем каждый отдельный продукт имел относительно короткую историю, количество лишних ненужных строк было бы еще выше.

С другой стороны, можно немного оптимизировать вариант с двумя вариантами сканирования, создав еще один, более узкий индекс только для (ProductID, TransactionDate) . Этот индекс будет использоваться для рассчитать начальные и конечные даты для каждого продукта ( CTE_Products ), и у него будет меньше страниц, чем покрытие индекса, и, как результат, меньше читает.

Итак, мы можем выбрать либо лишнее явное простое сканирование, либо иметь неявный рабочий стол.

BTW, если это нормально, чтобы получить результат только с ежедневными сводками, тогда лучше создать индекс, который не включает ReferenceOrderID . Он будет использовать меньше страниц => меньше IO.

  СОЗДАТЬ НЕУКАЗАННЫЙ ИНДЕКС [i2] ВКЛ [Производство]. [История транзакций]
(
    [ProductID] ASC,
    [TransactionDate] ASC
)
ВКЛЮЧИТЬ ([ActualCost])
 

Однопроходное решение с использованием CROSSПрименить

Это действительно длинный ответ, но вот еще один вариант, который снова возвращает только ежедневное резюме, но он выполняет только одно сканирование данных и не требует внешних знаний о диапазоне дат или списка ProductID. Он также не выполняет промежуточных сортировок. Общая производительность похожа на предыдущие варианты, хотя, кажется, немного хуже.

Основная идея - использовать таблицу чисел для генерации строк, которые заполнят пробелы в датах. Для каждой существующей даты используйте LEAD , чтобы рассчитать размер пробела в днях, а затем используйте CROSS APPLY , чтобы добавить необходимое количество строк в результирующий набор. Сначала я попробовал его с постоянной таблицей цифр. План показал большое количество чтений в этой таблице, хотя фактическая продолжительность была почти такой же, как когда я генерировал числа на лету, используя CTE .

  С
e1 (n) AS
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
) - 10
, e2 (n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b) - 10 * 10
, e3 (n) AS (SELECT 1 FROM e1 CROSS JOIN e2) - 10 * 100
, CTE_Numbers
В ВИДЕ
(
    SELECT ROW_NUMBER () OVER (ORDER BY n) AS Number
    FROM e3
)
, CTE_DailyCosts
В ВИДЕ
(
    ВЫБРАТЬ
        TH.ProductID
        , TH.TransactionDate
        , SUM (ActualCost) AS DailyActualCost
        , ISNULL (DATEDIFF (день,
            TH.TransactionDate,
            Свинец (TH.TransactionDate)
            OVER (PARTITION BY TH.ProductID ORDER BY TH.TransactionDate)), 1) AS DiffDays
    FROM [Производство]. [TransactionHistory] AS TH
    GROUP BY TH.ProductID, TH.TransactionDate
)
, CTE_NoGaps
В ВИДЕ
(
    ВЫБРАТЬ
        CTE_DailyCosts.ProductID
        , CTE_DailyCosts.TransactionDate
        , CASE WHEN CA.Number = 1
        THEN CTE_DailyCosts.DailyActualCost
        ELSE NULL END AS DailyCost
    ИЗ
        CTE_DailyCosts
        КРЕСТ ПРИМЕНЯЕТСЯ
        (
            SELECT TOP (CTE_DailyCosts.DiffDays) CTE_Numbers.Number
            FROM CTE_Numbers
            ORDER BY CTE_Numbers.Number
        ) AS CA
)
, CTE_Sum
В ВИДЕ
(
    ВЫБРАТЬ
        Код товара
        ,Дата сделки
        , DailyCost
        , SUM (DailyCost) OVER (
            РАЗДЕЛЕНИЕ по ProductID
            ORDER BY TransactionDate
            ROWS BREWEEN 45 PRECEDING AND CURRENT ROW) AS RollingSum45
    FROM CTE_NoGaps
)
ВЫБРАТЬ
    Код товара
    ,Дата сделки
    , DailyCost
    , RollingSum45
FROM CTE_Sum
WHERE DailyCost NOT NULL
СОРТИРОВАТЬ ПО
    Код товара
    ,Дата сделки
;
 

Этот план «длиннее», потому что запрос использует две функции окна ( LEAD и SUM ).

 cross apply

 ca stats

 ca io

ответил Vladimir Baranov 8 thEurope/Moscowp30Europe/Moscow09bEurope/MoscowTue, 08 Sep 2015 10:34:21 +0300 2015, 10:34:21
21

Альтернативное решение SQLCLR, которое выполняется быстрее и требует меньше памяти:

сценарий развертывания

Для этого требуется набор разрешений EXTERNAL_ACCESS , поскольку он использует loopback-соединение с целевым сервером и базой данных вместо (медленного) контекстного соединения. Вот как вызвать функцию:

  SELECT
    RS.ProductID,
    RS.TransactionDate,
    RS.ActualCost,
    RS.RollingSum45
От dbo.RollingSum
(
    N '. \ SQL2014', - Имя экземпляра
    N'AdventureWorks2012 '- Название базы данных
) AS RS
СОРТИРОВАТЬ ПО
    RS.ProductID,
    RS.TransactionDate,
    RS.ReferenceOrderID;
 

Производит точно такие же результаты в том же порядке, что и вопрос.

План выполнения:

 План выполнения SQLCLR TVF

 SQLCLR Исходный план выполнения запроса

 Статистика производительности плана проводника

Логические сообщения профайлера: 481

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

  1. Любые повторяющиеся строки (то же название продукта и транзакции). Это необходимо, потому что, пока ни продукт, ни дата не будут изменены, мы не знаем, какова будет конечная текущая сумма. В образце данных есть одна комбинация продукта и даты с 64 строками.
  2. Скользящий 45-дневный диапазон стоимости и даты транзакций, только для текущего продукта. Это необходимо для настройки простой текущей суммы для строк, которые выходят из 45-дневного скользящего окна.

Это минимальное кэширование должно гарантировать, что этот метод хорошо масштабируется; конечно, лучше, чем пытаться удерживать весь набор входных данных в памяти CLR.

Исходный код

ответил Paul White 14 thEurope/Moscowp30Europe/Moscow09bEurope/MoscowMon, 14 Sep 2015 13:03:04 +0300 2015, 13:03:04
14

Если вы используете 64-разрядную версию Enterprise, Developer или Evaluation для SQL Server 2014, вы можете использовать OLTP в памяти . Решение не будет одним сканированием и вряд ли будет использовать какие-либо функции окна, но может добавить какое-то значение в этот вопрос, и используемый алгоритм может быть использован как вдохновение для других решений.

Сначала вам нужно включить встроенную память OLTP в базе данных AdventureWorks.

  изменить базу данных AdventureWorks2014
  add filegroup InMem содержит memory_optimized_data;

изменить базу данных AdventureWorks2014
  добавить файл (name = 'AW2014_InMem',
            filename = 'D: \ SQL Server \ MSSQL12.MSSQLSERVER \ MSSQL \ DATA \ AW2014')
    to filegroup InMem;

изменить базу данных AdventureWorks2014
  set memory_optimized_elevate_to_snapshot = on;
 

Параметр процедуры - это переменная таблицы In-Memory, которая должна быть определена как тип.

  создать тип dbo.TransHistory как таблица
(
  ID int не равно нулю,
  ProductID int не null,
  TransactionDate datetime не равно null,
  ReferenceOrderID int не null,
  Фактические деньги не являются нулевыми,
  RunningTotal деньги не null,
  Деньги RollingSum45 не являются нулевыми,

  - Индекс, используемый во время цикла
  index IX_T1 некластеризованный хэш (ID) с (bucket_count = 1000000),

  - Используется для поиска текущей суммы, поскольку это было 45 дней назад (или больше)
  index IX_T2 некластеризованный (ProductID, TransactionDate desc)
) с (memory_optimized = on);
 

Идентификатор не уникален в этой таблице, он уникален для каждой комбинации ProductID и TransactionDate .

Есть несколько комментариев в процедуре, которые говорят вам, что она делает, но в целом она вычисляет текущее количество в цикле, и для каждой итерации он выполняет поиск текущей суммы, поскольку это было 45 дней назад (или больше).

Текущая текущая сумма минус общая сумма, как это было 45 дней назад, - это скользящая 45-дневная сумма, которую мы ищем.

  создать процедуру dbo.GetRolling45
  @TransHistory dbo.TransHistory readonly
с native_compilation, schemabinding, выполнить как владелец как
begin atom (уровень изоляции транзакции = моментальный снимок, язык = N'us_english)

  - Таблица для хранения результата
  объявить @TransRes dbo.TransHistory;

  - Переменная цикла
  объявить @ID int = 0;

  - Текущий идентификатор продукта
  declare @ProductID int = -1;

  - Предыдущий ProductID используется для перезапуска общей суммы
  объявить @PrevProductID int;

  - Текущая дата транзакции, используемая для получения общей суммы 45 дней назад (или более)
  объявлять @TransactionDate datetime;

  - Сумма фактической стоимости для группы ProductID и TransactionDate
  объявлять деньги @ActualCost;

  - Запуск всего до сих пор
  declare @RunningTotal money = 0;

  - Запуск всего, как было 45 дней назад (или больше)
  объявить @ RunningTotal45 money = 0;

  - Хотя цикл для каждого уникального появления комбинации ProductID, TransactionDate
  в то время как @ProductID> 0
  начать
    set @ID + = 1;
    set @PrevProductID = @ProductID;

    - Получить текущие значения
    выберите @ProductID = min (ProductID),
           @TransactionDate = min (TransactionDate),
           @ActualCost = sum (ActualCost)
    от @TransHistory
    где ID = @ID;

    если @ProductID> 0
    начать
      set @ RunningTotal45 = 0;

      если @ProductID> @PrevProductID
      начать
        - Новый продукт, сбрасывает общий итог
        set @RunningTotal = @ActualCost;
      конец
      еще
      начать
        - тот же продукт, что и последняя строка, суммарная общая сумма
        set @RunningTotal + = @ActualCost;

        - Получите текущую сумму, как это было 45 дней назад (или больше)
        select top (1) @ RunningTotal45 = TR.RunningTotal
        от @TransRes как TR
        где TR.ProductID = @ProductID и
              TR.TransactionDate <dateadd (день, -45, @TransactionDate)
        порядок по TR.TransactionDate desc;

      конец;

      - Добавить все строки, соответствующие ID, в таблицу результатов
      - RollingSum45 рассчитывается с использованием текущего текущего итога и текущей суммы, поскольку это было 45 дней назад (или больше)
      вставить в @TransRes (ID, ProductID, TransactionDate, ReferenceOrderID, ActualCost, RunningTotal, RollingSum45)
      выберите @ID,
             @Код товара,
             @Дата сделки,
             TH.ReferenceOrderID,
             TH.ActualCost,
             @RunningTotal,
             @RunningTotal - @ RunningTotal45
      от @TransHistory как TH
      где ID = @ID;

    конец
  конец;

  - вернуть таблицу результатов вызывающему
  выберите TR.ProductID, TR.TransactionDate, TR.ReferenceOrderID, TR.ActualCost, TR.RollingSum45
  от @TransRes как TR
  порядок по TR.ProductID, TR.TransactionDate, TR.ReferenceOrderID;

конец;
 

Вызовите процедуру следующим образом.

  - параметр хранимой процедуры GetRollingSum
объявить @T dbo.TransHistory;

- Загружать данные в таблицу вмятин
- ID уникален для каждой комбинации ProductID, TransactionDate
вставить в @T (ID,ProductID, TransactionDate, ReferenceOrderID, ActualCost, RunningTotal, RollingSum45)
выберите dense_rank () над (порядок от TH.ProductID, TH.TransactionDate),
       TH.ProductID,
       TH.TransactionDate,
       TH.ReferenceOrderID,
       TH.ActualCost,
       0,
       0
от Production.TransactionHistory как TH;

- Получите текущую 45-дневную сумму
exec dbo.GetRolling45 @T;
 

Тестирование этого на моем компьютере Отчеты о статистике клиентов a Общее время выполнения около 750 миллисекунд. Для сравнения версия суб-запроса занимает 3,5 секунды.

Дополнительные возможности:

Этот алгоритм может также использоваться обычным T-SQL. Вычислить текущее общее количество, используя range not rows, и сохранить результат в таблице temp. Затем вы можете запросить эту таблицу с самостоятельным присоединением к текущей сумме, как это было 45 дней назад, и рассчитать скользящую сумму. Тем не менее, реализация range по сравнению с rows довольно медленная из-за того, что нужно обрабатывать дубликаты порядка по предложению иначе, поэтому я не получил все это хорошо с этим подходом. Обходной путь к этому может заключаться в использовании другой функции окна, такой как last_value () , по расчетному запущенному итогу с использованием rows для имитации общей суммы range . Другой способ - использовать max () over () . У обоих были некоторые проблемы. Поиск соответствующего индекса для избежания сортировки и исключения катушек с версией max () over () . Я отказался от оптимизации этих вещей, но если вы заинтересованы в коде, который у меня есть, пожалуйста, дайте мне знать.

ответил Mikael Eriksson 15 thEurope/Moscowp30Europe/Moscow09bEurope/MoscowTue, 15 Sep 2015 15:38:42 +0300 2015, 15:38:42
12

Хорошо, это было весело :) Мое решение немного медленнее, чем @ GeoffPatterson, но частью этого является тот факт, что я привязываюсь к исходной таблице, чтобы исключить одно из предположений Джеффа (то есть одна строка для каждого продукта /дата пары). Я пошел с предположением, что это упрощенная версия окончательного запроса и может потребовать дополнительную информацию из исходной таблицы.

Примечание. Я заимствую таблицу календаря Джеффа и на самом деле оказался очень похожим решением:

  - Создать таблицу календаря для 2000 ~ 2020
CREATE TABLE dbo.calendar (d DATETIME NOT NULL CONSTRAINT PK_calendar ПЕРВИЧНЫЙ КЛЮЧ)
ИДТИ
DECLARE @d DATETIME = '1/1/2000'
WHILE (@d <'1/1/2021')
НАЧАТЬ
    INSERT INTO dbo.calendar (d) ЦЕННОСТИ (@d)
    SELECT @d = DATEADD (ДЕНЬ, 1, @d)
КОНЕЦ
 

Вот сам запрос:

  WITH myCTE AS (SELECT PP.ProductID, calendar.d AS TransactionDate,
                    SUM (ActualCost) AS CostPerDate
                ОТ производства.Продукт PP
                Календарь CROSS JOIN
                ЛЕВЫЙ ВНУТРЕННИЙ ПРИСОЕДИНЕНИЕ Production.TransactionHistory PTH
                    ON PP.ProductID = PTH.ProductID
                    AND calendar.d = PTH.TransactionDate
                CROSS APPLY (SELECT MAX (TransactionDate) AS EndDate,
                                MIN (TransactionDate) AS StartDate
                            FROM Production.TransactionHistory) AS Границы
                WHERE calendar.d МЕЖДУ Boundaries.StartDate AND Boundaries.EndDate
                GROUP BY PP.ProductID, calendar.d),
    RunningTotal AS (
        SELECT ProductId, TransactionDate, CostPerDate AS TBE,
                SUM (myCTE.CostPerDate) OVER (
                    РАЗДЕЛЕНИЕ myCTE.ProductID
                    ORDER BY myCTE.TransactionDate
                    ROWS МЕЖДУ
                        45 ПРЕОБРАЗОВАНИЕ
                        И ТЕКУЩАЯ РУКА) AS RollingSum45
        FROM myCTE)
ВЫБРАТЬ
    TH.ProductID,
    TH.TransactionDate,
    TH.ActualCost,
    RollingSum45
FROM Production.TransactionHistory AS TH
Присоединиться к RunningTotal
    ON TH.ProductID = RunningTotal.ProductID
    И TH.TransactionDate = RunningTotal.TransactionDate
WHERE RunTotal.TBE НЕ НУЛЛ
СОРТИРОВАТЬ ПО
    TH.ProductID,
    TH.TransactionDate,
    TH.ReferenceOrderID;
 

В основном я решил, что самый простой способ справиться с этим - использовать параметр для предложения ROWS. Но это требовало, чтобы у меня была только одна строка в комбинации ProductID , TransactionDate , а не только, но мне приходилось иметь одну строку в ProductID и возможная дата . Я сделал это, комбинируя таблицы Product, calendar и TransactionHistory в CTE. Затем мне пришлось создать еще один CTE для генерации скользящей информации. Я должен был это сделать, потому что, если бы я присоединился к нему непосредственно в исходной таблице, я получил исправление строки, которое отбросило мои результаты. После этого было просто подключиться ко второму CTE обратно к исходной таблице. Я добавил столбец TBE (который нужно устранить), чтобы избавиться от строк blank , созданных в CTE. Также я использовал CROSS APPLY в исходном CTE для создания границ для моей таблицы календаря.

Затем я добавил рекомендуемый индекс:

  СОЗДАТЬ НЕПРЕРЫВНЫЙ ИНДЕКС [TransactionHistory_IX1]
ON [Производство]. [TransactionHistory] ([TransactionDate])
INCLUDE ([ProductID], [ReferenceOrderID], [ActualCost])
 

И получил окончательный план выполнения:

 введите описание изображения здесь>> </a>
<a href= введите описание изображения здесь введите описание изображения здесь

EDIT: В конце я добавил индекс в таблицу календаря, который ускорил производительность с разумным размахом.

  CREATE INDEX ix_calendar ON calendar (d)
 
ответил Kenneth Fisher 10 thEurope/Moscowp30Europe/Moscow09bEurope/MoscowThu, 10 Sep 2015 19:34:51 +0300 2015, 19:34:51
2
Дата сделки ) ВЫБРАТЬ Код товара, Дата сделки, Действительная цена, RollingSum45 ИЗ (     ВЫБРАТЬ     TH.ProductID,     TH.ActualCost,     t.TransactionDate,     SUM (t.ActualCost) OVER (PARTITION BY TH.ProductID ORDER BY t.TransactionDate, t.OrderFlag) AS RollingSum45,     t.OrderFlag,     t.FilterFlag - определить этот столбец, чтобы избежать другого сортировки в конце     ОТ THGrouped AS TH     CROSS APPLY (         ЗНАЧЕНИЯ         (TH.ActualCost, TH.TransactionDate, 1, 0),         (-1 * TH.ActualCost, DATEADD (DAY, 46, TH.TransactionDate), 0, 1)     ) t (ActualCost, TransactionDate, OrderFlag, FilterFlag) ) tt WHERE tt.FilterFlag = 0 СОРТИРОВАТЬ ПО tt.ProductID, tt.TransactionDate, tt.OrderFlag ВАРИАНТ (MAXDOP 1);

На моей машине это заняло 702 мс процессорного времени с индексом покрытия и 734 мс процессорного времени без индекса. План запроса можно найти здесь: https://www.brentozar.com/pastetheplan/? ID = SJdCsGVSl

Единственным недостатком этого решения является то, что при упорядочивании с помощью нового столбца TransactionDate появляется неизбежный вид. Я не думаю, что этот вид можно разрешить, добавив индексы, потому что перед выполнением заказа мы должны объединить две копии данных. Я смог избавиться от сортировки в конце запроса, добавив в другой столбец ORDER BY. Если я заказал FilterFlag , я обнаружил, что SQL Server оптимизирует этот столбец из сортировки и будет выполнять явный вид.

Решения для того, когда нам нужно вернуть набор результатов с повторяющимися значениями TransactionDate для одного и того же ProductId , были намного сложнее. Я бы подвел итог этой проблеме, так как одновременно нужно было разделить и упорядочить по тому же столбцу. Синтаксис, предложенный Павлом, разрешает эту проблему, поэтому неудивительно, что так сложно выразить текущими функциями окна, доступными в SQL Server (если было бы трудно выразить, нет необходимости расширять синтаксис).

Если я использую вышеуказанный запрос без группировки, тогда я получаю разные значения для текущей суммы, когда имеется несколько строк с тем же ProductId и TransactionDate . Один из способов решить эту проблему - выполнить тот же расчет текущей суммы, что и выше, а также отметить последнюю строку в разделе. Это можно сделать с помощью LEAD (при условии, что ProductID никогда не является NULL) без дополнительной сортировки. Для конечного значения текущей суммы я использую MAX как функцию окна, чтобы применить значение в последней строке раздела ко всем строкам раздела.

  SELECT
Код товара,
Дата сделки,
ReferenceOrderID,
Действительная цена,
MAX (CASE WHEN LasttRowFlag = 1 THEN RollingSum ELSE NULL END) OVER (PARTITION by ProductID, TransactionDate) RollingSum45
ИЗ
(
    ВЫБРАТЬ
    TH.ProductID,
    TH.ActualCost,
    TH.ReferenceOrderID,
    t.TransactionDate,
    SUM (t.ActualCost) OVER (PARTITION BY TH.ProductID ORDER BY t.TransactionDate, t.OrderFlag, TH.ReferenceOrderID) RollingSum,
    CASE WHEN LEAD (TH.ProductID) OVER (PARTITION TH.ProductID, t.TransactionDate ORDER BY t.OrderFlag, TH.ReferenceOrderID) IS NULL THEN 1 ELSE 0 END LasttRowFlag,
    t.OrderFlag,
    t.FilterFlag - определить этот столбец, чтобы избежать другого сортировки в конце
    FROM Production.TransactionHistory AS TH
    CROSS APPLY (
        ЗНАЧЕНИЯ
        (TH.ActualCost, TH.TransactionDate, 1, 0),
        (-1 * TH.ActualCost, DATEADD (DAY, 46, TH.TransactionDate), 0, 1)
    ) t (ActualCost, TransactionDate, OrderFlag, FilterFlag)
) tt
WHERE tt.FilterFlag = 0
СОРТИРОВАТЬ ПО
tt.ProductID,
tt.TransactionDate,
tt.OrderFlag,
tt.ReferenceOrderID
ВАРИАНТ (MAXDOP 1);
 

На моей машине это заняло 2464 мс процессорного времени без индекса покрытия. Как и прежде, кажется неизбежным. План запроса можно найти здесь: https://www.brentozar.com/pastetheplan/? ID = HyWxhGVBl

Я думаю, что есть место для улучшения вышеупомянутого запроса. Существуют, конечно, другие способы использования функций Windows для получения желаемого результата.

ответил Joe Obbish 30 FriEurope/Moscow2016-12-30T20:49:14+03:00Europe/Moscow12bEurope/MoscowFri, 30 Dec 2016 20:49:14 +0300 2016, 20:49:14

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

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132