Блог

У меня есть 2 таблицы со следующими данными:

Заказы

Контракты

Я хочу назначить каждому заказу процент комиссии на основе кода поставщика, даты и типа. Вот желаемый результат:

Желаемый Результат:

Первый случай-это когда тип заказа совпадает с типом контракта, а дата заказа находится в пределах contract_start_date и contract_end_date. Это относится к заказам 1 и 3. Однако, в случае, если это условие неверно, я хочу получить последний контракт с max contract_start_date до даты заказа, и я хочу выбрать тип контракта OD, если он существует независимо от типа заказа, а если нет, то выберите последний контракт VD (до даты заказа). Единственный способ, который приходит на ум, — это использовать перекрестное соединение, после которого мне потребуется несколько операторов case для достижения этого результата. Однако это было бы очень медленно для моих данных, так как у меня миллионы записей в заказах, а у каждого поставщика множество контрактов.

Вот мой текущий код, который обслуживает заказы 1 и 3, но я не могу понять заказы 2 и 4.

 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')

select o.*, c.contract_type, c.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
 

Возможно, вам захочется использовать поле «отдельно subqueries с cross join «, так как left outer join поле «без равного ключа» в настоящее время не поддерживается в BigQuery.

 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
;