| |
| CREATE TABLE package_order ( |
| order_num string, |
| vin_id string, |
| package_start_dt string); |
| |
| CREATE TABLE package_order_gsp (order_num string, |
| cust_acct_sk decimal(38,0), |
| to_vin string, |
| cancellation_dt string, |
| confirmation_num string); |
| |
| insert into package_order values ('1', 'DEADBEAF', '2022-01-22'); |
| insert into package_order values ('1', 'DEADBEAF', '2022-01-22'); |
| insert into package_order values ('1', 'DEADBEAF1', '2022-01-23'); |
| insert into package_order values ('1', 'DEADBEAF1', '2022-01-23'); |
| insert into package_order_gsp values ('1', 1.1, '1', null, '1'); |
| |
| set hive.cbo.enable = false; |
| set hive.explain.user=false; |
| |
| explain select * |
| from ( |
| select t1.vin_id, row_number()over(partition by t1.vin_id order by package_start_dt desc) rn |
| from package_order_gsp su |
| inner join package_order t1 |
| on su.confirmation_num=t1.order_num |
| where su.cancellation_dt is null |
| ) tt |
| where tt.vin_id='DEADBEAF'; |
| |
| select * |
| from ( |
| select t1.vin_id, row_number()over(partition by t1.vin_id order by package_start_dt desc) rn |
| from package_order_gsp su |
| inner join package_order t1 |
| on su.confirmation_num=t1.order_num |
| where su.cancellation_dt is null |
| ) tt |
| where tt.vin_id='DEADBEAF'; |
| |
| set hive.cbo.enable = true; |
| |
| explain select * |
| from ( |
| select t1.vin_id, row_number()over(partition by t1.vin_id order by package_start_dt desc) rn |
| from package_order_gsp su |
| inner join package_order t1 |
| on su.confirmation_num=t1.order_num |
| where su.cancellation_dt is null |
| ) tt |
| where tt.vin_id='DEADBEAF'; |
| |
| select * |
| from ( |
| select t1.vin_id, row_number()over(partition by t1.vin_id order by package_start_dt desc) rn |
| from package_order_gsp su |
| inner join package_order t1 |
| on su.confirmation_num=t1.order_num |
| where su.cancellation_dt is null |
| ) tt |
| where tt.vin_id='DEADBEAF'; |
| |
| select * |
| from ( |
| select t1.vin_id, row_number()over(partition by t1.vin_id order by package_start_dt desc) rn |
| from package_order_gsp su |
| inner join package_order t1 |
| on su.confirmation_num=t1.order_num |
| where su.cancellation_dt is null |
| ) tt |
| where tt.vin_id != 'DEADBEAF'; |
| |