mysql按自然周自然月统计_自然周与自然月的Hive统计SQL

按照周或者月统计活跃数：

周:

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