我正在努力解决以下问题-如果我画得太多，很抱歉：
航班预订平台正在价格计算引擎上询问价格，并传递所有必要信息，以得出各种选项的价格。从技术上讲，平台要求各种路线组合(直接-间接航班、1、2、3…停靠站等(，因此我们将有许多不同的请求ID和路线。对于相同的条件，每个客户可以有多个请求，直到预订为止。
预订平台在询问路线价格时，会尝试向客户提供更高的舱位(只有在可用的情况下(，因此它会在0-5秒内再次进行价格呼叫，但会选择不同的舱位。
我知道哪些值来自更高的类，例如U，其他值只是正常类：例如I
我正在寻找一个sql查询，以了解是否提供了更高级别的课程(在接下来的5秒内(，以及客户是否预订了更高的课程-基本上增强了预订表并添加了"；提供追加销售"实现了追加销售"；。
"提供追加销售"->如果有对同一客户的请求，始发地、目的地和日期+-(5s(，但类别不同于"；是"；(可获得更高级别(如果没有"；否"；。
"追加销售实现追加销售->如果客户要求更低的舱位，但随后预订了追加销售优惠
可能存在没有更高级别可用的情况，因此在这种情况下，只有一个级别用于Customer、Origin等的组合。。
我要找的表应该是这样的：
request_id route customer origin destination req_date class price booked_request_id selected_route upselling_offered upselling_realized
124 2 c a b 2000-01-01 00:00:02.000 I 22 124 2 yes no
128 1 c a b 2000-01-05 00:00:03.000 U 24 128 1 yes yes
129 2 c a b 2000-01-05 00:00:08.000 I 23 129 2 no no
带预订表的SQL for Values:
with rr as (
select 123 as request_id, 2 as route, 'c' as customer, 'a' as origin, 'b' AS destination, convert(datetime, '2000-01-01 00:00:00') as req_date,'I' as class ,17 as price ,'normal request' as explanation union all
select 123 as request_id, 3 as route, 'c' as customer, 'a' as origin, 'b' AS destination, convert(datetime, '2000-01-01 00:00:00') as req_date,'I' as class ,20 as price ,'normal request' as explanation union all
select 124 as request_id, 1 as route, 'c' as customer, 'a' as origin, 'b' AS destination, convert(datetime, '2000-01-01 00:00:02') as req_date,'I' as class ,19 as price ,'normal request' as explanation union all
select 124 as request_id, 2 as route, 'c' as customer, 'a' as origin, 'b' AS destination, convert(datetime, '2000-01-01 00:00:02') as req_date,'I' as class ,22 as price ,'normal request' as explanation union all
select 124 as request_id, 3 as route, 'c' as customer, 'a' as origin, 'b' AS destination, convert(datetime, '2000-01-01 00:00:02') as req_date,'I' as class ,25 as price ,'normal request' as explanation union all
select 125 as request_id, 1 as route, 'c' as customer, 'a' as origin, 'b' AS destination, convert(datetime, '2000-01-01 00:00:06') as req_date,'U' as class ,26 as price ,'uselling offer' as explanation union all
select 125 as request_id, 2 as route, 'c' as customer, 'a' as origin, 'b' AS destination, convert(datetime, '2000-01-01 00:00:06') as req_date,'U' as class ,27 as price ,'uselling offer' as explanation union all
select 126 as request_id, 1 as route, 'c' as customer, 'a' as origin, 'b' AS destination, convert(datetime, '2000-01-03 00:00:03') as req_date,'I' as class ,24 as price ,'normal request' as explanation union all
select 126 as request_id, 2 as route, 'c' as customer, 'a' as origin, 'b' AS destination, convert(datetime, '2000-01-03 00:00:03') as req_date,'I' as class ,28 as price ,'normal request' as explanation union all
select 126 as request_id, 3 as route, 'c' as customer, 'a' as origin, 'b' AS destination, convert(datetime, '2000-01-03 00:00:03') as req_date,'I' as class ,23 as price ,'normal request' as explanation union all
select 127 as request_id, 1 as route, 'c' as customer, 'a' as origin, 'b' AS destination, convert(datetime, '2000-01-05 00:00:03') as req_date,'I' as class ,22 as price ,'normal request' as explanation union all
select 127 as request_id, 2 as route, 'c' as customer, 'a' as origin, 'b' AS destination, convert(datetime, '2000-01-05 00:00:03') as req_date,'I' as class ,26 as price ,'normal request' as explanation union all
select 128 as request_id, 3 as route, 'c' as customer, 'a' as origin, 'b' AS destination, convert(datetime, '2000-01-05 00:00:03') as req_date,'U' as class ,29 as price ,'uselling offer' as explanation union all
select 128 as request_id, 1 as route, 'c' as customer, 'a' as origin, 'b' AS destination, convert(datetime, '2000-01-05 00:00:03') as req_date,'U' as class ,24 as price ,'uselling offer' as explanation union all
select 129 as request_id, 2 as route, 'c' as customer, 'a' as origin, 'b' AS destination, convert(datetime, '2000-02-08 00:00:08') as req_date,'I' as class ,23 as price ,'normal request' as explanation union all
select 129 as request_id, 3 as route, 'c' as customer, 'a' as origin, 'b' AS destination, convert(datetime, '2000-02-08 00:00:08') as req_date,'I' as class ,26 as price ,'normal request' as explanation
),
bookings as (
select 124 as booked_request_id, 2 as selected_route union all
select 128 as booked_request_id, 1 as selected_route union all
select 129 as booked_request_id, 2 as selected_route
)
--select req_date, class, lead(req_date) over (partition by customer,origin, destination, class order by req_date )
--from rr left join bookings
-- on rr.request_id = bookings.booked_request_id
-- and rr.route = bookings.selected_route
--order by class,req_date
select req_date, request_id, route, class,
case when class = 'I' then
case when
lead(req_date) over (partition by customer,origin, destination, class, req_date order by req_date ) <= dateadd(second, 5, req_date)
and lead(class) over (partition by customer,origin, destination, class, req_date order by req_date ) <> class
then 'Yes' else 'No' end
when class = 'U' then
case when
lag(req_date) over (partition by customer,origin, destination, class, req_date order by req_date ) >= dateadd(second, -5, req_date)
and lag(class) over (partition by customer,origin, destination, class, req_date order by req_date ) <> class
then 'Yes' else 'No' end
end as upselling_offered
from rr left join bookings
on rr.request_id = bookings.booked_request_id
and rr.route = bookings.selected_route
order by req_date
不幸的是，我的查询没有给出想要的结果——你知道我遗漏了什么吗？
为什么不选择相同的客户、相同的路线、
WHERE customer = {customer}
AND price > {previous booked price}
AND class > {previous booked class}
AND req_date > {previous_req_date}
AND req_date <= {previous_req_date + 5 secs}
AND upselling_offered = yes
AND upselling_realized = yes
括号中的项是您将编码到查询请求中的值。此外，我还推荐了一个关于客户的索引，req_date、upselling_offed和upselling_everified。
如果这不能回答你的问题，我可能无法完全理解你想要实现的目标。
同时我已经解决了它-谢谢。我的问题是，在窗口函数中，我按类分组，这给了我错误的窗口来查找超前/滞后值。。。
以下是解决方案：
with rr as (
select 123 as request_id, 2 as route, 'c' as customer, 'a' as origin, 'b' AS destination, convert(datetime, '2000-01-01 00:00:00') as req_date,'I' as class ,17 as price ,'normal request' as explanation union all
select 123 as request_id, 3 as route, 'c' as customer, 'a' as origin, 'b' AS destination, convert(datetime, '2000-01-01 00:00:00') as req_date,'I' as class ,20 as price ,'normal request' as explanation union all
select 124 as request_id, 1 as route, 'c' as customer, 'a' as origin, 'b' AS destination, convert(datetime, '2000-01-01 00:00:02') as req_date,'I' as class ,19 as price ,'normal request' as explanation union all
select 124 as request_id, 2 as route, 'c' as customer, 'a' as origin, 'b' AS destination, convert(datetime, '2000-01-01 00:00:02') as req_date,'I' as class ,22 as price ,'normal request' as explanation union all
select 124 as request_id, 3 as route, 'c' as customer, 'a' as origin, 'b' AS destination, convert(datetime, '2000-01-01 00:00:02') as req_date,'I' as class ,25 as price ,'normal request' as explanation union all
select 125 as request_id, 1 as route, 'c' as customer, 'a' as origin, 'b' AS destination, convert(datetime, '2000-01-01 00:00:06') as req_date,'U' as class ,26 as price ,'uselling offer' as explanation union all
select 125 as request_id, 2 as route, 'c' as customer, 'a' as origin, 'b' AS destination, convert(datetime, '2000-01-01 00:00:06') as req_date,'U' as class ,27 as price ,'uselling offer' as explanation union all
select 126 as request_id, 1 as route, 'c' as customer, 'a' as origin, 'b' AS destination, convert(datetime, '2000-01-03 00:00:03') as req_date,'I' as class ,24 as price ,'normal request' as explanation union all
select 126 as request_id, 2 as route, 'c' as customer, 'a' as origin, 'b' AS destination, convert(datetime, '2000-01-03 00:00:03') as req_date,'I' as class ,28 as price ,'normal request' as explanation union all
select 126 as request_id, 3 as route, 'c' as customer, 'a' as origin, 'b' AS destination, convert(datetime, '2000-01-03 00:00:03') as req_date,'I' as class ,23 as price ,'normal request' as explanation union all
select 127 as request_id, 1 as route, 'c' as customer, 'a' as origin, 'b' AS destination, convert(datetime, '2000-01-05 00:00:03') as req_date,'I' as class ,22 as price ,'normal request' as explanation union all
select 127 as request_id, 2 as route, 'c' as customer, 'a' as origin, 'b' AS destination, convert(datetime, '2000-01-05 00:00:03') as req_date,'I' as class ,26 as price ,'normal request' as explanation union all
select 128 as request_id, 3 as route, 'c' as customer, 'a' as origin, 'b' AS destination, convert(datetime, '2000-01-05 00:00:03') as req_date,'U' as class ,29 as price ,'uselling offer' as explanation union all
select 128 as request_id, 1 as route, 'c' as customer, 'a' as origin, 'b' AS destination, convert(datetime, '2000-01-05 00:00:03') as req_date,'U' as class ,24 as price ,'uselling offer' as explanation union all
select 129 as request_id, 2 as route, 'c' as customer, 'a' as origin, 'b' AS destination, convert(datetime, '2000-02-08 00:00:08') as req_date,'I' as class ,23 as price ,'normal request' as explanation union all
select 129 as request_id, 3 as route, 'c' as customer, 'a' as origin, 'b' AS destination, convert(datetime, '2000-02-08 00:00:08') as req_date,'I' as class ,26 as price ,'normal request' as explanation
),
bookings as (
select 124 as booked_request_id, 2 as selected_route union all
select 128 as booked_request_id, 1 as selected_route union all
select 129 as booked_request_id, 2 as selected_route
),
upsell_table as (
select request_id, max(upselling_offered) as upselling, class from
( select req_date, request_id, route, class,
--lead(req_date) over (partition by customer,origin, destination order by req_date asc) AS lead_date,
--lead(class) over (partition by customer,origin, destination order by req_date asc) AS lead_class,
case when class = 'I' then
case when
lead(req_date) over (partition by customer,origin, destination order by req_date asc) <= dateadd(second, 5, req_date)
and lead(class) over (partition by customer,origin, destination order by req_date asc) <> class
then 1 else 0 end
when class = 'U' then
case when
lag(req_date) over (partition by customer, origin, destination order by req_date ) >= dateadd(second, -5, req_date)
and lag(class) over (partition by customer, origin, destination order by req_date ) <> class
then 1 else 0 end
end as upselling_offered
from rr left join bookings
on rr.request_id = bookings.booked_request_id
and rr.route = bookings.selected_route
) T1
group by request_id, class
)
select booked_request_id, selected_route,
case when upselling = 1 then 'yes' else 'no' end as 'upselling_offered'
,case when upselling = 1 and T1.class = 'U' then 'yes' else 'no' end as 'upselling_successfull'
from upsell_table T1 join bookings
on request_id = bookings.booked_request_id