Oracle資料函式庫進階SQL技巧

在 Oracle 資料函式庫中，處理複雜的資料轉換和字串操作常常需要運用一些進階的 SQL 技巧。本文將介紹如何使用 MODEL 子句、內建函式以及其他 SQL 語法來完成這些任務，例如資料轉置、字串解析、日期計算和特定字串搜尋等。這些技巧可以幫助開發者更有效率地處理資料，並提升 SQL 查詢的效能。以下將針對每個技巧提供詳細的說明和程式碼範例。

使用Oracle的MODEL子句轉置結果集

在處理資料時，經常需要將結果集從列的形式轉換為行的形式，或者反之亦然。Oracle的MODEL子句提供了一種強大的方法來實作這種轉換。本文將透過一個具體的例子來說明如何使用MODEL子句來轉置結果集。

問題描述

假設我們有一個名為emp的表，包含員工的資訊，其中deptno欄位表示部門編號。我們想要統計每個部門的員工數量，並將結果以部門編號為列標題進行展示。

原始查詢結果如下：

| DEPTNO | CNT | |

-|

–| | 10 | 3 | | 20 | 5 | | 30 | 6 |

期望的結果是：

| D10 | D20 | D30 | |

–|

–|

–| | 3 | 5 | 6 |

解決方案

為瞭解決這個問題，我們可以使用Oracle的MODEL子句結合聚合函式和CASE表示式。以下是具體的SQL查詢：

SELECT 
    MAX(d10) AS d10,
    MAX(d20) AS d20,
    MAX(d30) AS d30
FROM (
    SELECT 
        d10, d20, d30
    FROM (
        SELECT 
            deptno, 
            COUNT(*) AS cnt 
        FROM 
            emp 
        GROUP BY 
            deptno
    )
    MODEL 
        DIMENSION BY (deptno AS d)
        MEASURES (deptno, cnt AS d10, cnt AS d20, cnt AS d30)
        RULES (
            d10[ANY] = CASE WHEN deptno[CV()] = 10 THEN d10[CV()] ELSE 0 END,
            d20[ANY] = CASE WHEN deptno[CV()] = 20 THEN d20[CV()] ELSE 0 END,
            d30[ANY] = CASE WHEN deptno[CV()] = 30 THEN d30[CV()] ELSE 0 END
        )
);

內容解密：

1. 內部檢視：首先，內部檢視計算每個部門的員工數量。

   • SELECT deptno, COUNT(*) AS cnt FROM emp GROUP BY deptno 統計每個部門的員工數量。

2. MODEL子句：

   • DIMENSION BY (deptno AS d) 定義了陣列的索引，即部門編號。
   • MEASURES (deptno, cnt AS d10, cnt AS d20, cnt AS d30) 定義了陣列，包括部門編號和三個別名（d10、d20、d30）用於存放員工數量。
   • RULES 部分根據部門編號將相應的員工數量存入對應的陣列中。

3. CASE表示式：在RULES子句中，CASE表示式用於根據部門編號將正確的計數存入相應的陣列（d10、d20、d30）中。

4. 最終結果：外部查詢使用MAX聚合函式來取得轉置後的結果，因為內部查詢已經將資料存入了對應的陣列中。

結果與討論

透過使用MODEL子句，我們成功地將原始結果集從行形式轉換為列形式，達到了預期的結果。這種方法不僅展示了Oracle SQL的強大功能，也提供了一種靈活的資料處理手段。

在這個例子中，MODEL子句的使用使得資料轉換變得簡單直接。透過定義陣列和規則，我們能夠精確控制資料如何被轉換和呈現。這對於需要進行複雜資料處理和分析的應用場景尤其有價值。

圖表翻譯：

圖表翻譯： 此圖示呈現了資料處理的流程，從原始資料開始，經過統計部門員工數量，使用MODEL子句進行結果轉置，最終得到所需的結果格式。

從非固定位置的字串中提取元素

在處理字串資料時，經常會遇到需要從字串中提取特定資訊的情況。這些資訊可能被夾在特定的字元之間，例如方括號 []。本篇文章將介紹如何使用 Oracle 的內建函式 INSTR 和 SUBSTR 來實作這一功能。

問題描述

假設我們有一個字串欄位，其中包含序列化後的記錄資料。我們需要從這些字串中解析出相關的資訊。這些資訊的位置並不固定，但我們知道它們被方括號 [] 包圍。以下是一些範例字串：

