Извлечение и объединение номеров счетов переменной длины из неструктурированных строк с использованием Snowflake

Вопрос или проблема

WITH flattened AS (
  SELECT 
    f.sql_id,
    f.obj_name,
    a.Acc_No,
    a.Acc_name
  FROM 
    fact_table f
  JOIN 
    account_dim a ON REGEXP_LIKE(UPPER(f.param_value), UPPER(a.Acc_No))
)
SELECT 
  sql_id,
  obj_name,
  Acc_No,
  Acc_name
FROM 
  flattened;

Ответ или решение

Для извлечения и соединения переменных номерных аккаунтов из неструктурированных строк с использованием Snowflake, вы можете воспользоваться регулярными выражениями и функциями работы со строками, чтобы извлечь данные согласно представленной вами структуре таблиц.

Шаги решения

1. Создать временную таблицу для хранения извлечённых аккаунтов. Мы будем использовать CTE (Common Table Expression) для упрощения извлечения аккаунтов.

2. Использовать регулярные выражения для поиска номеров аккаунтов. Нам нужно создать регулярное выражение, которое неопределённо найдет номера аккаунтов из PARAM_VALUE, если они находятся в строке.

3. Объединить извлеченные данные с таблицей измерений. После извлечения аккаунтов, мы объединим их с таблицей account_dim, чтобы получить все соответствующие данные.

Пример запроса

Вот пример SQL-запроса, который можно использовать для решения данной задачи:

WITH extracted_accounts AS (
    SELECT 
        f.sql_id,
        f.obj_name,
        TRIM(value) AS extracted_account
    FROM 
        fact_table f,
        LATERAL FLATTEN(INPUT => REGEXP_SPLIT_TO_ARRAY(UPPER(f.param_value), '[,\'()\\s]+')) AS value
    WHERE 
        TRIM(value) IS NOT NULL AND TRIM(value) <> ''
),
matched_accounts AS (
    SELECT 
        e.sql_id,
        e.obj_name,
        a.Acc_no AS matched_account
    FROM 
        extracted_accounts e
    JOIN 
        account_dim a ON e.extracted_account = UPPER(a.Acc_no)
)
SELECT 
    sql_id,
    obj_name,
    matched_account
FROM 
    matched_accounts
ORDER BY 
    sql_id, obj_name;

Пояснение запроса

1. extracted_accounts CTE:

   • В этой части запроса мы используем функцию REGEXP_SPLIT_TO_ARRAY, чтобы разбить поле PARAM_VALUE на массив строк, используя различные разделители, такие как запятые, пробелы и скобки.
   • С помощью LATERAL FLATTEN мы преобразуем массив строк в набор строк, что позволит нам извлекать каждое значение отдельно.
   • После этого мы убираем пустые или пробельные значения.

2. matched_accounts CTE:

   • Здесь мы соединяем извлечённые аккаунты с таблицей account_dim, чтобы найти совпадения по номерам аккаунтов. Мы применяем UPPER() для приведения всех значений к верхнему регистру, чтобы избежать проблем с регистром.
3. Финальный запрос:
   • В этой части мы выбираем нужные поля из результатов, упорядоченные по sql_id и obj_name.

Заключение

Этот запрос позволит вам извлечь и соединить переменные номерные аккаунты из неструктурированных строк. Обязательно протестируйте его на ваших данных, поскольку могут понадобиться дополнительные корректировки для обработки специфических случаев в ваших строках PARAM_VALUE.