with event as (
select
id,
type,
time,
time_part,
lat,
lon,
lat_part,
lon_part,
location
from(
select
id,
type,
time,
floor(unix_timestamp(time)/10) as time_part,
lat,
lon,
floor(lat/0.001) as lat_part,
floor(lon/0.001) as lon_part,
location,
row_number() over(partition by id,time,location order by time) as rn
from test where type in ('man','woman')
) total
where rn=1
),
locations as (
select
type,
location,
lat_part,
lon_part,
lat,
lon
from (
select
type,
location,
lat_part,
lon_part,
lat,
lon,
row_number() over(partition by type,location order by lat) as rn
from event
)location_original
where rn=1
),
source_locations as(
select
location,
ad.lat_part as lat_part,
ad.lon_part as lon_part,
lat,
lon
from (
select
location,
lat_part,
lon_part,
lat,
lon
from locations where type='man'
) source_location_original
lateral view explode(split(concat(lat_part-1,',',lat_part,',',lat_part+1),',')) ad
as lat_part
lateral view explode(split(concat(lon_part-1,',',lon_part,',',lon_part+1),',')) ad
as lon_part
),
target_locations as (
select
location,
cast(lat_part as string) as lat_part,
cast(lon_part as string) as lon_part,
lat,
lon
from locations
where type='women' and lat_part is not null and lat_part!=0
),
relation as (
select
source_location,
target_location
from (
select
round(6378138*2*asin(sqrt(pow(sin((source_locations.lat*pi()/180-target_locations.lat*pi()/180)/2),2)+cos(source_locations.lat*pi()/180)*cos(target_locations.lat*pi()/180)* pow(sin((source_locations.lon*pi()/180-target_locations.lon*pi()/180)/2),2)))) as diff,
source_locations.location source_location,
target_locations.location target_location
from source_locations join target_locations on source_locations.lat_part=target_locations.lat_part and source_locations.lon_part=target_locations.lon_part
)site_diff
where diff<='100'
),
source as (
select
id,
time,
ad.time_part as time_part,
location
from (
select
id,
time,
time_part,
location
from event where data_type='man'
) source_original
lateral view explode(split(concat(time_part-1,',',time_part,',',time_part+1),',')) ad
as time_part
),
target as (
select
id,
time,
cast(time_part as string) as time_part,
location
from event_pre where data_type='women'
)
select
source_id,
target_id,
from (select
abs(unix_timestamp(source.time)-unix_timestamp(target.time)) as time_diff,
source.id as source_id,
target.id as target_id
from source join target join relation
on source.time_part=target.time_part and source.location=relation.source_location and target.location=relation.target_location
) diff
where time_diff<='10'
评论