sql查詢藝術(shù)第14講-范圍處理

1、惠普-濟(jì)寧國際軟件人才及產(chǎn)業(yè)基地 SQL查詢藝術(shù)目錄第14講：范圍處理第一節(jié)：缺失范圍求解問題第二節(jié)：現(xiàn)有范圍求解問題第三節(jié)：補(bǔ)充范圍內(nèi)丟失的值第14講：范圍處理第一節(jié)：缺失范圍求解問題缺失范圍求解問題也稱為間斷求解問題，是經(jīng)常用到的問題，該問題在產(chǎn)品系統(tǒng)中的顯現(xiàn)方式有很多種，例如，可用的或不可用的報(bào)表、活動(dòng)或不活動(dòng)的周期、找出缺失的或存在的鍵值的范圍，等等。我們使用NumSeq表來說明間斷求解問題，該表的創(chuàng)建代碼為NumSeq.txt，它代表一個(gè)由唯一值組成的數(shù)字序列，在該表中存在如下數(shù)據(jù)（2），（3），（11），（12），（13），（27），（33），（34），（35），（42）；第14講

2、：范圍處理第一節(jié)：缺失范圍求解問題下表顯示了NumSeq序列中存在的間斷：第14講：范圍處理第一節(jié)：缺失范圍求解問題解決間斷問題有幾種方法，本節(jié)將介紹3種不同的解決方案并討論它們的處理邏輯。解決方案1：使用子查詢解決方案1可以用下列步驟描述：1、找到間斷之前的值，每個(gè)值增加1個(gè)間隔；2、對(duì)于每個(gè)間斷的起點(diǎn)，找出序列中下一個(gè)現(xiàn)有的值，再減去1個(gè)間隔；第14講：范圍處理第一節(jié)：缺失范圍求解問題解決方案1：使用子查詢以下查詢返回位于間斷之前的值：第14講：范圍處理第一節(jié)：缺失范圍求解問題解決方案1：使用子查詢記住，間斷之前的值是指緊接其后的下一個(gè)連續(xù)不存在的值。注意輸出中的最后一行對(duì)我們沒用，因?yàn)樗?/p>

3、其后的間斷是達(dá)到無窮大的那個(gè)間斷（即序列的最大值）。第14講：范圍處理第一節(jié)：缺失范圍求解問題解決方案1：使用子查詢以下查詢返回各間斷的起點(diǎn)，通過為間斷之前的值加1就可以得到該階段的第一個(gè)值，并過濾掉序列中的最大值。第14講：范圍處理第一節(jié)：缺失范圍求解問題解決方案1：使用子查詢最后，對(duì)于每個(gè)間斷中的起點(diǎn)，用一個(gè)子查詢返回序列中下一個(gè)值減1的結(jié)果，也就是說是間斷的終點(diǎn)：第14講：范圍處理第一節(jié)：缺失范圍求解問題解決方案2：使用子查詢解決間斷問題還有一種方法，這種方法比前一種更簡單更直觀，它需要實(shí)現(xiàn)以下步驟：1、對(duì)于每個(gè)現(xiàn)有的值，匹配其下一個(gè)現(xiàn)有的值，生成一對(duì)當(dāng)前值和下一個(gè)值；2、只保留下一個(gè)值

4、減當(dāng)前值大于1個(gè)間隔的值對(duì)；3、對(duì)于剩下的值對(duì)，將每個(gè)當(dāng)前值增加1個(gè)間隔，將每個(gè)下一個(gè)值減去1個(gè)間隔；第14講：范圍處理第一節(jié)：缺失范圍求解問題解決方案2：使用子查詢這種方法利用的是間距大于1個(gè)間隔的相隔的相鄰值表示存在一個(gè)間斷邊界這一事實(shí)。在識(shí)別下一個(gè)現(xiàn)有值的基礎(chǔ)上識(shí)別一個(gè)間隔，這是另一種非常有用的基礎(chǔ)技巧。以下查詢先只為每個(gè)當(dāng)前值返回下一個(gè)值：第14講：范圍處理第一節(jié)：缺失范圍求解問題解決方案2：使用子查詢上述查詢將生成以下輸出：第14講：范圍處理第一節(jié)：缺失范圍求解問題解決方案2：使用子查詢最后，將上一步的查詢作為一個(gè)派生表，只保留nxt-cur大于1的對(duì)。cur加1得到間斷的實(shí)際起點(diǎn)，

