SQL 精编（5）
这里是直接计算出来值跟 the_date 进行比较，原因是如果依旧使用函数，可能会更通用，但是效能会无法忍
如下，则使用不到 the_date 上的索引
select distinct(user_id),count(*) as ct from cheshi_qa.cks where to_days(the_date) >= to_days(date_sub(now(),interval 60 day)) group by user_id having ct >= 5- 1周内未登陆：[7 days，0 days)
create table tmp_3 (select distinct(user_id) from cheshi_qa.cks where the_date >= '2016-05-10' )为三张表创建索引
create index idx_tmp_1 on tmp_1(id);
create index idx_tmp_2 on tmp_2(user_id);
create index idx_tmp_3 on tmp_3(user_id);拼接成结果
create table tmp_rs (select id,qq from tmp_1 where ( EXISTS (select user_id from tmp_2 where user_id = tmp_1.id)) and ( not exists (select user_id from tmp_3 where user_id = tmp_1.id)));导出结果
select id,qq from tmp_rs into outfile '/tmp/task1' fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\r\n';- 最近一个月登陆过
create table tast2_1 (select distinct(user_id) from cheshi_qa.cks where the_date >= '2016-04-16' )- 初始等级-当前等级 >= 2
- 留有手机号
select id,cellphone from test_qa.users where latest_level-begin_level >= 2 and cellphone is not null结合两者，其实第一个去另外再创建一张表包含这一个月以来的所有记录是很没必要的，因为我们只要知道 存在 就可以了
create table tast2_rs1 ( select id,cellphone from test_qa.users where latest_level-begin_level >= 2 and cellphone is not null and exists ( select user_id from cheshi_qa.cks where user_id = test_qa.users.id and the_date >= '2016-04-16' ));导出结果成CVS
select id,cellphone from tast2_rs1 into outfile '/tmp/task2' fields terminated by ',' optionally enclosed by '本文系转载，前往查看
如有侵权，请联系 cloudcommunity@tencent.com 删除。
本文系转载，前往查看
如有侵权，请联系 cloudcommunity@tencent.com 删除。
评论
登录后参与评论
推荐阅读