如何使用MySQL位图索引处理用户画像问题
发布时间：2021-12-24 13:30:37 所属栏目：MySql教程 来源：互联网
导读：这篇文章给大家分享的是有关如何使用MySQL位图索引解决用户画像问题的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。 经过王工的改良, 使用如下sql替换，则保存的位信息就连续了 SELECT CEIL(id / 60) g60, CEIL(id / 1200) g1
|
这篇文章给大家分享的是有关如何使用MySQL位图索引解决用户画像问题的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。 经过王工的改良, 使用如下sql替换，则保存的位信息就连续了 SELECT CEIL(id / 60) g60, CEIL(id / 1200) g1200, age grouped, COUNT(*) total, BIT_OR(1 << (if(MOD(id, 60)=0,60,MOD(id, 60)))) bitmap FROM o_huaxiang_big_0 o GROUP BY g1200 , g60 , age 创建位图索引的整体SQL如下 truncate table bitmap20_0; insert into bitmap20_0 select 'o_huaxiang_big' table_name, 'umc_sex' column_name, ((g1200-1)*60)*20 min_id, ((g1200-1)*60)*20+1200 max_id, v2.* from ( select g1200, grouped, sum(total) total, ifnull(max(case when abs((g1200-1)*20-g60)=20 then bitmap else null end),0) c20, ifnull(max(case when abs((g1200-1)*20-g60)=19 then bitmap else null end),0) c19, ifnull(max(case when abs((g1200-1)*20-g60)=18 then bitmap else null end),0) c18, ifnull(max(case when abs((g1200-1)*20-g60)=17 then bitmap else null end),0) c17, ifnull(max(case when abs((g1200-1)*20-g60)=16 then bitmap else null end),0) c16, ifnull(max(case when abs((g1200-1)*20-g60)=15 then bitmap else null end),0) c15, ifnull(max(case when abs((g1200-1)*20-g60)=14 then bitmap else null end),0) c14, ifnull(max(case when abs((g1200-1)*20-g60)=13 then bitmap else null end),0) c13, ifnull(max(case when abs((g1200-1)*20-g60)=12 then bitmap else null end),0) c12, ifnull(max(case when abs((g1200-1)*20-g60)=11 then bitmap else null end),0) c11, ifnull(max(case when abs((g1200-1)*20-g60)=10 then bitmap else null end),0) c10, ifnull(max(case when abs((g1200-1)*20-g60)=9 then bitmap else null end),0) c9, ifnull(max(case when abs((g1200-1)*20-g60)=8 then bitmap else null end),0) c8, ifnull(max(case when abs((g1200-1)*20-g60)=7 then bitmap else null end),0) c7, ifnull(max(case when abs((g1200-1)*20-g60)=6 then bitmap else null end),0) c6, ifnull(max(case when abs((g1200-1)*20-g60)=5 then bitmap else null end),0) c5, ifnull(max(case when abs((g1200-1)*20-g60)=4 then bitmap else null end),0) c4, ifnull(max(case when abs((g1200-1)*20-g60)=3 then bitmap else null end),0) c3, ifnull(max(case when abs((g1200-1)*20-g60)=2 then bitmap else null end),0) c2, ifnull(max(case when abs((g1200-1)*20-g60)=1 then bitmap else null end),0) c1 from ( SELECT CEIL(id / 60) g60, CEIL(id / 1200) g1200, umc_sex grouped, COUNT(*) total, BIT_OR(1 << (if(MOD(id, 60)=0,60,MOD(id, 60)))) bitmap FROM o_huaxiang_big_0 o GROUP BY g1200 , g60 , umc_sex ) v1 group by g1200,grouped ) v2; insert into bitmap20_0 select 'o_huaxiang_big' table_name, 'age' column_name, ((g1200-1)*60)*20 min_id, ((g1200-1)*60)*20+1200 max_id, v2.* from ( select g1200, grouped, sum(total) total, ifnull(max(case when abs((g1200-1)*20-g60)=20 then bitmap else null end),0) c20, ifnull(max(case when abs((g1200-1)*20-g60)=19 then bitmap else null end),0) c19, ifnull(max(case when abs((g1200-1)*20-g60)=18 then bitmap else null end),0) c18, ifnull(max(case when abs((g1200-1)*20-g60)=17 then bitmap else null end),0) c17, ifnull(max(case when abs((g1200-1)*20-g60)=16 then bitmap else null end),0) c16, ifnull(max(case when abs((g1200-1)*20-g60)=15 then bitmap else null end),0) c15, ifnull(max(case when abs((g1200-1)*20-g60)=14 then bitmap else null end),0) c14, ifnull(max(case when abs((g1200-1)*20-g60)=13 then bitmap else null end),0) c13, ifnull(max(case when abs((g1200-1)*20-g60)=12 then bitmap else null end),0) c12, ifnull(max(case when abs((g1200-1)*20-g60)=11 then bitmap else null end),0) c11, ifnull(max(case when abs((g1200-1)*20-g60)=10 then bitmap else null end),0) c10, ifnull(max(case when abs((g1200-1)*20-g60)=9 then bitmap else null end),0) c9, ifnull(max(case when abs((g1200-1)*20-g60)=8 then bitmap else null end),0) c8, ifnull(max(case when abs((g1200-1)*20-g60)=7 then bitmap else null end),0) c7, ifnull(max(case when abs((g1200-1)*20-g60)=6 then bitmap else null end),0) c6, ifnull(max(case when abs((g1200-1)*20-g60)=5 then bitmap else null end),0) c5, ifnull(max(case when abs((g1200-1)*20-g60)=4 then bitmap else null end),0) c4, ifnull(max(case when abs((g1200-1)*20-g60)=3 then bitmap else null end),0) c3, ifnull(max(case when abs((g1200-1)*20-g60)=2 then bitmap else null end),0) c2, ifnull(max(case when abs((g1200-1)*20-g60)=1 then bitmap else null end),0) c1 from ( SELECT CEIL(id / 60) g60, CEIL(id / 1200) g1200, age grouped, COUNT(*) total, BIT_OR(1 << (if(MOD(id, 60)=0,60,MOD(id, 60)))) bitmap FROM o_huaxiang_big_0 o GROUP BY g1200 , g60 , age ) v1 group by g1200,grouped ) v2; 感谢各位的阅读！关于“如何使用MySQL位图索引解决用户画像问题”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！ （编辑：荆州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
