时间戳

首页 > 解决方案 > SQL 查找客户 X 预订

sql - SQL 查找客户 X 预订

问题描述

有一个名为 cust (as c) 的表，它是从最旧到最新的所有预订列表，包含以下列：

clientid, bookref, tourno, book_date

我正在尝试使用此预制 SQL 查找第一次、第二次、第三次、第四次和第五次预订的客户：

    select c.clientid as ClientID, c.bookref as BookRef, left(c.tourno,6) as TourRef, t.place as Product, c.book_date as BookDate, c.paxnumb as Pax, c.tourcost as Revenue,
iif(c.book_date = fb.fbdate,'Y','N') as FirstBook, s.name as Source

from (select clientid, bookref, tourno, book_date, paxnumb, tourcost, sourceid from cust) as c
inner join (select tourno, place from tourlist) as t on c.tourno = t.tourno
inner join (select id, name from source) as s on c.sourceid = s.id
inner join (select clientid, min(created_date) as fbdate from custhist group by clientid) as fb on c.clientid = fb.clientid

where c.book_date between '2017-01-01' and '2019-09-30'

group by ClientID, BookRef, TourRef, Product, BookDate, Pax, Revenue, FirstBook, Source
Having FirstBook = 'Y'
order by BookRef asc

如果 bookref 是该 clientid 的第一个预订，这将返回一个带有“Y”或“N”标志的表。所以这是我的起点，我最终得到了一份 42,000 名客户的名单，这些客户在 2017 年 1 月 1 日至 2019 年 9 月 30 日期间进行了首次预订。

在这 42,000 名客户中，我如何找出他们的第 2、第 3、第 4 和第 5 次预订（如果存在）？

谢谢！

标签： sql

解决方案

您需要 row_number() 按组中的 orderdate 进行分区，根据 clientid，每组最多 5 条记录的 desc 顺序 

 select c.clientid as ClientID, c.bookref as BookRef, left(c.tourno,6) as TourRef, t.place as Product, c.book_date as BookDate, c.paxnumb as Pax, c.tourcost as Revenue,
ROW_NUMBER() over (partition by c.book_date order by c.book_date desc) rn, 
iif(c.book_date = fb.fbdate,'Y','N') as FirstBook, s.name as Source

from (select clientid, bookref, tourno, book_date, paxnumb, tourcost, sourceid from cust) as c
inner join (select tourno, place from tourlist) as t on c.tourno = t.tourno
inner join (select id, name from source) as s on c.sourceid = s.id
inner join (select clientid, min(created_date) as fbdate from custhist group by clientid) as fb on c.clientid = fb.clientid

where c.book_date between '2017-01-01' and '2019-09-30'

group by ClientID, BookRef, TourRef, Product, BookDate, Pax, Revenue, FirstBook, Source
Having rn<=5
order by BookRef asc

推荐阅读