在資料庫表中，存儲的資料經常是稀疏資料(sparse data)，而不是稠密資料(dense data)。先來了解一下什麼是稀疏資料，比如一個産品銷售情況表(比如有産品名、銷售時間(精确到年月)、銷售量3個列)，假設某個時間某些産品它沒有銷售，一般也不會将這些産品的銷售量存儲為0，而是不存儲，這樣在産品銷售情況表中就會産生很多缺失的行(gap rows)，導緻的結果就是特定産品銷售資料按時間次元進行排序，是不連續的，或者說此産品銷售在時間序列上是有缺失的。顧名思義，稠密資料是相對于稀疏資料來說的，還是用上面的假設說明，也就是說産品在某個時間沒有銷售，也必須存儲此産品銷售情況，銷售量置0存儲，這樣對某個特定産品來說它在時間序列就是連續的，但是事實經常不是如此，是以才有将稀疏資料稠密化的過程，資料稠密化在資料倉庫應用中很常見。
當然銷售情況表隻是一個典型的情況，在實際應用中，有各種各樣的缺失資料情況。如果決策者看銷售情況統計表，他可不希望有的産品按時間序列斷斷續續，而應該給他提供時間序列連續的分析報表，他可能需要看到每個産品每個時間的銷售情況，就算在某個時間沒有銷售，也必須置0，這樣的報表對決策者才有意義，而且可以進行更細粒度的分析，比如使用分析函數對每個産品按年月彙總計算銷售偏移量，這樣可以友善對比每個産品每個月的銷售情況，進而為決策支援提供強大保障。
為了實作将稀疏資料轉為稠密資料，oracle10g提供了partitioned outer join文法，和一般的outer join類似(但是不支援full outer join，隻支援left和right兩種)，隻不過增加了partition by的文法，根據partition by将表邏輯分區，然後對每個分區進行outer join，這樣就可以達到填補缺失行，實作資料稠密化的目的，也相當于對每個分區裡的資料outer join後進行union操作，了解這個很重要，否則經常不知道到底是哪個表哪些列該分區，不知道到底是用left join還是用right join，在後面的例子會詳細分析這個文法如何使用。
目錄
partitioned outer join執行個體
partitioned outer join總結
1partitioned outer join文法
partitioned outer join文法如下：
partitioned outer join文法很簡單，也就是在join的表後面on條件之前加入partition by語句即可。上面隻列出了最簡單的兩表(内聯視圖，視圖等其他結果集)連接配接，多個對象的連接配接類似，其他複雜的文法結構省略了，文法結構上partition by是可以放在任何合法連接配接對象後面的，而且和一般的partition by沒有差別，可以有多個分區列(表達式)，然後用外連接配接，注意一定要搞清楚是用left join還是用right join，比如第1個文法結構在join之前的對象使用了partition by，那麼就是對第1個對象填充缺失資料，是以必須用right join，第2個文法結構類似。
當然也可以直接用join，不用outer join，但是這樣無法填充缺失資料，沒有意義，另外注意不能使用86的外連接配接文法+，這是不行的，必須使用92文法。一般來說，根據需求确定partition by的鍵值，partition by語句要緊跟需要分區的對象後面，然後根據partition by的位置決定用left join還是right join，否則可能會出錯或獲得不正确的結果，如果要起到分區外連接配接的效果，必須牢牢按照上面兩種寫法來（partition端要緊跟對應表，并且是非基表），後面會詳細分析其他寫法的問題。
2 partitioned outer join執行個體
本節主要從相關執行個體中研究partitioned outer join的使用，主要執行個體有填充一維缺失資料、填充多元缺失資料、填充資料到清單表中等。例子中的建表等語句請參考代碼poj.sql。
poj.sql
1) 填充一維缺失資料
t表是一個産品銷售情況表，資料如下：
上面的表資料是很簡單的，在實際應用中，這個資料可能是語句的中間結果。從結果上可以看到，有2008年1、2、3這3個月的銷售資料，但是有些産品的銷售資料在某些月份是缺失的，比如2008年1月産品c就沒有資料。現在需要一個報表，能夠填充所有産品對應2008年前3月缺失的資料，銷售字段sales置0，要實作這樣的報表，如何做呢?
先來看下傳統做法：既然填充每個産品對應月份缺失的資料，那麼肯定需要構造一個結果集存儲了每個産品每個時間對應的資料，這樣再與原始表外連接配接，則可以達到填充缺失資料的目的，為了實作這個目的，很容易想到需要将表中對應的時間year、month與産品做笛卡爾積(每個部分資料都是唯一的，是這樣的資料做笛卡爾積)，生成每個産品每個時間的結果資料，然後與原始表外連接配接。下面用sql實作：
傳統填充缺失資料，往往就要通過笛卡爾積構造完整資料集，然後與原始表外連接配接。根據上面的sql，這個結果應該是生成所有産品所有年月的銷售資料，如果原始表中沒有，則對應缺失年月的資料為0，執行上面的sql結果為：
現在填充了3行缺失資料，實作了所有産品對應2008年前3月時間序列上的稠密化報表目的，你是否發現到傳統做法比較複雜，這裡是很簡單的一維缺失資料的填充，如果是多元缺失資料填充呢?在實際應用中sql經常很複雜，這個銷售表t也許都是sql的中間結果，那麼這樣的做法需要通過笛卡爾積生成所有組合情況，性能可能不好，而且sql比較複雜。
下面看10g對填充資料專門做的改進，使用partitioned outer join實作資料稠密化工作，更加簡單，而且往往性能往往要比傳統做法要好。通過前面對partituoned outer join的分析以及傳統實作資料稠密化的方法，使用partitioned outer join隻需要對産品進行分區然後和所有時間外連接配接，則可以補全缺失資料，如下：
一定要了解partitioned outer join的兩種文法結構，這裡的partition by是緊跟在表t後面的，相當于對每個按product_name分區的每個分區内的行和中間結果m外連接配接，這樣就能補起資料了，相當于每個按product_name劃分的行與m外連接配接的union all結果，通過這個例子，就可以很好地了解partitioned outer join的使用，這樣你就能正确用多種方法進行改寫了。這個語句的結果和上面的一緻，不再列出。如果你了解了上面說的話，就可以使用left join來改寫：
執行結果和上面的right join完全一樣的，為什麼上面又變成left join了呢?原因是現在t partition by移到join後面了，當然要左連接配接所有的時間才可以填充缺失資料，是以要使用第2種文法結構。下面看下此語句的執行計劃：
partitioned outer join的效果展現在第3到第8步，其中第8步就是将資料排序然後放入分區内，第3步就是外連接配接産生填充後的結果集，當然這裡的merge join可以為nested loop join,也可以使用hint,比如use_nl(m,t)來讓它走nested loop partition outer。這個執行計劃會與後面改寫的語句執行計劃做對比，如果沒有第3步和第8步，那麼partitioned outer join是不起作用的。使用partitioned outer join的過程如圖所示：
上面語句的實作的功能就是3個外連接配接的union all結果，其他複雜的資料稠密化以此類推。其實10g的model也可以實作資料填充，但是model語句比較複雜，比如上面可以用model簡單改寫為：
如果是多元或其它複雜情況的改寫，會很麻煩，對于資料稠化建議使用使用10g的partitioned outer join。
如果你的分區外連接配接寫法不遵守兩種文法結構，那麼可能寫的語句不報錯，但是結果卻不是正确的（起不到分區效果，計劃裡沒有partition outer），當然也有可能出錯，比如上面的right join改寫為：
上面語句不會報錯，但是結果：
第一條語句是将本來應該放在表t後面的partition by移到了m後面，沒有實作填充缺失行的目的，原因是oracle對這種文法結構不會按照partitioned outer join實作填充行的目的進行支援。第二條語句是對基表進行partition by 操作，應該是對非基表，改為right join才對。看下執行計劃就明白了，計劃如下：
上面這個語句的計劃和去掉partition by的語句計劃完全一緻，沒有原來的第3步和第8步的partition操作。為什麼我這麼強調partition outer join的文法結構呢?因為如果不了解這個文法結構，必然會導緻不正确的結果，如果了解了這個文法結構，那麼一切就變得很簡單，其他改寫的錯誤類似，還有一種錯誤是直接報錯，如下:
為什麼會出錯，因為這裡是left join，那麼基表是t，在m後面使用partition by并且引用了t的字段，那麼是引用不到的，是以出錯，如果是right join，則正确，但是又不符合文法結構，導緻partition by白寫。隻有前面說的兩種正确寫法才是對的，特别在多表連接配接以及多元度填充缺失行的時候一定要注意partition by的位置和其引用的字段有關，一定要放在緊跟要引用的對象後面，然後根據文法結構規則使用left join還是right join，否則要麼不正确，要麼錯誤。
這種文法很奇怪，left join不可以将partition by放在非基表端，并且partition by裡引用基表列（上面情況），但是可以把partition by放在基表端，引用非基表列。但是也起不到填充效果，rifht join相反。
ok，現在已經實作了資料稠密化工作，那麼稠密化工作的意義何在呢?比如要做按時間序清單示銷售情況波動圖，要求每個産品每個時間序列上都有資料，不産生gap值，是很有意義的，也可以進一步對資料進行明細分析，比如使用分析函數分析對比當月和上月的銷售情況，決策人員看到所有産品所有時間點的資料，這樣可以很好地做決策，如果你給他缺失行的分析報表，他怎麼能看到某個時間點某個産品沒有銷售呢，如何分析造成此情況的原因呢？下面就做一個使用分析函數對比銷售情況的報表：
結果如下：
現在這個報表是不是很有意義了呢!決策者可以專門對add_last_sales<=0的資料做分析，找出原因，進而改進銷售。
2) 填充多元缺失資料
在例1中已經詳細說了使用partitioned outer join解決一維缺失資料填充問題，重點講解了如何了解partitioned outer join的文法結構以及注意一些錯誤問題，其實，了解了例1的内容，多元缺失資料填充思想完全一緻，下面就在例1的基礎上對銷售表t增加一個字段regions，然後填充多元缺失資料。
現在按年、産品、區域彙總銷售額，結果如下：
這個報表不是我想看的最終報表，我需要的報表是稠密報表，也就是對于每個産品，橫跨所有年、所有區域都應該有彙總資料，但是現在缺失了很多資料，比如對于産品a來說，少了07年4個區域的彙總資料，08年少了北區的彙總資料，最終的報表資料應該有年數目(2)*産品數(3)*區域數(4)=24行記錄。
現在需要填補每個産品所有年份和所有區域2維缺失資料，那麼怎麼做呢?首先需要補全每個産品所有年或者每個産品所有區域的資料，然後按産品和年分區或産品和區域分區再與所有區域或所有年外連接配接，這樣就能填充缺失資料了，所有對2維缺失資料填充可以用2步partitioned outer join，當然也可以将年份和區域做笛卡爾積，然後轉為1維資料填充，這樣的好處很明顯，因為年份和區域很少，這樣可以減少表的掃描，強烈推薦此方法，見方法4，其他次元的類似。
第3個方法效率要比前2個好點，第4個方法明顯最好，因為年份和區域的資料很少，笛卡爾積的資料量也不大，但是如果用兩次partition outer join明顯掃描的資料增多。這裡的測試表也沒有建立索引，具體調整和優化過程可以根據實際情況選擇，上面4個sql的結果一緻，如下：
現在對所有産品實作了時間(年)和區域這兩個次元的彙總資料填充，總共有24行，符合預期分析的結果。讀者應該掌握上面說的從一維到多元資料稠密化的方法，當然，多元資料稠密化也可以用傳統方法解決，這裡不再叙述。
3) 使用last_value填充缺失資料
還有一種填充缺失資料的常用方法是使用last_value+ignore nulls(注意使用first_value含義就不一樣了，找的就不是最近行了)，對指定次元的資料有缺失，則使用最近時間的資料填充，這在報表中是很常見的，這樣的報表可以有很好的分析和對比功能。比如：
現在需求是：首先對每個産品的所有時間(精确到天)銷售實作資料填充，其次如果當天沒有銷售，則用此産品最近時間的銷售資料填充或者增加一行顯示當天沒有銷售的産品的最近時間銷售資料。對于實作次元資料的填充，前面已經說了使用partitioned outer join，實作産品沒有銷售則找最近時間此産品的銷售，想到了可以使用分析函數last_value+ignore nulls實作,11g的lag也可以加ignore nulls實作(同樣不能用lead)。sql如下：
顯示結果如下：
現在實作了上述需求，還有一行recent_sales為空，因為它是産品b的第1個時間點的資料。使用last_value+ignore nulls結合partitioned outer join實作上述報表是很常見的，實作報表資料稠密化思想主要就是上面這些，當然model語句也可以實作，但是沒有partitioned outer join簡單。
3partitioned outer join總結
傳統方法填補缺失資料，經常需要使用笛卡爾積構造中間結果，然後與原始資料外連接配接，往往性能不是很好，而且sql比較複雜，10g提供的partitioned outer join的文法簡單，可以高效地實作報表資料稠化，使用partitioned outer join一定要掌握文法結構中的2種結構：首先确定分區鍵，然後确定使用left join還是right join，此文法結構對full join不支援。另外model等文法也可以實作類似的功能，但是與partitioned outer join相比，就複雜多了，為了很好地使用partitioned outer join實作資料稠化，一定要分析清楚需求，然後根據本部分說的使用步驟以及一些注意點，比如如何高效地使用distinct構造結果集(常自己構造或從關聯表擷取)，這樣才能正确高效地實作報表資料的稠化。
作者介紹：丁俊
【dba+社群】聯合發起人，新炬網絡專家團成員。
性能優化專家，oracle acea，itpub開發版資深版主、itpub 2010-2013連續4屆最佳精華獲得者、2011-2014連續4屆最佳版主。
十年電信行業從業經驗，從事過系統開發與維護、業務架構和資料分析、系統優化等工作。
電子工業出版社終身榮譽作者，《劍破冰山-oracle開發藝術》副主編。
<b>本文來自雲栖社群合作夥伴"dbaplus"，原文釋出時間：2016-01-28</b>