Блог

Главная - Вопросы по программированию - Выполнение общего запроса SQL Server

Выполнение общего запроса SQL Server

#sql-server #sql-server-2005 #cumulative-sum #sql-cte

#sql-сервер #sql-server-2005 #кумулятивная сумма #sql-cte

Вопрос:

Пока что у меня есть следующий запрос SQL Server 2005:

 WITH D AS (
SELECT CONVERT(VARCHAR, '2020.11.01', 102) AS d_y_m, CAST('2020-11-01' AS DATETIME) AS dt
UNION ALL
SELECT CONVERT(VARCHAR, DATEADD(dd, 1, z.dt), 102) AS d_y_m, DATEADD(dd, 1, z.dt)
FROM D AS z
WHERE DATEADD(dd, 1, z.dt) <= '2020-11-30')
SELECT x.d_y_m, ISNULL(SUM(y.Total), 0) AS [Invoiced], ISNULL(SUM(FEI.Total), 0) AS [Paid] FROM D x
LEFT JOIN Invoices y ON CONVERT(VARCHAR, y.InvoiceDate, 102) = x.d_y_m
LEFT JOIN Payments AS FEI ON CONVERT(VARCHAR, FEI.PaymentDate, 102) = x.d_y_m
GROUP BY x.d_y_m
ORDER BY x.d_y_m OPTION (MAXRECURSION 0)

Как мне добавить еще один столбец (runningTotal) к запросу, который суммирует результат (оплаченный-оплаченный) за предыдущий день с сегодняшним

Пример:

 d_y_m | Invoiced | Paid | RunningTotal

2020.11.01 | 24 | 5 | 19

2020.11.02 | 45 | 2 | 62

2020.11.03 | 10 | 20 | 52

2020.11.04 | 5 | 0 | 57

2020.11.05 | 0 | 10 | 47

Ответ №1:

Пара замечаний по вашему текущему решению:

  • Не используйте «случайные» псевдонимы таблиц. D для «дат» имеет смысл. y для «Счетов-фактур» нет. d_y_m также не соответствует вашему формату даты. Сохраняйте значимые псевдонимы таблиц и столбцов.
  • Не перетаскивайте преобразование даты через все ваше решение. Работайте со значениями даты как date с типами и преобразуйте значения один раз в финале select .
  • Не группируйте суммы выставленных счетов и оплаченных сумм в одном запросе. Если у вас есть несколько счетов или платежей за один день, то суммы будут неверными! Объяснение см. В разделе «Дополнительные» внизу.
  • Упростите нам помощь. В следующий раз, пожалуйста, предоставьте образцы данных, которые мы можем скопировать и вставить, вместо того, чтобы придумывать свои собственные.
  • SQL Server 2005 официально не поддерживается с 12 апреля 2016 года. Время искать новую версию!

Пример данных

 create table Invoices
(
  InvoiceDate date,
  Total money
);

insert into Invoices (InvoiceDate, Total) values
('2020-11-01', 20),
('2020-11-01',  4),
('2020-11-02', 40),
('2020-11-02',  5),
('2020-11-03', 10),
('2020-11-04',  3),
('2020-11-04',  2);

create table Payments
(
  PaymentDate date,
  Total money
);

insert into Payments (PaymentDate, Total) values
('2020-11-01',  5),
('2020-11-02',  2),
('2020-11-03', 10),
('2020-11-03', 10),
('2020-11-05', 10);

Решение

 with DateRange as
(
  select convert(date, '2020-11-01') as DateValue
  union all
  select dateadd(day, 1, dr.DateValue)
  from DateRange dr
  where dr.DateValue < '2020-11-30'
),
InvoicedTotal as
(
  select dr.DateValue,
         isnull(sum(i.Total), 0) as Invoiced
  from DateRange dr
  left join Invoices i
    on i.InvoiceDate = dr.DateValue
  group by dr.DateValue
),
PaidTotal as
(
  select dr.DateValue,
         isnull(sum(p.Total), 0) as Paid
  from DateRange dr
  left join Payments p
    on p.PaymentDate = dr.DateValue
  group by dr.DateValue
)
select convert(varchar(10), dr.DateValue, 102) as [YYYY.MM.DD],
       it.Invoiced as [Invoiced],
       sum(it.Invoiced) over(order by it.DateValue
                             rows between unbounded preceding and current row) as [CumInvoiced],
       pt.Paid as [Paid],
       sum(pt.Paid) over(order by pt.DateValue
                         rows between unbounded preceding and current row) as [CumPaid],
       sum(it.Invoiced) over(order by it.DateValue
                             rows between unbounded preceding and current row) -
       sum(pt.Paid) over(order by pt.DateValue
                         rows between unbounded preceding and current row) as [RunningTotal]
from DateRange dr
join InvoicedTotal it
  on it.DateValue = dr.DateValue
join PaidTotal pt
  on pt.DateValue = dr.DateValue
order by dr.DateValue;

Результат

