Блог

Главная - Вопросы по программированию - SQL выберите с помощью GROUP BY и ПОДСЧЕТА?

SQL выберите с помощью GROUP BY и ПОДСЧЕТА?

#sql #sql-server #tsql

Вопрос:

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

  • Товары: со столбцами ID, Название, Цена
  • Клиенты: с идентификатором столбца, именем
  • Покупки: со столбцами ProductID, CustomerID, Дата покупки

Теперь, как бы я мог получить продукты, приобретенные более чем N разными клиентами?

Я попробовал следующее в пробной версии SQL Server 2019, но при подсчете я получаю синтаксическую ошибку.

 SELECT ProductID, CustomerID, COUNT(*) as C    
FROM Purchases    
GROUP BY ProductID, CustomerID    
HAVING C > 100    
ORDER BY C DESC

Еще лучше, как бы я мог получить продукты, приобретенные более чем N разными клиентами в течение 30-дневного периода?

Спасибо за любую помощь и/или указания.

Комментарии:

1. Ваша текущая схема не поддерживает это, вы не сохраняете временные метки покупок. В данном сценарии нет способа отфильтровать покупки по диапазону данных.

2. Пожалуйста, опубликуйте точное сообщение об ошибке

Ответ №1:

С вашим текущим запросом вы просто подсчитываете, как часто каждый клиент покупал каждый продукт, потому что вы группируетесь по сочетанию productid и customerid . Кроме того, вы не можете ссылаться на псевдоним столбца для количества в HAVING ORDER BY предложении или

Попробуйте это

 declare @purchasedatelower datetime = dateadd(day, -30, getdate())
declare @purchasedateupper datetime = getdate()
declare @distinctcustomers int = 100

select productid, count(distinct customerid) as customercount
from purchases
where purchasedate between @purchasedatelower and @purchasedateupper
group by productid
having count (distinct customerid) >= @distinctcustomers
order by count(distinct customerid) desc

Это вернет все продукты, которые были куплены по крайней мере 100 различными клиентами за последние 30 дней, вместе с определенным количеством клиентов.

Комментарии:

1. Это делает свое дело. И большое спасибо за ваше объяснение!

2. Я уже сделал это. И я получаю сообщение «Спасибо за отзыв! Вам нужно не менее 15 репутации, чтобы проголосовать, но ваши отзывы были записаны».

3. @KeineUrsache вы можете принять ответ, нажав на галочку. Чего вы не можете сделать (пока), так это озвучить ответ, нажав на стрелку вверх.

Ответ №2:

Попробуйте заказать их по COUNT() 

 SELECT ProductID, CustomerID

FROM Purchases

GROUP BY ProductID, CustomerID

HAVING COUNT(*) > 100

ORDER BY COUNT(*) DESC

Комментарии:

1. Это лучше, но я также получаю предметы, приобретенные несколько раз только одним клиентом.

Ответ №3:

Теперь, как бы я мог получить продукты, приобретенные более чем N различными клиентами?

Вы бы использовали COUNT(DISTINCT) :

 SELECT ProductID, COUNT(DISTINCT CustomerID) as num_customers   
FROM Purchases    
GROUP BY ProductID    
HAVING COUNT(DISTINCT CustomerID) > 100    
ORDER BY COUNT(DISTINCT CustomerID) DESC;

Если вы имеете в виду определенный период, добавьте WHERE предложение перед GROUP BY .

Ответ №4:

 DECLARE @Popularity int = 1
DECLARE @MonthsAgo int = 1

SELECT p.ProductId, p.ProductName, pc.CustomersDistinct
FROM (
    -- Sub-query to compute the aggregate.
    -- Count the number of distinct customers that purchased each product.
    SELECT ProductId, COUNT(DISTINCT CustomerId) AS CustomersDistinct
    FROM Purchases
    WHERE PurchaseDate >= DATEADD(MONTH, -@MonthsAgo, CURRENT_TIMESTAMP)
    GROUP BY ProductId
    HAVING COUNT(DISTINCT CustomerId) > @Popularity
) AS pc
    -- Now do JOINs to look up helpful values for the final SELECT list.
    INNER JOIN Products p ON pc.ProductId = p.ProductId
ORDER BY 3 DESC
Метки: