Реализация SCD типа 2 в Oracle

Question

Реализация SCD типа 2 в Oracle

Во-первых, я хотел бы сказать, что я новичок в сообществе stackru и относительно плохо знаком с самим SQL, поэтому прошу прощения, если я неправильно отформатировал свой вопрос или не сформулировал свои требования четко.

Я пытаюсь реализовать тип 2 SCD в Oracle. Структура исходной таблицы (customer_records) приводится ниже.

CREATE TABLE customer_records(
    day date,
    snapshot_day_dw_id number,
    vend_loy_acct_dw_id number,
    hub_cust_id number,
    cur_loy_tier_dw_id number
);

INSERT INTO customer_records 
(day,snapshot_day_dw_id,vend_loy_acct_dw_id,hub_cust_id,cur_loy_tier_dw_id)
VALUES
(9/24/2014,6266,71047795,476095,3103),
(10/1/2014,6273,71047795,476095,3103),
(10/8/2014,6280,71047795,476095,3103),
(10/15/2014,6287,71047795,476095,3103),
(10/22/2014,6291,71047795,476095,3102),
(10/29/2014,6330,71047795,476095,3102),
(11/05/2015,6351,71047795,476095,3102),
(11/12/2015,6440,71047795,476095,3103);

Таблица выше обновляется еженедельно, и я вытащил записи для конкретного клиента, представленного vend_loy_acct_dw_id а также hub_cust_id, Так что каждый клиент будет иметь уникальный vend_loy_acct_dw_id а также hub_cust_id, Я пытаюсь отслеживать изменения в уровне (cur_loy_tier_dw_id) клиента. Может так случиться, что уровень клиента может оставаться неизменным в течение нескольких недель, и мы готовы отслеживать только изменения уровня клиента.

Желаемый результат (таблица измерений) будет выглядеть примерно так:

SK  Version   Date_From    Date_To    Vend_Loy_Acct_Dw_Id   Hub_Cust_Id  Cur_Cust_Tier_Id

1     1       9/24/2014    10/22/2014    71047795            476095       3103
2     2       10/22/2014   11/05/2015    71047795            476095       3102
3     3       11/05/2015   12/31/2199    71047795            476095       3103

Так что, когда уровень клиента попадает в какое-либо изменение, мы отслеживаем это в новой таблице. Кроме того, желая включить current_flag = 'Y' для самого текущего уровня.

Я хочу быть в состоянии сделать это с помощью слияния.

1

sql oracle etl bulk-operations

Источник

user8740527 16 фев '19 в 19:07

2 ответа

Другие вопросы по тегам sql oracle etl bulk-operations

user146325 17 фев '19 в 14:44 2019-02-17 14:44 · Answer 1 · 2019-02-17 14:44

Я хочу быть в состоянии сделать это с помощью слияния.

Мердж не сделает это за вас. MERGE - это, в основном, оператор case: для каждой записи в подзапросе USING мы можем вставлять сопоставленные записи или обновлять сопоставленные записи. Подвох заключается в том, что при изменении уровня существующего клиента необходимо выполнить DML для двух записей измерений:

обновить предыдущую текущую запись - установить current_flag = 'N', установлено day_to в systimestamp (или что угодно).
вставить новую текущую запись.

Таким образом, у вас должен быть процесс - возможно, процедура PL/SQL - который выполняет инструкцию UPDATE, чтобы закрыть текущие записи с истекшим сроком действия, а затем INSERT для добавления новых текущих записей.

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

Вы описываете себя как относительно нового для SQL, поэтому вы можете беспокоиться об этом, но не волнуйтесь. Избегайте преждевременной оптимизации. Сделайте самое простое, что может сработать, и настройте его по мере необходимости. Подзапрос должен быть наиболее эффективным способом определения текущих записей, которые необходимо обновить. Базы данных Oracle являются рабочими лошадками и могут справляться с существенными нагрузками, если мы пишем разумный SQL.

В вашем случае это означает:

используйте операции над множествами (то есть не построчно) для UPDATE и INSERT.
убедитесь, что вы работаете с наименьшим необходимым набором записей. Применяйте изменения только к записям в базовой таблице, которые изменились с момента последнего обновления измерения. В вашем случае вам нужно отслеживать customer_records.snapshot_day_dw_id и применять изменения только для записей, которые имеют более высокий snapshot_day_dw_id (а может и нет, я догадываюсь на вашем процессе).
правильно индексировать таблицу измерений, чтобы она эффективно применяла подзапрос.

user10676716 16 фев '19 в 23:41 2019-02-16 23:41 · Answer 2 · 2019-02-16 23:41

Вот подход к группированию последовательных записей, имеющих один и тот же уровень, при обнаружении изменений.

Идея состоит в том, чтобы самостоятельно присоединиться к таблице и связать каждую запись со следующей записью, имеющей другой уровень. Это делается с помощью NOT EXISTS условие с коррелированным подзапросом.

LEFT JOIN необходимо, чтобы избежать фильтрации последней записи (которой принадлежит текущий уровень), у которой еще нет следующей записи: для этой записи мы используем COALESCE() установить дату окончания по умолчанию.

SELECT 
    c1.day day_from,
    COALESCE(c2.day, TO_DATE('2199-12-31', 'yyyy-mm-dd')) day_to,
    c1.VEND_LOY_ACCT_DW_ID,
    c1.HUB_CUST_ID, 
    c1.CUR_LOY_TIER_DW_ID
