Блог

У меня есть таблица SQLite, в которой я хотел бы отфильтровать клиентов, которые купили 2 или более ОТДЕЛЬНЫХ товара. Таблица выглядит следующим образом:

 customer  | product | quantity | date of purchase
---------------------------------------------
John      | coffee  |    1     |    02.20.2019
Bob       | coffee  |    2     |    01.13.2018
Chris     | milk    |    1     |    02.03.2012
Alice     | bread   |    1     |    05.01.2019
John      | banana  |    2     |    08.04.2015
Mary      | orange  |    3     |    12.16.2018
Mary      | orange  |    2     |    04.21.2019
Chris     | pie     |    1     |    07.02.2004
  

Мне удалось выбрать клиентов, сгруппировав их с помощью двух строк в ГРУППЕ ПО запросу (клиент и продукт):

 SELECT * FROM test_table GROUP BY customer, product
  

Это (почти) дало мне всех клиентов, которых я изначально хотел, за исключением того, что в него также вошла Мэри, что не является желаемым результатом (она не покупала два разных продукта). Я попытался использовать функцию COUNT DISTINCT, чтобы показывать только клиентов, которые купили два или более разных продукта, например:

 SELECT customer, product, COUNT(DISTINCT(product)) AS counter FROM test_table
GROUP BY customer, product HAVING counter>1
  

Запрос успешно вернул Chris и John без Mary, однако он выводит их только один раз, показывая только один из продуктов, которые они купили. Есть ли способ, которым я мог бы предоставить Крису и Джону все отдельные продукты, которые они купили? Ожидаемый результат запроса:

 John      | coffee  |    1     |    02.20.2019
John      | banana  |    2     |    08.04.2015
Chris     | milk    |    1     |    02.03.2012
Chris     | pie     |    1     |    07.02.2004
  

С помощью агрегации:

 SELECT *
FROM test_table
WHERE customer IN (SELECT customer
                   FROM test_table
                   GROUP BY customer
                   HAVING MIN(product) <> MAX(product));
  

В HAVING приведенном выше предложении утверждается, что «минимальные» и «максимальные» продукты не совпадают, что подразумевает, что с каждым соответствующим клиентом связано как минимум два разных продукта.

Для более сложного решения мы могли бы использовать аналитические функции здесь:

 WITH cte AS (
    SELECT *, MIN(product) OVER (PARTITION BY customer) AS min_product,
              MAX(product) OVER (PARTITION BY customer) AS max_product
    FROM test_table
)

SELECT customer, product, quantity, date_of_purchase
FROM cte
WHERE min_product <> max_product;
  

Вы можете использовать EXISTS для получения всех строк:

 SELECT t.*
FROM test_table t
WHERE EXISTS (SELECT 1 FROM test_table WHERE customer = t.customer AND product <> t.product)
  

Результат:

 > customer | product | quantity | date_of_purchase
> :------- | :------ | -------: | :---------------
> John     | coffee  |        1 | 02.20.2019      
> Chris    | milk    |        1 | 02.03.2012      
> John     | banana  |        2 | 08.04.2015      
> Chris    | pie     |        1 | 07.02.2004 
  

Или, если вам нужна 1 строка для каждого customer с продуктами и датами в виде списков, разделенных запятыми, вы можете использовать GROUP_CONCAT() :

 SELECT customer, 
       GROUP_CONCAT(product) products,
       GROUP_CONCAT(date_of_purchase) dates
FROM test_table
GROUP BY customer
HAVING COUNT(DISTINCT product) > 1 
  

Результат:

 > customer | products      | dates                
> :------- | :------------ | :--------------------
> Chris    | milk,pie      | 02.03.2012,07.02.2004
> John     | coffee,banana | 02.20.2019,08.04.2015
  

Посмотрите демонстрацию.