左连接在bigquery中使用两列和多个条件

Question

我有两个表，其中包含以下数据:

订单

<表类>代码order_typeorder_datevendor_codetbody><<tr>1OD2021-02-0112OD2021-05-0313VD2021-02-0414VD2021-07-011

您可能希望使用单独的subqueries和cross join因为left outer join没有相等的关键字段

with
contracts as (
select
date('2021-01-01') as contract_start_date,
date('2021-02-28') as contract_end_date,
'OD' as contract_type,
20 as commission_percentage
union all select '2021-01-01', '2021-02-28', 'VD', 25
union all select '2021-03-01', '2021-04-30', 'OD', 30
union all select '2021-06-01', current_date(), 'OD', 35
),
orders as (
select 1 as code, 'OD' as order_type, date('2021-02-01') as order_date
union all select 2, 'OD', '2021-05-03'
union all select 3, 'VD', '2021-02-04'
union all select 4, 'VD', '2021-07-01'
),
latest_contracts_OD AS (
select *
from contracts
WHERE contract_type = 'OD'
),
latest_contracts_VD AS (
select *
from contracts
WHERE contract_type = 'VD'
)
select
o.*,
IFNULL(
IFNULL(c.contract_type, lc_od.contract_type),
lc_vd.contract_type
) as contract_type,
IFNULL(
IFNULL(c.commission_percentage, lc_od.commission_percentage),
lc_vd.commission_percentage
) as commission_percentage,
from orders o
left join contracts c
on o.order_type = c.contract_type
and o.order_date between c.contract_start_date and c.contract_end_date
cross join latest_contracts_OD lc_od
left outer join latest_contracts_VD lc_vd
on o.order_type = lc_vd.contract_type
and o.order_date >= lc_vd.contract_start_date
WHERE o.order_date >= lc_od.contract_start_date
QUALIFY ROW_NUMBER() OVER (PARTITION BY code, order_type ORDER BY lc_od.contract_start_date DESC) = 1
order by code
;