Выражения SQL, использующие результат предыдущей строки UNION ALL

Question

У меня есть эти две таблицы:

Collect: Date, Rute, Value_Collected...
Records: Date, Rute, Value, Type...

Другие столбцы в данном случае не имеют значения. Итак, у меня есть этот рабочий код:

SELECT
Totals.Date                             AS [Date],
Sum(Totals.Collected)                   AS [Collect],
Sum(Totals.Injected)                    AS [Injections],
Sum(Totals.Spent)                       AS [Expenses],
([Collect] + [Injections]) - [Expenses] AS [Total Day]

FROM (
    SELECT
    Records.Date                        AS [Date],
    0                                   AS [Collected],
    Sum(IIF(Type =  3,Records.Value,0)) AS [Injected],
    Sum(IIF(Type <> 3,Records.Value,0)) AS [Spent]
    FROM Records GROUP BY Records.Date

    UNION ALL

    SELECT
    Collect.Date                 AS [Date],
    Sum(Collect.Value_Collected) AS [Collected],
    0                            AS [Injected],
    0                            AS [Spent]
    FROM Collect GROUP BY Collect.Date
) AS Totals GROUP BY Totals.Date

И это дает мне этот результат:

|Date       |Collect    |Injections  |Expenses   |Total Day
-----------------------------------------------------------
|5/09/2016  |$47.000   |$0           |$0         |$47.000 |
|6/09/2016  |$20.000   |$0           |$0         |$20.000 |
|7/09/2016  |$13.000   |$0           |$0         |$13.000 |
|11/09/2016 |$122.000  |$0           |$0         |$122.000|
|14/09/2016 |$7.000    |$0           |$0         |$7.000  |
|16/09/2016 |$100.000  |$0           |$7.000     |$93.000 |
|30/09/2016 |$0        |$80.000      |$65.000    |$15.000 |
-----------------------------------------------------------

И это нормально, но формула столбца [Total Day] должен быть рассчитан так ([Total Day Of Last Row] + [Collect] + [Injections]) - [Expenses] вместо ([Collect] + [Injections]) - [Expenses] чтобы дать мне этот желаемый результат:

|Date       |Collect      |Injections  |Expenses   |Total Day
-------------------------------------------------------------
|5/09/2016  |$47.000      |$0          |$0         |$47.000 |
|6/09/2016  |$20.000      |$0          |$0         |$67.000 |
|7/09/2016  |$13.000      |$0          |$0         |$80.000 |
|11/09/2016 |$122.000     |$0          |$0         |$202.000|
|14/09/2016 |$7.000       |$0          |$0         |$209.000|
|16/09/2016 |$100.000     |$0          |$7.000     |$302.000|
|30/09/2016 |$0           |$80.000     |$65.000    |$317.000|
-------------------------------------------------------------

Но я не знаю, как получить [Total Day] предыдущего ряда для расчета [Total Day] текущего ряда. Если я попытаюсь запустить подзапрос [Totals], это дает мне ошибку, потому что не может найти таблицу Totals, Я думаю, это потому, что это не "настоящий" стол, но на самом деле я не знаю, как этого добиться. Я действительно ценю любую помощь.

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

Answer 1

По сути, вам нужна совокупная сумма по полям по прогрессивным датам, которая может быть обработана с помощью подзапроса совокупной суммы. Единственная проблема состоит в том, что MS Access не имеет оконных функций, как другие диалекты SQL, для повторного использования производных запросов таблиц. Так что вам нужно повторить производную таблицу объединения внутри подзапроса:

SELECT main.Date, main.Collected AS [Collect], 
       main.Injected As [Injections], main.Spent As [Expenses],
       Ccur((SELECT Sum(sub.Collected) + Sum(sub.Injected) - Sum(sub.Spent) 
             FROM 
               (SELECT Records.Date AS [Date], 0 AS [Collected],
                       Sum(IIF(Type =  3,Records.Value,0)) AS [Injected],
                       Sum(IIF(Type <> 3,Records.Value,0)) AS [Spent]
                FROM Records GROUP BY Records.Date
                UNION ALL
                SELECT Collect.Date AS [Date],
                       Sum(Collect.Value_Collected) AS [Collected],
                       0 AS [Injected],
                       0 AS [Spent]
                FROM Collect GROUP BY Collect.Date
                ) As sub
             WHERE sub.Date <= main.Date)) As [Total Day]

FROM
  (SELECT Records.Date AS [Date], 0 AS [Collected],
          Sum(IIF(Type =  3,Records.Value,0)) AS [Injected],
          Sum(IIF(Type <> 3,Records.Value,0)) AS [Spent]
   FROM Records GROUP BY Records.Date
   UNION ALL
   SELECT Collect.Date AS [Date],
         Sum(Collect.Value_Collected) AS [Collected],
         0 AS [Injected],
         0 AS [Spent]
   FROM Collect GROUP BY Collect.Date
  ) As main

К счастью, MS Access поддерживает сохраненный объект запроса, где вы сохраняете запрос UNION как отдельный запрос и ссылаетесь на него в новом запросе для более короткого сценария:

SELECT main.Date, main.Collected As [Collect], 
       main.Injected As Injections, main.Spent As Expenses,  
       Ccur((SELECT Sum(sub.Collected) + Sum(sub.Injected) - Sum(sub.Spent) 
             FROM RecordsUnionQ As sub
             WHERE sub.Date <= main.Date)) As [Total Day]
FROM RecordsUnionQ As main

---

Используя SQLite 3, который поддерживает CTE, вы можете использовать WITH() предложение, чтобы держать запрос объединения и избежать повторения его в подзапросе. CTE действуют как временные представления только на время выполнения оператора SQL.

WITH main AS
  (SELECT Records.Date AS [Date], 0 AS [Collected],
          Sum(IIF(Type =  3,Records.Value,0)) AS [Injected],
          Sum(IIF(Type <> 3,Records.Value,0)) AS [Spent]
   FROM Records GROUP BY Records.Date
   UNION ALL
   SELECT Collect.Date AS [Date],
         Sum(Collect.Value_Collected) AS [Collected],
         0 AS [Injected],
         0 AS [Spent]
   FROM Collect GROUP BY Collect.Date)

SELECT main.Date, main.Collected AS [Collect], 
       main.Injected As [Injections], main.Spent As [Expenses],
       Ccur((SELECT Sum(sub.Collected) + Sum(sub.Injected) - Sum(sub.Spent) 
             FROM main As sub
             WHERE sub.Date <= main.Date)) As [Total Day]
FROM main;