ITPub博客

首页 > 数据库 > MySQL > 画像预先计算的一点设想.时间段分片

画像预先计算的一点设想.时间段分片

原创 MySQL 作者:壹頁書 时间:2018-11-16 15:57:41 0 删除 编辑

画像优化告一段落。

ES在分群阶段的效果非常好.但是在增量指标计算时 ES显得非常吃力。


什么是增量指标计算呢?比如用户的点击次数,消费情况,这个原来在一个Hive表保存.

每个用户每天一条记录.

那么如果一个群分了50万人,统计他们9月1日至11月1日的消费情况.则需要扫描 50w*62  三千一百万记录


这个数据量太大,所以增量指标计算的速度非常慢.


因为是离线数据,可以用空间换取时间。

我先给每周打一个标识.以2018年1月1日为基准,每增加一周,week id 自增 1.

这样以后所有的周都有了连续的自增ID。


2018年1月1日基准周开始,以每四周为维度统计一次,然后再以每两周为维度统计一次,最后在以每周为维度统计一次。


除了以若干周为维度,还可以更加细化。每周 星期一至星期三,星期四至星期五,周六至周日,再增加三个细分的维度。


那么 50w用户 从9月1日至11月1日的统计, 


set @startDate:='20180901';
set @endDate:='20181101';
select * from (
	select  
	concat('week',weekCount,'-',startWeek) flag
	,startWeek,startDate,endDate,7*weekCount days,weekCount
	from (
		select 
		case when days =14 then w4id else w2id end  startWeek,
		min(startDate) startDate,max(endDate) endDate,sum(days) days,floor(sum(days)/7) weekCount
		from (
			select w2id,max(w4id) w4id,min(startDate) startDate,max(endDate) endDate,sum(days) days from (
				select  
				wid,
				floor(wid/2)*2 w2id,floor(wid/4)*4 w4id,
				count(*) days, 
				min(dt) startDate, 
				max(dt) endDate 
				from ( 
					select 
					id,dt,
					floor(datediff(str_to_date(startDate,'%Y%m%d') + interval id-1 day,'2018-01-01')/7) as wid
					from (
						select 
						id, 
						str_to_date(@startDate,'%Y%m%d') + interval id-1 day  dt,
						@startDate startDate,
						@endDate endDate
						from nums where id<= 
						datediff(str_to_date(@endDate,'%Y%m%d'),str_to_date(@startDate,'%Y%m%d'))+1 
					) t1 
				) t2 group by wid  having count(*)=7
			) t3 group by w2id
		) t4 group by case when days =14 then w4id else w2id end 
	) t5 
	union 
	select if(days=1,'single day',flag) flag,wid,startDate,endDate,days,weekCount from (
		select   flag,min(wid) wid,min(startDate) startDate,max(endDate) endDate,count(*) days,0 weekCount
		from (
			select 
			wid,
			startDate+ interval (id-1) day startDate,
			startDate+ interval (id-1) day endDate,
			case 
			when
			 weekday(startDate+ interval (id-1) day)+1 in(1,2,3) then 'day-1-3'
			when
			 weekday(startDate+ interval (id-1) day)+1 in(4,5) then 'day-4-5'
			when
			 weekday(startDate+ interval (id-1) day)+1 in(6,7) then 'day-6-7'
			end flag
			from (
				select  
				wid,
				floor(wid/2)*2 w2id,floor(wid/4)*4 w4id,
				count(*) days, 
				min(dt) startDate, 
				max(dt) endDate 
				from ( 
					select 
					id,dt,
					floor(datediff(str_to_date(startDate,'%Y%m%d') + interval id-1 day,'2018-01-01')/7) as wid
					from (
						select 
						id, 
						str_to_date(@startDate,'%Y%m%d') + interval id-1 day  dt,
						@startDate startDate,
						@endDate endDate
						from nums where id<= 
						datediff(str_to_date(@endDate,'%Y%m%d'),str_to_date(@startDate,'%Y%m%d'))+1 
					) t1 
				) t2 group by wid  having count(*)!=7
			) t3 ,nums where nums.id<=days
		) t4 group by flag
	) t5 
) r order by startDate;





flag表示 聚合维度. 可以是 4周聚合维度,2周聚合维度,单周聚合维度,星期1,2,3维度,星期4,5维度,星期6,7维度和单天

startWeek表示维度开始时间所在的周ID

startDate表示开始日期

endDate表示结束日期

days表示维度内天数

weekCount表示维度包含的星期数


如果按照不同粗细维度预先计算.则原来需要扫描4 三千一百万 记录可以缩小为

50w*7 (上述范围可以拆分为7个时段)  三百五十万记录

扫描量仅为原来的 11%


代价是存储的增加.按照上述维度,存储空间会膨胀到原来的1.68倍。





来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29254281/viewspace-2220384/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-10-19

  • 博文量
    621
  • 访问量
    5948598