查询不同的行

How do I query for distinct customers? Here's the table I have..

如何查询不同的客户?这是我的桌子。

CustID  DATE    PRODUCT
=======================
1       Aug-31  Orange
1       Aug-31  Orange
3       Aug-31  Apple   
1       Sept-24 Apple
4       Sept-25 Orange

This is what I want.

这就是我想要的。

# of New Customers            DATE
========================================
2                            Aug-31 
1                            Sept-25

Thanks!

谢谢!

2 个解决方案

#1

This is a bit tricky. You want to count the first date a customer appears and then do the aggregation:

这有点棘手。您需要计算客户出现的第一个日期，然后进行聚合:

select mindate, count(*) as NumNew
from (select CustId, min(Date) as mindate
      from table t
      group by CustId
     ) c
group by mindate

#2

You could use a simple common table expression to find the first time a user id is used;

您可以使用一个简单的公共表表达式来查找第一次使用用户id;

WITH cte AS (
  SELECT date, ROW_NUMBER() OVER (PARTITION BY custid ORDER BY date) rn
  FROM customers
)
SELECT COUNT(*)[# of New Customers], date FROM cte
WHERE rn=1
GROUP BY date
ORDER BY date

An SQLfiddle to test with.

一个用来测试的SQLfiddle。

#1