求用户行为的漏斗计算,首页,搜索页,商品最终页,以下是查一天的速度,还有没有更好的优化方法呢,这个太慢了
MySQL [(none)]> SELECT count( distinct a1.user_id),
-> count( distinct a3.user_id),
-> count( distinct a2.user_id)
-> FROM
-> (SELECT user_id,
-> visit_time hpvisit_time,
-> page_page_id
-> FROM olap.mds_log_weblog_pageview
-> WHERE dt>=date_sub(current_date(),1)
-> AND site IN (‘www’,
-> ‘wap’,
-> ‘app’)
-> AND event_type<>‘native_page_end’
-> AND is_bot IS NULL
-> AND country_id<>‘CN’
-> AND page_page_id=‘hp’
-> and user_id is not null and user_id <>’’ ) a1
-> LEFT JOIN
-> (SELECT user_id,
-> visit_time spvisit_time,
-> page_page_id
-> FROM olap.mds_log_weblog_pageview
-> WHERE dt>=date_sub(current_date(),1)
-> AND site IN (‘www’,
-> ‘wap’,
-> ‘app’)
-> AND event_type<>‘native_page_end’
-> AND is_bot IS NULL
-> AND country_id<>‘CN’
-> AND page_page_id=‘sp’
-> and user_id is not null and user_id <>’’ ) a3 ON a1.user_id=a3.user_id
-> AND unix_timestamp(a1.hpvisit_time) < unix_timestamp(a3.spvisit_time)
-> AND unix_timestamp(a3.spvisit_time) - unix_timestamp(a1.hpvisit_time)<= 360024
->
->
-> LEFT JOIN
-> (SELECT user_id,
-> visit_time pdvisit_time,
-> page_page_id
-> FROM olap.mds_log_weblog_pageview
-> WHERE dt>=date_sub(current_date(),1)
-> AND site IN (‘www’,
-> ‘wap’,
-> ‘app’)
-> AND event_type<>‘native_page_end’
-> AND is_bot IS NULL
-> AND country_id<>‘CN’
-> AND page_page_id=‘pd’
-> and user_id is not null and user_id <>’’ ) a2 ON a3.user_id=a2.user_id
-> AND unix_timestamp(a3.spvisit_time) < unix_timestamp(a2.pdvisit_time)
-> AND unix_timestamp(a2.pdvisit_time) - unix_timestamp(a1.hpvisit_time)<= 360024
->
-> ;
±-------------------------------±-------------------------------±-------------------------------+
| count(DISTINCT a1.user_id) | count(DISTINCT a3.user_id) | count(DISTINCT a2.user_id) |
±-------------------------------±-------------------------------±-------------------------------+
| 387530 | 144916 | 118047 |
±-------------------------------±-------------------------------±-------------------------------+
1 row in set (21.22 sec)
后面用with直接超 时了
MySQL [(none)]> with t1 as
-> (select user_id,visit_time,page_page_id from olap.mds_log_weblog_pageview
-> where dt=date_sub(current_date(),1)
-> and site in (‘www’,‘wap’,‘app’)
-> and event_type<>‘native_page_end’
-> and is_bot is null and country_id<>‘CN’
-> and ( page_page_id=‘hp’ or page_page_id=‘pd’ or page_page_id=‘sp’ )
->
-> )
-> SELECT count( distinct a1.user_id),
-> count( distinct a3.user_id),
-> count( distinct a2.user_id)
-> FROM
-> (SELECT user_id,
-> visit_time hpvisit_time,
-> page_page_id
-> from t1
-> where page_page_id=‘hp’
-> ) a1
-> LEFT JOIN
-> (SELECT user_id,
-> visit_time spvisit_time,
-> page_page_id
-> from t1
-> where page_page_id=‘sp’ ) a3 ON a1.user_id=a3.user_id
-> AND unix_timestamp(a1.hpvisit_time) < unix_timestamp(a3.spvisit_time)
-> AND unix_timestamp(a3.spvisit_time) - unix_timestamp(a1.hpvisit_time)<= 360024
->
->
-> LEFT JOIN
-> (SELECT user_id,
-> visit_time pdvisit_time,
-> page_page_id
-> from t1
-> where page_page_id=‘pd’ ) a2 ON a3.user_id=a2.user_id
-> AND unix_timestamp(a3.spvisit_time) < unix_timestamp(a2.pdvisit_time)
-> AND unix_timestamp(a2.pdvisit_time) - unix_timestamp(a1.hpvisit_time)<= 360024
->
-> ;
ERROR 1064 (HY000): Ocurrs time out with specfied time 299476 MILLISECONDS
同样的漏斗测试ck只用了200ms
