MONTHS_BETWEEN / NTH_VALUE
У меня есть уникальная логика, которую я надеюсь поместить в запрос, а не вызывать функцию.
В этом приложении я просматриваю последние 4 отчетных квартала для данного тикерного символа. Даты в таблице представлены как ГГГГММ, как в этом случае:
201804,
201801,
201710,
201704
Теперь, возможно, не хватает четверти. Может, компания не представила квартальный отчет или что-то в этом роде.
Итак, что я пытаюсь сделать, это посмотреть, есть ли пробел или недостающая четверть:
201804,
201710,
201704,
201701
Здесь отсутствует 201801 ........ Я думаю, что каким-то образом мне нужно сравнить MONTHS_BETWEEN с текущей датой записи и значением даты LEAD 1 или NTH_VALUE 1 и увидеть, что разница составляет 3 месяца .. ..... за последние 4 записи назад (4 квартала). В противном случае какая-то запись отсутствует, и я верну значение NULL, что и требуется приложению.
Есть мысли или уникальная логика? Или мне нужно будет закодировать функцию и вызвать ее?
Спасибо!!!
Ответы 2
Просто конвертируйте все на сегодняшний день и используйте манипуляции с датой с помощью самостоятельных соединений.
With cte as (
SELECT "val",
TO_DATE("val", 'YYYYMM') as original
FROM Table1
)
SELECT c1."val", c1.original,
c2.original as next_quarter,
c3.original as previous_quarter
FROM cte c1
LEFT JOIN cte c2
ON c1.original = ADD_MONTHS(c2.original, -3)
LEFT JOIN cte c3
ON c1.original = ADD_MONTHS(c3.original, 3)
ORDER BY c1."val"
ВЫХОД
| val | ORIGINAL | NEXT_QUARTER | PREVIOUS_QUARTER |
|--------|----------------------|----------------------|----------------------|
| 201701 | 2017-01-01T00:00:00Z | 2017-04-01T00:00:00Z | (null) |
| 201704 | 2017-04-01T00:00:00Z | (null) | 2017-01-01T00:00:00Z |
| 201710 | 2017-10-01T00:00:00Z | (null) | (null) |
| 201804 | 2018-04-01T00:00:00Z | (null) | (null) |
Вы можете создать календарь кварталов и объединить эти данные с данными за квартал. Так выглядит запрос. p_start_year - это год, с которого начинается ваш календарь, а p_no_of_qtrs подсчитывает, сколько кварталов отображается в календаре.
WITH
mydata
AS
(SELECT '201804' AS quarter FROM DUAL
UNION
SELECT '201801' AS quarter FROM DUAL
UNION
SELECT '201710' AS quarter FROM DUAL
UNION
SELECT '201704' AS quarter FROM DUAL),
calendar
AS
(SELECT TO_CHAR (
ADD_MONTHS (
TO_DATE (:p_start_year || '-01-01', 'YYYY-MM-DD'),
(LEVEL
- 1)
* 3
),
'YYYYMM'
)
AS quarter
FROM DUAL
CONNECT BY LEVEL <= :p_no_of_qtrs)
SELECT c.quarter, d.quarter AS status
FROM mydata d,
calendar c
WHERE c.quarter = d.quarter(+)
ORDER BY c.quarter;
Вот результат.
QUARTER | STATUS
201701 |
201704 | 201704
201707 |
201710 | 201710
201801 | 201801
201804 | 201804
201807 |
201810 |
Отсутствующие четверти отображаются как NULL.