Блог

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

  ---------------- ------------------ ---------- 
| Transaction ID | Transaction Date | Quantity |
 ---------------- ------------------ ---------- 
|              1 | 03-May-20        |        3 |
|              2 | 06-May-20        |        5 |
|              3 | 05-Jun-20        |       10 |
|              4 | 06-Jul-20        |        2 |
|              5 | 07-Aug-20        |        8 |
 ---------------- ------------------ ---------- 
  

Теперь мой запрос oracle sql

 select
transaction_id,
transaction_date,
sum(quantity) over (order by transaction_date) as running_total

from table

where transaction_date >= '03-May-20' and transaction_date <= '06-Jul-20'
  

После того, как я выполнил запрос выше, я получил результат ниже

  ---------------- ------------------ ---------- --------------- 
| Transaction ID | Transaction Date | Quantity | Running Total |
 ---------------- ------------------ ---------- --------------- 
|              1 | 03-May-20        |        3 |            28 |
|              2 | 06-May-20        |        5 |            33 |
|              3 | 05-Jun-20        |       10 |            43 |
|              4 | 06-Jul-20        |        2 |            45 |
 ---------------- ------------------ ---------- --------------- 
  

Что неверно, потому что результат, который я хочу,:

 | Transaction ID | Transaction Date | Quantity | Running Total |
 ---------------- ------------------ ---------- --------------- 
|              1 | 03-May-20        |        3 |             3 |
|              2 | 06-May-20        |        5 |             8 |
|              3 | 05-Jun-20        |       10 |            18 |
|              4 | 06-Jul-20        |        2 |            20 |
 ---------------- ------------------ ---------- --------------- 
  

Пожалуйста, помогите, какой запрос я должен использовать для вычисления текущего итога с диапазоном дат в предложении where.

Привет, я попытался использовать функцию to_date, но я получил тот же результат, что и выше (2-я таблица). Пожалуйста, смотрите Запрос ниже,

 select
transaction_id,
transaction_date,
sum(quantity) over (order by transaction_date) as running_total

from table

where transaction_date between TO_DATE('2019-03-01', 'YYYY-MM-DD') AND TO_DATE('2020-10-25', 'YYYY-MM-DD')
  

Спасибо,

Вывод, который вы хотите (образец данных в строках # 1-7; запрос начинается со строки # 8).

 SQL> with test (transaction_id, transaction_date, quantity) as
  2    (select 1, date '2020-05-03',  3 from dual union all
  3     select 2, date '2020-05-06',  5 from dual union all
  4     select 3, date '2020-06-05', 10 from dual union all
  5     select 4, date '2020-07-06',  2 from dual union all
  6     select 5, date '2020-08-07',  8 from dual
  7    )
  8  select transaction_id,
  9    transaction_date,
 10    quantity,
 11    sum(quantity) over (order by transaction_date) running_total
 12  From test
 13  where transaction_date between date '2020-05-03' and date '2020-07-06'
 14  order by transaction_id;

TRANSACTION_ID TRANSACTIO   QUANTITY RUNNING_TOTAL
-------------- ---------- ---------- -------------
             1 03.05.2020          3             3
             2 06.05.2020          5             8
             3 05.06.2020         10            18
             4 06.07.2020          2            20

SQL>
  

Причина ваших проблем? Я предполагаю, что это тот факт, что вы сравниваете даты со строками ( '03-May-20' это строка, а не дата). Oracle пытается неявно преобразовать типы данных; иногда это удается, иногда нет. 03-May-20 Например, это может быть 3 мая 2020 года или 20 мая 2003 года.

Всегда контролируйте свои данные. Используйте даты, когда это необходимо, либо с помощью to_date функции с соответствующей маской формата даты, либо используйте литерал даты (как я сделал).

Ваш запрос «правильный», но ваши результаты нет, они содержат ожидаемый текущий итог, но он начинается с конечного итога (включая идентификатор транзакции 5). Вы выполнили другую инструкцию SQL.

Обратите внимание, что вы не должны хранить даты в виде строк или сравнивать их со строками. Тем не менее, Oracle сохранит ваш бекон, если ваш текущий параметр формата даты NLS (который очень легко изменить) соответствует вашему строковому вводу. Вы бы получили совершенно другие результаты, если бы вы заказывали по строке, ваши результаты имеют ожидаемые значения даты транзакции, поэтому можно предположить, что это сравнение сработало. Вам все равно следует исправить обработку дат, но проблема здесь не в этом.

Происходит что-то еще. Данные суммируются правильно с точки зрения дат, поэтому порядок дат, по-видимому, не является проблемой.

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

 select transaction_id, transaction_date, quantity,
       total_quantity   sum(quantity) over (order by transaction_date)as running_total
From (select t.*,
             sum(quantity) over () - first_value(quantity) over (order by transaction_date) as total_quantity
      from test t
     ) t
where transaction_date between date '2020-05-03' and date '2020-07-06'
order by transaction_date;
  

Я не могу объяснить, почему это происходит. Это может быть ошибка в Oracle. Или это может быть результатом чрезмерного упрощения вашего запроса, когда вы задавали вопрос.