依靠SQL中的一系列值

I have a table that has two columns - Customer ID and number of products they purchased.

我有一个包含两列的表 - 客户ID和他们购买的产品数量。

What SQL statement would I use to know how many customers purchased 1 or more products, 2 or more products, 3 or more products etc?

我会用什么SQL语句来了解有多少客户购买了1个或更多产品，2个或更多产品，3个或更多产品等？

Can this be done WITHOUT using Cross Joins (I'm using google bigquery which does not support that).

这可以在不使用交叉连接的情况下完成（我使用的谷歌bigquery不支持）。

3 个解决方案

#1

If you truly need to count the number of customers who purchase "one or more" and "two or more" separately, you'll need a CASE expression:

如果您真的需要计算分别购买“一个或多个”和“两个或更多”的客户数量，您需要一个CASE表达式：

SELECT SUM(CASE WHEN NumOfProducts >= 1 THEN 1 ELSE 0 END) AS Purchased_1_or_more
     , SUM(CASE WHEN NumOfProducts >= 2 THEN 1 ELSE 0 END) AS Purchased_2_or_more
     , SUM(CASE WHEN NumOfProducts >= 3 THEN 1 ELSE 0 END) AS Purchased_3_or_more
     , SUM(CASE WHEN NumOfProducts >= 4 THEN 1 ELSE 0 END) AS Purchased_4_or_more
     , SUM(CASE WHEN NumOfProducts >= 5 THEN 1 ELSE 0 END) AS Purchased_5_or_more
FROM Customers

And so on for however many categories you want.

等等，无论你想要多少类别。

#2

Try to use:

尝试使用：

SELECT 
CASE NumOfProducts >= 1 THEN 1
WHEN NumOfProducts >= 2 THEN 2
WHEN NumOfProducts >= 3 THEN 3
ELSE 0
END CASE,
COUNT(CustomerID) AS cnt
FROM Customers
GROUP BY CASE NumOfProducts >= 1 THEN 1
WHEN NumOfProducts >= 2 THEN 2
WHEN NumOfProducts >= 3 THEN 3
ELSE 0
END;

#3

-1

I don't believe this is possible using straight SQL without cross-product.

我不相信这可以使用没有交叉产品的直接SQL。

The reason is following - the operation we care about to do collapsing is using "group by". However the way group by works, is it takes multiple rows and collapses them into one row while performing some aggregate calculation.

原因如下 - 我们关心崩溃的操作是使用“分组依据”。然而，按组工作的方式是，它需要多行并将它们折叠成一行，同时执行一些聚合计算。

To do this kind of "one or more" analysis one row needs to be part of the calculation of multiple rows. i.e. the row for the user that has purchased 10 products needs to be part of the rows "1 or more", "2 or more", "3 or more", etc. There's no operation that lets you do this.

要进行这种“一个或多个”分析，一行需要成为多行计算的一部分。即，已购买10个产品的用户的行需要是“1或更多”，“2或更多”，“3或更多”等行的一部分，等等。没有任何操作可以让您这样做。

#1