• xxxxxabc[867]xxx[-]xxxx[5309]xxxxx
• xxxxxtime:[11271978]favnum:[4]id:[Joe]xxxxx
• call:[F_GET_ROWS()]b1:[ROSEWOOD...SIR]b2:[44400002]77.90xxxxx
• film:[non_marked]qq:[unit]tailpipe:[withabanana?]80sxxxxx

我們的目標是提取方括號中的值，傳回以下結果：

| FIRST_VAL | SECOND_VAL | LAST_VAL | |

|

–|

-| | 867 | - | 5309 | | 11271978 | 4 | Joe | | F_GET_ROWS() | ROSEWOOD…SIR | 44400002 | | non_marked | unit | withabanana? |

解決方案

儘管我們不知道所需值的確切位置，但我們知道它們位於方括號 [] 之間，並且有三個這樣的括號。使用 Oracle 的內建函式 INSTR 來找到括號的位置，然後使用 SUBSTR 函式來提取字串中的值。

首先，建立一個檢視 V 來包含需要解析的字串：

CREATE VIEW V AS
SELECT 'xxxxxabc[867]xxx[-]xxxx[5309]xxxxx' msg FROM dual
UNION ALL
SELECT 'xxxxxtime:[11271978]favnum:[4]id:[Joe]xxxxx' msg FROM dual
UNION ALL
SELECT 'call:[F_GET_ROWS()]b1:[ROSEWOOD...SIR]b2:[44400002]77.90xxxxx' msg FROM dual
UNION ALL
SELECT 'film:[non_marked]qq:[unit]tailpipe:[withabanana?]80sxxxxx' msg FROM dual;

提取值的 SQL 查詢

SELECT 
    SUBSTR(msg, INSTR(msg, '[', 1, 1) + 1, INSTR(msg, ']', 1, 1) - INSTR(msg, '[', 1, 1) - 1) AS first_val,
    SUBSTR(msg, INSTR(msg, '[', 1, 2) + 1, INSTR(msg, ']', 1, 2) - INSTR(msg, '[', 1, 2) - 1) AS second_val,
    SUBSTR(msg, INSTR(msg, '[', -1, 1) + 1, INSTR(msg, ']', -1, 1) - INSTR(msg, '[', -1, 1) - 1) AS last_val
FROM V;

#### 程式碼解密：

• INSTR(msg, '[', 1, 1)：找出訊息中第一個左方括號 [ 的位置。
• INSTR(msg, ']', 1, 1)：找出訊息中第一個右方括號 ] 的位置。
• SUBSTR(msg, start_pos + 1, end_pos - start_pos - 1)：提取第一對方括號中的內容。start_pos + 1 是為了跳過左方括號，而 end_pos - start_pos - 1 是為了計算括號內內容的長度。
• 對第二個和最後一個值，重複類別似的過程，但調整 INSTR 的引數以找到對應的括號位置。

結果與討論

使用上述 SQL 查詢，我們可以成功地從給定的字串中提取出所需的資訊。這種方法的關鍵在於正確地使用 INSTR 和 SUBSTR 函式來定位和提取方括號中的內容。

在實際應用中，這種技術可以用於處理日誌資料、序列化資料等場景下的字串解析任務，具有很高的實用價值。

使用 Oracle 的 MODEL 子句進行資料轉換

除了字串解析外，Oracle 的 MODEL 子句還可以用於進行複雜的資料轉換。

使用 MODEL 子句進行資料轉換的範例

假設我們有一個員工表 emp，並且想要統計每個部門的員工數量。

SELECT deptno, COUNT(*) cnt FROM emp GROUP BY deptno;

使用 MODEL 子句進行轉換

SELECT MAX(d10) d10, MAX(d20) d20, MAX(d30) d30
FROM (
    SELECT d10, d20, d30
    FROM (
        SELECT deptno, COUNT(*) cnt FROM emp GROUP BY deptno
    )
    MODEL 
        DIMENSION BY (deptno d)
        MEASURES (deptno, cnt d10, cnt d20, cnt d30)
        RULES (
            d10[ANY] = CASE WHEN deptno[CV()] = 10 THEN d10[CV()] ELSE 0 END,
            d20[ANY] = CASE WHEN deptno[CV()] = 20 THEN d20[CV()] ELSE 0 END,
            d30[ANY] = CASE WHEN deptno[CV()] = 30 THEN d30[CV()] ELSE 0 END
        )
);