5、nxt減1得到間斷的實(shí)際終點(diǎn)：注意，這種解決方案不用特殊處理就去掉了無窮大間斷之前的值，因?yàn)樗膎xt值是NULL。第14講：范圍處理第一節(jié)：缺失范圍求解問題解決方案3：使用排名函數(shù)第三種解決方案與第二種類似，但它使用不同的方法來計(jì)算由當(dāng)前值和下一個(gè)值組成的值對(duì)。它定義一個(gè)CTE，用于按SeqVal的順序?yàn)楦餍蟹峙湫刑?hào)。外部查詢接著聯(lián)接CTE的兩個(gè)實(shí)例，按照當(dāng)前值和下一個(gè)值的行號(hào)相差1的條件進(jìn)行匹配。以下是完整的解決方案：第14講：范圍處理第一節(jié)：缺失范圍求解問題解決方案3：使用排名函數(shù)第14講：范圍處理第二節(jié)：現(xiàn)有范圍求解問題現(xiàn)有范圍求解問題也稱為孤島求解問題，我們還是使用NumSeq表來說

6、明孤島求解問題，該表由唯一值組成的數(shù)字序列，在該表中存在如下數(shù)據(jù)（2），（3），（11），（12），（13），（27），（33），（34），（35），（42）；下表顯示了NumSeq序列中存在的孤島：第14講：范圍處理第二節(jié)：現(xiàn)有范圍求解問題和間斷問題一樣，解決孤島問題也有幾種方法，這里講介紹3種解決方案。孤島問題解決方案1：使用子查詢和排名計(jì)算孤島問題的第一個(gè)解決方案相當(dāng)直接，它包括以下幾步：1、找出位于間斷之后的點(diǎn)，為它們分配行號(hào)，這些點(diǎn)是孤島的起點(diǎn)；2、找出位于間斷之前的點(diǎn)，為它們分配行號(hào)，這些點(diǎn)是孤島的終點(diǎn)；3、以行號(hào)相等作為條件，匹配孤島的起點(diǎn)和終點(diǎn)；以下是針對(duì)唯一數(shù)字序列的解決方案

7、代碼：解決方案代碼第14講：范圍處理第二節(jié)：現(xiàn)有范圍求解問題孤島問題解決方案2：使用基于排名計(jì)算的組標(biāo)識(shí)符這種方法的原理是基于包含間斷的序列和行號(hào)序列之間具有的某種特殊關(guān)系。為了解釋這種方法，首先允許以下查詢，按SeqVal的順序來計(jì)算行號(hào)：第14講：范圍處理第二節(jié)：現(xiàn)有范圍求解問題孤島問題解決方案2：使用基于排名計(jì)算的組標(biāo)識(shí)符該查詢將生成以下輸出：第14講：范圍處理第二節(jié)：現(xiàn)有范圍求解問題孤島問題解決方案2：使用基于排名計(jì)算的組標(biāo)識(shí)符觀察這些數(shù)據(jù)，看看你能否找出SeqVal值的增長方式和行號(hào)的增長方式之間的聯(lián)系。因?yàn)樵诠聧u的范圍內(nèi)，這兩個(gè)序列都以相同的間隔來保持增長，所以這時(shí)它們之間的差將保

8、持不變。只要遇到了一個(gè)新的孤島，它們之間的差就會(huì)增加，因?yàn)镾eqVal值的增量要超過1，而行號(hào)的增量仍然是1.運(yùn)行以下查詢以生成這種差值：第14講：范圍處理第二節(jié)：現(xiàn)有范圍求解問題孤島問題解決方案2：使用基于排名計(jì)算的組標(biāo)識(shí)符第14講：范圍處理第二節(jié)：現(xiàn)有范圍求解問題孤島問題解決方案2：使用基于排名計(jì)算的組標(biāo)識(shí)符可以看到，對(duì)于同一個(gè)孤島內(nèi)的所有成員，這個(gè)差值是相同的；而不同孤島成員之間的差值是不同的。現(xiàn)在，只需要將上述查詢變?yōu)橐粋€(gè)CTE表，然后根據(jù)差值進(jìn)行分組，就可以得到想要的結(jié)果：第14講：范圍處理第三節(jié)：補(bǔ)充范圍內(nèi)丟失的值問題返回1980年起始的十年間每年聘用的員工數(shù)，但有些年份并沒有聘用員工，要求返回下列結(jié)果集：第14講：范圍處理第三節(jié)：補(bǔ)充范圍內(nèi)丟失的值解決方案這個(gè)解決方案的技巧是對(duì)未聘用任何員工的年份返回0。如果某一給定年份沒