SQL Server 2014中客户端的轮换

Question

我有一个SQL问题。我有下表：

declare @t table (date datetime, 
      shop VARCHAR(50), 
      client VARCHAR(50) 
     ); 
insert into @t 
VALUES ('2016-01-15', 'abc','a1'), 
     ('2016-01-15', 'abc','b1'), 
     ('2016-01-15', 'def','c1'), 
     ('2016-01-15', 'def','a1'), 
     ('2016-01-15', 'ghi','b1'), 
     ('2016-01-15', 'ghi','a1'), 
     ('2016-02-15', 'abc','a1'), 
     ('2016-02-15', 'abc','b1'), 
     ('2016-02-15', 'abc','c1'), 
     ('2016-02-15', 'def','a1'), 
     ('2016-02-15', 'ghi','b1'), 
     ('2016-02-15', 'ghi','a1'), 
     ('2016-03-15', 'abc','a1'), 
     ('2016-03-15', 'abc','c1'), 
     ('2016-03-15', 'def','a1'), 
     ('2016-03-15', 'ghi','b1'), 
     ('2016-03-15', 'ghi','e1') 

我wolud喜欢计算客户端的旋转。因此，对于每个月，对于每家商店，我都必须指出有多少客户流失了，有多少客户仍然是一个月前。我不能只看客户的数量，但我必须检查客户的具体名称是否在一个月前出现。所有日期看起来像这样：“年-15月”。

所以我想获得表如下：

因此，例如，对于ABC店后

declare @t2 table (date date, 
       shop VARCHAR(50), 
       churned INTEGER, 
       stayed INTEGER, 
       came INTEGER 
      ); 

INSERT INTO @t2 
VALUES ('2016-02-15', 'abc', 0, 2, 1), 
    ('2016-02-15', 'def', 1, 1, 0), 
    ('2016-02-15', 'ghi', 0, 2, 0), 
    ('2016-03-15', 'abc', 1, 2, 0), 
    ('2016-03-15', 'def', 0, 1, 0), 
    ('2016-03-15', 'ghi', 1, 1, 1) 

第一个月0客户搅动，2个客户端留校月前和1个新客户来了。 搅动意味着客户离开了。

感谢您的帮助。我使用SQL Server 2014

编辑： 再举一个例子： 对于这个数据：

declare @t table (date datetime, 
shop VARCHAR(50), 
client VARCHAR(50) 
    ); 
    insert into @t 
    VALUES ('2016-01-15', 'abc','a1'), 
    ('2016-01-15', 'abc','b1'), 

    ('2016-02-15', 'abc','b1'), 
    ('2016-02-15', 'abc','c1'), 

    ('2016-03-15', 'abc','z1'), 
    ('2016-03-15', 'abc','y1'), 
    ('2016-03-15', 'abc','a1') 

我应该得到下表：

declare @t2 table (date date, 
      shop VARCHAR(50), 
      churned INTEGER, 
      stayed INTEGER, 
      came INTEGER 
     ); 

    INSERT INTO @t2 
    VALUES 
    ('2016-01-15', 'abc', 0, 0, 2), 
    ('2016-02-15', 'abc', 1, 1, 1), 
    ('2016-03-15', 'abc', 2, 0, 3) 

这是一个只有一个简单的例子店铺澄清。

Answer 1

你可以试试这个

SELECT 
    ISNULL(T1.date, DATEADD(MONTH,1,T2.date)) date, 
    ISNULL(T2.shop,T1.shop) shop, 
    COUNT(CASE WHEN T1.client IS NULL THEN 1 END) churned, 
    COUNT(case when (T1.client = T2.client) then 1 end) stayed, 
    COUNT(CASE WHEN T2.client IS NULL THEN 1 END) came 
FROM 
    @t T1 
    FULL JOIN @t T2 ON MONTH(T1.date) - 1 = MONTH(T2.[date]) 
      AND T1.client = T2.client AND T1.shop = T2.shop 
GROUP BY 
    ISNULL(T1.date, DATEADD(MONTH,1,T2.date)) ,ISNULL(T2.shop,T1.shop) 
ORDER BY [date], shop