分析不同时间消费的趋势

xxxxxxxxxx
select time_stamp,count(item_id) num
from user_log
where time_stamp is not null
group by time_stamp
distribute by time_stamp
sort by time_stamp;

当distribute by和sort by后面的字段相同时，可以使用cluster by进行简化。功能是等价的；但是只能使用升序排序，不能指定排序规则为asc或者desc。

xxxxxxxxxx
select time_stamp,count(item_id) num
from user_log 
group by time_stamp
cluster by time_stamp;

查询结果：

xxxxxxxxxx
time_stamp  num
NULL    0   #导入数据时首行标题数据导致的
512 20704
520 225198
528 214501
608 179133
616 179872
624 220395
704 163427
712 154343
720 131491
728 124547
808 147392
816 164164
......
Time taken: 80.563 seconds, Fetched: 187 row(s)

注意：目前的时间戳不是完整的，如果是完整，还要考虑将时间戳进行转化。比如要分析按天分析趋势，需要将时间戳转换为年月日；要按小时分析趋势，需要将时间戳转换为年月日时。

扩展：

1. 按天分析消费数量的趋势

   xxxxxxxxxx
   select time_stamp,count(item_id) num
   from user_log
   where time_stamp is not null
   group by from_unixtime(cast(time_stamp/1000 as bigint),'yyyy-MM-dd')
   cluster by time_stamp;
   

2. 按天分析消费数量的趋势，指定的时间段

   xxxxxxxxxx
   select from_unixtime(cast(time_stamp/1000 as bigint),'yyyy-MM-dd'),count(item_id) num
   from user_log
   where time_stamp is not null
   and time_stamp >= unix_timestamp('2030-03-30','yyyy-MM-dd')*1000
   and time_stamp < unix_timestamp('2030-06-30','yyyy-MM-dd')*1000
   group by from_unixtime(cast(time_stamp/1000 as bigint),'yyyy-MM-dd')
   cluster by time_stamp;