改写SQL了。最后写的非常复杂,但是成功实现了功能。
delete tvcbook from tvcbook join (select r1.track_id as track_id,r1.date as date,r1.hour as hour,r1.lib as lib,r1.remark as remark ,r1.distinct_id as distinct_id,r1.video_id as video_id,r1.played_seconds as played_seconds ,r1.created_at as created_at,if(r2.maxsta is not null,1,0) as need from
(select track_id,date,hour,lib,remark,distinct_id,video_id,played_seconds,created_at,max(statu) as sta from
(select d1.*,case when d2.max_created_at=d1.created_at and d2.max_played_seconds=d1.played_seconds then 4 when d2.max_played_seconds=d1.played_seconds and d2.max_created_at-d1.created_at<60 then 3 when d2.max_played_seconds=d1.played_seconds then 2 when d2.max_created_at=d1.created_at then 1 else 0 end as statu from
(select track_id,created_at,date,hour,lib,remark,distinct_id,JSON_EXTRACT(all_json, ‘$.“properties”.“video_id”’) as video_id,JSON_EXTRACT(all_json, ‘$.“properties”.“video_played_seconds”’)*1000000 as played_seconds from tvcbook where date=‘2020-03-05’ and hour=16 and JSON_EXTRACT(all_json, ‘$.“properties”.“action”’)=214)d1
left join
(select date,hour,lib,remark,distinct_id,JSON_EXTRACT(all_json, ‘$.“properties”.“video_id”’) as video_id,max(JSON_EXTRACT(all_json, ‘$.“properties”.“video_played_seconds”’)1000000) as max_played_seconds,max(created_at) as max_created_at from tvcbook where date=‘2020-03-05’ and hour=16 and JSON_EXTRACT(all_json, ‘$.“properties”.“action”’)=214 GROUP BY date,hour,lib,remark,distinct_id,video_id)d2
on d1.date=d2.date and d1.hour=d2.hour and d1.lib=d2.lib and d1.remark=d2.remark and d1.distinct_id=d2.distinct_id
)tt
GROUP BY track_id,date,hour,lib,remark,distinct_id,video_id,played_seconds,created_at)r1
left join
(select date,hour,lib,remark,distinct_id,video_id,max(sta) as maxsta from
(select date,hour,lib,remark,distinct_id,video_id,played_seconds,created_at,max(statu) as sta from
(select d1.,case when d2.max_created_at=d1.created_at and d2.max_played_seconds=d1.played_seconds then 4 when d2.max_played_seconds=d1.played_seconds and d2.max_created_at-d1.created_at<60 then 3 when d2.max_played_seconds=d1.played_seconds then 2 when d2.max_created_at=d1.created_at then 1 else 0 end as statu from
(select track_id,created_at,date,hour,lib,remark,distinct_id,JSON_EXTRACT(all_json, ‘$.“properties”.“video_id”’) as video_id,JSON_EXTRACT(all_json, ‘$.“properties”.“video_played_seconds”’)*1000000 as played_seconds from tvcbook where date=‘2020-03-05’ and hour=16 and JSON_EXTRACT(all_json, ‘$.“properties”.“action”’)=214)d1
left join
(select date,hour,lib,remark,distinct_id,JSON_EXTRACT(all_json, ‘$.“properties”.“video_id”’) as video_id,max(JSON_EXTRACT(all_json, ‘$.“properties”.“video_played_seconds”’)*1000000) as max_played_seconds,max(created_at) as max_created_at from tvcbook where date=‘2020-03-05’ and hour=16 and JSON_EXTRACT(all_json, ‘$.“properties”.“action”’)=214 GROUP BY date,hour,lib,remark,distinct_id,video_id)d2
on d1.date=d2.date and d1.hour=d2.hour and d1.lib=d2.lib and d1.remark=d2.remark and d1.distinct_id=d2.distinct_id
)tt
GROUP BY date,hour,lib,remark,distinct_id,video_id,played_seconds,created_at)r1
GROUP BY date,hour,lib,remark,distinct_id,video_id
order by date,hour,lib,remark,distinct_id,video_id)r2
on r1.date=r2.date and r1.hour=r2.hour and r1.lib=r2.lib and r1.remark=r2.remark and r1.distinct_id=r2.distinct_id and r1.video_id=r2.video_id and r1.sta=r2.maxsta
having need = 0 ) list
where tvcbook.track_id=list.track_id and tvcbook.date=list.date and tvcbook.hour=list.hour and tvcbook.lib=list.lib and tvcbook.remark=list.remark and tvcbook.distinct_id=list.distinct_id