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

Представление моей таблицы:

CREATE TABLE Sales 
    (
     id int identity primary key, 
     SaleAmount numeric(10,2)
    );

DECLARE @i INT;
SELECT @i = 1;
SET NOCOUNT ON
WHILE @i <= 100
BEGIN
    INSERT INTO Sales VALUES (ABS(CHECKSUM(NEWID()))/10000000.0 );
    SELECT @i = @i + 1;
END;
SET NOCOUNT OFF

Мне нужно упорядочить мою таблицу Sales по SaleAmount, а затем выбрать все записи, в которых промежуточная сумма SaleAmount не превышает X.

Для этого в настоящее время я использую временную таблицу, чтобы сначала отсортировать записи, а затем выбрать записи, в которых промежуточная сумма меньше или равна X (в этом примере 10).

CREATE TABLE #TEMP_TABLE 
    (
      ID integer IDENTITY PRIMARY KEY, 
      SaleAmount numeric(10,2)
    );

INSERT INTO #TEMP_TABLE 
(SaleAmount)
SELECT SaleAmount FROM Sales
ORDER BY SaleAmount

SELECT * FROM
  (SELECT
      Id,
      SaleAmount,
      (SaleAmount+COALESCE((SELECT SUM(SaleAmount)
          FROM #TEMP_TABLE b
          WHERE b.Id < a.Id),0))
          AS RunningTotal
    FROM #TEMP_TABLE a) InnerTable
WHERE RunningTotal <= 10

Есть ли способ, которым я могу сначала заказать свою таблицу Sales без использования временной таблицы?


sql sql-server cumulative-sum sql-server-2005
person DMK    schedule 04.03.2013    source источник
comment
Мой лучший совет для промежуточных сумм: просто используйте курсор. Подходы с подвыборкой экспоненциально плохи по мере увеличения количества строк. В этом сообщении гораздо больше подробностей: sqlperformance.com/2012/ 07/t-sql-запросы/текущие итоги   -  person Aaron Bertrand    schedule 04.03.2013
comment
@AaronBertrand Спасибо за ссылку; сделано для интересного чтения. Если бы не подход курсора, то, по крайней мере, внутреннее соединение было бы улучшением.   -  person DMK    schedule 04.03.2013
comment
INNER JOIN будет страдать от той же проблемы с экспоненциальным чтением, не так ли?   -  person Aaron Bertrand    schedule 04.03.2013
comment
@AaronBertrand Да, вы правы, я считаю, что внутреннее соединение требует меньшего количества чтений по сравнению с подзапросом, который я использую в настоящее время.   -  person DMK    schedule 04.03.2013

Ответы (5)


arrow_upward
3
arrow_downward

Если вы используете SQL Server 2012, вы можете просто использовать оконную функцию для кумулятивной суммы:

select s.*,
       sum(SaleAmount) over (order by id) as RunningTotal
from Sales s

Это эквивалентно следующему коррелированному подзапросу:

select s.*,
       (select sum(SalesAmount) from sales s2 where s2.id <= s.id) as RunningTotal
from Sales s
person Gordon Linoff    schedule 04.03.2013
comment
Извините, моя вина. Я забыл добавить тег SQL-Server 2005. - person DMK; 04.03.2013
comment
Это именно то, что мне нужно; Если бы я только мог убедить всех наших клиентов обновиться до SQL Server 2012 :) - person DMK; 04.03.2013

arrow_upward
1
arrow_downward

Следуя предложению Аарона Бертрана об использовании метода курсора:

DECLARE @st TABLE
(
    Id       Int PRIMARY KEY,
    SaleAmount  Numeric(10,2),
    RunningTotal Numeric(10,2)
);

DECLARE
    @Id         INT,
    @SaleAmount  Numeric(10,2),
    @RunningTotal Numeric(10,2) = 0;

DECLARE c CURSOR
    LOCAL STATIC FORWARD_ONLY READ_ONLY
    FOR
    SELECT id, SaleAmount
      FROM Sales
      ORDER BY SaleAmount;

OPEN c;

FETCH NEXT FROM c INTO @Id, @SaleAmount;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @RunningTotal = @RunningTotal + @SaleAmount;

    INSERT @st(Id, SaleAmount,  RunningTotal)
        SELECT @Id, @SaleAmount, @RunningTotal;

    FETCH NEXT FROM c INTO @Id, @SaleAmount;
END

CLOSE c;
DEALLOCATE c;

