Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Запрос. Ненулевые суммы ближайшие к дате  [new]
Moss
Member

Откуда:
Сообщений: 173
Всем привет.
Натоклните на мысль.
with t as (
SELECT to_date('01.01.2019','dd.mm.yyyy') as dat , 6 as amount from dual union all
SELECT to_date('02.01.2019','dd.mm.yyyy') as dat , 2 as amount from dual  union all
SELECT to_date('03.01.2019','dd.mm.yyyy') as dat , 5 as amount  from dual union all
SELECT to_date('04.01.2019','dd.mm.yyyy') as dat , 0 as amount  from dual union all
SELECT to_date('05.01.2019','dd.mm.yyyy') as dat , 0 as amount  from dual union all
SELECT to_date('06.01.2019','dd.mm.yyyy') as dat , 2 as amount from dual  union all
SELECT to_date('08.01.2019','dd.mm.yyyy') as dat , 5 as amount  from dual union all
SELECT to_date('09.01.2019','dd.mm.yyyy') as dat , 3 as amount from dual  
)

SELECT t.*,
  lag(Amount, 1, NULL) over(PARTITION BY 1 ORDER BY dat) AS prev_val
                  FROM t
           


Нужно просуммировать amount-ы которые не равны нулю (до первого нулевого) относительно входящей даты (меньше либо равно входящей даты).

Пример : входящая дата 09.01.2019 - сумма = 10 (это сумма с дат 06.01.2019+08.01.2019+09.01.2019)
входящая дата 02.01.2019 - сумма = 8 (это сумма с дат 01.01.2019+02.01.2019)
Спасибо
12 июн 19, 11:19    [21907216]     Ответить | Цитировать Сообщить модератору
 Re: Запрос. Ненулевые суммы ближайшие к дате  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1823
Moss,

SQL> ed
Wrote file afiedt.buf

  1  with t as (
  2  SELECT to_date('01.01.2019','dd.mm.yyyy') as dat , 6 as amount from dual union all
  3  SELECT to_date('02.01.2019','dd.mm.yyyy') as dat , 2 as amount from dual  union all
  4  SELECT to_date('03.01.2019','dd.mm.yyyy') as dat , 5 as amount  from dual union all
  5  SELECT to_date('04.01.2019','dd.mm.yyyy') as dat , 0 as amount  from dual union all
  6  SELECT to_date('05.01.2019','dd.mm.yyyy') as dat , 0 as amount  from dual union all
  7  SELECT to_date('06.01.2019','dd.mm.yyyy') as dat , 2 as amount from dual  union all
  8  SELECT to_date('08.01.2019','dd.mm.yyyy') as dat , 5 as amount  from dual union all
  9  SELECT to_date('09.01.2019','dd.mm.yyyy') as dat , 3 as amount from dual
 10  )
 11  select sum(amount) ss from t
 12  where dat<=date '2019-01-09' and dat>
 13* (select max(dat) from t t2 where t2.dat<=date '2019-01-09' and t2.amount=0)
SQL> /

        SS
----------
        10


.....
stax
12 июн 19, 12:14    [21907256]     Ответить | Цитировать Сообщить модератору
 Re: Запрос. Ненулевые суммы ближайшие к дате  [new]
Moss
Member

Откуда:
Сообщений: 173
with t as (
SELECT to_date('01.01.2019','dd.mm.yyyy') as dat , 6 as amount from dual union all
SELECT to_date('02.01.2019','dd.mm.yyyy') as dat , 2 as amount from dual  union all
SELECT to_date('03.01.2019','dd.mm.yyyy') as dat , 5 as amount  from dual union all
SELECT to_date('04.01.2019','dd.mm.yyyy') as dat , 0 as amount  from dual union all
SELECT to_date('05.01.2019','dd.mm.yyyy') as dat , 0 as amount  from dual union all
SELECT to_date('06.01.2019','dd.mm.yyyy') as dat , 2 as amount from dual  union all
SELECT to_date('08.01.2019','dd.mm.yyyy') as dat , 5 as amount  from dual union all
SELECT to_date('09.01.2019','dd.mm.yyyy') as dat , 3 as amount from dual  
)

SELECT sum(amount) FROM t 
WHERE dat BETWEEN (
SELECT MAX(dat) FROM (
SELECT t.*,
  lag(Amount) over(PARTITION BY 1 ORDER BY dat) AS prev_val
                  FROM t 
                  ) tt
            WHERE tt.amount<>0 
                 AND ( prev_val=0 OR prev_val is null)
                 AND dat<=to_date('09.01.2019','dd.mm.yyyy') -- входящая дата
                  )
                 AND to_date('09.01.2019','dd.mm.yyyy') -- входящая дата


