Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / MySQL Новый топик    Ответить
 Объединение таблицы сгруппированной по нескольким полям  [new]
tlq
Member

Откуда:
Сообщений: 11
Добрый день, уважаемый форум. Прошу помощи в поиске решения:
есть таблица вида
------------------------------------------
|id|name|category|option1|option2|option3|option4|
------------------------------------------
Задача стоит следующая(попытался упростить и выделить только суть):
1. Сделать группировку сначала по option1
2. Затем объединить результаты с группировкой по option2(при этом исключить записи, которые уже были сгруппированы по option1)
3. Затем объединить результаты группировки в п.1 и в п.2 с результатами группировки по option3(при этом исключить записи, которые уже были сгруппированы по option1 и option2)
4. Затем объединить результаты группировки в п.1, в п.2 и в п.3 с результатами группировки по option4 (при этом исключить записи, которые уже были сгруппированы по optio1, option2 и option3)
5. Вывести все оставшиеся, не сгруппированные записи.

Сейчас делаю так:
SELECT GROUP_CONCAT(id) as id_list from table WHERE option1 IS NOT NULL GROUP by option1,category HAVING COUNT(id) > 1
UNION
SELECT GROUP_CONCAT(id) as id_list from table WHERE option2 IS NOT NULL GROUP by option2,category HAVING COUNT(id) > 1
UNION
SELECT GROUP_CONCAT(id) as id_list from table WHERE option3 IS NOT NULL GROUP by option3,category HAVING COUNT(id) > 1
UNION
SELECT GROUP_CONCAT(id) as id_list from table WHERE option4 IS NOT NULL GROUP by option4,category

Запрос работает, но если например есть записи, которые могут быть сгруппированы как по option1 так и по option3, то они попадают в обе группировки, цель исключить записи из последующих объединений.

Спасибо.
3 фев 20, 02:10    [22071375]     Ответить | Цитировать Сообщить модератору
 Re: Объединение таблицы сгруппированной по нескольким полям  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20016
tlq
если например есть записи, которые могут быть сгруппированы как по option1 так и по option3
Следует на каждом следующем "витке" отсекать то, что уже взято на предыдущем, реверсным условием: WHERE option3 IS NOT NULL AND option2 IS NULL AND option1 IS NULL
3 фев 20, 07:28    [22071417]     Ответить | Цитировать Сообщить модератору
 Re: Объединение таблицы сгруппированной по нескольким полям  [new]
tlq
Member

Откуда:
Сообщений: 11
Akina, спасибо за ответ, дело в том, что как правило у всех строк заполнены поля optionX, за редким исключением (~100-200 записей на миллион для каждого из полей optionX), т.е. условие "optionX IS NOT NULL" служит для того, чтобы просто исключить эти строки, с пустым параметром.
3 фев 20, 07:58    [22071423]     Ответить | Цитировать Сообщить модератору
 Re: Объединение таблицы сгруппированной по нескольким полям  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20016
Цитирую:
tlq
есть записи, которые могут быть сгруппированы как по option1 так и по option3

Смотрю текст запроса, чтобы определить, какие записи "могут быть сгруппированы ... по option1":
tlq
WHERE option1 IS NOT NULL

Нужно:
tlq
исключить записи из последующих объединений

Поскольку они соответствуют показанному выше условию, исключить их можно только обратным условием
Akina
WHERE ... AND option1 IS NULL


Что ещё??? речь идёт об исключении отдельных записей, а не о группированных агрегатах...

Поясните лучше на примере. Создайте fiddle с примером наполнения, эталонным ответом, и пояснением, почему те или иные строки включены в ту или иную группу.
3 фев 20, 08:31    [22071433]     Ответить | Цитировать Сообщить модератору
 Re: Объединение таблицы сгруппированной по нескольким полям  [new]
tlq
Member

Откуда:
Сообщений: 11
Akina, спасибо большое за подсказку, нужно было бы сразу догадаться мне сделать fiddle:
http://sqlfiddle.com/#!9/cee6e5/1