SELECT Id, SaleAmount, RunningTotal
    FROM @st
    WHERE RunningTotal<=10
    ORDER BY SaleAmount;

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

Следует отдать должное Аарону Бертранду за прекрасную статью о промежуточных итогах он написал.

person DMK    schedule 04.03.2013
comment
Я выбрал этот ответ как правильный ответ, он совместим с Sql Server 2005. Однако, если бы обратная совместимость не была фактором, я бы выбрал ответ Гордона Линоффа. - person DMK; 07.03.2013

arrow_upward
1
arrow_downward

Еще один вариант с CTE, ROW_NUMBER() функция ранжирования и APPLY()< /а> оператор

  ;WITH cte AS
   (
    SELECT ROW_NUMBER() OVER(ORDER BY SaleAmount) AS rn, SaleAmount
    FROM Sales s  
    )
    SELECT *
    FROM cte c CROSS APPLY (
                            SELECT SUM(s2.SaleAmount) AS RunningTotal
                            FROM Sales s2
                            WHERE c.SaleAmount >= s2.SaleAmount
                            ) o
    WHERE o.RunningTotal <= 10

К вашему сведению, чтобы избежать операции сортировки, вы можете использовать этот индекс:

CREATE INDEX ix_SaleAmount_Sales ON Sales(SaleAmount)

введите здесь описание изображения

person Aleksandr Fedorenko    schedule 05.03.2013

arrow_upward
0
arrow_downward

После некоторых исследований я считаю, что ваша цель невозможна, если только вы не используете SS2012 или Oracle.

Поскольку ваше решение работает, я бы посоветовал использовать табличную переменную вместо таблицы схемы:

DECLARE @TEMP_TABLE TABLE (
    ID integer IDENTITY PRIMARY KEY,
    SaleAmount numeric(10,2) 
);

INSERT INTO @TEMP_TABLE 
(SaleAmount)
SELECT SaleAmount FROM Sales
ORDER BY SaleAmount

SELECT * FROM
  (SELECT
      Id,
      SaleAmount,
      (SaleAmount+COALESCE((SELECT SUM(SaleAmount)
          FROM @TEMP_TABLE b
          WHERE b.Id < a.Id),0))
          AS RunningTotal
    FROM @TEMP_TABLE a) InnerTable
WHERE RunningTotal <= 10

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

person RASMiranda    schedule 04.03.2013
comment
Я согласен, что предложение Over в SQL-сервере ограничено по сравнению с Oracle; если вы не используете Sql server 2012. Я использовал временную таблицу, а не переменную таблицы, поскольку таблица Sales потенциально может быть таблицей с миллионом записей, а переменные таблицы не поддерживают параллелизм, который, как я полагаю, может значительно замедлить запрос при работе с такими большое количество записей. - person DMK; 04.03.2013
comment
действительно, при работе с большим количеством записей рекомендуется использовать временную таблицу (sqlserverplanet.com/tsql/) в этой статье 1000 считается пороговым значением. - person RASMiranda; 04.03.2013

arrow_upward
0
arrow_downward

Прежде всего, вы делаете подвыборку, а затем выбираете * из подвыборки. Это не нужно.

SELECT
  Id,
  SaleAmount,
  (SaleAmount+COALESCE((SELECT SUM(SaleAmount)
      FROM #TEMP_TABLE b
      WHERE b.Id < a.Id),0))
      AS RunningTotal
FROM #TEMP_TABLE
WHERE RunningTotal <= 10

Теперь временная таблица — это просто запрос к таблице Sales. Нет смысла упорядочивать временную таблицу, потому что по правилам SQL порядок во временной таблице не должен соблюдаться, только порядок по предложению во внешнем запросе, поэтому

SELECT
  Id,
  SaleAmount,
  (SaleAmount+COALESCE((SELECT SUM(SaleAmount)
      FROM Sales b
      WHERE b.Id < a.Id),0))
      AS RunningTotal
FROM Sales
WHERE RunningTotal <= 10
person Marlin Pierce    schedule 04.03.2013
comment
Причиной sub_select было удаление ошибки: Invalid column name 'RunningTotal'. - person DMK; 04.03.2013
comment
Если я попытаюсь использовать ваш второй пример, который я ранее пробовал сам, он возвращает The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified. - person DMK; 04.03.2013
comment
Хорошо, удалим. Я не знаком с COALESCE и не знаю, имеет ли там значение порядок, но я не думаю, что это будет иметь значение. - person Marlin Pierce; 04.03.2013