сделал так но странно выглядит
12 июн 19, 12:17    [21907258]     Ответить | Цитировать Сообщить модератору
 Re: Запрос. Ненулевые суммы ближайшие к дате  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1823
Stax,
ой nvl забыл
select sum(amount) ss from t 
where dat<=date '2019-01-02' and dat> 
nvl((select max(dat) from t t2 where t2.dat<=date '2019-01-02' and t2.amount=0),date '1-1-1')
/


.....
stax
12 июн 19, 12:18    [21907261]     Ответить | Цитировать Сообщить модератору
 Re: Запрос. Ненулевые суммы ближайшие к дате  [new]
Moss
Member

Откуда:
Сообщений: 173
Спасибо!
Возможно я описал неправильно.
У вас верно до первой нулевой суммы.
Нужно все-таки либо до нулевой либо просто до первого движения.

Stax
Moss,

SQL> ed
Wrote file afiedt.buf

  1  with t as (
  2  SELECT to_date('01.01.2019','dd.mm.yyyy') as dat , 6 as amount from dual union all
  3  SELECT to_date('02.01.2019','dd.mm.yyyy') as dat , 2 as amount from dual  union all
  4  SELECT to_date('03.01.2019','dd.mm.yyyy') as dat , 5 as amount  from dual union all
  5  SELECT to_date('04.01.2019','dd.mm.yyyy') as dat , 0 as amount  from dual union all
  6  SELECT to_date('05.01.2019','dd.mm.yyyy') as dat , 0 as amount  from dual union all
  7  SELECT to_date('06.01.2019','dd.mm.yyyy') as dat , 2 as amount from dual  union all
  8  SELECT to_date('08.01.2019','dd.mm.yyyy') as dat , 5 as amount  from dual union all
  9  SELECT to_date('09.01.2019','dd.mm.yyyy') as dat , 3 as amount from dual
 10  )
 11  select sum(amount) ss from t
 12  where dat<=date '2019-01-09' and dat>
 13* (select max(dat) from t t2 where t2.dat<=date '2019-01-09' and t2.amount=0)
SQL> /

        SS
----------
        10


.....
stax
12 июн 19, 12:24    [21907268]     Ответить | Цитировать Сообщить модератору
 Re: Запрос. Ненулевые суммы ближайшие к дате  [new]
Moss
Member

Откуда:
Сообщений: 173
with t as (
    SELECT to_date('01.01.2019','dd.mm.yyyy') as dat , 6 as amount from dual union all
    SELECT to_date('02.01.2019','dd.mm.yyyy') as dat , 2 as amount from dual  union all
    SELECT to_date('03.01.2019','dd.mm.yyyy') as dat , 5 as amount  from dual union all
    SELECT to_date('04.01.2019','dd.mm.yyyy') as dat , 0 as amount  from dual union all
    SELECT to_date('05.01.2019','dd.mm.yyyy') as dat , 0 as amount  from dual union all
    SELECT to_date('06.01.2019','dd.mm.yyyy') as dat , 2 as amount from dual  union all
    SELECT to_date('08.01.2019','dd.mm.yyyy') as dat , 5 as amount  from dual union all
    SELECT to_date('09.01.2019','dd.mm.yyyy') as dat , 3 as amount from dual
   )
   select sum(amount) ss from t
   where dat<=date '2019-01-02' 
       and dat>
     nvl((select max(dat) from t t2 where t2.dat<=date '2019-01-02' and t2.amount=0), (SELECT MIN(dat)-1 FROM t))


Типа того..
12 июн 19, 12:28    [21907271]     Ответить | Цитировать Сообщить модератору
 Re: Запрос. Ненулевые суммы ближайшие к дате  [new]
Moss
Member

Откуда:
Сообщений: 173
увидел спасибо
Stax
Stax,
ой nvl забыл
select sum(amount) ss from t 
where dat<=date '2019-01-02' and dat> 
nvl((select max(dat) from t t2 where t2.dat<=date '2019-01-02' and t2.amount=0),date '1-1-1')
/


.....
stax
12 июн 19, 12:37    [21907278]     Ответить | Цитировать Сообщить модератору
 Re: Запрос. Ненулевые суммы ближайшие к дате  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9462