В fiddle вместо option_1, option_2, option_3, option_4 использую поля option_a, option_b, option_c и option_d соответственно, думаю так будет удобнее воспринять.

Дело в том, что в базу автоматически добавляются данные со значением: имя, категория и значениями для полей option_a, option_b, option_c, option_d

Нужно получить на выходе эти данные, только запчасти должны быть сгруппированы по этим полям(они являются идентификаторами), в порядке приоритета:
1. сначала сгруппировать всё что возможно по идентификатору option_a
2. затем по option_b(исключив все запчасти, уже сгруппированные по option_a, даже если option_b у них совпадает)
3. затем по option_c(также исключив запчасти, которые были уже сгруппированы по полям option_a или option_b, даже несмотря на то, что option_c может совпадать)
4. последняя надежда определить уникальность запчасти: группировка по option_d(исключив запчасти, которые уже возможно были сгруппированы по одному из полей выше)
5. также получить все оставшиеся (не сгруппированные ни по одному из полей) записи

Эталонным ответом в данном fiddle будет результат вида:
---------------------------------------------------------------------------------------------
| id_list | name | category | group_type |

| 1,2 | Запчасть (поле А) | Станки | option_a |

| 3,4,5 | Запчасть (поле B) | Станки | option_b |

| 6,7,8 | Запчасть (поле C) | Станки | option_c |

| 9,10 | Запчасть (поле D) | Станки | option_d |

| 11 | Запчасть без группы №1 | Станки | option_d |

| 12 | Запчасть без группы №2 | Станки | option_d |

--------------------------------------------------------------------------------------------------------------

Тестовые данные с условной категорией "Автомобили" я не стал добавлять, чтобы не возникало путаницы, но при условии, что если будут точно такие же данные, но с другой категорией, они должны быть сгруппированы аналогично, учитывая сначала поле option_(a/b/c/d) затем category

Кажется ничего не упустил.

Заранее спасибо за ответ.

Сообщение было отредактировано: 3 фев 20, 11:26
3 фев 20, 11:21    [22071551]     Ответить | Цитировать Сообщить модератору
 Re: Объединение таблицы сгруппированной по нескольким полям  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20016
Насколько я вижу из fiddle (пардон, перенёс сюда, sqlfiddle регулярно глючит), в первую группу должны попадать те записи, у которых option_a='a0000000', dj вторую - не вошедшие в первую и имеющие option_b='b000001', в третью - не вошедшие в первые две и имеющие option_c='c005500000', в четвёртую - не вошедшие в первые три и имеющие option_d='d123000054', и без группировки идут все, что не вошли в первые 4 группы.

Сомневаюсь, что это верно - скорее всего, завязки на значения я определил неверно... нужны завязки на количество совпадающих значений без конкретных значений.

Соответственно возникает вопрос - какая версия MySQL?
3 фев 20, 11:46    [22071580]     Ответить | Цитировать Сообщить модератору
 Re: Объединение таблицы сгруппированной по нескольким полям  [new]
tlq
Member

Откуда:
Сообщений: 11
Akina
Насколько я вижу из fiddle (пардон, перенёс сюда, sqlfiddle регулярно глючит), в первую группу должны попадать те записи, у которых option_a='a0000000', dj вторую - не вошедшие в первую и имеющие option_b='b000001', в третью - не вошедшие в первые две и имеющие option_c='c005500000', в четвёртую - не вошедшие в первые три и имеющие option_d='d123000054', и без группировки идут все, что не вошли в первые 4 группы.

Сомневаюсь, что это верно - скорее всего, завязки на значения я определил неверно... нужны завязки на количество совпадающих значений без конкретных значений.

Соответственно возникает вопрос - какая версия MySQL?

Akina, в который раз благодарю за ответ, также спасибо за перенос на dbfiddle(к слову, не подозревал, что существует данный ресурс, sqlfiddle действительно пару раз давал мне ошибку, во время заполнения)

Версия MySQL 8.0.17