FROM customer_records c1
LEFT JOIN customer_records c2 
    ON  c2.VEND_LOY_ACCT_DW_ID = c1.VEND_LOY_ACCT_DW_ID
    AND c2.HUB_CUST_ID         = c1.HUB_CUST_ID
    AND c2.CUR_LOY_TIER_DW_ID <> c1.CUR_LOY_TIER_DW_ID
    AND c2.DAY                 > c1.DAY
    AND NOT EXISTS (
        SELECT 1
        FROM customer_records c3
        WHERE
                c3.VEND_LOY_ACCT_DW_ID = c1.VEND_LOY_ACCT_DW_ID
            AND c3.HUB_CUST_ID         = c1.HUB_CUST_ID
            AND c3.CUR_LOY_TIER_DW_ID <> c1.CUR_LOY_TIER_DW_ID
            AND c3.DAY                 > c1.DAY
            AND c3.DAY                 < c2.DAY
    )

Это возвращает:

 DAY_FROM  | DAY_TO    | VEND_LOY_ACCT_DW_ID | HUB_CUST_ID | CUR_LOY_TIER_DW_ID
 :-------- | :-------- | ------------------: | ----------: | -----------------:
 24-SEP-14 | 22-OCT-14 |            71047795 |      476095 |               3103
 01-OCT-14 | 22-OCT-14 |            71047795 |      476095 |               3103
 08-OCT-14 | 22-OCT-14 |            71047795 |      476095 |               3103
 15-OCT-14 | 22-OCT-14 |            71047795 |      476095 |               3103
 22-OCT-14 | 12-NOV-15 |            71047795 |      476095 |               3102
 29-OCT-14 | 12-NOV-15 |            71047795 |      476095 |               3102
 05-NOV-15 | 12-NOV-15 |            71047795 |      476095 |               3102
 12-NOV-15 | 31-DEC-99 |            71047795 |      476095 |               3103

Теперь мы можем сгруппировать набор записей по уровню и дате окончания, чтобы получить ожидаемые результаты. ROW_NUMBER() могу дать вам номер версии. Также легко проверить, какая запись является текущей, как описано выше.

SELECT 
    ROW_NUMBER() OVER(ORDER BY c2.day) version,
    DECODE(c2.day, NULL, 'Y') current_flag,
    MIN(c1.day) day_from,
    COALESCE(c2.day, TO_DATE('2199-12-31', 'yyyy-mm-dd')) day_to,
    c1.VEND_LOY_ACCT_DW_ID,
    c1.HUB_CUST_ID, 
    c1.CUR_LOY_TIER_DW_ID
FROM customer_records c1
LEFT JOIN customer_records c2 
    ON  c2.VEND_LOY_ACCT_DW_ID = c1.VEND_LOY_ACCT_DW_ID
    AND c2.HUB_CUST_ID         = c1.HUB_CUST_ID
    AND c2.CUR_LOY_TIER_DW_ID <> c1.CUR_LOY_TIER_DW_ID
    AND c2.DAY                 > c1.DAY
    AND NOT EXISTS (
        SELECT 1
        FROM customer_records c3
        WHERE
                c3.VEND_LOY_ACCT_DW_ID = c1.VEND_LOY_ACCT_DW_ID
            AND c3.HUB_CUST_ID         = c1.HUB_CUST_ID
            AND c3.CUR_LOY_TIER_DW_ID <> c1.CUR_LOY_TIER_DW_ID
            AND c3.DAY                 > c1.DAY
            AND c3.DAY                 < c2.DAY
    )
GROUP BY
    c1.VEND_LOY_ACCT_DW_ID, 
    c1.HUB_CUST_ID, 
    c1.CUR_LOY_TIER_DW_ID, 
    c2.day
ORDER BY
    day_from

Результаты:

ВЕРСИЯ | CURRENT_FLAG | DAY_FROM  | DAY_TO    | VEND_LOY_ACCT_DW_ID | HUB_CUST_ID | CUR_LOY_TIER_DW_ID
------: |:----------- |:-------- |:-------- | ------------------: | ----------: | -----------------:
      1 | N            | 24-СЕНТЯБРЯ-14 | 22-ОКТ-14 |            71047795 |      476095 |               3103
      2 | N            | 22-ОКТ-14 | 12-НОЯБРЯ-15 |            71047795 |      476095 |               3102
      3 | Y            | 12-НОЯБРЯ-15 | 31-DEC-99 |            71047795 |      476095 |               3103

Демо на БД Fiddle

В Oracle вы можете превратить любой выбор в запрос слияния, используя синтаксис MERGE. Вы можете сопоставить все ожидаемые столбцы current_flag а также day_toи обновите их, если запись уже существует; иначе просто вставьте новый.

MERGE INTO dimensions dim
USING (
   -- above query goes here --
) cust 
    ON  dim.DAY_FROM            = cust.DAY_FROM
    AND dim.VEND_LOY_ACCT_DW_ID = cust.VEND_LOY_ACCT_DW_ID
    AND dim.HUB_CUST_ID         = cust.HUB_CUST_ID
    AND dim.CUR_LOY_TIER_DW_ID  = cust.CUR_LOY_TIER_DW_ID
WHEN MATCHED THEN UPDATE SET 
    dim.DAY_TO = cust.DAY_TO,
    dim.CURRENT_FLAG = cust.CURRENT_FLAG
WHEN NOT MATCHED THEN 
    INSERT (
        dim.DAY_FROM, 
        dim.VERSION, 
        dim.CURRENT_FLAG, 
        dim.DAY_FROM, 
        dim.DAY_TO, 
        dim.VEND_LOY_ACCT_DW_ID, 
        dim.HUB_CUST_ID, 
        dim.CUR_LOY_TIER_DW_ID
    ) VALUES (
        cust.DAY_FROM, 
        cust.VERSION, 
        cust.CURRENT_FLAG, 
        cust.DAY_FROM, 
        cust.DAY_TO, 
        cust.VEND_LOY_ACCT_DW_ID, 
        cust.HUB_CUST_ID, 
        cust.CUR_LOY_TIER_DW_ID
    )