Форум программистов
 

Восстановите пароль или Зарегистрируйтесь на форуме, о проблемах и с заказом рекламы пишите сюда - alarforum@yandex.ru, проверяйте папку спам!

Вернуться   Форум программистов > Web программирование > SQL, базы данных
Регистрация

Восстановить пароль
Повторная активизация e-mail

Купить рекламу на форуме - 42 тыс руб за месяц

Ответ
 
Опции темы Поиск в этой теме
Старый 15.12.2017, 17:17   #1
OmegaBerkut
Спокойный псих
Участник клуба
 
Аватар для OmegaBerkut
 
Регистрация: 19.03.2013
Сообщений: 1,538
По умолчанию Нужно построить

Здравствуйте.
Работая на сайте в магазине автозапчастей, столкнулся со следующей задачей ...

Для тех, кто знает что такое "кроссы" в автомобильной тематике - можно сразу читать суть задачи.
Есть набор взаимно заменяющихся запчастей (аналоги). Их может быть сколько угодно. Это могут быть запчасти от разных производителей, соответственно с разными артикулами (а иногда производители разные, сами запчасти тоже разные, а артикулы одинаковые). Для возможности подбора аналогов существуют "кроссы", где один кросс содержит четыре основных поля:
1 - наименование первого производителя.
2 - артикул первой запчасти.
3 - наименование второго производителя.
4 - артикул второй запчасти.

Суть задачи.
Для справки - в базе данных сайта имеется почти 12 миллионов записей кроссов. Все кроссировки двухсторонние (это само собой разумеется).
Часто встречаются случаи, когда есть (допустим) три запчасти. Одна запчасть имеет кросс на вторую, вторая на третью. А третья не имеет кросса на первую (на вторую есть за счёт двусторонней кроссировки). А по логике вещей - должна быть.
Задача: в имеющейся базе найти все кроссы, которые должны иметь кроссировку, но не имеют из-за описанного случая.
Важно то, что в каждой такой "цепочке" может быть сколько угодно кроссов (в описанном случае их всего три), то есть по большому счёту предполагается связь "все со всеми". Дубликаты будут отсеиваться непосредственно перед добавлением, а за счёт двусторонней кроссировки дубликатов будет много.

Какие идеи по реализации поиска вы можете предложить ?
Выполнение предполагается на сервере; и да - я понимаю, что база кроссов разрастётся, но это собственно и нужно (но что бы правильно было всё).
Подпись ? Не, не слышал ...

Последний раз редактировалось OmegaBerkut; 15.12.2017 в 18:18.
OmegaBerkut вне форума Ответить с цитированием
Старый 15.12.2017, 18:22   #2
evg_m
Старожил
 
Регистрация: 20.04.2008
Сообщений: 5,515
По умолчанию

1. "забыть" про двухсторнность, и честно проверять и добавлять(если надо) ВСЕ возможные комбинации трех артикулов.
Код:
insert ....
select a1,a2 
from ( select t1.a1, t2.a2  --- 1-->2  2-->3 ==>  1-->3
         from t1
         inner join t2 on t1.a2 =t2.a1 
                   and t1.a1<t2.a2 //для исключения "двойной" записи
     union
          ....                  1-->2 3-->2 ==> ...
                                1-->2 1-->3
                                1-->2 3-->1
                                2-->1 3-->2  === //кажется он уже есть как 1--2 2--3 ( с точностью до перестановки)
                                2-->1 2-->3  ===// 1-->2 1-->3
                                2-->1 3-->1  ===// ????
                                ...???
                ) as tx
where not exists (select 1 from t where a1=tx.a1 and a2=tx.a2) ... )
дальше думать на использованием упорядоченности артикулов. да-да-да строки тоже имеют естественную(алфавитную) упорядоченность и могут сравнимы на больше-меньше.
программа — запись алгоритма на языке понятном транслятору

Последний раз редактировалось evg_m; 15.12.2017 в 18:26.
evg_m вне форума Ответить с цитированием
Старый 15.12.2017, 18:23   #3
Аватар
Старожил
 
Аватар для Аватар
 
Регистрация: 17.11.2010
Сообщений: 19,042
По умолчанию

mysql? Задача разовая для лечения?
Если бы архитекторы строили здания так, как программисты пишут программы, то первый залетевший дятел разрушил бы цивилизацию
Аватар вне форума Ответить с цитированием
Старый 15.12.2017, 19:17   #4
OmegaBerkut
Спокойный псих
Участник клуба
 
