最近在做留存统计，看了一篇使用位图函数写留存的，效率很高；
表结构
CREATE TABLE tb_book_chapter_request_record
(
`id` UInt64 COMMENT '自增ID',
`uid` UInt64 COMMENT '用户ID',
`bid` UInt64 COMMENT '书籍ID',
`chapterno` UInt64 COMMENT '章节排序值',
`client` UInt8 COMMENT '客户端 1 安卓 2 苹果',
`ctime` UInt64 COMMENT '创建时间',
`device` String COMMENT '设备号',
`cdate` Date COMMENT '创建日期'
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(cdate)
ORDER BY (bid,
chapterno,
uid,
device)
SETTINGS index_granularity = 8192
使用retention函数做留存统计
- 两百个章节留存，看代码就有点头痛了
SELECT
sum(retain1[2]) as retain_uv_1,
sum(retain2[2]) as retain_uv_2,
sum(retain3[2]) as retain_uv_3,
sum(retain4[2]) as retain_uv_4,
sum(retain5[2]) as retain_uv_5,
sum(retain6[2]) as retain_uv_6,
sum(retain7[2]) as retain_uv_7,
sum(retain8[2]) as retain_uv_8,
sum(retain9[2]) as retain_uv_9,
sum(retain10[2]) as retain_uv_10,
sum(retain11[2]) as retain_uv_11,
sum(retain12[2]) as retain_uv_12,
sum(retain13[2]) as retain_uv_13,
sum(retain14[2]) as retain_uv_14,
sum(retain15[2]) as retain_uv_15,
sum(retain16[2]) as retain_uv_16,
sum(retain17[2]) as retain_uv_17,
sum(retain18[2]) as retain_uv_18,
sum(retain19[2]) as retain_uv_19,
sum(retain20[2]) as retain_uv_20,
sum(retain21[2]) as retain_uv_21,
sum(retain22[2]) as retain_uv_22,
sum(retain23[2]) as retain_uv_23,
sum(retain24[2]) as retain_uv_24,
sum(retain25[2]) as retain_uv_25,
sum(retain26[2]) as retain_uv_26,
sum(retain27[2]) as retain_uv_27,
sum(retain28[2]) as retain_uv_28,
sum(retain29[2]) as retain_uv_29,
sum(retain30[2]) as retain_uv_30,
sum(retain31[2]) as retain_uv_31,
sum(retain32[2]) as retain_uv_32,
sum(retain33[2]) as retain_uv_33,
sum(retain34[2]) as retain_uv_34,
sum(retain35[2]) as retain_uv_35,
sum(retain36[2]) as retain_uv_36,
sum(retain37[2]) as retain_uv_37,
sum(retain38[2]) as retain_uv_38,
sum(retain39[2]) as retain_uv_39,
sum(retain40[2]) as retain_uv_40,
sum(retain41[2]) as retain_uv_41,
sum(retain42[2]) as retain_uv_42,
sum(retain43[2]) as retain_uv_43,
sum(retain44[2]) as retain_uv_44,
sum(retain45[2]) as retain_uv_45,
sum(retain46[2]) as retain_uv_46,
sum(retain47[2]) as retain_uv_47,
sum(retain48[2]) as retain_uv_48,
sum(retain49[2]) as retain_uv_49,
sum(retain50[2]) as retain_uv_50,
sum(retain51[2]) as retain_uv_51,
sum(retain52[2]) as retain_uv_52,
sum(retain53[2]) as retain_uv_53,
sum(retain54[2]) as retain_uv_54,
sum(retain55[2]) as retain_uv_55,
sum(retain56[2]) as retain_uv_56,
sum(retain57[2]) as retain_uv_57,
sum(retain58[2]) as retain_uv_58,
sum(retain59[2]) as retain_uv_59,
sum(retain60[2]) as retain_uv_60,
sum(retain61[2]) as retain_uv_61,
sum(retain62[2]) as retain_uv_62,
sum(retain63[2]) as retain_uv_63,
sum(retain64[2]) as retain_uv_64,
sum(retain65[2]) as retain_uv_65,
sum(retain66[2]) as retain_uv_66,
sum(retain67[2]) as retain_uv_67,
sum(retain68[2]) as retain_uv_68,
sum(retain69[2]) as retain_uv_69,
sum(retain70[2]) as retain_uv_70,
sum(retain71[2]) as retain_uv_71,
sum(retain72[2]) as retain_uv_72,
sum(retain73[2]) as retain_uv_73,
sum(retain74[2]) as retain_uv_74,
sum(retain75[2]) as retain_uv_75,
sum(retain76[2]) as retain_uv_76,
sum(retain77[2]) as retain_uv_77,
sum(retain78[2]) as retain_uv_78,
sum(retain79[2]) as retain_uv_79,
sum(retain80[2]) as retain_uv_80,
sum(retain81[2]) as retain_uv_81,
sum(retain82[2]) as retain_uv_82,
sum(retain83[2]) as retain_uv_83,
sum(retain84[2]) as retain_uv_84,
sum(retain85[2]) as retain_uv_85,
sum(retain86[2]) as retain_uv_86,
sum(retain87[2]) as retain_uv_87,
sum(retain88[2]) as retain_uv_88,
sum(retain89[2]) as retain_uv_89,
sum(retain90[2]) as retain_uv_90,
sum(retain91[2]) as retain_uv_91,
sum(retain92[2]) as retain_uv_92,
sum(retain93[2]) as retain_uv_93,
sum(retain94[2]) as retain_uv_94,
sum(retain95[2]) as retain_uv_95,
sum(retain96[2]) as retain_uv_96,
sum(retain97[2]) as retain_uv_97,
sum(retain98[2]) as retain_uv_98,
sum(retain99[2]) as retain_uv_99,
sum(retain100[2]) as retain_uv_100,
sum(retain101[2]) as retain_uv_101,
sum(retain102[2]) as retain_uv_102,
sum(retain103[2]) as retain_uv_103,
sum(retain104[2]) as retain_uv_104,
sum(retain105[2]) as retain_uv_105,
sum(retain106[2]) as retain_uv_106,
sum(retain107[2]) as retain_uv_107,
sum(retain108[2]) as retain_uv_108,
sum(retain109[2]) as retain_uv_109,
sum(retain110[2]) as retain_uv_110,
sum(retain111[2]) as retain_uv_111,
sum(retain112[2]) as retain_uv_112,
sum(retain113[2]) as retain_uv_113,
sum(retain114[2]) as retain_uv_114,
sum(retain115[2]) as retain_uv_115,
sum(retain116[2]) as retain_uv_116,
sum(retain117[2]) as retain_uv_117,
sum(retain118[2]) as retain_uv_118,
sum(retain119[2]) as retain_uv_119,
sum(retain120[2]) as retain_uv_120,
sum(retain121[2]) as retain_uv_121,
sum(retain122[2]) as retain_uv_122,
sum(retain123[2]) as retain_uv_123,
sum(retain124[2]) as retain_uv_124,
sum(retain125[2]) as retain_uv_125,
sum(retain126[2]) as retain_uv_126,
sum(retain127[2]) as retain_uv_127,
sum(retain128[2]) as retain_uv_128,
sum(retain129[2]) as retain_uv_129,
sum(retain130[2]) as retain_uv_130,
sum(retain131[2]) as retain_uv_131,
sum(retain132[2]) as retain_uv_132,
sum(retain133[2]) as retain_uv_133,
sum(retain134[2]) as retain_uv_134,
sum(retain135[2]) as retain_uv_135,
sum(retain136[2]) as retain_uv_136,
sum(retain137[2]) as retain_uv_137,
sum(retain138[2]) as retain_uv_138,
sum(retain139[2]) as retain_uv_139,
sum(retain140[2]) as retain_uv_140,
sum(retain141[2]) as retain_uv_141,
sum(retain142[2]) as retain_uv_142,
sum(retain143[2]) as retain_uv_143,
sum(retain144[2]) as retain_uv_144,
sum(retain145[2]) as retain_uv_145,
sum(retain146[2]) as retain_uv_146,
sum(retain147[2]) as retain_uv_147,
sum(retain148[2]) as retain_uv_148,
sum(retain149[2]) as retain_uv_149,
sum(retain150[2]) as retain_uv_150,
sum(retain151[2]) as retain_uv_151,
sum(retain152[2]) as retain_uv_152,
sum(retain153[2]) as retain_uv_153,
sum(retain154[2]) as retain_uv_154,
sum(retain155[2]) as retain_uv_155,
sum(retain156[2]) as retain_uv_156,
sum(retain157[2]) as retain_uv_157,
sum(retain158[2]) as retain_uv_158,
sum(retain159[2]) as retain_uv_159,
sum(retain160[2]) as retain_uv_160,
sum(retain161[2]) as retain_uv_161,
sum(retain162[2]) as retain_uv_162,
sum(retain163[2]) as retain_uv_163,
sum(retain164[2]) as retain_uv_164,
sum(retain165[2]) as retain_uv_165,
sum(retain166[2]) as retain_uv_166,
sum(retain167[2]) as retain_uv_167,
sum(retain168[2]) as retain_uv_168,
sum(retain169[2]) as retain_uv_169,
sum(retain170[2]) as retain_uv_170,
sum(retain171[2]) as retain_uv_171,
sum(retain172[2]) as retain_uv_172,
sum(retain173[2]) as retain_uv_173,
sum(retain174[2]) as retain_uv_174,
sum(retain175[2]) as retain_uv_175,
sum(retain176[2]) as retain_uv_176,
sum(retain177[2]) as retain_uv_177,
sum(retain178[2]) as retain_uv_178,
sum(retain179[2]) as retain_uv_179,
sum(retain180[2]) as retain_uv_180,
sum(retain181[2]) as retain_uv_181,
sum(retain182[2]) as retain_uv_182,
sum(retain183[2]) as retain_uv_183,
sum(retain184[2]) as retain_uv_184,
sum(retain185[2]) as retain_uv_185,
sum(retain186[2]) as retain_uv_186,
sum(retain187[2]) as retain_uv_187,
sum(retain188[2]) as retain_uv_188,
sum(retain189[2]) as retain_uv_189,
sum(retain190[2]) as retain_uv_190,
sum(retain191[2]) as retain_uv_191,
sum(retain192[2]) as retain_uv_192,
sum(retain193[2]) as retain_uv_193,
sum(retain194[2]) as retain_uv_194,
sum(retain195[2]) as retain_uv_195,
sum(retain196[2]) as retain_uv_196,
sum(retain197[2]) as retain_uv_197,
sum(retain198[2]) as retain_uv_198,
sum(retain199[2]) as retain_uv_199,
sum(retain200[2]) as retain_uv_200
FROM
(
SELECT
device_hash,
retention( chapterno = 1,
chapterno = 2) as retain1,
retention( chapterno = 2,
chapterno = 3) as retain2,
retention( chapterno = 3,
chapterno = 4) as retain3,
retention( chapterno = 4,
chapterno = 5) as retain4,
retention( chapterno = 5,
chapterno = 6) as retain5,
retention( chapterno = 6,
chapterno = 7) as retain6,
retention( chapterno = 7,
chapterno = 8) as retain7,
retention( chapterno = 8,
chapterno = 9) as retain8,
retention( chapterno = 9,
chapterno = 10) as retain9,
retention( chapterno = 10,
chapterno = 11) as retain10,
retention( chapterno = 11,
chapterno = 12) as retain11,
retention( chapterno = 12,
chapterno = 13) as retain12,
retention( chapterno = 13,
chapterno = 14) as retain13,
retention( chapterno = 14,
chapterno = 15) as retain14,
retention( chapterno = 15,
chapterno = 16) as retain15,
retention( chapterno = 16,
chapterno = 17) as retain16,
retention( chapterno = 17,
chapterno = 18) as retain17,
retention( chapterno = 18,
chapterno = 19) as retain18,
retention( chapterno = 19,
chapterno = 20) as retain19,
retention( chapterno = 20,
chapterno = 21) as retain20,
retention( chapterno = 21,
chapterno = 22) as retain21,
retention( chapterno = 22,
chapterno = 23) as retain22,
retention( chapterno = 23,
chapterno = 24) as retain23,
retention( chapterno = 24,
chapterno = 25) as retain24,
retention( chapterno = 25,
chapterno = 26) as retain25,
retention( chapterno = 26,
chapterno = 27) as retain26,
retention( chapterno = 27,
chapterno = 28) as retain27,
retention( chapterno = 28,
chapterno = 29) as retain28,
retention( chapterno = 29,
chapterno = 30) as retain29,
retention( chapterno = 30,
chapterno = 31) as retain30,
retention( chapterno = 31,
chapterno = 32) as retain31,
retention( chapterno = 32,
chapterno = 33) as retain32,
retention( chapterno = 33,
chapterno = 34) as retain33,
retention( chapterno = 34,
chapterno = 35) as retain34,
retention( chapterno = 35,
chapterno = 36) as retain35,
retention( chapterno = 36,
chapterno = 37) as retain36,
retention( chapterno = 37,
chapterno = 38) as retain37,
retention( chapterno = 38,
chapterno = 39) as retain38,
retention( chapterno = 39,
chapterno = 40) as retain39,
retention( chapterno = 40,
chapterno = 41) as retain40,
retention( chapterno = 41,
chapterno = 42) as retain41,
retention( chapterno = 42,
chapterno = 43) as retain42,
retention( chapterno = 43,
chapterno = 44) as retain43,
retention( chapterno = 44,
chapterno = 45) as retain44,
retention( chapterno = 45,
chapterno = 46) as retain45,
retention( chapterno = 46,
chapterno = 47) as retain46,
retention( chapterno = 47,
chapterno = 48) as retain47,
retention( chapterno = 48,
chapterno = 49) as retain48,
retention( chapterno = 49,
chapterno = 50) as retain49,
retention( chapterno = 50,
chapterno = 51) as retain50,
retention( chapterno = 51,
chapterno = 52) as retain51,
retention( chapterno = 52,
chapterno = 53) as retain52,
retention( chapterno = 53,
chapterno = 54) as retain53,
retention( chapterno = 54,
chapterno = 55) as retain54,
retention( chapterno = 55,
chapterno = 56) as retain55,
retention( chapterno = 56,
chapterno = 57) as retain56,
retention( chapterno = 57,
chapterno = 58) as retain57,
retention( chapterno = 58,
chapterno = 59) as retain58,
retention( chapterno = 59,
chapterno = 60) as retain59,
retention( chapterno = 60,
chapterno = 61) as retain60,
retention( chapterno = 61,
chapterno = 62) as retain61,
retention( chapterno = 62,
chapterno = 63) as retain62,
retention( chapterno = 63,
chapterno = 64) as retain63,
retention( chapterno = 64,
chapterno = 65) as retain64,
retention( chapterno = 65,
chapterno = 66) as retain65,
retention( chapterno = 66,
chapterno = 67) as retain66,
retention( chapterno = 67,
chapterno = 68) as retain67,
retention( chapterno = 68,
chapterno = 69) as retain68,
retention( chapterno = 69,
chapterno = 70) as retain69,
retention( chapterno = 70,
chapterno = 71) as retain70,
retention( chapterno = 71,
chapterno = 72) as retain71,
retention( chapterno = 72,
chapterno = 73) as retain72,
retention( chapterno = 73,
chapterno = 74) as retain73,
retention( chapterno = 74,
chapterno = 75) as retain74,
retention( chapterno = 75,
chapterno = 76) as retain75,
retention( chapterno = 76,
chapterno = 77) as retain76,
retention( chapterno = 77,
chapterno = 78) as retain77,
retention( chapterno = 78,
chapterno = 79) as retain78,
retention( chapterno = 79,
chapterno = 80) as retain79,
retention( chapterno = 80,
chapterno = 81) as retain80,
retention( chapterno = 81,
chapterno = 82) as retain81,
retention( chapterno = 82,
chapterno = 83) as retain82,
retention( chapterno = 83,
chapterno = 84) as retain83,
retention( chapterno = 84,
chapterno = 85) as retain84,
retention( chapterno = 85,
chapterno = 86) as retain85,
retention( chapterno = 86,
chapterno = 87) as retain86,
retention( chapterno = 87,
chapterno = 88) as retain87,
retention( chapterno = 88,
chapterno = 89) as retain88,
retention( chapterno = 89,
chapterno = 90) as retain89,
retention( chapterno = 90,
chapterno = 91) as retain90,
retention( chapterno = 91,
chapterno = 92) as retain91,
retention( chapterno = 92,
chapterno = 93) as retain92,
retention( chapterno = 93,
chapterno = 94) as retain93,
retention( chapterno = 94,
chapterno = 95) as retain94,
retention( chapterno = 95,
chapterno = 96) as retain95,
retention( chapterno = 96,
chapterno = 97) as retain96,
retention( chapterno = 97,
chapterno = 98) as retain97,
retention( chapterno = 98,
chapterno = 99) as retain98,
retention( chapterno = 99,
chapterno = 100) as retain99,
retention( chapterno = 100,
chapterno = 101) as retain100,
retention( chapterno = 101,
chapterno = 102) as retain101,
retention( chapterno = 102,
chapterno = 103) as retain102,
retention( chapterno = 103,
chapterno = 104) as retain103,
retention( chapterno = 104,
chapterno = 105) as retain104,
retention( chapterno = 105,
chapterno = 106) as retain105,
retention( chapterno = 106,
chapterno = 107) as retain106,
retention( chapterno = 107,
chapterno = 108) as retain107,
retention( chapterno = 108,
chapterno = 109) as retain108,
retention( chapterno = 109,
chapterno = 110) as retain109,
retention( chapterno = 110,
chapterno = 111) as retain110,
retention( chapterno = 111,
chapterno = 112) as retain111,
retention( chapterno = 112,
chapterno = 113) as retain112,
retention( chapterno = 113,
chapterno = 114) as retain113,
retention( chapterno = 114,
chapterno = 115) as retain114,
retention( chapterno = 115,
chapterno = 116) as retain115,
retention( chapterno = 116,
chapterno = 117) as retain116,
retention( chapterno = 117,
chapterno = 118) as retain117,
retention( chapterno = 118,
chapterno = 119) as retain118,
retention( chapterno = 119,
chapterno = 120) as retain119,
retention( chapterno = 120,
chapterno = 121) as retain120,
retention( chapterno = 121,
chapterno = 122) as retain121,
retention( chapterno = 122,
chapterno = 123) as retain122,
retention( chapterno = 123,
chapterno = 124) as retain123,
retention( chapterno = 124,
chapterno = 125) as retain124,
retention( chapterno = 125,
chapterno = 126) as retain125,
retention( chapterno = 126,
chapterno = 127) as retain126,
retention( chapterno = 127,
chapterno = 128) as retain127,
retention( chapterno = 128,
chapterno = 129) as retain128,
retention( chapterno = 129,
chapterno = 130) as retain129,
retention( chapterno = 130,
chapterno = 131) as retain130,
retention( chapterno = 131,
chapterno = 132) as retain131,
retention( chapterno = 132,
chapterno = 133) as retain132,
retention( chapterno = 133,
chapterno = 134) as retain133,
retention( chapterno = 134,
chapterno = 135) as retain134,
retention( chapterno = 135,
chapterno = 136) as retain135,
retention( chapterno = 136,
chapterno = 137) as retain136,
retention( chapterno = 137,
chapterno = 138) as retain137,
retention( chapterno = 138,
chapterno = 139) as retain138,
retention( chapterno = 139,
chapterno = 140) as retain139,
retention( chapterno = 140,
chapterno = 141) as retain140,
retention( chapterno = 141,
chapterno = 142) as retain141,
retention( chapterno = 142,
chapterno = 143) as retain142,
retention( chapterno = 143,
chapterno = 144) as retain143,
retention( chapterno = 144,
chapterno = 145) as retain144,
retention( chapterno = 145,
chapterno = 146) as retain145,
retention( chapterno = 146,
chapterno = 147) as retain146,
retention( chapterno = 147,
chapterno = 148) as retain147,
retention( chapterno = 148,
chapterno = 149) as retain148,
retention( chapterno = 149,
chapterno = 150) as retain149,
retention( chapterno = 150,
chapterno = 151) as retain150,
retention( chapterno = 151,
chapterno = 152) as retain151,
retention( chapterno = 152,
chapterno = 153) as retain152,
retention( chapterno = 153,
chapterno = 154) as retain153,
retention( chapterno = 154,
chapterno = 155) as retain154,
retention( chapterno = 155,
chapterno = 156) as retain155,
retention( chapterno = 156,
chapterno = 157) as retain156,
retention( chapterno = 157,
chapterno = 158) as retain157,
retention( chapterno = 158,
chapterno = 159) as retain158,
retention( chapterno = 159,
chapterno = 160) as retain159,
retention( chapterno = 160,
chapterno = 161) as retain160,
retention( chapterno = 161,
chapterno = 162) as retain161,
retention( chapterno = 162,
chapterno = 163) as retain162,
retention( chapterno = 163,
chapterno = 164) as retain163,
retention( chapterno = 164,
chapterno = 165) as retain164,
retention( chapterno = 165,
chapterno = 166) as retain165,
retention( chapterno = 166,
chapterno = 167) as retain166,
retention( chapterno = 167,
chapterno = 168) as retain167,
retention( chapterno = 168,
chapterno = 169) as retain168,
retention( chapterno = 169,
chapterno = 170) as retain169,
retention( chapterno = 170,
chapterno = 171) as retain170,
retention( chapterno = 171,
chapterno = 172) as retain171,
retention( chapterno = 172,
chapterno = 173) as retain172,
retention( chapterno = 173,
chapterno = 174) as retain173,
retention( chapterno = 174,
chapterno = 175) as retain174,
retention( chapterno = 175,
chapterno = 176) as retain175,
retention( chapterno = 176,
chapterno = 177) as retain176,
retention( chapterno = 177,
chapterno = 178) as retain177,
retention( chapterno = 178,
chapterno = 179) as retain178,
retention( chapterno = 179,
chapterno = 180) as retain179,
retention( chapterno = 180,
chapterno = 181) as retain180,
retention( chapterno = 181,
chapterno = 182) as retain181,
retention( chapterno = 182,
chapterno = 183) as retain182,
retention( chapterno = 183,
chapterno = 184) as retain183,
retention( chapterno = 184,
chapterno = 185) as retain184,
retention( chapterno = 185,
chapterno = 186) as retain185,
retention( chapterno = 186,
chapterno = 187) as retain186,
retention( chapterno = 187,
chapterno = 188) as retain187,
retention( chapterno = 188,
chapterno = 189) as retain188,
retention( chapterno = 189,
chapterno = 190) as retain189,
retention( chapterno = 190,
chapterno = 191) as retain190,
retention( chapterno = 191,
chapterno = 192) as retain191,
retention( chapterno = 192,
chapterno = 193) as retain192,
retention( chapterno = 193,
chapterno = 194) as retain193,
retention( chapterno = 194,
chapterno = 195) as retain194,
retention( chapterno = 195,
chapterno = 196) as retain195,
retention( chapterno = 196,
chapterno = 197) as retain196,
retention( chapterno = 197,
chapterno = 198) as retain197,
retention( chapterno = 198,
chapterno = 199) as retain198,
retention( chapterno = 199,
chapterno = 200) as retain199,
retention( chapterno = 200,
chapterno = 201) as retain200
FROM
qrxs_tj.tb_book_chapter_request_record
where
bid = 60292
and cdate between '2021-04-28' and '2021-05-29'
GROUP BY
device_hash )
- 用了327ms
使用位图函数
- sql语句少了很多
select chapterno,bitmapAndCardinality(
neighbor(bmp,-1), bmp
) retetion
FROM (
select chapterno,groupBitmapState(sipHash64(device)) bmp FROM qrxs_tj.tb_book_chapter_request_record
where bid=60292 and cdate between '2021-04-28' and '2021-05-29' and chapterno >0
GROUP BY chapterno order by chapterno asc
)
-
查询结果只需要92ms
-
通过上面的列子一目了然，retention完败
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