#### 程式碼解密：

• DIMENSION BY (deptno d)：將 deptno 定義為維度。
• MEASURES (deptno, cnt d10, cnt d20, cnt d30)：定義需要操作的欄位。
• RULES 部分根據 deptno 的值，將對應的計數分配到 d10, d20, 和 d30 中。
• 外層查詢使用 MAX 聚合函式來取得每個部門的計數。

這種方法展示瞭如何使用 Oracle 的 MODEL 子句來進行資料的重塑和轉換，適用於需要進行複雜資料操作的場景。

計算一年中的天數與搜尋混合字母數字字串

計算一年中的天數

要計算某一年有多少天，可以使用Oracle的日期函式。首先，利用TRUNC函式將給定的日期截斷到該年的第一天。然後，使用ADD_MONTHS函式在這個日期上加一年（12個月），再減去一天，就得到了該年的最後一天。最後，使用TO_CHAR函式將這個日期轉換為該年中的第幾天（三位數字表示）。

程式碼範例：

select to_char(
    add_months(
        trunc(to_date('01-SEP-2020','DD-MON-YYYY'),'Y'),
        12)-1,'DDD') num_days_in_year
from dual;

內容解密：

1. to_date('01-SEP-2020','DD-MON-YYYY') 將字串轉換為日期格式。
2. trunc(..., 'Y') 將日期截斷到該年的第一天（1月1日）。
3. add_months(..., 12) 在截斷後的日期上加一年。
4. ... - 1 減去一天，得到該年的最後一天（12月31日）。
5. to_char(..., 'DDD') 將日期轉換為該年中的第幾天。

搜尋混合字母數字字串

在資料表中找出同時包含字母和數字的字串，可以使用Oracle的TRANSLATE函式。這個函式可以將字串中的特定字元替換成其他字元，從而幫助我們識別字串中是否同時包含字母和數字。

程式碼範例：

with v as (
    select 'ClassSummary' strings from dual union
    select '3453430278' from dual union
    select 'findRow 55' from dual union
    select '1010 switch' from dual union
    select '333' from dual union
    select 'threes' from dual
)
select strings
from (
    select strings,
    translate(
        strings,
        'abcdefghijklmnopqrstuvwxyz0123456789',
        rpad('#',26,'#')||rpad('*',10,'*')) translated
    from v
) x
where instr(translated,'#') > 0
and instr(translated,'*') > 0;

內容解密：

1. translate函式將字串中的字母和數字分別替換成#和*。
   • 第一個引數是原始字串。
   • 第二個引數是所有要被替換的字元（字母和數字）。
   • 第三個引數是替換字元，分別是26個#（代表字母）和10個*（代表數字）。
2. instr(translated, '#') > 0 檢查替換後的字串中是否包含#（即原字串中是否有字母）。
3. instr(translated, '*') > 0 檢查替換後的字串中是否包含*（即原字串中是否有數字）。
4. 同時滿足上述兩個條件的字串即為同時包含字母和數字的字串。

將整數轉換為二進製表示

在Oracle中，可以利用MODEL子句的迭代功能，將整數轉換為二進製表示。然而，這裡未直接給出完整的程式碼範例。轉換的基本思路是反覆地將整數除以2，並記錄餘數，直到商為0。然後，將所有餘數逆序排列，即得到二進製表示。

程式碼範例（簡化示意）：

-- 這裡需要使用到MODEL子句進行迭代運算，具體實作略為複雜，需要根據實際情況進行調整。

內容解密：

1. 使用MODEL子句進行迭代，每次迭代將當前值除以2，並記錄餘數。
2. 將餘數收集起來，最後逆序排列得到二進製表示。

這些範例展示瞭如何在Oracle資料函式庫中處理日期、字串和數字轉換等常見問題。透過使用適當的內建函式和查詢技巧，可以有效地解決各種資料處理需求。

在Oracle中將整數轉換為二進位制

本文將介紹如何使用Oracle的MODEL子句將整數轉換為二進製表示。雖然這種方法可能不是最實用的，但它展示了MODEL子句在執行程式性任務時的強大功能。

解決方案

下面的查詢使用MODEL子句將EMP表中的SAL列轉換為二進製表示：

