按财政年度分组并应用这些组作为筛选器

Question

我希望建立6个小组，客户将落入：

非购买者(从未从我们这里购买)

新购买者(本财政年度内首次购买)

重新启用的采购员(在本财政年度购买，也在最近第二个年度购买)

失效购买者(在上一财政年度购买，而不是本财政年度购买)

连续购买2年(在本财政年度和最近一个财政年度购买过)

3-4年连续采购者(在过去3或4个财政年度内每年都购买)

5+年度连续采购商(每一财政年度至少购买5年)

我将使用的财政年度是从4月1日到3月31日，并将使用以下表格：

purchaser (including id (primary key))

purchases (date_purchased, purchases_purchaser_id)

表是在purchaser_id = purchases_purchaser_id上连接的，每个购买者在任何财政年度都可以有多个购买(所以也可以按年分组)

它已经把我逼疯了，所以任何帮助我都会非常感激！

谢谢，戴文

Answer 1

这是动态版本

Declare @currentYear int
Declare @OlderThan5yrs datetime 

Set @currentYear  = Year(GetDate()) - Case When month(GetDate())<4 then 1 else 0 end
Set @OlderThan5yrs = cast(cast( @currentYear-5 as varchar(4))+'/04/01' as datetime)

Select p.pName,
       p.purchaser_id,
       isNull(a.[5+YrAgo],0) as [5+YrAgo],
       isNull(a.[4YrAgo], 0)  as [4YrAgo],
       isNull(a.[3YrAgo], 0)  as [3YrAgo],
       isNull(a.[2YrAgo], 0)  as [2YrAgo],
       isNull(a.[1YrAgo], 0)  as [1YrAgo],
       isNull(a.[CurYr],  0)   as [CurYr],
       isNull(a.Category, 'Non-purchaser (ever)') as Category
From purchasers p
Left Join 
(        
   Select purchases_purchaser_id, 
          [5] as [5+YrAgo],
          [4] as [4YrAgo],
          [3] as [3YrAgo],
          [2] as [2YrAgo],
          [1] as [1YrAgo],
          [0]  as [CurYr],
         Case When [4]+[3]+[2]+[1]+[0] = 5     Then '5+ year consecutive'
              When [2]+[1]+[0] = 3             Then '3-4 yr consecutive'
              When [1]+[0] = 2                 Then '2 yr Consecutive'
              When [1]=1 and [0]=0             Then 'Lapsed'
              When [2]=1 and [1]=0 and [0]=1   Then 'Reactivated'
              When [4]+[3]+[2]+[1]=0 and [0]=1 Then 'New'
              When [4]+[3]+[2]+[1]+[0] = 0     Then 'Non-purchaser (last 5 yrs)'
              Else 'non categorized'
         End as Category     
   From (
            Select purchases_purchaser_id, 
                   Case When date_purchased < @OlderThan5yrs Then 5 
                        Else @currentYear - Year(date_purchased)+ Case When month(date_purchased)<4 Then 1 else 0 end 
                     end as fiscalYear, count(*) as nPurchases
            From purchases 
            Group by purchases_purchaser_id,
                   Case When date_purchased < @OlderThan5yrs Then 5 
                        Else @currentYear - Year(date_purchased)+ Case When month(date_purchased)<4 Then 1 else 0 end 
                     end
         ) as AggData
   PIVOT ( count(nPurchases) for fiscalYear in ([5],[4],[3],[2],[1],[0]) ) pvt
) as a
on p.purchaser_id=a.purchases_purchaser_id

更新：

