SQL-запрос для выбора только строк с уникальным набором из 3 столбцов, но с неуникальным подмножеством из 2 столбцов
Сначала я разместил этот вопрос на Code Review, но там он был сочтен не относящимся к теме, поскольку представляет собой упрощенную выдержку, а не настоящий проект. Видимо MCVE там недостаточно.
Я создаю SQL-запрос для следующей (упрощенной) таблицы:
ID Key1 Key2 Value Something
----------------------------
1 10 100 v1 aaa
2 10 100 v2 bbb
3 11 111 v3 ccc
4 10 100 v1 ddd
5 10 100 v1 eee
6 11 111 v2 fff
8 12 222 v0 ggg
В результате мне нужно включить строки, которые в самой исходной таблице удовлетворяют следующим двум условиям фильтрации:
- Комбинации (
key1, key2, value) уникальны - Комбинации (
key1, key2) не уникальны
Итак, результат должен быть:
ID Key1 Key2 Value Something
----------------------------
2 10 100 v2 bbb
3 11 111 v3 ccc
6 11 111 v2 fff
У меня (по-видимому) есть решение с двумя соединениями с агрегированными производными таблицами.
SELECT t0.*
FROM Table_3 t0
JOIN
(SELECT key1, key2
FROM Table_3
GROUP BY key1, key2
HAVING COUNT(*) > 1) t1 ON t0.key1 = t1.key1
AND t0.key2 = t1.key2
JOIN
(SELECT key1, key2, value
FROM Table_3
GROUP BY key1, key2, value
HAVING COUNT(*) = 1) t2 ON t0.key1 = t2.key1
AND t0.key2 = t2.key2
AND t0.value = t2.value
Можно ли упростить код? Возможно с оконными функциями?
Настоящий проект будет в BigQuery
Примечание: поле something (которое представляет несколько полей в реальной таблице) должно быть частью результата.
@nbk В 8 комбинация key1 и key2 уникальна, поэтому нарушает второе условие.
Ответы 3
select *
from Table_3
qualify count(1)over(partition by key1, key2, value)=1
and count(1)over(partition by key1, key2)>1
Спасибо. Я не знал о квалификационной оговорке
На мой взгляд, с CTE это проще, но я не знаю, подходит ли вам это для BigQuery:
WITH notUnique2 as (
SELECT key1, key2
FROM Table_3
GROUP BY key1, key2
HAVING COUNT(1) > 1
), unique3 as (
SELECT key1, key2, value
FROM Table_3
GROUP BY key1, key2, value
HAVING COUNT(1) = 1
)
SELECT u3.*
FROM unique3 u3
JOIN notUnique2 nu2
ON u3.key1 = nu2.key1 and u3.key2 = nu2.key2;
И еще одно решение - отредактировано после комментария спрашивающего:
SELECT r.* FROM Table_3 r JOIN
(
SELECT DISTINCT a.key1, a.key2, a.value
FROM Table_3 a
LEFT JOIN Table_3 b -- for uniqueness
ON a.ID != b.ID -- not itself
AND a.key1 = b.key1
AND a.key2 = b.key2
AND a.value = b.value
JOIN Table_3 c -- join proves not uniqueness of key1, key2
ON a.ID != c.ID -- not itself
AND a.key1 = c.key1
AND a.key2 = c.key2
WHERE 1=1
AND b.ID IS NULL -- uniqueness
) i
ON i.key1 = r.key1
AND i.key2 = r.key2
AND i.value = r.value;
Решение @Абай Саякбаева завершено - спрашивающий запросил полную строку в результате:
WITH counted as (
SELECT key1, key2, value,
COUNT(1) OVER (PARTITION BY key1, key2) AS cnt1,
COUNT(1) OVER (PARTITION BY key1, key2, value) AS cnt2
FROM Table_3
), alternativeKey as (
SELECT key1, key2, value
FROM counted
WHERE cnt1 > 1 AND cnt2 = 1
)
SELECT r.*
FROM Table_3 r
JOIN alternativeKey a
ON a.key1 = r.key1
AND a.key2 = r.key2
AND a.value = r.value;
BigQuery поддерживает CTE.
Мне нужны полные строки из таблицы. Поэтому я добавил поле something.
Я обновил 2-е решение - здесь с соединением - можно было бы сделать и с CTE.
Да, этот код SQL можно упростить с помощью оконных функций. Вот упрощенный запрос:
SELECT key1, key2, value
FROM (
SELECT key1, key2, value,
COUNT(*) OVER (PARTITION BY key1, key2) AS cnt1,
COUNT(*) OVER (PARTITION BY key1, key2, value) AS cnt2
FROM Table_3
) t
WHERE cnt1 > 1 AND cnt2 = 1
Этот запрос использует две оконные функции для подсчета вхождений каждой комбинации ключей и каждой комбинации ключей и значений. Затем он выбирает строки, в которых количество комбинаций клавиш больше 1, а количество комбинаций ключ-значение ровно 1. Это эффективно отфильтровывает все строки, которые появляются несколько раз с одной и той же комбинацией клавиш, и выбирает уникальную строки, которые появляются только один раз с данной комбинацией клавиш.
@ pm-77-1 упоминается для моего (второго) решения (только), что ему требуются все столбцы таблицы, поэтому я адаптировал его. Здесь этого не хватает. Адаптированную/обновленную версию я добавил в качестве третьего решения в своем ответе со ссылкой на автора источника :-).
а почему не 8 строчка?