下面两个脚本的写法得到的结果是相同的。但是聚合的过程一个发生在外部一个发生在内部,执行过程是大不相同的,其运行的效率也自然不同。
脚本1:
select t1.meterial_id
,t2.play_cnt
,t2.play_device_cnt
from t_material as t1
inner join (
select track_id
,count(1) as play_cnt
,count(distinct device_id) as play_device_cnt
from t_play_daily_log
)as t2 on t1.track_id=t2.track_id
where t1.meterial_id='M000236'
脚本2:
select t1.meterial_id
,count(1) as play_cnt
,count(distinct device_id) as play_device_cnt
from t_material as t1
inner join (
select track_id,device_id
from t_play_daily_log
)as t2 on t1.track_id=t2.track_id
where t1.meterial_id='M000236'
group by t1.meterial_id
为什么?