SELECT ename,
       sal,
       (SELECT bin
        FROM dual
        MODEL
        DIMENSION BY (0 attr)
        MEASURES (sal num,
                  CAST(NULL AS VARCHAR2(30)) bin,
                  '0123456789ABCDEF' hex)
        RULES ITERATE (10000) UNTIL (num[0] <= 0) (
          bin[0] = SUBSTR(hex[cv()], MOD(num[cv()], 2) + 1, 1) || bin[cv()],
          num[0] = TRUNC(num[cv()] / 2)
        )) sal_binary
FROM emp;

程式碼解密：

1. 查詢結構：外部查詢從EMP表中選擇ename和sal列。子查詢使用MODEL子句將sal轉換為二進位制。
2. MODEL子句：
   • DIMENSION BY：定義維度，本例中使用常數0。
   • MEASURES：定義度量，本例中包括num（二進位制轉換的數字）、bin（二進位制結果）和hex（包含二進位制數字字元的字串）。
   • RULES：定義迭代規則，直到num小於或等於0。
     • bin[0] = SUBSTR(hex[cv()], MOD(num[cv()], 2) + 1, 1) || bin[cv()]：根據num的當前值計算二進製表示的下一位，並將其前置到現有的bin值。
     • num[0] = TRUNC(num[cv()] / 2)：將num除以2，進行下一次迭代的準備。

討論

本解決方案改編自Tom Kyte的TO_BASE函式，展示了MODEL子句的迭代和陣列存取功能。雖然在實際應用中可能使用儲存函式更合適，但本例展示了MODEL子句在SQL中執行程式性任務的能力。

將排名結果樞紐轉換

本文討論如何將排名結果轉換為樞紐表，以顯示前三名、接下來的三名和其他。

解決方案

下面的查詢使用Oracle語法實作此功能：

SELECT MAX(CASE grp WHEN 1 THEN RPAD(ename, 6) || ' (' || sal || ')' END) top_3,
       MAX(CASE grp WHEN 2 THEN RPAD(ename, 6) || ' (' || sal || ')' END) next_3,
       MAX(CASE grp WHEN 3 THEN RPAD(ename, 6) || ' (' || sal || ')' END) rest
FROM (
  SELECT ename,
         sal,
         rnk,
         CASE
           WHEN rnk <= 3 THEN 1
           WHEN rnk <= 6 THEN 2
           ELSE 3
         END grp,
         ROW_NUMBER() OVER (
           PARTITION BY CASE
                          WHEN rnk <= 3 THEN 1
                          WHEN rnk <= 6 THEN 2
                          ELSE 3
                        END
           ORDER BY sal DESC, ename
         ) grp_rnk
  FROM (
    SELECT ename,
           sal,
           DENSE_RANK() OVER (ORDER BY sal DESC) rnk
    FROM emp
  ) x
) y
GROUP BY grp_rnk;

程式碼解密：

1. 內部查詢：使用DENSE_RANK()對員工按薪水進行排名。
2. 中間查詢：
   • 使用CASE陳述式將排名分為三組：前三名、接下來的三名和其他。
   • 使用ROW_NUMBER()為每組內的員工進行排序。
3. 外部查詢：使用樞紐操作將結果分為三列：top_3、next_3和rest。

圖表翻譯：

此圖示呈現了查詢的邏輯流程，從內部查詢到外部查詢的逐步處理過程。

@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle

title Oracle資料函式庫進階SQL技巧

package "機器學習流程" {
    package "資料處理" {
        component [資料收集] as collect
        component [資料清洗] as clean
        component [特徵工程] as feature
    }

    package "模型訓練" {
        component [模型選擇] as select
        component [超參數調優] as tune
        component [交叉驗證] as cv
    }

    package "評估部署" {
        component [模型評估] as eval
        component [模型部署] as deploy
        component [監控維護] as monitor
    }
}

collect --> clean : 原始資料
clean --> feature : 乾淨資料
feature --> select : 特徵向量
select --> tune : 基礎模型
tune --> cv : 最佳參數
cv --> eval : 訓練模型
eval --> deploy : 驗證模型
deploy --> monitor : 生產模型

note right of feature
  特徵工程包含：
  - 特徵選擇
  - 特徵轉換
  - 降維處理
end note

note right of eval
  評估指標：
  - 準確率/召回率
  - F1 Score
  - AUC-ROC
end note

@enduml

圖表翻譯： 此圖表呈現了查詢的逐步流程，從使用DENSE_RANK()進行初始排名，到中間查詢的分組與排序，最終到外部查詢的樞紐操作，形成最終結果。