实习期间基于业务的SQL代码整理
1. 周报提数
1.1某时间段某省的活跃用户数
统计口径:活跃用户数 (dmid_client_user_detail_d_yyyymm 客户端细节表,MID层)
prov_id字段中15表示江苏省 (ddim_province省份号段表,关联prov_id)
msidn字段表示手机号
参考代码:
SELECT COUNT(DISTINCT( msisdn) )
FROM dmid_client_user_detail_d_201808
WHERE oprt_date BETWEEN '20180801' AND '20189809'
AND prov_id='15';
解释: 统计江苏省2018年8月1日至8月9日的客户端手机活跃用户数,周报提取活跃用户数是本月累积的方式,若在周报需求中提取20180810-20180817的活跃用户,则相当于提取本月截至20180817的活跃用户数。
1.2提取特会用户数
统计口径: 会员日订购用户数明细表 dmid_mem_order_detail_d_yyyymm
mem_lvl字段中3表示特会
prov_id字段中15表示江苏省
mem_status字段表示中0表示用户状态正常
msidn字段表示手机号
deal_day字段表示业务时间
参考代码:
SELECT COUNT(DISTINCT(msisdn))
FROM dmid_mem_order_detail_d_201808
WHERE mem_lvl='3'
AND prov_id='15'
AND deal_day BETWEEN '20180801'AND '20180809'
AND mem_status='0'
解释:统计江苏省2018年8月1日至8月9日的客户端手机特级会员人数,特会人数可直接选取对应时间段进行统计。
1.3 提取某省付费白金会员用户数
统计口径:白金会员用户明细表 dmid_member_pt_detail_m_yyyymm
fee_flag字段中2表示付费
prov_id字段中15表示江苏省
参考代码:
SELECT COUNT(DISTINCT( msisdn) )
FROM dmid_member_pt_detail_m_201808
WHERE fee_flag='2'
AND prov_id='15'
1.4 提取指定日期的活跃用户数
统计口径:活跃用户数 (dmid_client_user_detail_d_yyyymm 客户端细节表,MID层)
msidn字段表示手机号,login_no登录名称
提取思路是将8.1号和7.26-7.31 的活跃用户数的取出,两个结果进行左连接,若msisdn2中有值,则说明8.1号的活跃用户在7.26-7.31日活跃过,因此需要将最后的结果限定为B.msisdn2 中的值为空,然后再统计A.msidn1的数量。
参考代码:
SELECT COUNT(msisdn1)
FROM(SELECT DISTINCT( COALESCE(msisdn, login_no, imei_no, imsi_no)) as msisdn1
FROM dmid_client_user_detail_d_201808
WHERE oprt_date =‘20180801’
AND prov_id=’