1、group by rollup
GROUP BY rollup(grass_region):# of grass_region+1(sum)
GROUP BY rollup(x1,x2): x1*x2+x1+1
2、时间函数
datediff('endtime', 'starttime'): 返回时间差
date_sub('yyyy-mm-dd',n/-m):返回初始日期n天前、m天后的日期
date_add('yyyy-mm-dd',n/-m):返回初始日期n天后、m天前的日期
ps:三个date函数日期均只能为'yyyy-mm-dd'格式 & 'yyyy-mm-dd hh:mm:s'格式
l7d: date(from_unixtime(create_timestamp)) BETWEEN date_trunc('week', current_date - interval '1' day) AND current_date - interval '1' day
MTD: date(from_unixtime(create_timestamp)) BETWEEN date_trunc('month', current_date - interval '1' day) AND current_date - interval '1' day
LM: date(from_unixtime(create_timestamp)