代码之家 › 专栏 › 技术社区 › shamim

如何计算丢失的客户端

tsql sql-server sql

-2

shamim · 技术社区 · 8 年前

如何计算上一年缺少的客户记录。示例输入2008具有客户端ID( 1、2、3、4、5、6 )但是2009年只有客户ID( 2和3 )所以丢失的客户端ID总数( 1、4、5、6 )所需输出包含此总数四在里面 二千零九 .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

2 回复 | 直到 8 年前

Wolfgang Kais 8 年前

您所需的输出仅包含基表中包含的年份/产品组合,这意味着如果缺少上一年的所有客户,您将看不到这一点。示例:产品B在2008年有1个客户,但在2009年没有客户。此外,产品B在2007年有2个客户,在2008年有1个(其他的,所以是新的)客户。在这种情况下,您希望的结果显示为空,但它不应该显示2个丢失的客户机(和1个新客户)? 这些数字似乎只取决于年份和产品。我认为看到上一年和今年之间的整个增量(即客户数量、缺失客户和新客户)会更有用。当然,这只有在不缺年的情况下才能奏效。为了隐藏上一个记录年份和下一年之间的增量,我使用了一个秩函数:

WITH
  compare ([Year], Product, ThisYearClient, PrevYearClient, LoyalClient) AS (
    SELECT ISNULL(this.[Year],  prev.[Year]+1)
      , ISNULL(this.Product, prev.Product)
      , this.Client, prev.Client
      , CASE WHEN this.Client = prev.Client THEN this.Client ELSE NULL END
    FROM tblProductClient this
      FULL JOIN tblProductClient prev
        ON this.Product = prev.Product 
        AND this.Client = prev.Client 
        AND this.[Year] = prev.[Year]+1
  ),
  totals ([Year], Product, ClientsThisYear, ClientsPrevYear, LoyalClients, R) AS (
    SELECT [Year], Product
      , COUNT(ThisYearClient)
      , COUNT(PrevYearClient)
      , COUNT(LoyalClient)
      , RANK() OVER (ORDER BY [Year] DESC)
    FROM compare
    GROUP BY [Year], Product
  )
SELECT [Year], Product, ClientsThisYear, ClientsPrevYear, LoyalClients 
  , ClientsThisYear - LoyalClients AS NewClients
  , ClientsPrevYear - LoyalClients AS MissingClients
FROM totals WHERE R > 1
ORDER BY [Year], Product

Gordon Linoff 8 年前

我认为这正是你想要的:

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;