Вопрос по – Запрос SQL Server с промежуточным итоговым значением сбрасывается при изменении столбца A

4

У моего коллеги есть проблема, с которой я пытаюсь ему помочь.

У него есть SQL-представление, которое содержит следующие данные (пример данных):

<code>Category          Value
Cat A             10
Cat A             20
Cat A             30
Cat B             15
Cat B             15
Cat C             10
Cat C             10
</code>

Он хочет добавить в представление столбец, в котором хранится промежуточная сумма столбца Значение.

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

Таким образом, выходные данные должны быть такими:

<code>Category          Value       Running
Cat A             10          10
Cat A             20          30
Cat A             30          60
Cat B             15          15
Cat B             15          30
Cat C             10          10
Cat C             10          20
</code>

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

<code>select t1.id, t1.[count], SUM(t2.[count]) as sum
from TableA t1
inner join TableA t2 on t1.id >= t2.id
group by t1.id, t1.[count]
order by t1.id
</code>

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

SQL Server 2005 к сожалению. general exception
Какая версия SQL Server? Это довольно легко с SQL Server 2012, потому что если, наконец, (полностью) поддерживает функции управления окнами. a_horse_with_no_name
Как рассчитывается порядок промежуточного итога? В таблице есть поле даты? Dibstar
Таблица будет отсортирована по категориям, а затем по значению, поэтому итоговую сумму следует рассчитывать в порядке, указанном выше. Обратите внимание, что условие order by должно быть в запросе, который выполняет промежуточный итог, так как данные извлекаются из представления, которое не разрешает предложения order by. general exception

Ваш Ответ

2   ответа
3

ROW_NUMBER() функция в сочетании сOUTER APPLY

DECLARE @T TABLE (Category VARCHAR(5), Value INT)
INSERT INTO @T VALUES 
    ('Cat A', 10),
    ('Cat A', 20),
    ('Cat A', 30),
    ('Cat B', 15),
    ('Cat B', 15),
    ('Cat C', 10),
    ('Cat C', 10)

;WITH T AS
(   SELECT  Category, Value, ROW_NUMBER() OVER(PARTITION BY Category ORDER BY Value) [RowNumber]
    FROM    @T
)
SELECT  T1.Category,
        T1.Value,
        RunningTotal
FROM    T T1
        OUTER APPLY
        (   SELECT  SUM(Value) [RunningTotal]
            FROM    T T2
            WHERE   T2.Category = T1.Category
            AND     T2.RowNumber <= T1.RowNumber
        ) RunningTotal
Error: User Rate Limit Exceeded
4

Это не будет особенно хорошо работать на больших столах, но сделает работу!

   select 'Cat A' as class,10 as value into #x
UNION ALL SELECT 'Cat A',20 
UNION ALL SELECT 'Cat A',30 
UNION ALL SELECT 'Cat B',15 
UNION ALL SELECT 'Cat B',15 
UNION ALL SELECT 'Cat C',10 
UNION ALL SELECT 'Cat C',10 

;WITH running_total AS
(
select *
,ROW_number() OVER (PARTITION BY class order by value ASC) as row 
from #x 
)
SELECT 
r1.class
,MAX(r1.value) as value
,SUM(r2.value) as running_total
FROM running_total r1
LEFT OUTER JOIN running_total r2 on r2.class = r1.class
                AND r2.row <= r1.row
GROUP BY 
r1.class
,r1.row

Это работает с использованием общего табличного выражения (CTE) и оконных функций. CTE работает аналогично подзапросу, где первая часть (называемая running_total) добавляет основанный на строке идентификатор в каждый класс на основе значения.

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

В этом примере partitionign by class гарантирует, что для каждого нового имени категории будет установлено самое низкое значение, равное 1 - если вы хотите получить итоговую промежуточную сумму, не основанную на конкретных категориях, вы удалите эту часть предложения.

Вторая часть CTE выбирает результаты из первой части CTE и объединяется там, где совпадают классы. При объединении по r2.row & lt; = r1.row это гарантирует, что второе объединение включает все значения & lt; = текущую строку - т.е. строка 3 в R1 будет включать в себя строку 1,2,3 в соединении R2.

Error: User Rate Limit Exceeded general exception
Error: User Rate Limit Exceeded
Error: User Rate Limit Exceeded
Error: User Rate Limit Exceeded

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