SQL Вычисление промежуточной суммы заказа клиентом с использованием самообъединения

Так что я знаю, что это было несколько сделано до смерти, но я действительно борюсь с этим. На работе у меня есть SQL Server 2008, и меня попросили сделать промежуточный итог, поэтому для меня нет оконных функций. Это означает, что я должен делать это старым школьным способом, поэтому, чтобы научиться этому, я использую базу данных AdventureWorks 2012 и пытаюсь сделать промежуточный итог totaldue для каждого salesorderid на customerid в таблице sales.salesorderheader. Соответствующие столбцы:

salesorderid int not null
customerid int not null
totaldue money not null

Вот что у меня есть до сих пор:

select soh1.salesorderid
, soh1.customerid
, soh1.totaldue
, sum(soh2.totaldue) 'running_total'
from sales.salesorderheader soh1
inner join sales.salesorderheader soh2 on soh1.totaldue <= soh2.totaldue
                                       and soh1.customerid = soh2.customerid
group by soh1.customerid, soh1.salesorderid, soh1.totaldue
order by soh1.customerid, soh1.salesorderid;

Я получаю следующее (первые 10 строк):

salesorderid    customerid  totaldue    runningtotal
43793           11000       3756.989    3756.989
51522           11000       2587.8769   9115.1341
57418           11000       2770.2682   6527.2572
43767           11001       3729.364    3729.364
51493           11001       2674.0227   6403.3867
43736           11002       3756.989    3756.989
51238           11002       2535.964    8966.0143
53237           11002       2673.0613   6430.0503
43701           11003       3756.989    3756.989
51315           11003       2562.4508   8993.9155
57783           11003       2674.4757   6431.4647

Я чувствую, что я близок - я заметил, что когда есть только два заказа на продажу для данного идентификатора клиента, промежуточная сумма вычисляется правильно. Однако, когда их 3 или больше, это становится дерьмом.

Я посмотрел на выход из объединения для customerid = 11000, чтобы понять, почему второй и третий идентификаторы порядка (51522 и 57418) не вычисляются правильно. Я запустил следующий код:

select soh1.salesorderid
, soh1.customerid
, soh1.totaldue
, soh2.salesorderid
, soh2.customerid
, soh2.totaldue
from sales.salesorderheader soh1
inner join sales.salesorderheader soh2 on soh1.totaldue <= soh2.totaldue
                                          and soh1.customerid = soh2.customerid
where soh1.customerid = 11000
order by soh1.customerid, soh1.salesorderid;

и я получаю это:

salesorderid  customerid  totaldue   salesorderid  customerid  totaldue
43793         11000       3756.989   43793         11000       3756.989
51522         11000       2587.8769  43793         11000       3756.989
51522         11000       2587.8769  51522         11000       2587.8769
**51522       11000       2587.8769  57418         11000       2770.2682**
57418         11000       2770.2682  57418         11000       2770.2682
57418         11000       2770.2682  43793         11000       3756.989

Я вижу, что проблема в 4-й строке, где она вводит строку, которую я не хочу, а затем для salesorderid 57418 отсутствует одна строка. Я подозреваю, что проблема связана с моим соединением, но я не могу понять логику, которая привела бы к правильным строкам.

Я попросил использовать SQL 2012 или более позднюю версию, но не в кости. Пожалуйста помоги!


sql-server sql-server-2008 cumulative-sum self-join
person Julian Drago    schedule 04.08.2015    source источник
comment
Разве вам не нужно просто сгруппировать идентификатор заказа на продажу и идентификатор заказа на продажу2, а затем просуммировать его общую сумму?   -  person ivan    schedule 04.08.2015
comment
Не совсем понял, можно пример кода? В настоящее время я группирую по salesorderid1, но не уверен, что будет делать группировка salesorderid2. Если вы имеете в виду сгруппировать обе в отдельные таблицы, а затем добавить результаты, я не думаю, что это даст мне промежуточный итог.   -  person Julian Drago    schedule 04.08.2015
comment
вы также можете использовать CROSS APPLY для промежуточных сумм.   -  person Paul Maxwell    schedule 04.08.2015

Ответы (3)


arrow_upward
2
arrow_downward

Разве ваше самосоединение не должно выполняться по идентификатору клиента и заказа на продажу вместо totaldue? См. следующее:

select soh1.salesorderid
, soh1.customerid
, soh1.totaldue
, sum(soh2.totaldue) 'running_total'
from sales.salesorderheader soh1
inner join sales.salesorderheader soh2 on soh2.salesorderid <= soh1.salesorderid
                                   and soh2.customerid = soh1.customerid
group by soh1.customerid, soh1.salesorderid, soh1.totaldue
order by soh1.customerid, soh1.salesorderid;
person Carsten Massmann    schedule 04.08.2015
comment
Ты понял. Не могу поверить, что я этого не видел! - person Julian Drago; 04.08.2015

arrow_upward
0
arrow_downward

Или вы можете попробовать CTE

;with grouped as
(
    select *, ROW_NUMBER() OVER (PARTITION BY customerid ORDER BY salesorderid) as groupId 
    from sales.salesorderheader
), cte as
(
    select *, totaldue as runningtotal from grouped where groupId = 1
    union all
    select grouped.*, grouped.totaldue + cte.runningtotal
    from grouped inner join cte on 
        grouped.groupId - 1 = cte.groupId 
        and grouped.customerid = cte.customerid
)
select * from cte order by customerid, salesorderid
person Eric    schedule 04.08.2015
comment
Пытался запустить это, и, похоже, он не вычисляет промежуточную сумму - person Julian Drago; 04.08.2015

arrow_upward
0
arrow_downward

Используйте этот код:

select soh1.salesorderid
, soh1.customerid
, soh1.totaldue
,(select  sum(soh2.totaldue) from sales.salesorderheader soh2      
  where soh1.totaldue <= soh2.totaldue 
  and  soh1.customerid = soh2.customerid) as 'Running Totoal'
from sales.salesorderheader soh1
where soh1.customerid = 11000
order by soh1.customerid, soh1.salesorderid
person Hell Boy    schedule 04.08.2015
comment
Это дает результат, идентичный моему ближайшему запросу выше. Он правильно вычисляет промежуточную сумму, когда есть только 2 продажи для данного идентификатора клиента, но когда их 3 или более, он суммирует неправильно с учетом порядка сортировки. - person Julian Drago; 04.08.2015