Oracle收集統(tǒng)計(jì)信息的一些思考( 二 ) _生活百科

三、采樣算法以下是個(gè)人的一些娛樂(lè)性思考

等比放大，即(采樣得到distinct值 / 采樣行數(shù)) x 總行數(shù) 。舉個(gè)例子，假設(shè)表有1000行數(shù)據(jù) ，只采樣100行， A列有95個(gè)不同的值，即count(distinct A) / count(A) = 95% ，那么等比放大很容易推導(dǎo)出1000行數(shù)據(jù) ，有950個(gè)A的不同值。但是如果這100行中B列只有2個(gè)不同的值，即count(distinct B) / count(B) = 2% ，那么對(duì)于1000行的表來(lái)講， B的不同值是不是等于2% * 1000呢？很有可能不是，說(shuō)不定全表就這兩個(gè)不同值，例如性別。所以通過(guò)等比放大得到的distinct值就不準(zhǔn) 。這種算法有明顯的缺陷。
按增長(zhǎng)率估算，即將采樣得到的前5%作為一個(gè)基數(shù) ，采樣得到的后5%作為一個(gè)增長(zhǎng)率。（假設(shè)采樣比例是10%）還是舉個(gè)例子，假設(shè)表有1000行數(shù)據(jù) ，只采樣100行，采樣的前50行， C列有40個(gè)不同值。采樣的后50行， C列又多了30個(gè)不同值，即總共有70個(gè)不同值。那么后面的90%都會(huì)保持這個(gè)增長(zhǎng)速度。則總體的C列不同值為40 + 30 * ((100-5)/5) = 610 。再來(lái)看一種情況，假設(shè)采樣的前50行， D列有2個(gè)不同值。采樣的后50行， D列多了0個(gè)不同值，即不同值總數(shù)保持不變。那么后面的90%都會(huì)保持這個(gè)增長(zhǎng)速度。則總體的D列不同值仍為2 。這種方式似乎比等比采樣更加合乎實(shí)際情況一點(diǎn) 。接下來(lái)就用python去實(shí)現(xiàn)這個(gè)算法，看看與oracle的估算差別有多大。以下為python代碼。

import randomimport cx_Oracledef func(ins):SAMPLE_PERCENT = 10# 采樣比例%sample_size = int(len(ins) * SAMPLE_PERCENT / 100)# 對(duì)數(shù)據(jù)進(jìn)行采樣sample = random.sample(ins, sample_size)head_half_sample = sample[0:int(len(sample)/2)]# 采樣數(shù)據(jù)的前一半head_half_sample_distinct = len(set(head_half_sample))# 采樣數(shù)據(jù)的前一半的distinct值full_sample_distinct = len(set(sample))# 采樣數(shù)據(jù)的全量distinct值tail_half_inc = full_sample_distinct - head_half_sample_distinct# 采樣數(shù)據(jù)的distinct增量estimate_distinct = round(head_half_sample_distinct + tail_half_inc * (100 - SAMPLE_PERCENT/2) / (SAMPLE_PERCENT/2))return estimate_distinctdef test(colname):DATABASE_URL = 'xxxxx'conn = cx_Oracle.connect(DATABASE_URL)curs = conn.cursor()sql = 'select {} from t1'.format(colname)curs.execute(sql)tmpdata = https://www.huyubaike.com/biancheng/[]for i in curs.fetchall():tmpdata.append(i[0])res = func(tmpdata)curs.close()conn.close()return resfor i in ['OBJECT_NAME', 'SUBOBJECT_NAME', 'TIMESTAMP', 'LAST_DDL_TIME', 'CREATED', 'NAMESPACE', 'OBJECT_TYPE','OWNER', 'TEMPORARY', 'DUPLICATED', 'STATUS', 'SHARDED', 'GENERATED', 'SECONDARY', 'SHARING', 'EDITIONABLE','ORACLE_MAINTAINED', 'APPLICATION', 'DEFAULT_COLLATION', 'DATA_OBJECT_ID', 'OBJECT_ID']:print(i, '估算的distinct->', test(i))

運(yùn)行結(jié)果

文章插圖
再來(lái)跟之前的一個(gè)表格進(jìn)行對(duì)比，按增長(zhǎng)率的方式估算的distinct值看上去也能接受。

COLUMN_NAME實(shí)際的distinct數(shù)據(jù)庫(kù)估算的distinctpython估算的distinct------------------------------ -------------------------------------------------------OBJECT_NAME645521030074865SUBOBJECT_NAME10153851210TIMESTAMP258512405641LAST_DDL_TIME249012575536CREATED231212094983NAMESPACE211589OBJECT_TYPE453992OWNER8071176TEMPORARY222DUPLICATED111STATUS222SHARDED111GENERATED222SECONDARY111SHARING444EDITIONABLE223ORACLE_MAINTAINED222APPLICATION111DEFAULT_COLLATION112DATA_OBJECT_ID777857810077752OBJECT_ID145212146100145178

限于時(shí)間，測(cè)試到此結(jié)束。后面有時(shí)間再學(xué)點(diǎn)統(tǒng)計(jì)相關(guān)的知識(shí) 。