自然周与自然月的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