这篇文章给大家分享的是有关如何使用MySQL位图索引解决用户画像问题的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。

每个bigint类型包括60个记录的位信息.

但是第0位表示第六十个记录的位

第1位至第59位表示第一至五十九的记录的位信息.

这样记录的位信息保存并不连续,

使用的时候还得把最右边的一位挪到最左边,不好理解还非常麻烦,性能也有损耗.

经过王工的改良,

使用如下sql替换，则保存的位信息就连续了

SELECTCEIL(id/60)g60,CEIL(id/1200)g1200,agegrouped,COUNT(*)total,BIT_OR(1<<(if(MOD(id,60)=0,60,MOD(id,60))))bitmapFROMo_huaxiang_big_0oGROUPBYg1200,g60,age

创建位图索引的整体SQL如下

truncatetablebitmap20_0;insertintobitmap20_0select'o_huaxiang_big'table_name,'umc_sex'column_name,((g1200-1)*60)*20min_id,((g1200-1)*60)*20+1200max_id,v2.*from(selectg1200,grouped,sum(total)total,ifnull(max(casewhenabs((g1200-1)*20-g60)=20thenbitmapelsenullend),0)c20,ifnull(max(casewhenabs((g1200-1)*20-g60)=19thenbitmapelsenullend),0)c19,ifnull(max(casewhenabs((g1200-1)*20-g60)=18thenbitmapelsenullend),0)c18,ifnull(max(casewhenabs((g1200-1)*20-g60)=17thenbitmapelsenullend),0)c17,ifnull(max(casewhenabs((g1200-1)*20-g60)=16thenbitmapelsenullend),0)c16,ifnull(max(casewhenabs((g1200-1)*20-g60)=15thenbitmapelsenullend),0)c15,ifnull(max(casewhenabs((g1200-1)*20-g60)=14thenbitmapelsenullend),0)c14,ifnull(max(casewhenabs((g1200-1)*20-g60)=13thenbitmapelsenullend),0)c13,ifnull(max(casewhenabs((g1200-1)*20-g60)=12thenbitmapelsenullend),0)c12,ifnull(max(casewhenabs((g1200-1)*20-g60)=11thenbitmapelsenullend),0)c11,ifnull(max(casewhenabs((g1200-1)*20-g60)=10thenbitmapelsenullend),0)c10,ifnull(max(casewhenabs((g1200-1)*20-g60)=9thenbitmapelsenullend),0)c9,ifnull(max(casewhenabs((g1200-1)*20-g60)=8thenbitmapelsenullend),0)c8,ifnull(max(casewhenabs((g1200-1)*20-g60)=7thenbitmapelsenullend),0)c7,ifnull(max(casewhenabs((g1200-1)*20-g60)=6thenbitmapelsenullend),0)c6,ifnull(max(casewhenabs((g1200-1)*20-g60)=5thenbitmapelsenullend),0)c5,ifnull(max(casewhenabs((g1200-1)*20-g60)=4thenbitmapelsenullend),0)c4,ifnull(max(casewhenabs((g1200-1)*20-g60)=3thenbitmapelsenullend),0)c3,ifnull(max(casewhenabs((g1200-1)*20-g60)=2thenbitmapelsenullend),0)c2,ifnull(max(casewhenabs((g1200-1)*20-g60)=1thenbitmapelsenullend),0)c1from(SELECTCEIL(id/60)g60,CEIL(id/1200)g1200,umc_sexgrouped,COUNT(*)total,BIT_OR(1<<(if(MOD(id,60)=0,60,MOD(id,60))))bitmapFROMo_huaxiang_big_0oGROUPBYg1200,g60,umc_sex)v1groupbyg1200,grouped)v2;insertintobitmap20_0select'o_huaxiang_big'table_name,'age'column_name,((g1200-1)*60)*20min_id,((g1200-1)*60)*20+1200max_id,v2.*from(selectg1200,grouped,sum(total)total,ifnull(max(casewhenabs((g1200-1)*20-g60)=20thenbitmapelsenullend),0)c20,ifnull(max(casewhenabs((g1200-1)*20-g60)=19thenbitmapelsenullend),0)c19,ifnull(max(casewhenabs((g1200-1)*20-g60)=18thenbitmapelsenullend),0)c18,ifnull(max(casewhenabs((g1200-1)*20-g60)=17thenbitmapelsenullend),0)c17,ifnull(max(casewhenabs((g1200-1)*20-g60)=16thenbitmapelsenullend),0)c16,ifnull(max(casewhenabs((g1200-1)*20-g60)=15thenbitmapelsenullend),0)c15,ifnull(max(casewhenabs((g1200-1)*20-g60)=14thenbitmapelsenullend),0)c14,ifnull(max(casewhenabs((g1200-1)*20-g60)=13thenbitmapelsenullend),0)c13,ifnull(max(casewhenabs((g1200-1)*20-g60)=12thenbitmapelsenullend),0)c12,ifnull(max(casewhenabs((g1200-1)*20-g60)=11thenbitmapelsenullend),0)c11,ifnull(max(casewhenabs((g1200-1)*20-g60)=10thenbitmapelsenullend),0)c10,ifnull(max(casewhenabs((g1200-1)*20-g60)=9thenbitmapelsenullend),0)c9,ifnull(max(casewhenabs((g1200-1)*20-g60)=8thenbitmapelsenullend),0)c8,ifnull(max(casewhenabs((g1200-1)*20-g60)=7thenbitmapelsenullend),0)c7,ifnull(max(casewhenabs((g1200-1)*20-g60)=6thenbitmapelsenullend),0)c6,ifnull(max(casewhenabs((g1200-1)*20-g60)=5thenbitmapelsenullend),0)c5,ifnull(max(casewhenabs((g1200-1)*20-g60)=4thenbitmapelsenullend),0)c4,ifnull(max(casewhenabs((g1200-1)*20-g60)=3thenbitmapelsenullend),0)c3,ifnull(max(casewhenabs((g1200-1)*20-g60)=2thenbitmapelsenullend),0)c2,ifnull(max(casewhenabs((g1200-1)*20-g60)=1thenbitmapelsenullend),0)c1from(SELECTCEIL(id/60)g60,CEIL(id/1200)g1200,agegrouped,COUNT(*)total,BIT_OR(1<<(if(MOD(id,60)=0,60,MOD(id,60))))bitmapFROMo_huaxiang_big_0oGROUPBYg1200,g60,age)v1groupbyg1200,grouped)v2;

感谢各位的阅读！关于“如何使用MySQL位图索引解决用户画像问题”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！