sql - 每周在 SQL Server 中获得新客户

Question

我有一些与客户相关的数据以及他们每天执行的交易数量。我想看看我们每周有多少"new"客户。数据如下所示:

Custnum    Created    Revenue
1    2014/10/23    30
4    2014/10/23    20
5    2014/10/23    40
2    2014/10/30    13
3    2014/10/30    45
1    2014/10/30    56

在上面的(示例)数据中，我们可以看到 custnum 1 的客户连续几周有交易，我只想要下周的新客户，那些从未与过去的我们。换句话说，我想要每周的全新客户数量。所以结果应该是:

CustCount    Created
3    2014/10/23
2    2014/10/30

我尝试使用以下查询:

select 
    count(distinct custnum), 
    DATEADD(wk, DATEDIFF(wk, 0, created), 0) as Date
from ORDERS
where created > '2013-01-01'
group by 
    DATEADD(wk, DATEDIFF(wk, 0, created), 0)
order by
    DATEADD(wk, DATEDIFF(wk, 0, created), 0)

但是这个查询给了我每周唯一客户的数量，我想要每周新客户的数量。

任何帮助将不胜感激。

Answer 1

我对你问题的理解

我接受了这些陈述:

• 我想看看我们每周有多少"new"客户。
• 过去从未与我们有过业务往来的[客户]
• 我想知道每周新客户的数量

你要吗

 CustCount    Created
 2            week1    -- customer 1 and 2
 1            week2    -- customer 3
 2            week3    -- customer 4 and 5
 -- Option A
 1            week4    -- customer 6 is new and 2 was not counted 
 -- or Option B
 2            week4    -- customer 6 and 2; 
                       -- since customer 2 did not order anything in week3

选项A

此查询 SELECT Custnum, DATEPART ( week , created) as WeekNumber from Revenues Order by Custnum 为提供的示例数据返回此输出

Custnum    WeekNumber 
1          31       -- counts
1          44       -- does not count, since customer already ordered once
2          36       -- counts
3          36       -- counts
3          44       -- does not count
4          43       -- counts
5          43       -- counts
5          45       -- does not count

第一步:过滤记录

要只获取客户(新客户)的第一条记录，您可以这样做:

SELECT Distinct Custnum, Min(Created) as Min_Created 
FROM Revenues 
GROUP BY Custnum

第二步:按周统计和分组

首先我使用了来自grouping customer orders by week 的sql ，您可以在 old sqlfiddle 找到.但后来我决定使用

Select Count(Custnum) as CountCust 
    , DATEPART(week, Min_Created) as Week_Min_Created 
FROM (
        SELECT Distinct Custnum, Min(Created) as Min_Created  
        FROM Revenues Group By Custnum
    ) sq Group by DATEPART(week, Min_Created)

在我的 sql-server-2008-r2这返回

CountCust  Week_Min_Created
1          31    -- only customer 1
2          36    -- customer 2 and 3
2          43    -- customer 4 and 5
-- nothing for week 45 since customer 5 was already counted

一些示例数据

这是我使用的示例数据

CREATE TABLE Revenues 
(
   Custnum int , 
   Created datetime,
   Revenue int 
);

INSERT INTO Revenues (Custnum, Created, Revenue)
VALUES
  (1, '20140801', 30),
  (2, '20140905', 13), (3, '20140905', 45),
  (4, '20141023', 20), (5, '20141023', 40),
  (3, '20141030', 45), (1, '20141030', 56),
  (5, '20141106', 60);