今天是星期五,一个脑细胞耗得很厉害的星期五,今天完成麦肯锡的仪表盘需求,编写一个代码,反复出现错误,反复修改,沙箱和承影反复测试,
代码保存下
SELECT a1.Week
,a1.City_name
,(a1.W_au_cnt - a2.w_new_finish_cnt) ---老用户当周完单人数
,(a1.W_au_cnt - a2.w_new_finish_cnt) / a3.w_register_pcnt ---老用户活跃占比
FROM (SELECT WEEKOFYEAR(TO_TIMESTAMP(Stats_date,'yyyyMMdd')) AS Week
,City_name
,MAX(W_finish_user_cnt) AS W_au_cnt ---当周完单用户数
FROM Pub_cockpit_db.Dm_wky_meeting_user_act_ws
WHERE Stats_date >= from_unixtime(unix_timestamp( date_add(now(),-(dayofweek(now())-2))- interval 8 weeks ),'yyyyMMdd')
AND City_name in ('全国','重庆市', '杭州市','长春市','广州市','南京市','武汉市','福州市','成都市','长沙市','郑州市','天津市')
AND source=1 ---t3app
GROUP BY Stats_date
,City_name
) a1
LEFT JOIN