这个马甲属于一个表,appStore另一张表,用户登录表,主要操作的就是这三个表。
我这里的马甲和app的id都与用户登录表中的channel对应,在channel存放的是majiaId + “|” + storeId
第一版sql:
select count(*), sms_tag_name, store_name
from
(select
id,
create_date,
SUBSTR(channel,0,NVL(INSTR(channel, '|', 1,1),0)-1) sms_tag_id,
SUBSTR(channel,NVL(INSTR(channel, '|', 1,1),0)+1, length(channel)- NVL(INSTR(channel, '|', 1,1),0)) store_id,
channel
from user_login
where
NVL(INSTR(channel, '|', 1,1),0) != 0) u
INNER JOIN majia_sms_tag mst ON mst.sms_tag_id = u.sms_tag_id
INNER JOIN app_store aps ON aps.store_id = u.store_id
where store_name='华为'
group by sms_tag_name, store_name
发现存在问题,针对用户登录中农没有存储到的马甲和应用商店的注册量找不出来等,信息不全面
第二版:
select
count(u.id),
sms_tag_name,
store_name
from
(
select
sms_tag_id,
sms_tag_name,
store_id,
store_name,
concat(concat(sms_tag_id,'|'),store_id) as channel
from
majia_sms_tag cross join app_store
) ma
left join (
select *
from user_login
where
to_char(create_date, 'YYYYMMDDHH24MISS') >= '20160801000000'
and to_char(create_date, 'YYYYMMDDHH24MISS') <= '20170801000000'
) u on u.channel = ma.channel
where
sms_tag_id='com.rybring.weilidai'
and store_id='platf.360'
group by sms_tag_name,store_name
order by sms_tag_name,store_name
还是用了cross join,第一次用到擦,并且将两个查询的条件分别放在子查询中和外面。神来之笔。
以后再有类似需求可以参考。