Привет, твоя задача - воссоздать трансформацию из Informatica Power Center на Oracle…
- User
Привет, твоя задача - воссоздать трансформацию из Informatica Power Center на Oracle SQL. Мне нужно воссоздать функционал из Informatica Power Center на Oracle SQL При этом тебе нельзя использовать переменные в скрипте и нельзя использовать рекурсивные CTE, можешь использовать оконные функции вроде LISTAGG, LAG и других
Вот информация о трансформации
Тип трансформации: Expression Имя: EXPTRANS_VX Версия объекта: 1 Перепользуемый: Нет Номер версии: 1 Детальный Разбор Полей Поле: ACCOUNT__OID Тип данных: Decimal Точность: 18 Масштаб: 0 Тип порта: Input/Output Выражение (Формула): plaintext
Copy ACCOUNT__OID Описание: Входное/выходное поле, передающее идентификатор аккаунта. Поле: ACNT_CONTRACT__ID Тип данных: Decimal Точность: 10 Масштаб: 0 Тип порта: Input/Output Выражение (Формула): plaintext
Copy ACNT_CONTRACT__ID Описание: Входное/выходное поле, передающее идентификатор контракта аккаунта. Поле: M_TRANSACTION__ID Тип данных: Decimal Точность: 18 Масштаб: 0 Тип порта: Input/Output Выражение (Формула): plaintext
Copy M_TRANSACTION__ID Описание: Входное/выходное поле, передающее идентификатор транзакции. Поле: POSTING_DATE Тип данных: Date/Time Точность: 29 Масштаб: 9 Тип порта: Input/Output Выражение (Формула): plaintext
Copy POSTING_DATE Описание: Входное/выходное поле, передающее дату проводки. Поле: BAL Тип данных: Decimal Точность: 28 Масштаб: 2 Тип порта: Input/Output Выражение (Формула): plaintext
Copy BAL Описание: Входное/выходное поле, передающее баланс. Поле: DAY_ARR_OD Тип данных: Decimal Точность: 18 Масштаб: 0 Тип порта: Output Выражение (Формула): plaintext
Copy IIF (BAL = 0, 0, DATE_DIFF(POSTING_DATE, ADD_TO_DATE(TO_DATE('04.10.2024','DD.MM.YYYY'), 'DAY',1), 'DD')) Описание: Вычисляет разницу в днях между датой проводки и заданной датой 04.10.2024, если баланс не равен нулю. Если баланс равен нулю, возвращает 0. Поле: STEP Тип данных: Decimal Точность: 10 Масштаб: 0 Тип порта: Локальная переменная Выражение (Формула): plaintext
Copy IIF(CONT_v <> ACNT_CONTRACT__ID OR ISNULL(CONT_v), 0, IIF( BAL = 0 AND REG_REPLACE(LIST_ACCT, TO_CHAR(ACCOUNT__OID), '1') = '1', STEP + 1, IIF( BAL <> 0 AND LIST_ACCT = '' AND POSTING_DATE = POSTING_DATE_v AND INSTR(ACCT_v, TO_CHAR(ACCOUNT__OID)) = 0 AND CCAT <> 'C', STEP - 1, STEP ) ) ) Описание: Логика определения шага (STEP) на основе условий сравнения различных полей и переменных. Примеры условий включают проверку баланса, наличия аккаунта в списке и соответствия дат проводки. Поле: LIST_ACCT Тип данных: String Точность: 2500 Масштаб: 0 Тип порта: Локальная переменная Выражение (Формула): plaintext
Copy IIF( CONT_v <> ACNT_CONTRACT__ID OR ISNULL(CONT_v), TO_CHAR(ACCOUNT__OID), IIF( CONT_v = ACNT_CONTRACT__ID AND INSTR(LIST_ACCT, TO_CHAR(ACCOUNT__OID)) <> 0 AND BAL <> 0, LIST_ACCT, IIF( CONT_v = ACNT_CONTRACT__ID AND INSTR(LIST_ACCT, TO_CHAR(ACCOUNT__OID)) = 0 AND BAL <> 0, LIST_ACCT || TO_CHAR(ACCOUNT__OID), IIF( CONT_v = ACNT_CONTRACT__ID AND INSTR(LIST_ACCT, TO_CHAR(ACCOUNT__OID)) <> 0 AND BAL = 0, REG_REPLACE(LIST_ACCT, TO_CHAR(ACCOUNT__OID), ''), '' ) ) ) ) Описание: Формирует список аккаунтов (LIST_ACCT) на основе условий сравнения контрактного ID, наличия аккаунта в списке, баланса и других параметров. Поле: STEP_O Тип данных: Decimal Точность: 10 Масштаб: 0 Тип порта: Output Выражение (Формула): plaintext
Copy STEP Описание: Выходное поле, передающее значение шага (STEP). Поле: LAST_ROW_v Тип данных: Decimal Точность: 10 Масштаб: 0 Тип порта: Локальная переменная Выражение (Формула): plaintext
Copy IIF(CONT_v <> ACNT_CONTRACT__ID OR ISNULL(CONT_v), 0, LAST_ROW_v + 1) Описание: Логика для определения последней строки (LAST_ROW_v). Если контрактный ID не совпадает или CONT_v пуст, устанавливает 0, иначе увеличивает LAST_ROW_v на 1. Поле: LAST_ROW_o Тип данных: Decimal Точность: 10 Масштаб: 0 Тип порта: Output Выражение (Формула): plaintext
Copy LAST_ROW_v Описание: Выходное поле, передающее значение последней строки (LAST_ROW_v). Поле: POSTING_DATE_v Тип данных: Date/Time Точность: 29 Масштаб: 9 Тип порта: Локальная переменная Выражение (Формула): plaintext
Copy POSTING_DATE Описание: Локальная переменная для хранения даты проводки. Поле: ACCT_v Тип данных: NString Точность: 2500 Масштаб: 0 Тип порта: Локальная переменная Выражение (Формула): plaintext
Copy IIF( CONT_v <> ACNT_CONTRACT__ID OR ISNULL(CONT_v), TO_CHAR(ACCOUNT__OID), ACCT_v || ' ' || TO_CHAR(ACCOUNT__OID) ) Описание: Формирует строку аккаунтов (ACCT_v) на основе условий сравнения контрактного ID и наличия аккаунта. Поле: CONT_v Тип данных: Decimal Точность: 18 Масштаб: 0 Тип порта: Локальная переменная Выражение (Формула): plaintext
Copy ACNT_CONTRACT__ID Описание: Локальная переменная для хранения контрактного ID. Поле: SUM_ACCT_v Тип данных: Decimal Точность: 28 Масштаб: 0 Тип порта: Локальная переменная Выражение (Формула): plaintext
Copy SUM_ACCT_v Описание: Локальная переменная для хранения суммы по аккаунтам. Поле: REPORT_DATE Тип данных: Date/Time Точность: 29 Масштаб: 9 Тип порта: Output Выражение (Формула): plaintext
Copy TO_DATE('04.10.2024','DD.MM.YYYY') Описание: Выходное поле, содержащее фиксированную дату отчёта 04.10.2024. Поле: LIST_ACCT_O Тип данных: String Точность: 2500 Масштаб: 0 Тип порта: Output Выражение (Формула): plaintext
Copy ACCT_v Описание: Выходное поле, передающее список аккаунтов (ACCT_v). Поле: CCAT Тип данных: String (nstring в первой версии) Точность: 1 Масштаб: 0 Тип порта: Input/Output Выражение (Формула): plaintext
Copy CCAT Описание: Входное/выходное поле, вероятно, классификатор или категория.
Вот код таблицы, из которой берутся данные для трансформации
CREATE TABLE u14626.SRTTRANS_To_CHECK_v1 ( ACNT_CONTRACT__ID number(18), POSTING_DATE date, M_TRANSACTION__ID number(18), ACCOUNT__OID number(18), BAL number(28,2), CCAT varchar2(1) );
- Assistant
Привет! Понял задачу. Нужно преобразовать логику трансформации из Informatica Power Center в Oracle SQL, используя оконные функции и избегая переменных и рекурсивных CTE. Постараюсь воспроизвести основные элементы трансформации, насколько это возможно с помощью SQL.
Общий Подход
-
Поля-передатчики:
ACCOUNT__OID,ACNT_CONTRACT__ID,M_TRANSACTION__ID,POSTING_DATE,BAL,CCAT— просто передаются из исходной таблицы. -
Вычисляемое поле
DAY_ARR_OD: ИспользуемCASEдля реализации логикиIIF. -
Поля
STEP,LIST_ACCTи другие переменные: Эти поля зависят от накопительной логики, которая в SQL без рекурсии и переменных реализуется ограниченно. Попробуем использовать оконные функции для эмуляции этой логики.
Реализация SQL Запроса
WITH base AS ( SELECT ACCOUNT__OID, ACNT_CONTRACT__ID, M_TRANSACTION__ID, POSTING_DATE, BAL, CCAT, -- Вычисление DAY_ARR_OD CASE WHEN BAL = 0 THEN 0 ELSE TRUNC(POSTING_DATE) - TO_DATE('04.10.2024', 'DD.MM.YYYY') - 1 END AS DAY_ARR_OD, -- Порядок строк для оконных функций ROW_NUMBER() OVER (PARTITION BY ACNT_CONTRACT__ID ORDER BY POSTING_DATE, ACCOUNT__OID) AS rn FROM u14626.SRTTRANS_To_CHECK_v1 ) , list_acct AS ( SELECT b.*, -- Составление списка ACCOUNT__OID до текущей строки LISTAGG(b.ACCOUNT__OID, ',') WITHIN GROUP (ORDER BY b.rn) OVER (PARTITION BY b.ACNT_CONTRACT__ID ORDER BY b.rn ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS LIST_ACCT FROM base b ) , step_calc AS ( SELECT la.*, -- Вычисление шага STEP с использованием накопительной суммы SUM( CASE WHEN (CONT_v <> ACNT_CONTRACT__ID OR CONT_v IS NULL) THEN 0 WHEN BAL = 0 AND REGEXP_REPLACE(LIST_ACCT, TO_CHAR(ACCOUNT__OID), '1') = '1' THEN 1 WHEN BAL <> 0 AND LIST_ACCT = '' AND POSTING_DATE = POSTING_DATE_v AND INSTR(ACCT_v, TO_CHAR(ACCOUNT__OID)) = 0 AND CCAT <> 'C' THEN -1 ELSE 0 END ) OVER (PARTITION BY ACNT_CONTRACT__ID ORDER BY rn ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS STEP FROM ( SELECT la.*, -- Предыдущие значения для переменных LAG(ACNT_CONTRACT__ID) OVER (PARTITION BY ACNT_CONTRACT__ID ORDER BY rn) AS CONT_v, LAG(POSTING_DATE) OVER (PARTITION BY ACNT_CONTRACT__ID ORDER BY rn) AS POSTING_DATE_v, LAG(LIST_ACCT) OVER (PARTITION BY ACNT_CONTRACT__ID ORDER BY rn) AS LIST_ACCT_v, LAG(BAL) OVER (PARTITION BY ACNT_CONTRACT__ID ORDER BY rn) AS BAL_v, LAG(CCAT) OVER (PARTITION BY ACNT_CONTRACT__ID ORDER BY rn) AS CCAT_v FROM list_acct la ) la ) SELECT sc.ACCOUNT__OID, sc.ACNT_CONTRACT__ID, sc.M_TRANSACTION__ID, sc.POSTING_DATE, sc.BAL, sc.CCAT, sc.DAY_ARR_OD, sc.STEP, -- Пример использования LIST_AGCT sc.LIST_ACCT, -- Выходные поля sc.STEP AS STEP_O, -- LAST_ROW_o можно определить как ROW_NUMBER ROW_NUMBER() OVER (PARTITION BY sc.ACNT_CONTRACT__ID ORDER BY sc.rn DESC) AS LAST_ROW_o, TO_DATE('04.10.2024','DD.MM.YYYY') AS REPORT_DATE, sc.LIST_ACCT AS LIST_ACCT_O FROM step_calc sc ORDER BY sc.ACNT_CONTRACT__ID, sc.rn;Пояснения
-
CTE
base: Основной набор данных с добавлением вычисленияDAY_ARR_ODи нумерации строк (ROW_NUMBER) для каждой группыACNT_CONTRACT__ID. -
CTE
list_acct: ИспользуетLISTAGGдля составления спискаACCOUNT__OIDдо текущей строки в пределах каждой группыACNT_CONTRACT__ID. -
CTE
step_calc: Пытается эмулировать логику вычисленияSTEPс помощью оконной функцииSUM. Здесь используетсяCASEдля определения, когда увеличивать или уменьшать шаг. Также применяются функцииLAGдля получения предыдущих значений необходимых переменных. -
Основной
SELECT: Передает необходимые поля и добавляет выходные поля, включая фиксированную дату отчёта и список аккаунтов.
Ограничения и Допущения
-
Эмуляция Логики STEP: Полная эмуляция сложной логики
STEPиз Informatica без использования рекурсивных CTE или переменных затруднительна. В представленном решении использована накопительная сумма (SUM) с условиями, что может не полностью соответствовать исходной логике. -
Переменные
CONT_v,POSTING_DATE_vи др.: Используются функцииLAGдля получения предыдущих значений, что приближает к использованию переменных, но не является идентичным механизмом. -
Функция
REG_REPLACE: В Oracle используетсяREGEXP_REPLACE. В примере заменены наREGEXP_REPLACE, но возможно потребуется уточнение регулярного выражения. -
Список Аккаунтов
LIST_ACCT: Предполагается, что разделитель,подходит для всех случаев. Если требуются другие разделители или формат, это нужно скорректировать. -
Поле
LAST_ROW_o: Реализовано какROW_NUMBERв обратном порядке, что может отличаться от логикиLAST_ROW_v + 1в исходной трансформации.
Заключение
Данный SQL-запрос пытается воспроизвести логику трансформации из Informatica Power Center, используя возможные оконные функции Oracle SQL. Однако, из-за характера некоторых вычислений, требующих пошаговой обработки и состояния между строками, полная идентичность функционала может быть недостижима без использования рекурсивных CTE или процедурного кода. Рекомендуется протестировать данный запрос на ваших данных и внести необходимые корректировки.
-