tsql - 每月过去 12 个月的数据以及过去 12 个月的数据

Question

这是 TSQL，我正在尝试计算过去 12 个月的重复购买率。这是通过查看过去 12 个月购买超过 1 次的客户总和以及过去 12 个月的客户总数来实现的。下面的 SQL 代码将为我提供这样的信息；但我想在过去 12 个月内动态地执行此操作。这是我被卡住的部分，不应该如何最好地实现这一点。每个月都应包括 12 个月前的数据。即 6 月应保存 2018 年 6 月至 2018 年 6 月之间的数据，5 月应保存 2018 年 5 月至 2019 年 5 月的数据。

[Order Date] 是一个普通的日期字段 (yyyy-mm-dd hh:mm:ss)

DECLARE @startdate1 DATETIME
DECLARE @enddate1 DATETIME

SET @enddate1 = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) -- Starting June 2018
SET @startdate1 = DATEADD(mm,DATEDIFF(mm,0,GETDATE())-13,0) -- Ending June 2019
;

with dataset as (
select [Phone No_] as who_identifier,
count(distinct([Order No_])) as mycount
from [MyCompany$Sales Invoice Header]
where [Order Date] between @startdate1 and @enddate1
group by [Phone No_]
),

frequentbuyers as (
select who_identifier, sum(mycount) as frequentbuyerscount
from dataset
where mycount > 1
group by who_identifier),

allpurchases as (
select who_identifier, sum(mycount) as allpurchasescount
from dataset
group by who_identifier
)

select sum(frequentbuyerscount) as frequentbuyercount, (select sum(allpurchasescount) from allpurchases) as allpurchasecount
from frequentbuyers

我希望最终结果看起来像这样： ...Dec、Jan、Feb、March、April、May、June 每个月都持有frequentbuyercount和allpurchasescount的值。

标签： tsql

Answer 1

这是代码。我对frequentbuyerscount 和allpurchasescount 做了一点修改。如果您使用类似 sumif 的表达式，则不需要第二个 cte。

if object_id('tempdb.dbo.#tmpMonths') is not null drop table #tmpMonths
create table #tmpMonths ( MonthID datetime, StartDate datetime, EndDate datetime)

declare @MonthCount int = 12
declare @Month datetime = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

while @MonthCount > 0 begin
    insert into #tmpMonths( MonthID, StartDate, EndDate )
    select @Month, dateadd(month, -12, @Month), @Month
    set @Month = dateadd(month, -1, @Month)
    set @MonthCount = @MonthCount - 1
end


;with dataset as (
    select m.MonthID as MonthID, [Phone No_] as who_identifier,
    count(distinct([Order No_])) as mycount
    from [MyCompany$Sales Invoice Header]
    inner join #tmpMonths m on [Order Date] between m.StartDate and m.EndDate
    group by m.MonthID, [Phone No_]
    ),
buyers as (
    select MonthID, who_identifier
        , sum(iif(mycount > 1, mycount, 0)) as frequentbuyerscount --sum only if count > 1
        , sum(mycount) as allpurchasescount
    from dataset
    group by MonthID, who_identifier
    )
select 
    b.MonthID
    , max(tm.StartDate) StartDate, max(tm.EndDate) EndDate
    , sum(b.frequentbuyerscount) as frequentbuyercount
    , sum(b.allpurchasescount) as allpurchasecount
from buyers b inner join #tmpMonths tm on tm.MonthID = b.MonthID
group by b.MonthID

请注意，代码仅在语法方面进行了测试。

测试数据后，结果如下：

 MonthID   | StartDate  | EndDate    | frequentbuyercount | allpurchasecount
-----------------------------------------------------------------------------
2018-08-01 | 2017-08-01 | 2018-08-01 | 340                | 3702
2018-09-01 | 2017-09-01 | 2018-09-01 | 340                | 3702
2018-10-01 | 2017-10-01 | 2018-10-01 | 340                | 3702
2018-11-01 | 2017-11-01 | 2018-11-01 | 340                | 3702
2018-12-01 | 2017-12-01 | 2018-12-01 | 340                | 3703
2019-01-01 | 2018-01-01 | 2019-01-01 | 340                | 3703
2019-02-01 | 2018-02-01 | 2019-02-01 | 2                  | 8
2019-03-01 | 2018-03-01 | 2019-03-01 | 2                  | 3
2019-04-01 | 2018-04-01 | 2019-04-01 | 2                  | 3
2019-05-01 | 2018-05-01 | 2019-05-01 | 2                  | 3
2019-06-01 | 2018-06-01 | 2019-06-01 | 2                  | 3
2019-07-01 | 2018-07-01 | 2019-07-01 | 2                  | 3