Как получить старое значение из значений добавочного года?

У меня есть две таблицы:

Таблица 1:

|  Year  |  CRN  |  CID   | Cap |
| 201910 |  14   |  ABC1  | 12  |
| 201910 |  15   |  ABC1  | 14  |
| 201820 |  25   |  ABC1  | 15  |
| 201820 |  26   |  ABC1  | 25  |
| 201810 |  43   |  ABC1  | 10  |
| 201720 |  55   |  ABC1  | 11  |

Таблица 2:

|  Year   |  CRN  | BLCK  |
| 201910  |  14   |  A1   |
| 201910  |  15   |  A1   |
| 201820  |  25   |  B2   |
| 201820  |  26   |  B2   |
| 201810  |  43   |  C3   |
| 201720  |  55   |  C4   |
| 201720  |  95   |  F5   |
| 201710  |  65   |  D4   |

Я хочу вернуться:

  1. CID из таблицы 1 для CRN из таблицы 2. Должен возвращать только один CID, поскольку один год может иметь несколько CRN для одного и того же CID в таблице 1 и для одного и того же BLCK в таблице 2. Например, для 201910 CRN 14 и 15 имеют один и тот же CID ABC1 и один и тот же BLCK A1. Таким образом, он должен вернуть ABC1 один раз.
  2. Значение BLCK за предыдущий год и сумма значений Cap из таблицы 1 для всех CRN для CID, найденных выше (ABC1) в предыдущем году. 201820 всегда идет после 201810, а значения Year всегда увеличиваются как 201810, 201820, 201910, 201920... Например, для 201910 я должен получить 40 как сумму Cap, потому что для 201820 года есть два CRN для CID ABC1. , Не уверен, что rownumber() всегда будет работать, потому что мне нужно рассмотреть возможность уменьшения года, чтобы вернуться от текущего значения к предыдущему значению.

Я использую Oracle 11g.

Expected Output: 

| Year  |  CID |CurrYear Cap|CurrYear BLCK|Last Year|LastYear Cap|LastYear BLCK|
|201910 | ABC1 |      26    |      A1     | 201820  |    40      |     B2      |


sql oracle group-by aggregation analytic-functions
person django-unchained    schedule 03.02.2019    source источник
comment
Пожалуйста, покажите нам ожидаемый результат в виде табличных данных. Структуры данных — это язык, который мы все разделяем.   -  person GMB    schedule 03.02.2019
comment
Извинения @GMB. Добавил в вопрос выше.   -  person django-unchained    schedule 03.02.2019

Ответы (1)


arrow_upward
2
arrow_downward

Вы можете использовать функцию lag() и grouping с помощью year как

select "Year", "CID", "CurrYear Cap", "CurrYear BLCK",
       "Last Year", "LastYear Cap", "LastYear BLCK"
  from
    (
    select "Year", "CID", "CurrYear Cap", "CurrYear BLCK",
       lag("Year") over (order by "Year") as "Last Year", 
       lag("CurrYear Cap") over (order by "Year") "LastYear Cap",
       lag("CurrYear BLCK") over (order by "Year") "LastYear BLCK",
       row_number() over (order by "Year" desc) as rn
      from
        (
        with table1(Year, CRN, CID, Cap) as
        (
         select 201910 ,  14   ,  'ABC1'  , 12 from dual union all 
         select 201910 ,  15   ,  'ABC1'  , 14 from dual union all 
         select 201820 ,  25   ,  'ABC1'  , 15 from dual union all 
         select 201820 ,  26   ,  'ABC1'  , 25 from dual union all 
         select 201810 ,  43   ,  'ABC1'  , 10 from dual union all 
         select 201720 ,  55   ,  'ABC1'  , 11 from dual    
        )  ,
             table2(Year   ,  CRN  , BLCK) as
        (
         select 201910  ,  14   ,  'A1'   from dual union all
         select 201910  ,  15   ,  'A1'   from dual union all
         select 201820  ,  25   ,  'B2'   from dual union all
         select 201820  ,  26   ,  'B2'   from dual union all
         select 201810  ,  43   ,  'C3'   from dual union all
         select 201720  ,  55   ,  'C4'   from dual union all
         select 201720  ,  95   ,  'F5'   from dual union all
         select 201710  ,  65   ,  'D4'   from dual
        )
        select max(t1.year) as "Year", 
               max(t1.CID) as "CID", sum(t1.Cap) as "CurrYear Cap", max(t2.blck) as "CurrYear BLCK"           
          from table1 t1
          join table2 t2 on t1.year = t2.year and t1.crn = t2.crn 
         group by t1.year  
        ) 
    )
where rn = 1;

Year    CID   CurrYear Cap  CurrYear BLCK   Last Year   LastYear Cap  LastYear BLCK
------  ----  ------------  -------------   ----------  ------------  -------------- 
201910  ABC1       26             A1          201820         40             B2

если where rn = 1 в конце заменить на order by rn, то все строки для всех значений "Год" будут перечислены в порядке.

Демо

person Barbaros Özhan    schedule 03.02.2019
comment
@django-unchained всегда пожалуйста. Я рассматривал значения cab, т.е. 15 и 25, в table1 - person Barbaros Özhan; 03.02.2019
comment
Извините, я ошибся в ожидаемом результате. - person django-unchained; 04.02.2019
comment
Сможете ли вы изменить свой запрос для ожидаемого результата ниже? - person django-unchained; 04.02.2019