Open aakulaga-ydb opened 2 weeks ago
select promotions,total,cast(promotions as float)/cast(total as float)*100
from
(select sum(ss_ext_sales_price) promotions
from bindings.store_sales as store_sales
cross join bindings.store as store
cross join bindings.promotion as promotion
cross join bindings.date_dim as date_dim
cross join bindings.customer as customer
cross join bindings.customer_address as customer_address
cross join bindings.item as item
where ss_sold_date_sk = d_date_sk
and ss_store_sk = s_store_sk
and ss_promo_sk = p_promo_sk
and ss_customer_sk= c_customer_sk
and ca_address_sk = c_current_addr_sk
and ss_item_sk = i_item_sk
and ca_gmt_offset = -6
and i_category = 'Sports'
and (p_channel_dmail = 'Y' or p_channel_email = 'Y' or p_channel_tv = 'Y')
and s_gmt_offset = -6
and d_year = 2001
and d_moy = 12) promotional_sales cross join
(select sum(ss_ext_sales_price) total
from bindings.store_sales as store_sales
cross join bindings.store as store
cross join bindings.date_dim as date_dim
cross join bindings.customer as customer
cross join bindings.customer_address as customer_address
cross join bindings.item as item
where ss_sold_date_sk = d_date_sk
and ss_store_sk = s_store_sk
and ss_customer_sk= c_customer_sk
and ca_address_sk = c_current_addr_sk
and ss_item_sk = i_item_sk
and ca_gmt_offset = -6
and i_category = 'Sports'
and s_gmt_offset = -6
and d_year = 2001
and d_moy = 12) all_sales
order by promotions, total
limit 100;
Внутри двух подзапросов все cross join переписываются в inner. После чего два подзапроса джойнятся еще раз: promotional_sales cross join all_sales
. У этого join нет никаких предикатов равенства колонок, соответственно, его невозможно переписать в inner join.
Есть CrossJoin в финальном плане. Запускал с прагмами pragma FilterPushdownOverJoinOptionalSide; pragma RotateJoinTree="false";