sql l1

sql tianchi

sql
--blogsum*weight
--uid 各个月份 blog 总数--
select
c.uid,
sum(c.month1num) as month1sum,
sum(c.month2num) as month2sum,
sum(c.month3num) as month3sum,
sum(c.month4num) as month4sum,
sum(c.month5num) as month5sum
from
(
select
b.uid,
b.month,
case when b.month=1 then 1 else 0 end as month1num,
case when b.month=2 then 1 else 0 end as month2num,
case when b.month=3 then 1 else 0 end as month3num,
case when b.month=4 then 1 else 0 end as month4num,
case when b.month=5 then 1 else 0 end as month5num
from
(
select *,
case
when substr(blog_time,1,10)<"2014-12-01" and substr(blog_time,1,10)>="2014-11-01" then 1
when substr(blog_time,1,10)<"2015-01-01" and substr(blog_time,1,10)>="2014-12-01" then 2
when substr(blog_time,1,10)<"2015-02-01" and substr(blog_time,1,10)>="2015-01-01" then 3
when substr(blog_time,1,10)<"2015-03-01" and substr(blog_time,1,10)>="2015-02-01" then 4
when substr(blog_time,1,10)<"2015-04-01" and substr(blog_time,1,10)>="2015-03-01" then 5
end as month
from tianchi_weibo.weibo_blog_data_train
)b
group by b.uid,b.month
)c
group by c.uid
order by month5sum desc limit 20000;--从大到小
sql