Вы абсолютно верно помогли мне сформулировать эталонный результат, это именно то, что нужно на выходе, на примере данных из fiddle:
автор
в первую группу должны попадать те записи, у которых option_a='a0000000', dj вторую - не вошедшие в первую и имеющие option_b='b000001', в третью - не вошедшие в первые две и имеющие option_c='c005500000', в четвёртую - не вошедшие в первые три и имеющие option_d='d123000054', и без группировки идут все, что не вошли в первые 4 группы.


Данные идентификаторы, которыми заполнены поля option_(a,b,c,d) могут иметь любое буквенно-цифровое значение произвольной длины, порой выглядящее достаточно хаотично, без определенных последовательностей и как правило это уникальные значения для данной запчасти(записи).

Т.е. цель вернуть одинаковые запчасти(записи) по этим идентификаторам

Спасибо.
3 фев 20, 12:10    [22071607]     Ответить | Цитировать Сообщить модератору
 Re: Объединение таблицы сгруппированной по нескольким полям  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20016
tlq
Версия MySQL 8.0.17

Так это же прекрасно!

WITH 
cte_a AS ( SELECT *, COUNT(option_a) OVER (PARTITION BY option_a) cnt_a
           FROM example_table ),
cte_b AS ( SELECT *, CASE WHEN cnt_a > 1 
                          THEN 0 
                          ELSE COUNT(option_b) OVER (PARTITION BY option_b, cnt_a > 1) 
                          END cnt_b
           FROM cte_a),
cte_c AS ( SELECT *, CASE WHEN (cnt_a > 1) OR (cnt_b > 1)
                          THEN 0 
                          ELSE COUNT(option_c) OVER (PARTITION BY option_c, cnt_a > 1, cnt_b > 1) 
                          END cnt_c
           FROM cte_b),
cte_d AS ( SELECT *, CASE WHEN (cnt_a > 1) OR (cnt_b > 1) OR (cnt_c > 1)
                          THEN 0 
                          ELSE COUNT(option_d) OVER (PARTITION BY option_d, cnt_a > 1, cnt_b > 1, cnt_c > 1) 
                          END cnt_d
           FROM cte_c),
cte_x AS ( SELECT id, 
                  name, 
                  category, 
                  CASE WHEN cnt_a > 1 THEN 'option_a'
                       WHEN cnt_b > 1 THEN 'option_b'
                       WHEN cnt_c > 1 THEN 'option_c'
                       WHEN cnt_d > 1 THEN 'option_d'
                       ELSE 'option_z'
                       END group_type 
                  FROM cte_d )
SELECT GROUP_CONCAT(id ORDER BY id) ids,
       name,
       category,
       ANY_VALUE(group_type) group_type
FROM cte_x
GROUP BY CASE WHEN group_type = 'option_z'
              THEN id
              ELSE group_type
              END,
         name,
         category
ORDER BY group_type, ids


Вот fiddle.
Специально оставил шаги построения, чтобы была видна логика.
3 фев 20, 12:17    [22071610]     Ответить | Цитировать Сообщить модератору
 Re: Объединение таблицы сгруппированной по нескольким полям  [new]
tlq
Member

Откуда:
Сообщений: 11
Akina, спасибо большое за помощь! Сутки ушли на различные тесты и перенос решения на реальные данные, всё отлично работает, спасибо также за сохранение всего пути построения запроса, это также очень и очень помогло, вопросов никаких не осталось, всё предельно ясно.
Пожалуйста, подскажите, как я могу выразить вам благодарность в материальном выражении? Спасибо!
4 фев 20, 05:52    [22072296]     Ответить | Цитировать Сообщить модератору
 Re: Объединение таблицы сгруппированной по нескольким полям  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20016
+ offtop
tlq
Пожалуйста, подскажите, как я могу выразить вам благодарность в материальном выражении?
Если Вы считаете это необходимым - свяжитесь со мной через akina(at)vingrad.ru
4 фев 20, 07:47    [22072317]     Ответить | Цитировать Сообщить модератору
Все форумы / MySQL Ответить