Аватар для OmegaBerkut
 
Регистрация: 19.03.2013
Сообщений: 1,538
По умолчанию

Цитата:
Сообщение от evg_m Посмотреть сообщение
строки тоже имеют естественную(алфавитную) упорядоченность и могут сравнимы на больше-меньше
Ну это я знаю. Ибо символы - это всё те же числа (из кодовых таблиц).
Только ваш SQL-запрос - это не совсем то, что мне нужно, или я чего то не понял.
Я предполагаю, что мою задачу нужно выполнять скриптом, или ещё чем. Ну и такая обработка будет длиться довольно долго.

Цитата:
Сообщение от Аватар Посмотреть сообщение
mysql? Задача разовая для лечения?
На основе имеющихся кроссов предполагается создание текстового файла, который будет содержать SQL-запросы для добавления тех кроссов, которые не "закольцованы".
И этот файл в дальнейшем будет использоваться для последовательного импорта данных в базу.
Иногда появляются новые источники кроссов. И в дальнейшем предполагается подготовка "закольцованных" кроссов на основе каждого отдельно взятого источника кроссов.
Подпись ? Не, не слышал ...
OmegaBerkut вне форума Ответить с цитированием
Старый 15.12.2017, 21:37   #5
Аватар
Старожил
 
Аватар для Аватар
 
Регистрация: 17.11.2010
Сообщений: 19,042
По умолчанию

А чего не ответил по поводу СУБД? Скорее всего мускул, а там с иерархическими средствами туго. Набросал на сиквеле за не имением под рукой мускула.

Вот табличка
Код:
CREATE TABLE Otladka (k1 int NOT NULL, k2 int NOT NULL)

INSERT INTO Otladka VALUES(1,2)
INSERT INTO Otladka VALUES(1,4)
INSERT INTO Otladka VALUES(1,5)
INSERT INTO Otladka VALUES(1,6)
INSERT INTO Otladka VALUES(2,1)
INSERT INTO Otladka VALUES(2,3)
INSERT INTO Otladka VALUES(3,4)
INSERT INTO Otladka VALUES(6,4)

k1	k2
1	2
1	4
1	5
1	6
2	1
2	3
3	4
6	4
сделал двухсторонние кроссы во временную табличку (t=1 - раньше не было)
Код:
SELECT u.* INTO #Otladka1 FROM
(SELECT k1,k2,0 as t FROM Otladka
 UNION
 SELECT o1.k2,o1.k1,1 AS t FROM Otladka o1 WHERE NOT EXISTS(SELECT 0 FROM Otladka o2 WHERE o2.k1=o1.k2 and o2.k2=o1.k1)) U

k1	k2	t
1	2	0
1	4	0
1	5	0
1	6	0
2	1	0
2	3	0
3	2	1
3	4	0
4	1	1
4	3	1
4	6	1
5	1	1
6	1	1
6	4	0
и добавил разорванные кроссы
Код:
SELECT u.* INTO #Otladka2 FROM
 (SELECT * FROM #Otladka1
 UNION
 SELECT o1.k1,o2.k2,1 as t FROM 
   #Otladka1 o1,#Otladka1 o2 
   WHERE o1.k2=o2.k1 AND o1.k1<>o2.k2 AND NOT EXISTS(SELECT 0 FROM #Otladka1 o3 WHERE o3.k1=o1.k1 and o3.k2=o2.k2)
   GROUP BY o1.k1,o2.k2) U

k1	k2	t
1	2	0
1	3	1
1	4	0
1	5	0
1	6	0
2	1	0
2	3	0
2	4	1
2	5	1
2	6	1
3	1	1
3	2	1
3	4	0
3	6	1
4	1	1
4	2	1
4	3	1
4	5	1
4	6	1
5	1	1
5	2	1
5	4	1
5	6	1
6	1	1
6	2	1
6	3	1
6	4	0
6	5	1
Вроде все. А может на свежую голову совсем не все будет)) C t=1 нужно лечить основную таблицу
Если бы архитекторы строили здания так, как программисты пишут программы, то первый залетевший дятел разрушил бы цивилизацию
Аватар вне форума Ответить с цитированием
Старый 16.12.2017, 00:09   #6
OmegaBerkut
Спокойный псих
Участник клуба
 
Аватар для OmegaBerkut
 
Регистрация: 19.03.2013
Сообщений: 1,538
По умолчанию

Аватар, да, mysql. Нужно для лечения, но не одноразового.
У меня полная свобода действий.
Для нахождения полного перечня "разрывов" наверное нужен полный анализ данных.
В ваших вариантах я не понимаю, где анализ или поиск ?
Мне обязательно задачу выполнять в "один запрос" ? Данные же можно вытащить, расположить как угодно, и делать с ними что угодно ...
Подпись ? Не, не слышал ...
OmegaBerkut вне форума Ответить с цитированием
Старый 16.12.2017, 12:13   #7
evg_m
Старожил
 
Регистрация: 20.04.2008
Сообщений: 5,515
По умолчанию

Цитата:
Код:
insert ....
select a1,a2 
from ( select t1.a1, t2.a2  --- 1-->2  2-->3 ==>  1-->3
         from t1
         inner join t2 on t1.a2 =t2.a1 
                   and t1.a1<t2.a2 //для исключения "двойной" записи
1. insert ... select ... -- добавить на основе запроса!!!
2. есть связи 1-->2 2-->3 будем добавлять связь 1-->3
3. not exists -- если вдруг уже есть
программа — запись алгоритма на языке понятном транслятору

Последний раз редактировалось evg_m; 16.12.2017 в 12:15.
evg_m вне форума Ответить с цитированием
Старый 16.12.2017, 13:57   #8
OmegaBerkut
Спокойный псих
Участник клуба
 
Аватар для OmegaBerkut
 
Регистрация: 19.03.2013
Сообщений: 1,538
По умолчанию

Цитата:
Сообщение от evg_m Посмотреть сообщение
есть связи 1-->2 2-->3 будем добавлять связь 1-->3
Если у меня есть пять запчастей, и цепочка идёт как 1-2, 2-3, 3-4, 4-5

Тогда получается следующее:
- эту цепочку нужно обнаружить;
- проверить наличие связей 1-3, 1-4, 1-5, 2-4, 2-5, 3-5 (с учётом двусторонней кроссировки);
- добавить нужное при отсутствии.

Или хватит проверки глубиной в две записи кроссов ?
Подпись ? Не, не слышал ...

Последний раз редактировалось OmegaBerkut; 16.12.2017 в 14:12.
OmegaBerkut вне форума Ответить с цитированием
Старый 16.12.2017, 17:11   #9
Аватар
Старожил
 
Аватар для Аватар
 
Регистрация: 17.11.2010
Сообщений: 19,042
По умолчанию

Цитата:
В ваших вариантах я не понимаю, где анализ или поиск
В запросах )) Твой вариант
Код:
k1	k2
1	2
2	3
3	4
4	5
5	6
Код:
SELECT u.* INTO #Otladka1 FROM
(SELECT k1,k2,0 as t FROM Otladka
 UNION
 SELECT o1.k2,o1.k1,1 AS t FROM Otladka o1 WHERE NOT EXISTS(SELECT 0 FROM Otladka o2 WHERE o2.k1=o1.k2 and o2.k2=o1.k1)) U

SELECT * FROM #Otladka1
Код:
k1	k2	t
1	2	0
2	1	1
2	3	0
3	2	1
3	4	0
4	3	1
4	5	0
5	4	1
5	6	0
6	5	1
Код:
SELECT u.* INTO #Otladka2 FROM
 (SELECT * FROM #Otladka1
 UNION
 SELECT o1.k1,o2.k2,1 as t FROM 
   #Otladka1 o1,#Otladka1 o2 
   WHERE o1.k2=o2.k1 AND o1.k1<>o2.k2 AND NOT EXISTS(SELECT 0 FROM #Otladka1 o3 WHERE o3.k1=o1.k1 and o3.k2=o2.k2)
   GROUP BY o1.k1,o2.k2) U

SELECT * FROM #Otladka2
Код:
k1	k2	t
1	2	0
1	3	1
2	1	1
2	3	0
2	4	1
3	1	1
3	2	1
3	4	0
3	5	1
4	2	1
4	3	1
4	5	0
4	6	1
5	3	1
5	4	1
5	6	0
6	4	1
6	5	1
Код:
SELECT u.* INTO #Otladka3 FROM
 (SELECT * FROM #Otladka2
 UNION
 SELECT o1.k1,o2.k2,1 as t FROM 
   #Otladka2 o1,#Otladka2 o2 
   WHERE o1.k2=o2.k1 AND o1.k1<>o2.k2 AND NOT EXISTS(SELECT 0 FROM #Otladka2 o3 WHERE o3.k1=o1.k1 and o3.k2=o2.k2)
   GROUP BY o1.k1,o2.k2) U

SELECT * FROM #Otladka3
Код:
k1	k2	t
1	2	0
1	3	1
1	4	1
1	5	1
2	1	1
2	3	0
2	4	1
2	5	1
2	6	1
3	1	1
3	2	1
3	4	0
3	5	1
3	6	1
4	1	1
4	2	1
4	3	1
4	5	0
4	6	1
5	1	1
5	2	1
5	3	1
5	4	1
5	6	0
6	2	1
6	3	1
6	4	1
6	5	1
Код:
SELECT u.* INTO #Otladka4 FROM
 (SELECT * FROM #Otladka3
 UNION
 SELECT o1.k1,o2.k2,1 as t FROM 
   #Otladka3 o1,#Otladka3 o2 
   WHERE o1.k2=o2.k1 AND o1.k1<>o2.k2 AND NOT EXISTS(SELECT 0 FROM #Otladka3 o3 WHERE o3.k1=o1.k1 and o3.k2=o2.k2)
   GROUP BY o1.k1,o2.k2) U

SELECT * FROM #Otladka4
Код:
k1	k2	t
1	2	0
1	3	1
1	4	1
1	5	1
1	6	1
2	1	1
2	3	0
2	4	1
2	5	1
2	6	1
3	1	1
3	2	1
3	4	0
3	5	1
3	6	1
4	1	1
4	2	1
4	3	1
4	5	0
4	6	1
5	1	1
5	2	1
5	3	1
5	4	1
5	6	0
6	1	1
6	2	1
6	3	1
6	4	1
6	5	1
Все, потому что следующий запрос ни чего не вернет
Код:
SELECT o1.k1,o2.k2,1 as t FROM 
   #Otladka4 o1,#Otladka4 o2 
   WHERE o1.k2=o2.k1 AND o1.k1<>o2.k2 AND NOT EXISTS(SELECT 0 FROM #Otladka4 o3 WHERE o3.k1=o1.k1 and o3.k2=o2.k2)
   GROUP BY o1.k1,o2.k2
Теперь те строки из #Otladka4, у которых t=1 и являются данными для пополнения в исходную таблицу. Так или примерно так, по другому ни как, иерархический поиск и анализ, для которого мускул не имеет средств в отличии от других СУБД. Можно еще в хранимке с помощью курсоров и циклов реализовать, код будет возможно и покороче, но прозрачности еще меньше станет ))
Если бы архитекторы строили здания так, как программисты пишут программы, то первый залетевший дятел разрушил бы цивилизацию

Последний раз редактировалось Аватар; 16.12.2017 в 17:16.
Аватар вне форума Ответить с цитированием
Старый 16.12.2017, 17:15   #10
evg_m
Старожил
 
Регистрация: 20.04.2008
Сообщений: 5,515
По умолчанию

Цитата:
Или хватит проверки глубиной в две записи кроссов ?
1-->2 2-->3 3-->4 4-->5 5-->6 ....
первый проход скрипта
добавит 1-->3 2-->4 3-->5 4-->6
второй проход добавит
1-->4 1-->5 ...
т.о. за M проходов добавим все цепочки длиной <=M.
а какова максимальная группа схожих артикулов (какова может быть максимальная цепочка)?
программа — запись алгоритма на языке понятном транслятору
evg_m вне форума Ответить с цитированием
Ответ


Купить рекламу на форуме - 42 тыс руб за месяц

Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск


Похожие темы
Тема Автор Раздел Ответов Последнее сообщение
Нужно построить диаграммы UML RoseAle Помощь студентам 15 02.04.2015 07:56
Нужно построить диаграмму((( grooveone Паскаль, Turbo Pascal, PascalABC.NET 3 14.12.2013 15:22
нужно построить график Kara777 Помощь студентам 1 17.05.2011 18:29
Нужно построить блок схемы FEAREX Помощь студентам 0 27.12.2010 18:02