Clickhouse 留存方法对比retention 和bitmapAndCardinality

最近在做留存统计，看了一篇使用位图函数写留存的，效率很高；

表结构

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完败