sql-server - 我们如何在 SQL 中按日期计算新的不同值组

Question

我需要在一列中使用不同数量的值并按日期对其进行分组，真正的问题是如果它已经出现在先前的结果中，则不应该包括计数。

例如：考虑表 tblcust

 Date        Customers
 March 1     Mike
 March 1     Yusuf
 March 1     John
 March 2     Ajay
 March 2     Mike
 March 2     Anna

结果应该是

Date       Customer_count
March 1       3
March 2       2

如果我使用

select date,count(distinct(customer)) as customer_count
group by date

我得到的结果是

Date      customer_count    
March 1   3
March 2   3

客户 Mike 已被访问过两次，不应算作新客户。

标签： sql-servercountdistinct

Answer 1

您可以尝试使用SQL Server ROW_NUMBER 函数 来实现这一点。

create table tblCust (DtDate varchar(20), Customers Varchar(20))
insert into tblCust Values
('March 1', 'Mike'),
('March 1', 'Yusuf'),
('March 1', 'John'),
('March 2', 'Ajay'),
('March 2', 'Mike'),
('March 2', 'Anna')

 Select dtDate
     , Customers
     , ROW_NUMBER() OVER (PARTITION BY Customers ORDER BY DtDate) as SrNo
from tblCust
order by DtDate, SrNo

select Dtdate,
    count(distinct(customers)) as customer_count
from(Select dtDate
     , Customers
     , ROW_NUMBER() OVER (PARTITION BY Customers ORDER BY DtDate) as SrNo
from tblCust
)a where SrNo = 1
group by Dtdate

这是现场db<>fiddle演示