这是我在前面的查询中插入的数据的结果(您必须在查询中的表名后加上#)。

pName                purchaser_id 5+YrAgo 4YrAgo 3YrAgo 2YrAgo 1YrAgo CurYr Category
-------------------- ------------ ------- ------ ------ ------ ------ ----- --------------------------
Non-purchaser                   0       0      0      0      0      0     0 Non-purchaser (ever)
New purchaser                   1       0      0      0      0      0     1 New
Reactivated                     2       0      0      1      1      0     1 Reactivated
Lapsed                          3       0      0      0      1      1     0 Lapsed
2 yr Consecutive                4       0      0      0      0      1     1 2 yr Consecutive
3 yr consecutive                5       0      0      0      1      1     1 3-4 yr consecutive
4 yr consecutive                6       0      0      1      1      1     1 3-4 yr consecutive
5+ year consecutive             7       1      1      1      1      1     1 5+ year consecutive
Uncategorized                   8       0      0      1      0      0     0 non categorized
old one                         9       1      0      0      0      0     0 Non-purchaser (last 5 yrs)

你也不需要列5+YrAgo，4YrAgo，3YrAgo，2YrAgo，1YrAgo和CurYr。我添加它们是为了更容易检查查询逻辑。

更新2

下面是你在评论中提出的问题。我在中使用过的表结构有：

Table purchasers ( purchaser_id int, pName varchar(20))
Table purchases (purchases_purchaser_id int, date_purchased datetime)

还有一个外键表示购买(purchaser_id)，也就是外键(purchases_purchaser_id)。

;With AggData as (
Select   purchases_purchaser_id, 
         Case When [4]+[3]+[2]+[1]+[0] = 5     Then 1 end as [Consec5],
         Case When [4]=0 and [2]+[1]+[0] = 3   Then 1 end as [Consec34],
         Case When [2]=0 and [1]+[0] = 2       Then 1 end as [Consec2],
         Case When [1]=1 and [0]=0             Then 1 end as [Lapsed],
         Case When [2]=1 and [1]=0 and [0]=1   Then 1 end as [Reactivated],
         Case When [4]+[3]+[2]+[1]=0 and [0]=1 Then 1 end as [New],
         Case When [4]+[3]+[2]>0 and [1]+[0]=0 Then 1 end as [Uncateg]
   From  (
            Select purchases_purchaser_id, 
                   @currentYear - Year(date_purchased) + Case When month(date_purchased)<4 Then 1 else 0 end as fiscalYear, 
                   count(*) as nPurchases
              From purchases      
             Where date_purchased >= @OlderThan5yrs  
             Group by purchases_purchaser_id,
                      @currentYear - Year(date_purchased) + Case When month(date_purchased)<4 Then 1 else 0 end
         ) as AggData
   PIVOT ( count(nPurchases) for fiscalYear in ([4],[3],[2],[1],[0]) ) pvt
)
Select count([Consec5])     as [Consec5],
       count([Consec34])    as [Consec34],
       count([Consec2])     as [Consec2],
       count([Lapsed])      as [Lapsed],
       count([Reactivated]) as [Reactivated],
       count([New])         as [New],
       count(*)-count(a.purchases_purchaser_id) as [Non],
       count([Uncateg])     as [Uncateg]
  From purchasers p
 Left Join AggData as a
  on p.purchaser_id=a.purchases_purchaser_id              

结果(使用上一篇文章中的测试数据)

Consec5 Consec34 Consec2 Lapsed Reactivated New Non Uncateg
------- -------- ------- ------ ----------- --- --- -------
      1        2       1      1           1   1   2       1

Answer 2

虽然使用另一个显示5个会计年度的日期范围表可以更容易一些，但我已经为您的查询硬编码了from/to日期引用，并且看起来很有效……

内部选择将预先收集基于给定日期范围内的任何1个或更多购买的“标志”...例如: Apr 1,2010 = "20100401“表示日期转换为2011年3月31日= "20110331"，并在过去5年中循环...此外，一个标志，以计数任何与实际购买表中的日期购买，以确认“从未购买”与某人购买6年，7年或更早的历史…

该查询的基础将基本上创建发生活动的可能单个年份的交叉表。然后，我可以用最详细的标准查询他们的分类的一些标题，直到最小……

我尽可能地从另一种SQL语言转换为符合SQL-Server语法(主要是关于日期转换)，但除此之外，原理和查询确实有效...最终的分类列是character，但是可以是您想要替换的任何内容。

SELECT
      id,
      CASE 
         WHEN year1 + year2 + year3 + year4 + year5 = 5 THEN "5+yrs "
         WHEN year1 + year2 + year3 + year4 >= 3 THEN "3-4yrs"
         WHEN year1 + year2 = 2, "2yrs  "
         WHEN year1 = 1 AND year2 = 0 AND year3 = 1 THEN "Reacti"
         WHEN year1 = 1 THEN "New   "
         WHEN year1 = 0 AND year2 = 1 THEN "Lapsed"
         WHEN AnyPurchase = 1, "over5"
         ELSE "never" BuyerClassification
      END
   FROM
      ( SELECT
            id,
            MAX( CASE WHEN date_purchased >= CONVERT( Date, "20100401", 112 ) 
                       AND date_purchased <= CONVERT( Date, "20110331", 112 ) 
                 THEN 1 ELSE 0 END ) Year1,
            MAX( CASE WHEN date_purchased >= CONVERT( Date, "20090401", 112 ) 
                       AND date_purchased <= CONVERT( Date, "20100331", 112 )
                 THEN 1 ELSE 0 END ) Year2,
            MAX( CASE WEHEN date_purchased >= CONVERT( Date, "20080401", 112 ) 
                       AND date_purchased <= CONVERT( Date, "20090331", 112 )
                 THEN 1 ELSE 0 END ) Year3,
            MAX( CASE WHEN date_purchased >= CONVERT( Date, "20070401", 112 ) 
                       AND date_purchased <= CONVERT( Date, "20080331", 112 )
                 THEN 1 ELSE 0 END ) Year4,
            MAX( CASE WHEN date_purchased >= CONVERT( Date, "20060401", 112 ) 
                       AND date_purchased <= CONVERT( Date, "20070331", 112 )
                 THEN 1 ELSE 0 END ) Year5,
            MAX( CASE WHEN date_purchased <= CONVERT( Date, "20100401", 112 )
                 THEN 1 ELSE 0 END ) AnyPurchase
         FROM
            purchaser LEFT OUTER JOIN purchases
               ON purchaser.id = purchases.purchases_purchaser_id
         GROUP BY 
            1 ) PreGroup1

EDIT --修复了通过语法转换而丢失的括号...

"Group By 1“是指按查询中的第一列进行分组，第一列是来自购买者的购买者ID。通过执行左-外连接，将保证购买者表中所有可能的人，而不管是否有任何实际购买。"PreGroup1“是select语句的”别名“，以防您想要在最外层的select语句中执行后续的其他连接，以检测要分类的年份值。

虽然它可以工作，但可能不会像其他人所说的那样有效，通过对查询进行分析，它可能会打开您的思维，使您了解一些查询和聚合技术。这个过程基本上是通过利用内部SQL-Select上的case/when结构和最外层的SQL-Select上的最终分类来创建一种交叉表。

Answer 3

MS SQL Server (适用于2000、2005、2008)

SET NOCOUNT ON

CREATE TABLE #purchasers (purchaser_id int, pName varchar(20))

Insert Into #purchasers values (0, 'Non-purchaser')
Insert Into #purchasers values (1, 'New purchaser')
Insert Into #purchasers values (2, 'Reactivated')
Insert Into #purchasers values (3, 'Lapsed')
Insert Into #purchasers values (4, '2 yr Consecutive')
Insert Into #purchasers values (5, '3 yr consecutive')
Insert Into #purchasers values (6, '4 yr consecutive')
Insert Into #purchasers values (7, '5+ year consecutive')
Insert Into #purchasers values (8, 'Uncategorized')
Insert Into #purchasers values (9, 'old one')


CREATE TABLE #purchases (date_purchased datetime, purchases_purchaser_id int)

Insert Into #purchases values ('2010/05/03', 1)

Insert Into #purchases values ('2007/05/03', 2)
Insert Into #purchases values ('2008/05/03', 2)
Insert Into #purchases values ('2010/05/03', 2)

Insert Into #purchases values ('2008/05/03', 3)
Insert Into #purchases values ('2009/05/03', 3)


Insert Into #purchases values ('2009/05/03', 4)
Insert Into #purchases values ('2010/05/03', 4)

Insert Into #purchases values ('2008/05/03', 5)
Insert Into #purchases values ('2009/05/03', 5)
Insert Into #purchases values ('2010/05/03', 5)

Insert Into #purchases values ('2007/05/03', 6)
Insert Into #purchases values ('2008/05/03', 6)
Insert Into #purchases values ('2009/05/03', 6)
Insert Into #purchases values ('2010/05/03', 6)

Insert Into #purchases values ('2004/05/03', 7)
Insert Into #purchases values ('2005/05/03', 7)
Insert Into #purchases values ('2006/05/03', 7)
Insert Into #purchases values ('2007/05/03', 7)
Insert Into #purchases values ('2008/05/03', 7)
Insert Into #purchases values ('2009/05/03', 7)
Insert Into #purchases values ('2009/05/03', 7)
Insert Into #purchases values ('2009/05/03', 7)
Insert Into #purchases values ('2010/05/03', 7)

Insert Into #purchases values ('2007/05/03', 8)

Insert Into #purchases values ('2000/05/03', 9)

Select p.pName,
       p.purchaser_id,
       isNull(a.[2005],0) as [Bef.2006],
       isNull(a.[2006],0) as [2006],
       isNull(a.[2007],0) as [2007],
       isNull(a.[2008],0) as [2008],
       isNull(a.[2009],0) as [2009],
       isNull(a.[2010],0) as [2010],
       isNull(a.Category, 'Non-purchaser') as Category
From #purchasers p
Left Join 
(        
   Select purchases_purchaser_id, [2005],[2006],[2007],[2008],[2009],[2010],
         Case When [2006]+[2007]+[2008]+[2009]+[2010] = 5 Then '5+ year consecutive'
              When [2008]+[2009]+[2010] = 3               Then '3-4 yr consecutive'
              When [2009]+[2010] = 2                      Then '2 yr Consecutive'
              When [2009]=1 and [2010]=0                  Then 'Lapsed'
              When [2008]=1 and [2009]=0 and [2010]=1     Then 'Reactivated'
              When [2006]+[2007]+[2008]+[2009]=0 and [2010]=1 Then 'New'
              When [2006]+[2007]+[2008]+[2009]+[2010] = 0 Then 'Non-purchaser in last 5 yrs'
              Else 'non categorized'
         End as Category     
   From (
            Select purchases_purchaser_id, 
                   Case When date_purchased < '2006/04/01' Then 2005 
                        Else Year(date_purchased)- Case When month(date_purchased)<4 Then -1 else 0 end 
                     end as fiscalYear, count(*) as nPurchases
            From #purchases 
            Group by purchases_purchaser_id,
                     Case When date_purchased < '2006/04/01' Then 2005 
                        Else Year(date_purchased)- Case When month(date_purchased)<4 Then -1 else 0 end 
                     end
         ) as AggData
   PIVOT ( count(nPurchases) for fiscalYear in ([2005],[2006],[2007],[2008],[2009],[2010]) ) pvt
) as a
on p.purchaser_id=a.purchases_purchaser_id