Перечисляются только первые 10 из 30 строк за ноябрь.

 YYYY.MM.DD Invoiced CumInvoiced Paid    CumPaid RunningTotal
---------- -------- ----------- ------- ------- ------------
2020.11.01  24.0000     24.0000  5.0000  5.0000      19.0000
2020.11.02  45.0000     69.0000  2.0000  7.0000      62.0000
2020.11.03  10.0000     79.0000 20.0000 27.0000      52.0000
2020.11.04   5.0000     84.0000  0.0000 27.0000      57.0000
2020.11.05   0.0000     84.0000 10.0000 37.0000      47.0000
2020.11.06   0.0000     84.0000  0.0000 37.0000      47.0000
2020.11.07   0.0000     84.0000  0.0000 37.0000      47.0000
2020.11.08   0.0000     84.0000  0.0000 37.0000      47.0000
2020.11.09   0.0000     84.0000  0.0000 37.0000      47.0000
2020.11.10   0.0000     84.0000  0.0000 37.0000      47.0000

Поиграйте, чтобы увидеть это в действии.

Дополнительно: почему бы не подсчитать оба итога в одном запросе.

Используя те же примерные данные, вы можете выполнить этот запрос для увеличения масштаба конкретной даты, здесь: 2020-11-01 . На эту дату образец данных содержит 2 счета-фактуры и 1 платеж.

 with DateRange as
(
  select '2020-11-01' as DateValue -- filtering data to explain
)
select dr.DateValue,
       isnull(sum(i.Total), 0) as Invoiced,
       isnull(sum(p.Total), 0) as Paid
from DateRange dr
left join Invoices i
  on i.InvoiceDate = dr.DateValue
left join Payments p
  on p.PaymentDate = dr.DateValue
group by dr.DateValue
order by dr.DateValue;

Простое выполнение соединений даст вам приведенный ниже результат. Из-за комбинирования left join строка оплаты отображается дважды!

 dr.DateValue | i.Total | p.Total
------------ | ------- | -------
2020-11-01   |      20 |       5
2020-11-01   |       4 |       5 --> payment row got joined TWICE

Суммирование этих строк дает неверную сумму платежа за этот день.

 group by dr.DateValue | sum(i.Total) | sum(p.Total)
--------------------- | ------------ | ------------
2020-11-01            |           24 |           10 --> last sum is WRONG !

Редактировать: версия SQL Server 2005 с cross apply . Но обновление версии SQL Server по-прежнему рекомендуется!

 with DateRange as
(
  select convert(date, '2020-11-01') as DateValue
  union all
  select dateadd(day, 1, dr.DateValue)
  from DateRange dr
  where dr.DateValue < '2020-11-30'
),
InvoicedTotal as
(
  select dr.DateValue,
         isnull(sum(i.Total), 0) as Invoiced
  from DateRange dr
  left join Invoices i
    on i.InvoiceDate = dr.DateValue
  group by dr.DateValue
),
PaidTotal as
(
  select dr.DateValue,
         isnull(sum(p.Total), 0) as Paid
  from DateRange dr
  left join Payments p
    on p.PaymentDate = dr.DateValue
  group by dr.DateValue
)
select convert(varchar(10), dr.DateValue, 102) as [YYYY.MM.DD],
       it1.Invoiced as [Invoiced],
       it3.Invoiced as [CumInvoiced],
       pt1.Paid as [Paid],
       pt3.Paid as [CumPaid],
       it3.Invoiced - pt3.Paid as [RunningTotal]
from DateRange dr
join InvoicedTotal it1
  on it1.DateValue = dr.DateValue
join PaidTotal pt1
  on pt1.DateValue = dr.DateValue
cross apply ( select sum(it2.Invoiced) as Invoiced
              from InvoicedTotal it2
              where it2.DateValue <= dr.DateValue ) it3
cross apply ( select sum(pt2.Paid) as Paid
              from PaidTotal pt2
              where pt2.DateValue <= dr.DateValue ) pt3
order by dr.DateValue;

Обновлена скрипка.

Комментарии:

1. Спасибо вам, Сандер, за ваше решение и объяснение! Я попытался добавить сценарий таблиц и образцов данных, но не смог опубликовать, потому что это было слишком много кода, и stackoverflow не позволил бы этого. Проблема в том, что я использую SQL Server 2005 и получаю сообщение об ошибке: сообщение 102, уровень 15, состояние 1, строка 30 Неправильный синтаксис рядом с «строками».

2. Похоже, мне придется перейти на улучшенную версию SQL Server.

3. Я где-то потерял требование SQL Server 2005… Ответ обновлен версией, которая должна работать в SQL Server 2005.

4. Отлично работает. Спасибо! Могу ли я каким-либо образом добавить начальное значение к runningTotal?

5. Да, либо определите новую переменную для фиксированной базовой суммы и добавьте ее в вычисление runningTotal следующим образом, либо добавьте новый CTE и объедините для переменной, вычисляемой базовой суммы.