В моем SQL-запросе Snowflake у меня есть вывод, как показано ниже, все остальные поля содержат одни и те же данные, сгруппированные по столбцу A:
В запросе используются предложения SELECT DISTINCT и GROUP BY для удаления любого дублирования, если оно существует. Я знаю, что это не настоящие «дубликаты», но я хотел бы удалить строки, которые я пометил как 1 и 2, поскольку они имеют NULLS, поэтому мой результат должен выглядеть следующим образом:
Я уже пробовал использовать ROW_NUMBER(), PARTITION BY, RANK() и т. д., чтобы исключить ненужные строки, но поскольку строки с тегами 3 и 4 законно содержат NULL, они также удаляются. Нет других полей (например, даты и времени), которые я мог бы использовать для упорядочивания и удаления на их основе.
Уменьшенный пример моего текущего запроса:
SELECT
COLUMN A,
COLUMN B,
COLUMN C,
COLUMN D,
COLUMN E,
COLUMN F
FROM
(
WITH CTE1 AS
(
SELECT DISTINCT
COLUMN A,
COLUMN B,
COLUMN C,
COLUMN D,
COLUMN E,
COLUMN F
FROM
SOURCE_TABLE WHERE SOURCE = '1'
),
CTE2 AS
(
SELECT DISTINCT
COLUMN A,
COLUMN B,
COLUMN C,
COLUMN D,
COLUMN G,
COLUMN H
FROM
SOURCE_TABLE WHERE SOURCE = '2'
),
CTE3 AS
(
SELECT DISTINCT
COLUMN A,
COLUMN B,
COLUMN C,
COLUMN D,
COLUMN E,
COLUMN F
FROM
SOURCE_TABLE WHERE SOURCE = '3'
),
BASE_CTE AS
(
SELECT DISTINCT
COLUMN A,
COLUMN B,
COLUMN C,
COLUMN D,
MAX(COLUMN E) AS COLUMN E,
LISTAGG(COLUMN F, ', ') AS COLUMN F,
FROM
(
SELECT
COLUMN A,
COLUMN B,
COLUMN C,
COLUMN D,
COLUMN E,
COLUMN F
FROM
CTE1
UNION
(
SELECT
COLUMN A,
COLUMN B,
COLUMN C,
COLUMN D,
COLUMN E,
COLUMN F
FROM
CTE2
)
UNION
(
SELECT
COLUMN A,
COLUMN B,
COLUMN C,
COLUMN D,
COLUMN E,
COLUMN F
FROM
CTE3
)
) GROUP BY ALL
)
SELECT * FROM BASE_CTE
);
Надеюсь, я пропустил что-то очевидное, и, поскольку это мой первый пост, дайте мне знать, если мне нужно добавить дополнительную информацию или подробности.
Если выходные данные содержат 3 строки, такие как A AAAA NULL NULL
, A NULL BBBB NULL
и A NULL NULL CCCC
, какую строку[ы] следует удалить?
Извините, но я добавил номера строк, но по какой-то причине они не отображаются. Строки, которые нужно «удалить», — это первая и пятая строки.
Вы можете использовать следующий подход:
CASE WHEN
, чтобы пометить строки цифрой 0, если левая часть соединения дублирует правую сторону; и с 0 в противном случае.MAX(dedup) OVER(...)
), и отфильтруйте по ней (QUALIFY ...
).DISTINCT
строк, чтобы избежать дублирования.Два примечания:
dedup
.WITH data AS (
SELECT 'A' AS col_a, NULL AS col_b, 'AAAA' AS col_c, NULL AS col_d
UNION ALL
SELECT 'A' AS col_a, 'BBBB' AS col_b, 'AAAA' AS col_c, 'CCCC' AS col_d
UNION ALL
SELECT 'B' AS col_a, 'DDDD' AS col_b, NULL AS col_c, 'EEEE' AS col_d
UNION ALL
SELECT 'B' AS col_a, 'FFFF' AS col_b, NULL AS col_c, 'GGGG' AS col_d
UNION ALL
SELECT 'B' AS col_a, 'HHHH' AS col_b, NULL AS col_c, 'IIII' AS col_d
UNION ALL
SELECT 'B' AS col_a, 'HHHH' AS col_b, 'JJJJ' AS col_c, 'IIII' AS col_d
UNION ALL
SELECT 'C' AS col_a, 'AAAA' AS col_b, 'BBBB' AS col_c, 'CCCC' AS col_d
UNION ALL
SELECT 'D' AS col_a, NULL AS col_b, NULL AS col_c, NULL AS col_d
UNION ALL
SELECT 'E' AS col_a, NULL AS col_b, NULL AS col_c, NULL AS col_d
UNION ALL
SELECT 'E' AS col_a, 'AAAA' AS col_b, 'BBBB' AS col_c, 'CCCC' AS col_d
),
processed AS (
SELECT
d1.col_a,
d1.col_b,
d1.col_c,
d1.col_d,
CASE
WHEN (
(d1.col_b IS NULL OR d1.col_b = d2.col_b)
AND (d1.col_c IS NULL OR d1.col_c = d2.col_c)
AND (d1.col_d IS NULL OR d1.col_d = d2.col_d)
)
THEN 1
ELSE 0
END AS dedup
FROM data d1
LEFT JOIN data d2 ON d1.col_a = d2.col_a AND hash(d1.col_b, d1.col_c, d1.col_d) != hash(d2.col_b, d2.col_c, d2.col_d)
QUALIFY MAX(dedup) OVER(PARTITION BY d1.col_a, d1.col_b, d1.col_c, d1.col_d) = 0
)
SELECT DISTINCT
col_a,
col_b,
col_c,
col_d
FROM processed
ORDER BY 1,2,3,4
Спасибо, Климент, у меня это сработало!
ты можешь попробовать:
WITH CTE1 AS (
SELECT DISTINCT
COLUMN A,
COLUMN B,
COLUMN C,
COLUMN D,
COLUMN E,
COLUMN F
FROM SOURCE_TABLE
WHERE SOURCE = '1'
),
CTE2 AS (
SELECT DISTINCT
COLUMN A,
COLUMN B,
COLUMN C,
COLUMN D,
COLUMN E,
COLUMN F
FROM SOURCE_TABLE
WHERE SOURCE = '2'
),
CTE3 AS (
SELECT DISTINCT
COLUMN A,
COLUMN B,
COLUMN C,
COLUMN D,
COLUMN E,
COLUMN F
FROM SOURCE_TABLE
WHERE SOURCE = '3'
),
BASE_CTE AS (
SELECT
COLUMN A,
COLUMN B,
COLUMN C,
COLUMN D,
MAX(COLUMN E) AS COLUMN E,
LISTAGG(COLUMN F, ', ') WITHIN GROUP (ORDER BY COLUMN F) AS COLUMN F
FROM (
SELECT COLUMN A, COLUMN B, COLUMN C, COLUMN D, COLUMN E, COLUMN F FROM CTE1
UNION ALL
SELECT COLUMN A, COLUMN B, COLUMN C, COLUMN D, COLUMN E, COLUMN F FROM CTE2
UNION ALL
SELECT COLUMN A, COLUMN B, COLUMN C, COLUMN D, COLUMN E, COLUMN F FROM CTE3
)
GROUP BY COLUMN A, COLUMN B, COLUMN C, COLUMN D
)
SELECT
COLUMN A,
COLUMN B,
COLUMN C,
COLUMN D,
COLUMN E,
COLUMN F
FROM BASE_CTE;
Привет @Macleane, добро пожаловать, вы всегда хотите, чтобы значения NULL были исключены из SOURCE в 1 и 2, но хотите, чтобы они были включены, если SOURCE равен 3 или 4? Если да, то вы можете использовать NVL и дополнить столбцы NULL с помощью '-' или любое другое жестко закодированное значение и исключите строки, если столбец содержит это значение. например, это для всех столбцов, из которых вы хотите, чтобы были исключены нули - NVL(NULLIF(TRIM(column_1) , ''), '-') AS columns_1 , и в вашем предложении исключено, если условия ae выполнены WHERE columns_1 < > '-'.