2019-07-05

一.重点sql

1.hive动态分区

set hive.exec.dynamici.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;


insert overwrite table mds_new_user_temp   partition (dt)
select uid,first_date,last_date,app_platform,app_version,wm,dt
from mds_mbportal_newsclient_activate_retain_normaluser
where dt between 20190625 and 20190703
and pt_channel='news'
and pt_type='activate'
and wm not in ('12030_0001','12040_0001','12433_0001','12040_0006')
and concat(substr(first_date,1,4),substr(first_date,6,2),substr(first_date,9,2))=dt 
and first_date=last_date;

2.回访计算

create table  xiaojie_retain as
select a.dt,a.ustat
from 
(--获取统计期间内去互保新用户的dt,uid,first_date
select  dt,uid as ustat,first_date,
case
when app_platform in ('Android','Android_Lite','Android_Pad','Android_TV') then 'android'
when app_platform in ('iPad','iPhone') then 'ios'
else 'others' end as app_platform
from mds_mbportal_newsclient_activate_retain_normaluser --去互保用户表
where dt='20180919'
and pt_channel='news' 
and pt_type='activate'
and dt = concat(substr(last_date,1,4),substr(last_date,6,2),substr(last_date,9,2))
and first_date=last_date
)a 
inner join 
(-- 用于计算次日留存
select  dt,uid as ustat,
date_sub(concat_ws('-', substring(dt,1,4), substring(dt,5,2), substring(dt,7,2)),1) dt_1
from mds_mbportal_newsclient_activate_retain_normaluser --去互保用户表
where dt='20180920'
and pt_channel='news' 
and pt_type='activate'
and dt = concat(substr(last_date,1,4),substr(last_date,6,2),substr(last_date,9,2))
)e 
on a.ustat=e.ustat and  concat(substr(a.dt,1,4),'-',substr(a.dt,5,2),'-',substr(a.dt,7,2))=e.dt_1

3.linux常用

• 统计词频：find -name "*zip" | wc -l
• 修改权限：chgrp huzhuzhe t.txt
• 大小写敏感：grep -i "huzhuzhe"

更多参考见：https://www.jianshu.com/p/c8259702a22e