Помогите составить 2 SQL-запроса

Question

[Андрей]

Есть таблица с тремя колонками:
Компания — Покупатель — Продукт
Условия:
— один покупатель может быть соотнесен лишь с одной компанией
— У покупателя может быть несколько продуктов
— У компании может быть несколько покупателей
— связь компаний с продуктами не рассматривается
Соответственно записи выглядят примерно так:

company_1 | customer_1 | item_1
company_1 | customer_1 | item_2
company_1 | customer_1 | item_3
company_1 | customer_2 | item_2
company_2 | customer_3 | item_1
company_2 | customer_4 | item_3
company_2 | customer_3 | item_2

Задача:
1. построить ТОП-100 компаний по количеству покупателей, купивших более 10 разных продуктов.Записей в таблице до фига.
2. имея то или иное значение компании выяснить её место в этом рейтинге

Answer 1

[Vampiro]

SELECT  `company` , customer, COUNT(  `item` ) AS items
FROM  `test` 
GROUP BY company
HAVING COUNT(  `item` ) >10
ORDER BY COUNT(  `customer` ) DESC 

Я бы выгружал это в отдельную табличку и там уже искал рейтинг нужной. Хотя можно потестить вычисляемое поле в этот запрос затискать.

Comments

[erakitin]

Может быть я ошибаюсь, но этот запрос не будет работать, потому что в SELECT есть поле customer. У компании же может быть несколько покупателей.

[Vampiro]

Может быть это окажется откровением для Вас, но поле customer будет в выборке взято первое из отсортированных, то есть в данном запросе это окажется самый покупающий покупатель. Хотя я бы не стал на это особо завязываться. Можно вставить count, смотря что в отчетах нужнее.

[Андрей]

Vampiro, самый покупающий покупатель не нужен, нужна самая продающая компания.
Ваш запрос работает, спасибо!

Answer 2

[balloon]

-- checked on 4M rows

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company` int(11) NOT NULL,
  `customer` int(11) NOT NULL,
  `item` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `search` (`company`,`customer`,`item`)
) ENGINE=MyISAM AUTO_INCREMENT=4000001 DEFAULT CHARSET=utf8;

-- 1) ~ 2.2sec
SELECT company, count(customer) as `customers_count`
FROM (SELECT customer, company FROM test GROUP BY customer HAVING COUNT(DISTINCT item) > 5) as `temp`
GROUP BY company
ORDER BY customers_count DESC
LIMIT 100

-- 2) ~2.2sec

SET @rank = 0;
SELECT rank, company FROM (
	SELECT @rank := @rank + 1 as `rank`, company FROM (
		SELECT company, count(customer) as `customers_count`
		FROM (SELECT customer, company FROM test GROUP BY customer HAVING COUNT(DISTINCT item) > 5) as `temp`
		GROUP BY company
		ORDER BY customers_count DESC
		LIMIT 100
	) as `temp2`
) as `temp3`
WHERE company = 159

Comments

[Андрей]

Круто, спасибо!

[3dm]

разве учитывается «1. построить ТОП-100 компаний по количеству покупателей, купивших более 10 разных продуктов. Записей в таблице до фига.»

[iSleep]

Да, учитывается, используется:
HAVING COUNT(DISTINCT item) > 5

Answer 3

[wartur]

Вам нужно активно пользоваться GROUP BY и DISTRINCT операторами. Чуть позже напишу более подробное решение.

Comments

[wartur]

пардон: DISTINCT

[wartur]

а так же агрегатными функциями SUM(), COUNT()

[wartur]

Вроде как-то так, могу ошибаться:
1-й ответ, это скорее всего либо с подзапросом, либо с временной таблицей, в общем получается какой-то такой поток разума =):

Выбрать все купившие товары, сгруппированные по пользователям и уникальные по ID (DISTRICT) и посчитать количесвто COUNT(айди товара) и сделать условие, где этот COUNT > вашего количества, этим мы получаем количество товаров купленным каждым пользователем. Далее вместе с выбранными пользователями выбираем компании и группируем по компаниям сортируем по количеству пользователей с LIMIT до вашего количества

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

Извиняюсь за сумбур, но без рабочей базы трудно все это протестировать. Удачи, надеюсь помог, если сказал все верно.