时间戳

首页 > 解决方案 > 如何计算丢失的客户

sql - 如何计算丢失的客户

问题描述

如何计算上一年缺失的客户记录。示例输入 2008 具有客户端 ID（1,2,3,4,5,6），但 2009 仅具有客户端 ID（2 和 3），因此缺少的客户端 ID（1,4,5,6）所需的总输出包含此总数4在2009 年。2007 年和 2008 年的方式相同。需要查询所需的输出。

表结构是 

CREATE TABLE [dbo].[tblProductClient](
    [Year] [nchar](10) NULL,
    [Product] [nchar](10) NULL,
    [Client] [nchar](10) NULL,
    [TotalMissingClient] [int] NULL
) ON [PRIMARY]

表格样本数据 

Year    Product Client  TotalMissingClient
2008        A    1          NULL
2008        A    2          NULL
2008        A    3          NULL
2007        B    3          NULL
2007        B    2          NULL
2008        B    1          NULL
2007        A    2          NULL
2009        A    2          NULL
2009        A    3          NULL
2008        A    4          NULL
2008        A    5          NULL
2008        A    6          NULL

所需的输出是 

Year    Product Client  TotalMissingClient
2008        A      1            0
2008        A      2            0
2008        A      3            0
2007        B      3            NULL
2007        B      2            NULL
2008        B      1            NULL
2007        A      2            NULL
2009        A      2            4
2009        A      3            4
2008        A      4            0
2008        A      5            0
2008        A      6            0

标签： sqlsql-servertsql

解决方案

我认为这可以通过以下方式捕获您想要的内容：

select t.*,
       (count(distinct client) over () -
        count(distinct clinet) over (partition by year)
       ) as TotalMissingClient
from t;

唉，SQL Server 不支持count(distinct)窗口函数。因此，一种方法是使用嵌套窗口函数：

select t.*,
       (max(rank_c) over () -
        max(rank_yc) over (partition by year)
       ) as TotalMissingClient
from (select t.*,
             dense_rank() over (order by client) as rank_c,
             dense_rank() over (partition by year order by client) as rank_yc
      from t
     ) t;

编辑：

我想我误解了这个问题。以上解决了所有客户多年来的问题。你只想要一年与以前相比：

select t.*, tprev.TotalMissingClient
from t left join
     (select tprev.year, count(distinct tt.client) as TotalMissingClient
      from t tprev left join
           t tt
           on tprev.year = tt.year - 1
              tprev.client = tt.client
      where tt.client is null
      group by tprev.year
     ) tprev
     on tprev.year = t.year - 1;

推荐阅读