Блог

У меня есть этот запрос ниже, и мне нужно изменить условия WHERE в зависимости от квартала.

Это означает, что я должен скопировать этот запрос и изменить условия даты на 06.2020, а затем использовать ОБЪЕДИНЕНИЕ.

Я понятия не имею, как я могу оптимизировать этот запрос только с помощью SQL, потому что я не могу определить некоторые переменные / параметры в SQL (не используя PL / SQL).

 SELECT sum(a.limit_amount) as LIMIT,  
       sum(b.balance_amount) as OUTSTANDING,
        'LOAN' as TYPE,
        'Q1 2020' as QUARTER
FROM   accounts a 
        left join account_balances b 
              ON a.account_key = b.account_key
                 AND b.balance_type_key = 16
                 AND b.balance_date = last_day(to_date('03.2020', 'MM.YYYY'))
WHERE a.account_close_date > last_day(to_date('03.2020', 'MM.YYYY')) and a.account_open_date <= last_day(to_date('03.2020', 'MM.YYYY'))
  

Спасибо за помощь

Если SQL * Developer или SQLPlus, вы можете использовать «переменные подстановки».

 SELECT sum(a.limit_amount) as LIMIT,  
       sum(b.balance_amount) as OUTSTANDING,
        'LOAN' as TYPE,
        'Q1 2020' as QUARTER -- << probably want TO_CHAR('amp;amp;1', 'Q YYYY') or similar here
FROM   accounts a 
        left join account_balances b 
              ON a.account_key = b.account_key
                 AND b.balance_type_key = 16
                 AND b.balance_date = last_day(to_date('amp;amp;1', 'MM.YYYY'))
WHERE a.account_close_date > last_day(to_date('amp;amp;1', 'MM.YYYY')) and a.account_open_date <= last_day(to_date('amp;amp;1', 'MM.YYYY'))
  

Когда вы запустите это, вам будет предложено ввести значение.

Если бы вы использовали только ‘amp;1’, это выдавало бы вам запросы для каждого вхождения. ‘amp;amp;1’ повторно использует значение первого вхождения.

Вы могли бы указать разные переменные, используя amp;1, amp;2, …

Также вы можете использовать именованную переменную следующим образом:

Для запроса даты :

 ACCEPT dt CHAR PROMPT 'ENter the date (MM.YYYY): '
  

Или установка значения при объявлении:

 DEFINE dt='03.2020'
  

Затем:

 SELECT sum(a.limit_amount) as LIMIT,  
       sum(b.balance_amount) as OUTSTANDING,
        'LOAN' as TYPE,
        'Q1 2020' as QUARTER -- << probably want TO_CHAR('amp;amp;dt', 'Q YYYY') or similar here
FROM   accounts a 
        left join account_balances b 
              ON a.account_key = b.account_key
                 AND b.balance_type_key = 16
                 AND b.balance_date = last_day(to_date('amp;amp;dt', 'MM.YYYY'))
WHERE a.account_close_date > last_day(to_date('amp;amp;dt', 'MM.YYYY')) and a.account_open_date <= last_day(to_date('amp;amp;dt', 'MM.YYYY'))
  

Хорошо, прежде всего, отказ от ответственности:

Поскольку я ничего не знаю о логике вашего исходного запроса — предлагаемый мной запрос показывает только основную логику — и, вероятно, не будет выполняться как есть.

Сказав это, подход был бы:

На основе параметров :start_y (год начала) и :end_y (год окончания) создайте список со всеми кварталами за эти годы years_and_quarters .

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

 with quarters as 
(
select 'Q1' quarter, '03'  q_end_date, '01'  q_start_date from dual 
union 
select 'Q2' quarter, '06'  q_end_date,'04'  q_start_date from dual
union 
select 'Q3' quarter, '09'  q_end_date,'07'  q_start_date from dual
union 
select 'Q4' quarter, '12'  q_end_date,'10'  q_start_date from dual
),
years as
(select :start_y   (level-1) as mydate
    from dual
    connect by level <= (:end_y - :start_y)  1
    ),
 years_and_quarters as (   
    select * from years,quarters
   order by mydate 
   ),
accounts as (
    SELECT a.limit_amount ,  
           b.balance_amount,
            'LOAN' as TYPE, b.balance_date,a.account_open_date,a.account_close_date
    FROM   accounts a 
            left join account_balances b 
                  ON a.account_key = b.account_key
                     AND b.balance_type_key = 16
   )        
select sum(accounts.limit_amount) as LIMIT,  
       sum(accounts.balance_amount) as OUTSTANDING,
       years_and_quarters.quarter,
       accounts.TYPE 
from 
years_and_quarters,accounts 
where
trunc(balance_date) = last_day(to_date(q_end_date||'.'||mydate, 'MM.YYYY'))
and trunc(account_close_date) > last_day(to_date(q_end_date||'.'||mydate, 'MM.YYYY')) and trunc(account_open_date) <= last_day(to_date(q_end_date||'.'||mydate, 'MM.YYYY')) 
group by years_and_quarters.mydate,years_and_quarters.quarter
  

Как насчет простого агрегирования по кварталам?

 SELECT sum(a.limit_amount) as LIMIT,  
       sum(b.balance_amount) as OUTSTANDING,
       'LOAN' as TYPE,
       to_char(balance_date, '"Q"Q YYYY') as quarter
FROM accounts a LEFT JOIN
     account_balances b 
     ON a.account_key = b.account_key AND
        b.balance_type_key = 16 AND
        b.balance_date = LAST_DAY(TRUNC(balance_date, 'Q')   INTERVAL '2' MONTH)
WHERE a.account_close_date > LAST_DAY(TRUNC(balance_date, 'Q')   INTERVAL '2' MONTH) AND
      a.account_open_date <= LAST_DAY(TRUNC(balance_date, 'Q')   INTERVAL '2' MONTH)
GROUP BY TRUNC(balance_date, 'Q') ;
  

Похоже, ваш запрос сфокусирован на последнем дне квартала. Это достигается добавлением двух месяцев к началу и использованием LAST_DAY() .