WITH T AS (
SELECT to_date('01.01.2019','dd.mm.yyyy') as dat , 6 as amount from dual union all
SELECT to_date('02.01.2019','dd.mm.yyyy') as dat , 2 as amount from dual  union all
SELECT to_date('03.01.2019','dd.mm.yyyy') as dat , 5 as amount  from dual union all
SELECT to_date('04.01.2019','dd.mm.yyyy') as dat , 0 as amount  from dual union all
SELECT to_date('05.01.2019','dd.mm.yyyy') as dat , 0 as amount  from dual union all
SELECT to_date('06.01.2019','dd.mm.yyyy') as dat , 2 as amount from dual  union all
SELECT to_date('08.01.2019','dd.mm.yyyy') as dat , 5 as amount  from dual union all
SELECT to_date('09.01.2019','dd.mm.yyyy') as dat , 3 as amount from dual  
),
X AS (
      SELECT * FROM T WHERE DAT <= DATE '2019-01-09'
     )
SELECT  TOTAL_AMOUNT
  FROM  X
  MATCH_RECOGNIZE(
                  ORDER BY DAT DESC
                  MEASURES SUM(AMOUNT) TOTAL_AMOUNT
                  PATTERN(DOWN+)
                  DEFINE DOWN AS AMOUNT != 0 AND MATCH_NUMBER() = 1
                 )
/

TOTAL_AMOUNT
------------
          10

SQL> 


SY.
12 июн 19, 14:08    [21907293]     Ответить | Цитировать Сообщить модератору
 Re: Запрос. Ненулевые суммы ближайшие к дате  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9462
Надеюсь DAT уникальны. Если нет, то каковы правила когда та-же DAT и с нулевыми и с ненулевыми AMOUNT?

SY.
12 июн 19, 14:13    [21907294]     Ответить | Цитировать Сообщить модератору
 Re: Запрос. Ненулевые суммы ближайшие к дате  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1823
SY
Надеюсь DAT уникальны.

а какая разница?
добавить в сортировку "AMOUNT"

....
stax
12 июн 19, 14:56    [21907309]     Ответить | Цитировать Сообщить модератору
 Re: Запрос. Ненулевые суммы ближайшие к дате  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9462
Stax

добавить в сортировку "AMOUNT"


Вопрос к правилам. Если есть 0 для даты нужно ли суммировать остальные AMOUNT этой даты?

SY.
12 июн 19, 16:16    [21907336]     Ответить | Цитировать Сообщить модератору
 Re: Запрос. Ненулевые суммы ближайшие к дате  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1823
SY
Stax

добавить в сортировку "AMOUNT"


Вопрос к правилам. Если есть 0 для даты нужно ли суммировать остальные AMOUNT этой даты?

SY.

сортировать в зависимости "нужно ли суммировать "

.....
stax
12 июн 19, 16:33    [21907345]     Ответить | Цитировать Сообщить модератору
 Re: Запрос. Ненулевые суммы ближайшие к дате  [new]
Moss
Member

Откуда:
Сообщений: 173
Спасибо, но Оракл 11 ((
SY
WITH T AS (
SELECT to_date('01.01.2019','dd.mm.yyyy') as dat , 6 as amount from dual union all
SELECT to_date('02.01.2019','dd.mm.yyyy') as dat , 2 as amount from dual  union all
SELECT to_date('03.01.2019','dd.mm.yyyy') as dat , 5 as amount  from dual union all
SELECT to_date('04.01.2019','dd.mm.yyyy') as dat , 0 as amount  from dual union all
SELECT to_date('05.01.2019','dd.mm.yyyy') as dat , 0 as amount  from dual union all
SELECT to_date('06.01.2019','dd.mm.yyyy') as dat , 2 as amount from dual  union all
SELECT to_date('08.01.2019','dd.mm.yyyy') as dat , 5 as amount  from dual union all
SELECT to_date('09.01.2019','dd.mm.yyyy') as dat , 3 as amount from dual  
),
X AS (
      SELECT * FROM T WHERE DAT <= DATE '2019-01-09'
     )
SELECT  TOTAL_AMOUNT
  FROM  X
  MATCH_RECOGNIZE(
                  ORDER BY DAT DESC
                  MEASURES SUM(AMOUNT) TOTAL_AMOUNT
                  PATTERN(DOWN+)
                  DEFINE DOWN AS AMOUNT != 0 AND MATCH_NUMBER() = 1
                 )
/

TOTAL_AMOUNT
------------
          10

SQL> 


SY.
12 июн 19, 16:39    [21907349]     Ответить | Цитировать Сообщить модератору
 Re: Запрос. Ненулевые суммы ближайшие к дате  [new]
Moss
Member

Откуда:
Сообщений: 173
SY
Надеюсь DAT уникальны. Если нет, то каковы правила когда та-же DAT и с нулевыми и с ненулевыми AMOUNT?

SY.

Даты уникальны.
12 июн 19, 16:41    [21907351]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить