Форум программистов
 
Контакты: о проблемах с регистрацией, почтой и по другим вопросам пишите сюда - alarforum@yandex.ru, проверяйте папку спам! Обязательно пройдите активизацию e-mail.

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

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

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

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

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

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

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

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

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 в 19:26.
evg_m вне форума   Ответить с цитированием
Старый 15.12.2017, 19:23   #3
Аватар
Модератор
Заслуженный модератор
 
Аватар для Аватар
 
Регистрация: 17.11.2010
Адрес: Северодонецк.ua
Сообщений: 17,683
Репутация: 6211
По умолчанию

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

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

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

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

Вот табличка
Код:

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, 01:09   #6
OmegaBerkut
Участник клуба
 
Аватар для OmegaBerkut
 
Регистрация: 19.03.2013
Сообщений: 885
Репутация: 111
По умолчанию

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

Цитата:
Код:

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 в 13:15.
evg_m вне форума   Ответить с цитированием
Старый 16.12.2017, 14:57   #8
OmegaBerkut
Участник клуба
 
Аватар для OmegaBerkut
 
Регистрация: 19.03.2013
Сообщений: 885
Репутация: 111
По умолчанию

Цитата:
Сообщение от 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 в 15:12.
OmegaBerkut вне форума   Ответить с цитированием
Старый 16.12.2017, 18:11   #9
Аватар
Модератор
Заслуженный модератор
 
Аватар для Аватар
 
Регистрация: 17.11.2010
Адрес: Северодонецк.ua
Сообщений: 17,683
Репутация: 6211
По умолчанию

Цитата:
В ваших вариантах я не понимаю, где анализ или поиск
В запросах )) Твой вариант
Код:

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 в 18:16.
Аватар на форуме   Ответить с цитированием
Старый 16.12.2017, 18:15   #10
evg_m
Профессионал
 
Регистрация: 20.04.2008
Сообщений: 4,656
Репутация: 2097
По умолчанию

Цитата:
Или хватит проверки глубиной в две записи кроссов ?
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 вне форума   Ответить с цитированием
Ответ

Опции темы

Ваши права в разделе
Вы не можете создавать новые темы
Вы не можете отвечать в темах
Вы не можете прикреплять вложения
Вы не можете редактировать свои сообщения

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.

Быстрый переход

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


22:03.


Powered by vBulletin® Version 3.8.8 Beta 2
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.

RusProfile.ru


Справочник российских юридических лиц и организаций.
Проекты отопления, пеллетные котлы, бойлеры, радиаторы
интернет магазин respective.ru