Блог

У меня есть таблица, которая выглядит следующим образом:

  -------------------- --------- 
|    Month (date)    |  amount |
 -------------------- --------- 
| 2016-10-01         | 20      |
| 2016-08-01         | 10      |
| 2016-07-01         | 17      |
 -------------------- --------- 
  

Я ищу запрос (оператор sql), который удовлетворяет следующим условиям:

• Дайте мне значение за предыдущий месяц.
• Если для предыдущего месяца нет значения, заблокируйте время назад, пока не будет найдено значение.
• Если есть только значение для текущего месяца, дайте мне это значение.

В таблице примеров строка, которую я ищу, будет такой:

  -------------------- --------- 
| 2016-08-01         | 10      |
 -------------------- --------- 
  

У кого-нибудь есть идея для несложного запроса select?

Заранее спасибо, Питер

Вам может понадобиться следующее:

 SELECT *
  FROM (  SELECT *
            FROM test
           WHERE TRUNC(SYSDATE, 'month') >= month
        ORDER BY CASE
                    WHEN TRUNC(SYSDATE, 'month') = month
                        THEN 0 /* if current month, ordered last */
                        ELSE 1 /* previous months are ordered first */
                    END DESC,
                    month DESC /* among previous months, the greatest first */
       )
 WHERE ROWNUM = 1    
  

Другой способ, использующий MAX

 WITH tbl AS (
    SELECT TO_DATE('2016-10-01', 'YYYY-MM-DD') AS "month", 20 AS amount FROM dual
    UNION 
    SELECT TO_DATE('2016-08-01', 'YYYY-MM-DD') AS "month", 10 AS amount FROM dual    
    UNION 
    SELECT TO_DATE('2016-07-01', 'YYYY-MM-DD') AS "month", 5 AS amount FROM dual        
)
SELECT * 
  FROM tbl
 WHERE TRUNC("month", 'MONTH') = NVL((SELECT MAX(t."month") 
                                        FROM  tbl t 
                                       WHERE t."month" < TRUNC(SYSDATE, 'MONTH')), 
                                     TRUNC(SYSDATE, 'MONTH'));
  

Я бы использовал row_number() :

 select t.*
from (select t.*,
             row_number() over (order by (case when to_char(dte, 'YYYY-MM') = to_char(sysdate, 'YYYY-MM') then 1 else 2 end) desc,
                                          dte desc
                               ) as seqnum
      from t
     ) t
where seqnum = 1;
  

На самом деле, вам не нужно row_number() для этого:

 select t.*
from (select t.*
      from t
      order by (case when to_char(dte, 'YYYY-MM') = to_char(sysdate, 'YYYY-MM') then 1 else 2 end) desc,
               dte desc
     ) t
where rownum = 1;
  

Это не самый приятный запрос, но он должен сработать.

 select amount, date from (
  select amount, date, row_number over(partition by HERE_PUT_ID order by 
    case trunc(date, 'month') when trunc(sysdate, 'month') then to_date('00010101', 'yyyymmdd') else trunc(date, 'month')  end
    desc) r)
where r = 1;
  

Я предполагаю, что у вас есть какой-то идентификатор в таблице, поэтому поместите столбец id вместо HERE_PUT_ID, если вы хотите запросить всю таблицу, просто удалите: partition by HERE_PUT_ID

Я добавил больше данных для тестирования и столбец «id» (более реалистичный сценарий), чтобы показать, как это будет работать. Если в ваших данных нет «id», просто удалите любую ссылку на него из решения.

Примечания — month это зарезервированное слово Oracle, не используйте его в качестве имени столбца. Решение предполагает, что столбец date содержит даты, которые уже усечены до начала месяца. Хитрость в «порядке по» в dense_rank last заключается в присвоении значения (ЛЮБОГО значения!), когда месяц является текущим месяцем; по умолчанию значение, присвоенное всем другим месяцам, равно NULL, которые по умолчанию следуют за любым ненулевым значением в порядке возрастания.

Возможно, вы захотите протестировать различные решения на эффективность, если важно время выполнения.

 with
     inputs ( id, mth, amount ) as (
       select 1, date '2016-10-01', 20 from dual union all
       select 1, date '2016-08-01', 10 from dual union all
       select 1, date '2016-07-01', 17 from dual union all
       select 2, date '2016-10-01', 30 from dual union all
       select 2, date '2016-09-01', 25 from dual union all
       select 3, date '2016-10-01', 20 from dual union all
       select 4, date '2016-08-01', 45 from dual union all
       select 4, date '2016-06-01', 30 from dual
     )
-- end of TEST DATA - the solution (SQL query) is below this line
select id,
       max(mth) keep(dense_rank last order by
                case when mth = trunc(sysdate, 'mm') then 0 end, mth) as mth,
       max(amount) keep(dense_rank last order by
                case when mth = trunc(sysdate, 'mm') then 0 end, mth) as amount
from inputs
group by id
order by id   -- ORDER BY is optional
;


 ID MTH         AMOUNT
--- ---------- -------
  1 2016-08-01      10
  2 2016-09-01      25
  3 2016-10-01      20
  4 2016-08-01      45
  

Вы могли бы отсортировать данные в нужном направлении:

 with MyData as
(
  SELECT to_date('2016-10-01','YYYY-MM-DD') MY_DATE, 20 AMOUNT FROM DUAL UNION
  SELECT to_date('2016-08-01','YYYY-MM-DD') MY_DATE, 10 AMOUNT FROM DUAL UNION
  SELECT to_date('2016-07-01','YYYY-MM-DD') MY_DATE, 17 AMOUNT FROM DUAL 
),
MyResult AS (
  SELECT 
    D.*  
  FROM MyData D
  ORDER BY 
    DECODE(
      12*TO_CHAR(MY_DATE,'YYYY')   TO_CHAR(MY_DATE,'MM'),
      12*TO_CHAR(SYSDATE,'YYYY')   TO_CHAR(SYSDATE,'MM'),
      -1,
      12*TO_CHAR(MY_DATE,'YYYY')   TO_CHAR(MY_DATE,'MM')) 
  DESC
)
SELECT * FROM MyResult WHERE RowNum = 1