摘要：很有用，但是卻很慢計(jì)算非重復(fù)的數(shù)目是分析的一個(gè)災(zāi)難，顯然，我們要在第一篇博文上討論。在來(lái)看一下，圖解可以清楚無(wú)誤的表明原因。也許你下次計(jì)算非重復(fù)結(jié)果需要花費(fèi)一天的時(shí)間，試著用子查詢(xún)的方法減輕它的負(fù)載。

本文說(shuō)的這個(gè)技術(shù)是通用的，但為了解釋說(shuō)明，我們選用了 PostgreSQL。感謝 pgAdminIII 提供的解釋性插圖，這些插圖有很大幫助。

計(jì)算非重復(fù)的數(shù)目是SQL分析的一個(gè)災(zāi)難，顯然，我們要在第一篇博文上討論。

首先一點(diǎn)：我們?nèi)绻幸粋€(gè)很大的數(shù)據(jù)集而且可以容忍它不精確。一個(gè)像 HyperLogLog 的概率統(tǒng)計(jì)器可能是你的首選（我們?cè)谝院蟮牟┛椭袝?huì)講到HyperLogLog ），但是要追求快速精準(zhǔn)的結(jié)果，子查詢(xún)的方法會(huì)節(jié)省你很多時(shí)間。

讓我們從一個(gè)簡(jiǎn)單的查詢(xún)語(yǔ)句開(kāi)始吧：哪一個(gè)dashboard用戶(hù)訪問(wèn)的最頻繁。

select
  dashboards.name, 
  count(distinct time_on_site_logs.user_id)
from time_on_site_logs 
join dashboards on time_on_site_logs.dashboard_id = dashboards.id
group by name
order by count desc

首先，讓我們假設(shè)在user_id 和 dashboard_id上都有高效的索引，并且日志行數(shù)要比user_id 和 dashboard_id多很多。

僅僅一千萬(wàn)行數(shù)據(jù)，查詢(xún)語(yǔ)句就花費(fèi)了48秒的時(shí)間。知道為什么嗎？讓我們看一下明了的圖解吧。

慢的原因是數(shù)據(jù)庫(kù)要遍歷dashboards表和logs表的所有記錄，然后JOIN操作，然后排序，之后才進(jìn)行實(shí)際需要的分組和聚集操作。

分組和聚集之后，一切數(shù)據(jù)庫(kù)操作的代價(jià)都變小了，因?yàn)閿?shù)據(jù)的數(shù)量變小了。在分組和聚集的時(shí)候，因?yàn)槲覀儾恍枰猟ashboards.name，所以我們可以在JOIN操作前先進(jìn)行聚集操作：

select
  dashboards.name,
  log_counts.ct
from dashboards
join (
  select
    dashboard_id,
    count(distinct user_id) as ct
  from time_on_site_logs 
  group by dashboard_id
) as log_counts 
on log_counts.dashboard_id = dashboards.id
order by log_counts.ct desc

語(yǔ)句運(yùn)行了24秒，獲得了2.4倍的性能提高。在來(lái)看一下，圖解可以清楚無(wú)誤的表明原因。

像我們預(yù)期地那樣，join操作之前先進(jìn)行了group-and-aggregate操作?？焐霞涌?，我們還可以在time_on_site_logs 表上加上索引。

我們還可以做的更好，我們對(duì)日志表做group-and-aggregate操作時(shí)，我們處理了一些無(wú)關(guān)的數(shù)據(jù)，其實(shí)沒(méi)有必要。我們可以對(duì)每個(gè)分組上創(chuàng)建一個(gè)哈希集合,這樣，在每個(gè)哈希桶中讓每個(gè)dashboard_id 挑出那些需要被看到處理的數(shù)據(jù)。

不用做那么多工作，只用一個(gè)哈希集合，我們就可以先去除那些重復(fù)的值。然后我們?cè)谶@個(gè)結(jié)果上做聚集操作。

select
  dashboards.name,
  log_counts.ct
from dashboards 
join (
  select distinct_logs.dashboard_id, 
  count(1) as ct
  from (
    select distinct dashboard_id, user_id
    from time_on_site_logs
  ) as distinct_logs
  group by distinct_logs.dashboard_id
) as log_counts 
on log_counts.dashboard_id = dashboards.id
order by log_counts.ct desc

我們讓去重和分組聚集一步一步進(jìn)行，分成兩個(gè)階段。首先在(dashboard_id, user_id)對(duì)上去重，然后在這基礎(chǔ)上做簡(jiǎn)單快速的分組計(jì)算工作，JOIN操作還是放在最后。

讓我們來(lái)揭曉最終效果：總共花費(fèi)了0.7秒，是上一次的28倍，最初的68倍。

一般來(lái)說(shuō)，數(shù)據(jù)大小和數(shù)據(jù)位置是很重要的，表中的屬性字段的可能取值個(gè)數(shù)相對(duì)很少，所以才有那么明顯的效果，與數(shù)據(jù)總量相比較，(user_id, dashboard_id) 對(duì)的不同值很少。越多的不同的值，各行的數(shù)據(jù)越分散，所以分組和計(jì)算它們花費(fèi)越長(zhǎng)的時(shí)間，果然天下沒(méi)有白吃的午餐。

也許你下次計(jì)算非重復(fù)結(jié)果需要花費(fèi)一天的時(shí)間，試著用子查詢(xún)的方法減輕它的負(fù)載。

我們做了 Periscope，一個(gè)可以使SQL數(shù)據(jù)分析更快的工具。我們?cè)谶@里分享一下我們的工具蘊(yùn)含的算法和技術(shù)。你可以到我們的主頁(yè)上注冊(cè)，從而作為我們的新客戶(hù)，我們可以通知你相關(guān)事宜。

原文：Use Subqueries to Count Distinct 50X Faster
轉(zhuǎn)載于：伯樂(lè)在線 - sunbiaobiao