Oracle SQL или PL/SQL: добавьте несколько столбцов в MATCH_RECOGNIZE с разными вычислениями

Question

Этот пост предназначен только для обучения и образовательных целей.

Структура таблицы, операторы вставки и SQL-запрос обновлены по ссылке ниже db fiddle, чтобы этот пост был чистым и кратким. Пожалуйста, обратитесь по ссылке ниже:

db<> скрипка

Я пытаюсь вычислить «скользящее среднее за 7 дней», а также добавить вычисленные столбцы на основе этого нового столбца «скользящее среднее за 7 дней». Пробовал разные методы и получал ошибку "оконные функции здесь не разрешены". Позже попробовал в электронной таблице Excel и получил формулу работы. Все еще не уверен, как добиться этого в SQL с Match_Recognize, и поэтому обращаюсь за помощью к вам, всем экспертам.

Обратите внимание: я загружаю скриншоты Excel и формул, поскольку stackoverflow не дает мне возможности загрузить электронную таблицу Excel.

Ниже приведен ожидаемый результат (столбцы выделены желтым цветом):

На скриншоте ниже показаны формулы для новых столбцов (столбцы J, K и L): здесь строка 8 представляет собой среднее значение предыдущих строк, тогда как строка 9 и далее имеет каскадную формулу в предыдущих строках.

Ценю любую помощь в этом. Заранее спасибо.

Спасибо,
Рича

Answer 1

Похоже, это то, что вам нужно: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=611827551a8d454d69a0986ea8c0b2da

      with
 rs_fixed as (select stock,to_date(Close_Date,'dd-mm-yyyy') as Close_Date, price from raw_source) 
,pre as (
    SELECT *
    FROM   rs_fixed
    MATCH_RECOGNIZE (
      PARTITION BY stock
      ORDER BY Close_Date
      MEASURES
        COUNT(*) AS match_size,
        CLASSIFIER() AS pttrn
      ALL ROWS PER MATCH
      PATTERN (
        down+  | up+  | other
      )
      DEFINE
        down AS PREV(price) > price,
        up AS  PREV(price) < price
    )
)
select 
    pre.*
   ,avg(price)
        over(
            partition by stock 
            order by Close_Date
            range between 7 preceding and 1 preceding
            ) as "7_mov_avg"

from pre

Как видите, я исправил ваше определение close_date- так должно быть dateне varchar2 для арифметики дат. Также я не знаю, почему вы агрегируете C2-C9 для avg для ячейки C8 , поэтому я изменил его в соответствии с вашим описанием за предыдущие 7 дней, а не за 5 предыдущих и 1 последующий, но если вам это действительно нужно, вы можете заменить Оконная оговорка выше, чтобы range between 5 preceding and 1 following. И, наконец, если вам нужно показать только строки после 7-го дня, вы можете использовать caseпредложение из последнего запроса в DBFiddle.