ODPS MAPJOIN与数据过滤

1.需求：

表1有device,time,data 三列（约100W行）

表2有device,time_begin,time_end 三列（约100行）

我们需要实现的逻辑是：

当表1的device=表2的device 且表1的time在表2的time_begin和time_end之间，从表1中剔除此数据。

 

尝试方案1：表1 left join 表2：

不行：原因是表2中对一个device存在多行数据，所以left join会导致数据丢失

 

尝试方案2：表1 join 表2，对笛卡尔积的结果使用where进行过滤：

不行：原因同样由于表2对一个device存在多行数据，

表1：dev1 11:30:01

表2：dev1 10:00:00 -12:00:00 

         dev1 14:00:00-16:00:00

这种情况下，会出现两行数据：分别是

dev1 11:30:01 10:00:00 -12:00:00 

dev1 11:30:01 14:00:00-16:00:00

使用where过滤时，第二行数据不会被剔除。

 

方案3： 表1 - （表1∩表2）

select time_min,data,dev from

table1 table_a where ds = '${bizdate}' and dev is not null and time_min is not null

and not exists (select 1 from

(

select

/* +mapjoin(t2) */

t2.gmt_begin,t2.gmt_end,

t1.time_min,t1.data,t1.dev from

(

select time_min,data,dev from table1

where ds = '${bizdate}' and dev is not null and time_min is not null

)t1

join

(SELECT dev,gmt_begin,gmt_end FROM table2

where (gmt_begin is not null and gmt_end is not null)

)t2

on t1.dev = t2.dev AND (time_min between gmt_begin and gmt_end)

)table_ab

where table_a.time_min = table_ab.time_min and table_a.dev = table_ab.dev

--表1∩表2