Подсчет совпадений в двух полях разных таблиц
У меня есть 2 таблицы с продуктами, которые содержат информацию о номере счета, коде продукта, производителе, бренде, модели и артикуле. Поля «Бренд» и «Артикул» могут содержать нулевые значения.
Например
Table1
+-------------+--------+-----------+-------+---------+---------+
| Num | Code | Developer | Brand |Model | Article |
+-------------+--------+-----------+-------+---------+---------+
| 111/111/111 | 0803 | Dev1 | Bra1 | Mod1 | Art1 |
| 222/222/222 | 0706 | Dev2 | Bra2 | Mod2 | Art2 |
| 222/222/222 | 0706 | Dev2 | Bra2 | Mod2 | Art3 |
| 222/222/222 | 0706 | Dev2 | Bra2 | Mod3 | Art5 |
| 333/333/333 | 0717 | Dev3 | Bra3 | Mod4 | Art4 |
| 333/333/333 | 0717 | Dev3 | Bra3 | Mod4 | Art6 |
| 444/444/444 | 0805 | Dev1 | Bra1 | Mod1 | Art1 |
| 444/444/444 | 0805 | Dev1 | Bra1 | Mod1 | Art7 |
+-------------+--------+-----------+-------+---------+---------+
Table2
+-------------+--------+-----------+-------+---------+---------+
| Num | Code | Developer | Brand |Model | Article |
+-------------+--------+-----------+-------+---------+---------+
| 666/666/666 | 0803 | Dev1 | Bra1 | Mod1 | Art1 |
| 777/777/777 | 0706 | Dev2 | Bra2 | Mod7 | Art7 |
| 777/777/777 | 0706 | Dev2 | Bra2 | Mod7 | Art7 |
| 888/888/888 | 0717 | Dev3 | Bra2 | Mod4 | Art4 |
| 888/888/888 | 0717 | Dev3 | Bra3 | Mod4 | Art4 |
| 888/888/888 | 0717 | Dev3 | Bra3 | Mod8 | Art8 |
| 999/999/999 | 0805 | Dev1 | Bra1 | Mod1 | Art1 |
| 999/999/999 | 0805 | Dev1 | Bra1 | Mod1 | Art7 |
+-------------+--------+-----------+-------+---------+---------+
Я объединяю таблицы по полям «Код» и «Разработчик» и использую функцию listagg (или другую) для агрегирования полей «Бренд», «Модель» и «Артикул». В результате я получаю следующую таблицу
+-------------+-------------+-----------+------------+------------+----------------+------------+
| Num_Tab1 | Num_Tab2 | Developer | Model_Tab1 | Model_Tab2 | Art_Tab1 | Art_Tab2 |
+-------------+-------------+-----------+------------+------------+----------------+------------+
| 111/111/111 | 666/666/666 | Dev1 | Mod1 | Mod1 | Art1 | Art1 |
| 222/222/222 | 777/777/777 | Dev2 | Mod2;Mod3 | Mod7 | Art2;Art3,Art5 | Art7 |
| 333/333/333 | 888/888/888 | Dev3 | Mod4 | Mod4;Mod8 | Art4;Art6 | Art4;Art8 |
| 444/444/444 | 999/999/999 | Dev1 | Mod1 | Mod1 | Art1;Art7 | Art1;Art7 |
+-------------+-------------+-----------+------------+------------+----------------+------------+
Кроме того, мне нужно найти количество совпадений уникальных значений брендов, моделей и товаров для каждой пары счетов-фактур.
+-------------+-------------+------+------------+------------+----------------+------------+-------+-----+
| Num_Tab1 | Num_Tab2 | Dev | Model_Tab1 | Model_Tab2 | Art_Tab1 | Art_Tab2 | Mod_c |Art_c|
+-------------+-------------+------+------------+------------+----------------+------------+-------+-----+
| 111/111/111 | 666/666/666 | Dev1 | Mod1 | Mod1 | Art1 | Art1 | 1 | 1 |
| 222/222/222 | 777/777/777 | Dev2 | Mod2;Mod3 | Mod7 | Art2;Art3,Art5 | Art7 | 0 | 0 |
| 333/333/333 | 888/888/888 | Dev3 | Mod4 | Mod4;Mod8 | Art4;Art6 | Art4;Art8 | 1 | 1 |
| 444/444/444 | 999/999/999 | Dev1 | Mod1 | Mod1 | Art1;Art7 | Art1;Art7 | 1 | 2 |
+-------------+-------------+------+------------+------------+----------------+------------+-------+-----+
Но я не могу этого сделать.
Я попробовал использовать функцию regexp_count(), используя поле из одной таблицы в качестве строки и поле из второй таблицы в качестве шаблона, заменив ";" на "|". Это работает, но некоторые счета содержат большое количество позиций, поэтому я получаю слишком длинное регулярное выражение ora-12733. Я также попытался добавить столбец в запрос
SELECT ... , (SELECT count(*)
FROM Tab1 t1 JOIN Tab2 t2 ON t1.Model=t2.Model )
FROM Tab1 t1 JOIN Tab2 t2 ON t1.Code=t2.Code AND t1.Developer=t2.Developer
WHERE ...
но получаю ошибку. Я попробовал включить аналогичный подзапрос в раздел «FROM», но, к сожалению, это не дало желаемых результатов.
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
0
0
60
2
Перейти к ответу
Данный вопрос помечен как решенный
Ответы 2
Вы можете использовать UNION ALL для объединения двух таблиц, а затем использовать условное агрегирование для объединения строк:
SELECT MAX(CASE tbl WHEN 1 THEN num END) AS num_tab1,
MAX(CASE tbl WHEN 2 THEN num END) AS num_tab2,
code,
developer,
LISTAGG(DISTINCT CASE tbl WHEN 1 THEN model END, ';')
WITHIN GROUP (ORDER BY tbl, model) AS model_tab1,
LISTAGG(DISTINCT CASE tbl WHEN 2 THEN model END, ';')
WITHIN GROUP (ORDER BY tbl, model) AS model_tab2,
LISTAGG(DISTINCT CASE tbl WHEN 1 THEN article END, ';')
WITHIN GROUP (ORDER BY tbl, model) AS article_tab1,
LISTAGG(DISTINCT CASE tbl WHEN 2 THEN article END, ';')
WITHIN GROUP (ORDER BY tbl, model) AS article_tab2,
COUNT(DISTINCT CASE model_tab_count WHEN 2 THEN model END) AS mod_count,
COUNT(DISTINCT CASE article_tab_count WHEN 2 THEN article END) AS art_count
FROM (
SELECT t.*,
COUNT(DISTINCT tbl) OVER (PARTITION BY code, developer, model)
AS model_tab_count,
COUNT(DISTINCT tbl) OVER (PARTITION BY code, developer, article)
AS article_tab_count
FROM (
SELECT 1 AS tbl, t.*
FROM table1 t
UNION ALL
SELECT 2 AS tbl, t.*
FROM table2 t
) t
)
GROUP BY code, developer
Что для примера данных:
CREATE TABLE Table1 (Num, Code, Developer, Brand, Model, Article) AS
SELECT '111/111/111', '0803', 'Dev1', 'Bra1', 'Mod1', 'Art1' FROM DUAL UNION ALL
SELECT '222/222/222', '0706', 'Dev2', 'Bra2', 'Mod2', 'Art2' FROM DUAL UNION ALL
SELECT '222/222/222', '0706', 'Dev2', 'Bra2', 'Mod2', 'Art3' FROM DUAL UNION ALL
SELECT '222/222/222', '0706', 'Dev2', 'Bra2', 'Mod3', 'Art5' FROM DUAL UNION ALL
SELECT '333/333/333', '0717', 'Dev3', 'Bra3', 'Mod4', 'Art4' FROM DUAL UNION ALL
SELECT '333/333/333', '0717', 'Dev3', 'Bra3', 'Mod4', 'Art6' FROM DUAL UNION ALL
SELECT '444/444/444', '0805', 'Dev1', 'Bra1', 'Mod1', 'Art1' FROM DUAL UNION ALL
SELECT '444/444/444', '0805', 'Dev1', 'Bra1', 'Mod1', 'Art7' FROM DUAL;
CREATE TABLE Table2 (Num, Code, Developer, Brand, Model, Article) AS
SELECT '666/666/666', '0803', 'Dev1', 'Bra1', 'Mod1', 'Art1' FROM DUAL UNION ALL
SELECT '777/777/777', '0706', 'Dev2', 'Bra2', 'Mod7', 'Art7' FROM DUAL UNION ALL
SELECT '777/777/777', '0706', 'Dev2', 'Bra2', 'Mod7', 'Art7' FROM DUAL UNION ALL
SELECT '888/888/888', '0717', 'Dev3', 'Bra2', 'Mod4', 'Art4' FROM DUAL UNION ALL
SELECT '888/888/888', '0717', 'Dev3', 'Bra3', 'Mod4', 'Art4' FROM DUAL UNION ALL
SELECT '888/888/888', '0717', 'Dev3', 'Bra3', 'Mod8', 'Art8' FROM DUAL UNION ALL
SELECT '999/999/999', '0805', 'Dev1', 'Bra1', 'Mod1', 'Art1' FROM DUAL UNION ALL
SELECT '999/999/999', '0805', 'Dev1', 'Bra1', 'Mod1', 'Art7' FROM DUAL;
Выходы:
NUM_TAB1
NUM_TAB2
КОД
РАЗРАБОТЧИК
МОДЕЛЬ_TAB1
МОДЕЛЬ_TAB2
ARTICLE_TAB1
ARTICLE_TAB2
MOD_COUNT
ART_COUNT
222/222/222
777/777/777
0706
Дев2
Мод2;Мод3
Мод7
Арт2;Арт3;Арт5
Арт7
0
0
333/333/333
888/888/888
0717
Дев3
Мод4
Мод4;Мод8
Арт4;Арт6
Арт4;Арт8
1
1
111/111/111
666/666/666
0803
Разработчик1
Мод1
Мод1
Арт1
Арт1
1
1
444/444/444
999/999/999
0805
Разработчик1
Мод1
Мод1
Арт1;Арт7
Арт1;Арт7
1
2
Ответ принят как подходящий
Ваш запрос, в котором таблицы объединены CODE и DEVELOPER (тот, который использует LISTAGG()), может содержать два дополнительных столбца счетчика, которые вы запрашиваете. Используйте условную (t1.MODEL = t2.MODEL / t1.ARTICLE = t2.ARTICLE) отдельную аналитическую функцию Count(), как показано ниже. Вы получите повторяющиеся строки, от которых вам следует избавиться либо с помощью ключевого слова Distinct, либо с помощью группы по.
WITH -- S a m p l e D a t a :
tbl_1 (NUM, CODE, DEVELOPER, BRAND, MODEL, ARTICLE) AS
( Select '111/111/111', '0803', 'Dev1', 'Bra1', 'Mod1', 'Art1' From Dual Union All
Select '222/222/222', '0706', 'Dev2', 'Bra2', 'Mod2', 'Art2' From Dual Union All
Select '222/222/222', '0706', 'Dev2', 'Bra2', 'Mod2', 'Art3' From Dual Union All
Select '222/222/222', '0706', 'Dev2', 'Bra2', 'Mod3', 'Art5' From Dual Union All
Select '333/333/333', '0717', 'Dev3', 'Bra3', 'Mod4', 'Art4' From Dual Union All
Select '333/333/333', '0717', 'Dev3', 'Bra3', 'Mod4', 'Art6' From Dual Union All
Select '444/444/444', '0805', 'Dev1', 'Bra1', 'Mod1', 'Art1' From Dual Union All
Select '444/444/444', '0805', 'Dev1', 'Bra1', 'Mod1', 'Art7' From Dual
),
tbl_2 (NUM, CODE, DEVELOPER, BRAND, MODEL, ARTICLE) AS
( Select '666/666/666', '0803', 'Dev1', 'Bra1', 'Mod1', 'Art1' From Dual Union All
Select '777/777/777', '0706', 'Dev2', 'Bra2', 'Mod7', 'Art7' From Dual Union All
Select '777/777/777', '0706', 'Dev2', 'Bra2', 'Mod7', 'Art7' From Dual Union All
Select '888/888/888', '0717', 'Dev3', 'Bra2', 'Mod4', 'Art4' From Dual Union All
Select '888/888/888', '0717', 'Dev3', 'Bra3', 'Mod4', 'Art4' From Dual Union All
Select '888/888/888', '0717', 'Dev3', 'Bra3', 'Mod8', 'Art8' From Dual Union All
Select '999/999/999', '0805', 'Dev1', 'Bra1', 'Mod1', 'Art1' From Dual Union All
Select '999/999/999', '0805', 'Dev1', 'Bra1', 'Mod1', 'Art7' From Dual
)
-- S Q L :
Select Distinct
t1.NUM "NUM_TABLE_1", t2.NUM "NUM_TABLE_2", t1.CODE, t1.DEVELOPER,
LISTAGG(Distinct t1.MODEL, ';') WITHIN GROUP (Order By t1.MODEL) Over(Partition By t1.NUM, t2.NUM, t1.CODE, t1.DEVELOPER) "MODEL_TABLE_1",
LISTAGG(Distinct t2.MODEL, ';') WITHIN GROUP (Order By t2.MODEL) Over(Partition By t1.NUM, t2.NUM, t1.CODE, t1.DEVELOPER) "MODEL_TABLE_2",
--
LISTAGG(Distinct t1.ARTICLE, ';') WITHIN GROUP (Order By t1.ARTICLE) Over(Partition By t1.NUM, t2.NUM, t1.CODE, t1.DEVELOPER) "ARTICLE_TABLE_1",
LISTAGG(Distinct t2.ARTICLE, ';') WITHIN GROUP (Order By t2.ARTICLE) Over(Partition By t1.NUM, t2.NUM, t1.CODE, t1.DEVELOPER) "ARTICLE_TABLE_2",
--
Count(Distinct Case When t1.MODEL = t2.MODEL Then t1.MODEL End) Over(Partition By t1.NUM, t2.NUM, t1.CODE, t1.DEVELOPER) "MOD_C",
Count(Distinct Case When t1.ARTICLE = t2.ARTICLE Then t1.ARTICLE End) Over(Partition By t1.NUM, t2.NUM, t1.CODE, t1.DEVELOPER) "ART_C"
From tbl_1 t1
Inner Join tbl_2 t2 ON(t2.CODE = t1.CODE And t2.DEVELOPER = t1.DEVELOPER)
Order By t1.NUM, t2.NUM
/* R e s u l t :
NUM_TABLE_1 NUM_TABLE_2 CODE DEVELOPER MODEL_TABLE_1 MODEL_TABLE_2 ARTICLE_TABLE_1 ARTICLE_TABLE_2 MOD_C ART_C
----------- ----------- ---- --------- ------------- ------------- --------------- --------------- ----- -----
111/111/111 666/666/666 0803 Dev1 Mod1 Mod1 Art1 Art1 1 1
222/222/222 777/777/777 0706 Dev2 Mod2;Mod3 Mod7 Art2;Art3;Art5 Art7 0 0
333/333/333 888/888/888 0717 Dev3 Mod4 Mod4;Mod8 Art4;Art6 Art4;Art8 1 1
444/444/444 999/999/999 0805 Dev1 Mod1 Mod1 Art1;Art7 Art1;Art7 1 2
*/
ПРИМЕЧАНИЕ:
Если возникают ситуации, когда в Таблице 2 нет строк для любого КОДА и РАЗРАБОТЧИКА в Таблице 1, и вам тоже нужны эти строки, используйте левое соединение и обрабатывайте нулевые значения из Таблицы 2.
Другие вопросы по теме
Перемещение размера большого объекта больше фактического размера
Соединение Apache Airflow и Oracle
Oracle APEX – перемещение нескольких страниц в новое приложение
В чем разница между общей подсказкой и параллельной подсказкой?
Триггер вызывает ошибку, поскольку он изменяет таблицу
Обновить строки с тем же значением
Как оптимизировать мой запрос, чтобы сократить время выполнения
Управляемый ODP.Net: сервер LDAP не обнаружен и не настроен
SQL-запрос в Oracle для суммирования значений из столбца псевдонимов
Нужен запрос, чтобы найти текущий статус последнего запуска определенного задания Oracle
Похожие вопросы
Хранение арифметического дерева разбора в SQL
Вызвать исключение изнутри CTE (Postgres)
Как суммировать количество заказов для каждого номера детали с текущей даты до текущей даты — времени выполнения заказа?
Почему функция сведения в SQL Server выводит несколько строк данных, если в несведенных данных присутствует отдельное значение?
Как сделать цикл в SQL Server
Генератор простых чисел с использованием рекурсивного запроса SQL
Как выполнить чередующуюся сортировку в SQL Server 2019
Предложение GROUP BY с суммой нулевых (и ненулевых) данных
Как преобразовать массив в один столбец в Clickhouse?
Что означает ошибка sql «Аналитическая функция не может быть аргументом другой аналитической функции»?