同一客戶在特定天數或特定持續時間內重新購買同一產品的計數

我需要計算同一客戶在兩種情況下重新購買同一產品的次數：在特定的日子和特定的時間內。

這是示例資料：

create table tbl
(
  Customer  varchar(5),
  StartDay date,
  EndDay date,
  Product  varchar(5),
  Cost  decimal(10,2)
);

insert into tbl values 
('A',       '1/1/2019',      '1/4/2019',      'Shoe',        10.00),
('B',       '2/4/2021',      '2/7/2021',      'Hat',          10.00),
('A',       '1/7/2019',      '1/8/2019',      'Shoe',        10.00),
('B',       '5/8/2018',      '5/9/2018',      'Shoe',         10.00),
('A',       '2/1/2019',      '2/3/2019',      'Shoe',        10.00),
('C',       '6/6/2020',      '6/6/2020',      'Hat',         10.00),
('C',       '11/9/2021',     '12/9/2021',     'Cloth',       10.00),
('A',       '3/3/2019',      '3/17/2019',     'Cloth',        10.00),
('C',       '7/8/2020',      '7/12/2020',      'Hat',         10.00),
('E',       '7/2/2020',      '9/1/2020',      'Hat',        10.00),
('A',       '3/3/2019',      '3/7/2019',      'Shoe',        10.00),
('A',       '7/5/2022',      '7/9/2022',      'Hat',       10.00),
('C',       '6/6/2020',      '6/8/2020',      'Shoe',        10.00),
('B',       '8/2/2018',      '8/9/2018',      'Shoe',         10.00),
('A',       '1/1/2019',      '1/11/2019',     'Cloth',        10.00),
('E',       '9/3/2020',      '10/1/2020',      'Hat',        10.00),
('E',       '7/2/2020',      '7/8/2020',      'Shoe',       10.00);

持續時間是同一客戶購買同一產品的最后一個 EndDay 和下一個 StartDay 之間的差異。

例如：

對于客戶 A，他第一次購買“Shoe”時的 EndDay 是 '1/4/2019。而他第二次購買“鞋子”的起始日是“1/7/2019”。因此，持續時間在 30 天內。

對于客戶 B，他第一次購買“Shoe”時的 EndDay 是 '5/8/2018。而他第二次購買“鞋子”的開始日是“201 年 8 月 2 日”。所以持續時間在 60 - 90 天內。

第一種情況的預期結果：

第二種情況的預期結果：

非常感謝您提前提供的幫助！！

uj5u.com熱心網友回復：

場景一：

select  Customer    
       ,StartDay    
       ,EndDay  
       ,Product 
       ,Cost
       ,[0]                  as '0-30 days'
       ,[0]   [1]            as '0-60 days'
       ,[0]   [1]   [2]      as '0-90 days'
       ,[0]   [1]   [2]  [3] as 'total'
from
(
select  t.Customer
       ,t.StartDay
       ,t.EndDay
       ,t.Product
       ,t.Cost
       ,case when (datediff(day, t.EndDay, t2.StartDay)-1)/30 > 3 then 3 else (datediff(day, t.EndDay, t2.StartDay)-1)/30 end as dd
from    t left join t t2 on  t2.Customer = t.Customer 
                         and t2.Product = t.Product
                         and t2.StartDay > t.EndDay
) t
pivot(count(dd) for dd in([0],[1],[2],[3])) p
order by Customer, Product, StartDay

小提琴

uj5u.com熱心網友回復：

select  Customer    
       ,StartDay    
       ,EndDay  
       ,Product 
       ,Cost
       ,[0] as '0-30 days'
       ,[1] as '30-60 days'
       ,[2] as '60-90 days'
       ,[3] as '90  days'
from
(
select  t.Customer
       ,t.StartDay
       ,t.EndDay
       ,t.Product
       ,t.Cost
       ,case when (datediff(day, t.EndDay, t2.StartDay)-1)/30 > 3 then 3 else (datediff(day, t.EndDay, t2.StartDay)-1)/30 end as dd
from    t left join t t2 on  t2.Customer = t.Customer 
                         and t2.Product = t.Product
                         and t2.StartDay > t.EndDay
) t
pivot(count(dd) for dd in([0],[1],[2],[3])) p
order by Customer, Product, StartDay

小提琴

標籤：sqlsql服务器

上一篇：獲取特定范圍內的總和數量

下一篇：添加多個索引會減慢頻繁向表末尾插入行的速度嗎？