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

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

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

首先一點：我們?nèi)绻幸粋€很大的數(shù)據(jù)集而且可以容忍它不精確。一個像 HyperLogLog 的概率統(tǒng)計器可能是你的首選（我們在以后的博客中會講到HyperLogLog ），但是要追求快速精準(zhǔn)的結(jié)果，子查詢的方法會節(jié)省你很多時間。

讓我們從一個簡單的查詢語句開始吧：哪一個dashboard用戶訪問的最頻繁。

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多很多。

僅僅一千萬行數(shù)據(jù)，查詢語句就花費了48秒的時間。知道為什么嗎？讓我們看一下明了的圖解吧。

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

分組和聚集之后，一切數(shù)據(jù)庫操作的代價都變小了，因為數(shù)據(jù)的數(shù)量變小了。在分組和聚集的時候，因為我們不需要dashboards.name，所以我們可以在JOIN操作前先進行聚集操作：

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

語句運行了24秒，獲得了2.4倍的性能提高。在來看一下，圖解可以清楚無誤的表明原因。

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

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

不用做那么多工作，只用一個哈希集合，我們就可以先去除那些重復(fù)的值。然后我們在這個結(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

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

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

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

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

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

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