Запрос на получение предыдущей даты в oracle в конкретном сценарии

У меня есть следующие данные в таблицеA , которые мне нужно вставить в таблицуB вместе с одним вычисляемым столбцом.



ТАБЛИЦА А: 



Account_No |  Balance   |  As_on_date  

1001       |-100        |  1-Jan-2013  

1001       |-150        |  2-Jan-2013  

1001       | 200        |  3-Jan-2013  

1001       |-250        |  4-Jan-2013  

1001       |-300        |  5-Jan-2013  

1001       |-310        |  6-Jan-2013


Таблица B:



В таблице B не должно быть дней, которые будут показаны, когда баланс отрицателен и
дата, на которую он ушел в минус.



Итак, за 6 января 2013 года эта таблица должна показать следующие данные: 



Account_No |  Balance   |  As_on_date   | Days_passed | Start_date   

1001       |    -310    |    6-Jan-2013 |    3        |     4-Jan-2013


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



Мне нужно написать SQL-запрос, чтобы получить число прошедших дней и дату начала, когда
баланс стал отрицательным.



Я попытался сформулировать запрос, используя аналитическую функцию Lag, но мне это не удалось.

Как я должен проверить первый экземпляр отрицательного баланса, перейдя назад с помощью функции LAG?



Даже функции first_value была дана попытка, но не было получено, как разбить в нем на основе отрицательного значение.



Любая помощь или направление по этому вопросу будет действительно полезным.

640   3  

3 ответов:

Вот способ достичь этого, используя аналитические функции.

INSERT INTO tableb
   WITH tablea_grouped1
        AS (SELECT account_no,
                   balance,
                   as_on_date,
                   SUM (CASE WHEN balance >= 0 THEN 1 ELSE 0 END)
                      OVER (PARTITION BY account_no ORDER BY as_on_date)
                      grp
              FROM tablea),
        tablea_grouped2
        AS (SELECT account_no,
                   balance,
                   as_on_date,
                   grp,
                   LAST_VALUE (
                      balance)
                   OVER (
                      PARTITION BY account_no, grp
                      ORDER BY as_on_date
                      ROWS BETWEEN UNBOUNDED PRECEDING
                           AND     UNBOUNDED FOLLOWING)
                      closing_balance
              FROM tablea_grouped1
             WHERE balance < 0 
               AND grp != 0 --keep this, if starting negative balance is to be ignored
           )
     SELECT account_no,
            closing_balance,
            MAX (as_on_date),
            MAX (as_on_date) - MIN (as_on_date) + 1,
            MIN (as_on_date)
       FROM tablea_grouped2
   GROUP BY account_no, grp, closing_balance
   ORDER BY account_no, MIN (as_on_date);
  • Во-первых, сумма используется в качестве аналитической функции для присвоения номера группы последовательным остаткам меньше 0.
  • функция LAST_VALUE затем используется для нахождения последнего баланса в каждой группе
  • Наконец, результат агрегируется на основе каждой группы. Макс(дата) дает последнюю дату, мин(Дата) дает начальную дату, а разница в два дает количество дней.

Демонстрация на sqlfiddle .

2  
2013-09-14 15:48:56

Попробуйте это и используйте gone_negative для вычисления указанного значения столбца для вставки в другую таблицу: 

select temp.account_no,
       temp.balance,
       temp.prev_balance,
       temp.on_date,
       temp.prev_on_date,
       case
         WHEN (temp.balance < 0 and temp.prev_balance >= 0) THEN
          1
         else
          0
       end as gone_negative
  from (select account_no,
               balance,
               on_date,
               lag(balance, 1, 0) OVER(partition by account_no ORDER BY account_no) prev_balance,
               lag(on_date, 1) OVER(partition by account_no ORDER BY account_no) prev_on_date
          from tblA
         order by account_no) temp;

Надеюсь, это поможет пал.

2  
2013-09-14 16:42:36

Вот как это сделать.

  1. выберите все записи из my_table, где баланс положительный.
  2. сделайте самосоединение и получите все записи, у которых as_on_date больше текущей строки, но amounts находятся в отрицательном
  3. Как только мы получим их, мы отсечем строки WHERE date difference между текущим и предыдущим row для as_on_date является > 1. Затем мы фильтруем результаты a outer sub query
  4. конечный select просто группирует строки и получает значения min, max для фильтрованные строки, которые сгруппированы.

Запрос: 

SELECT 
      account_no,
      min(case when row_number = 1 then balance end) as balance,
      max(mt2_date) as As_on_date,
      max(mt2_date) - mt1_date as Days_passed,
      min(mt2_date)  as Start_date
    FROM
      (
        SELECT 
         *,
         MIN(break_date) OVER( PARTITION BY mt1_date ) AS min_break_date,
         ROW_NUMBER() OVER( PARTITION BY mt1_date ORDER BY mt2_date desc ) AS row_number
        FROM 
          (
            SELECT 
              mt1.account_no,
              mt2.balance,
              mt1.as_on_date as mt1_date,
              mt2.as_on_date as mt2_date,
              case when mt2.as_on_date - lag(mt2.as_on_date,1) over () > 1 then mt2.as_on_date end as break_date
            FROM
              my_table mt1
              JOIN my_table mt2 ON ( mt2.balance < mt1.balance AND mt2.as_on_date > mt1.as_on_date ) 
            WHERE 
              MT1.balance > 0
            order by
              mt1.as_on_date,
              mt2.as_on_date ) sub_query
        ) T
    WHERE
      min_break_date is null 
      OR mt2_date < min_break_date
    GROUP BY
      mt1_date,
      account_no

SQLFIDDLE

Я добавил еще несколько строк в скрипку, просто чтобы проверить ее

0  
2013-09-14 19:00:01

Comments

    Ничего не найдено.
Click here to cancel reply.