按照周或者月统计活跃数:
周:
SELECT week, COUNT(DISTINCT pin), business_type
FROM (
SELECT DISTINCT user_log_acct AS pin,weekofyear(dt) AS week
, CASE locate('bdp', url_domain)
WHEN 0 THEN 'pinpiao'
ELSE 'caixiao'
END AS business_type
FROM gdm.gdm_online_log
WHERE ((url_domain LIKE '%tmall.com%'
OR url_domain LIKE '%bdp.tmall.com%')
AND user_log_acct NOT IN ('xxx', 'xx111', 'xxx22')
AND dt >= '2018-07-30')
) weekTab
GROUP BY week, business_type;
月:
SELECT month, COUNT(DISTINCT pin), business_type
FROM (
SELECT DISTINCT user_log_acct AS pin, month(dt) AS month
, CASE locate('bdp', url_domain)
WHEN 0 THEN 'brand'
ELSE 'caixiao'
END AS business_type
FROM gdm.gdm_online_log
WHERE ((url_domain LIKE '%xx.xx.com%'
OR url_domain LIKE '%aa.bdp.aaa.com%')
AND user_log_acct NOT IN ('aaa', 'bbb', 'ccc')
AND dt >= '2018-08-01')
) monthTab
GROUP BY month, business_type;
主要是日期函数的使用!!!!!
https://www.cnblogs.com/MOBIN/p/5618747.html
https://www.tutorialspoint.com/hive/hive_built_in_functions.htm
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF