MySQL: присоединение таблицы к себе, устранение дублирующихся строк

Question

MySQL: присоединение таблицы к себе, устранение дублирующихся строк

У меня есть запрос, который соединяет таблицу с самим собой. Результаты содержат повторяющиеся строки (вроде). Цель этого запроса — составить список продуктов, которые чаще всего покупаются вместе. Рассмотрим этот запрос:

SELECT o1.ITEM
,o2.ITEM as ITEM2
,o3.ITEM AS ITEM3
,count(DISTINCT o1.ORDERNUM) as oCount
FROM orders o1
INNER JOIN orders o2 ON o2.ORDERNUM = o1.ORDERNUM AND o2.ITEM != o1.ITEM
LEFT OUTER JOIN orders o3 ON o3.ORDERNUM = o1.ORDERNUM AND o3.ITEM != o2.ITEM AND o3.ITEM != o1.ITEM
GROUP BY o1.ITEM, o2.ITEM, o3.ITEM
ORDER BY oCount DESC

И первые 12 результатов:

+-------------+-------------+-------------+--------+
| ITEM        | ITEM2       | ITEM3       | oCount |
+-------------+-------------+-------------+--------+
| 02B13.04.GP | 77A04.10    | 45A04.04.GP |     54 |
| 02B13.04.GP | 45A04.04.GP | 77A04.10    |     54 |
| 77A04.10    | 45A04.04.GP | 02B13.04.GP |     54 |
| 45A04.04.GP | 02B13.04.GP | 77A04.10    |     54 |
| 77A04.10    | 02B13.04.GP | 45A04.04.GP |     54 |
| 45A04.04.GP | 77A04.10    | 02B13.04.GP |     54 |
| 57B01.01.GP | 57B01.11.GP | 57B01.10.GP |     12 |
| 57B01.10.GP | 57B01.11.GP | 57B01.01.GP |     12 |
| 57B01.01.GP | 57B01.10.GP | 57B01.11.GP |     12 |
| 57B01.10.GP | 57B01.01.GP | 57B01.11.GP |     12 |
| 57B01.11.GP | 57B01.10.GP | 57B01.01.GP |     12 |
| 57B01.11.GP | 57B01.01.GP | 57B01.10.GP |     12 |

Обратите внимание, что первые 6 результатов — это одни и те же соединения в другом порядке. Вторые 6 результатов имеют ту же проблему (и это продолжается на протяжении всех результатов). Моя цель — иметь одну запись для каждой группы элементов, а не одну строку для каждой комбинации каждой группы элементов.

Как я могу избежать этих повторных результатов?

Также приветствуются любые советы по более эффективному подходу к этому запросу (я хотел бы добавить дополнительное объединение, но с 1 000 000 заказов требования к ресурсам выходят из-под контроля).

================================================

РЕДАКТИРОВАТЬ: Чтобы ответить на вопросы Даршана

Можете ли вы поделиться структурой таблицы:

Таблица содержит строки для всех заказов. Если заказ содержит несколько продуктов, для каждого продукта будет строка (несколько строк для данного заказа). В этом запросе есть только следующие столбцы:

ORDERNUM CHAR : Order Number
ITEM CHAR : SKU for the item
QTY INT : Quantity purchased
ORDDATE DATETIME : Order Date

Возвращенные результаты: все, что мне нужно, это то, что я перечислил в приведенном выше примере. Цель состоит в том, чтобы получить список продуктов, которые чаще всего покупаются вместе.

0

mysql mysql-5.7 php

Решение

Другие решения

Других решений пока нет …

Источник

Accepted Answer

Что вы хотите сделать, это удалить дублирующиеся строки независимо от позиции; Одна хитрость, поскольку у вас всегда есть все комбинации элементов, это отфильтровать результаты в соответствии с предикатом, который говорит item1. < item2 < item3

Вот возможное решение:

SELECT a.item, b.item, c.item, count(*)
from `orders` a left join orders b
on a.ordernum = b.ordernum and a.item <> b.item
left join orders c on a.ordernum = c.ordernum
and a.item <> c.item and b.item <> c.item
where a.item < b.item and b.item < c.item
group by a.item, b.item, c.item
order by count(*) desc

1