Snowflake資料函式庫查詢與視窗函式應用

Snowflake 作為雲端資料倉儲解決方案，提供強大的 SQL 查詢和資料處理能力。本文將探討資料函式庫查詢的各個方面，包含資料表資訊查詢、重複檢視查詢，並講解如何利用視窗函式進行排名、篩選和聚合計算。同時，文章也涵蓋了 Snowflake 中 JSON 檔案的處理與查詢最佳化技巧，讓讀者能夠有效地處理和分析半結構化資料。透過一系列的練習題和程式碼範例，讀者可以逐步掌握 Snowflake 的進階查詢技巧，並將其應用於實際的資料分析場景中。

資料函式庫查詢與視窗函式實戰演練

本章節將探討資料函式庫查詢與視窗函式的實際應用，透過具體範例與程式碼解析，幫助讀者掌握相關技術。

練習題13-1：查詢資料表名稱與列數

首先，我們需要查詢資料函式庫中所有資料表的名稱及其列數。這可以透過查詢 information_schema.tables 檢視來實作。

select table_name, row_count
from information_schema.tables
where row_count > 1000;

內容解密：

information_schema.tables：這是一個系統檢視，包含了資料函式庫中所有資料表的後設資料。
table_name 和 row_count：分別代表資料表的名稱和列數。
where row_count > 1000：篩選出列數大於1000的資料表。

執行結果顯示了符合條件的資料表名稱及其列數，例如 CUSTOMER、PART、SUPPLIER 等。

練習題13-2：查詢重複建立的檢視

接下來，我們需要找出在 account_usage.views 中被重複建立的檢視名稱。

select table_name
from snowflake.account_usage.views
group by table_name
having count(*) > 1;

內容解密：

snowflake.account_usage.views：這是一個系統檢視，記錄了所有檢視的建立資訊。
group by table_name：按照檢視名稱分組。
having count(*) > 1：篩選出被重複建立的檢視。

執行結果顯示了重複建立的檢視名稱，例如 ORDER_CALCULATION_VW、SUPPLIER_VW 和 YEARLY_PART_SALES_VW。

練習題14-1：使用視窗函式進行排名

本題要求我們對訂單資料進行排名，根據訂單數量和總銷售額進行排序。

select date_part(year, o_orderdate) as order_year,
       count(*) as num_orders, 
       sum(o_totalprice) as tot_sales,
       rank() over (order by count(*) desc) as ordr_rnk,
       rank() over (order by sum(o_totalprice) desc) as sls_rnk
from orders
group by date_part(year, o_orderdate);

內容解密：

date_part(year, o_orderdate)：提取訂單日期的年份部分。
count(*) 和 sum(o_totalprice)：分別計算每年的訂單數量和總銷售額。
rank() over (order by count(*) desc) 和 rank() over (order by sum(o_totalprice) desc)：使用視窗函式對訂單數量和總銷售額進行排名。

執行結果顯示了每年的訂單數量、總銷售額及其對應的排名。

練習題14-2：篩選特定排名的行

本題要求我們在前一題的基礎上，篩選出排名為2或6的行。

select date_part(year, o_orderdate) as order_year,
       count(*) as num_orders, 
       sum(o_totalprice) as tot_sales,
       rank() over (order by count(*) desc) as ordr_rnk,
       rank() over (order by sum(o_totalprice) desc) as sls_rnk
from orders
group by date_part(year, o_orderdate)
qualify ordr_rnk in (2,6) or sls_rnk in (2,6);

內容解密：

qualify ordr_rnk in (2,6) or sls_rnk in (2,6)：篩選出訂單數量排名或總銷售額排名為2或6的行。

執行結果顯示了符合條件的年份、訂單數量、總銷售額及其排名。

練習題14-3：計算總銷售額

本題要求我們在查詢結果中新增一列，顯示所有年份的總銷售額。

select date_part(year, o_orderdate) as order_year,
       count(*) as num_orders, 
       sum(o_totalprice) as tot_sales,
       sum(sum(o_totalprice)) over () as grnd_tot_sales
from orders
group by date_part(year, o_orderdate);

內容解密：

sum(sum(o_totalprice)) over ()：計算所有年份的總銷售額，並在每行中顯示。

執行結果顯示了每年的訂單數量、總銷售額以及所有年份的總銷售額。

練習題14-4：計算累計銷售額

最後，本題要求我們修改視窗函式，計算每年的累計銷售額。

select date_part(year, o_orderdate) as order_year,
       count(*) as num_orders, 
       sum(o_totalprice) as tot_sales,
       sum(sum(o_totalprice)) over (order by date_part(year, o_orderdate) rows unbounded preceding) as rng_tot_sales
from orders
group by date_part(year, o_orderdate);

內容解密：

sum(sum(o_totalprice)) over (order by date_part(year, o_orderdate) rows unbounded preceding)：計算每年的累計銷售額，從最早年份開始累計到當前年份。

執行結果顯示了每年的訂單數量、總銷售額以及累計銷售額。

第15章練習題解答

練習15-1解答

撰寫一個指令碼，宣告一個遊標（cursor）用於查詢 select max(o_totalprice) from orders，開啟遊標，擷取結果到一個數值變數中，關閉遊標，並傳回擷取到的值。

declare
    v_prc number;
    v_cur cursor for select max(o_totalprice) from orders;
begin
    open v_cur;
    fetch v_cur into v_prc;
    close v_cur;
    return v_prc;
end;

內容解密：

宣告變數和遊標：宣告一個 number 型別的變數 v_prc 用於儲存查詢結果，並宣告一個遊標 v_cur 用於執行 select max(o_totalprice) from orders 查詢。
開啟和擷取遊標：使用 open v_cur; 開啟遊標，接著使用 fetch v_cur into v_prc; 將查詢結果擷取到 v_prc 變數中。
關閉遊標：使用 close v_cur; 關閉遊標以釋放資源。
傳回結果：最後，使用 return v_prc; 傳回擷取到的最大值。

練習15-2解答

撰寫一個指令碼，使用根據計數器的 for 迴圈迭代範圍從1到100，當迭代到第60次時跳出迴圈。

begin
    for n in 1 to 100 do
        if (n > 60) then
            break;
        end if;
    end for;
end;

內容解密：

for迴圈迭代：使用 for n in 1 to 100 do 建立一個從1到100的迴圈。
條件判斷：在迴圈內，使用 if (n > 60) then 判斷是否超出第60次迭代。
跳出迴圈：當 n 大於60時，使用 break; 跳出迴圈。

練習15-3解答

撰寫一個指令碼，宣告一個例外（exception）並賦予其編號 -20200 和字串 ‘The sky is falling!’，引發該例外，捕捉它，並重新引發它。

declare
    e_my_excptn exception (-20200, 'The sky is falling!');
begin
    raise e_my_excptn;
exception
    when e_my_excptn then
        raise;
end;

內容解密：

宣告例外：使用 exception (-20200, 'The sky is falling!') 宣告一個自定義例外 e_my_excptn。
引發例外：在 begin 部分，使用 raise e_my_excptn; 引發該例外。
捕捉和重新引發例外：在 exception 部分，使用 when e_my_excptn then raise; 捕捉該例外並重新引發。

練習15-4解答

撰寫一個指令碼，使用根據遊標的 for 迴圈遍歷 Nation 表中的所有 n_name 值，當檢索到 ‘EGYPT’ 時跳出迴圈。

declare
    v_cur cursor for select n_name from nation;
begin
    for rec in v_cur do
        if (rec.n_name = 'EGYPT') then
            break;
        end if;
    end for;
end;

內容解密：

宣告遊標：宣告一個遊標 v_cur 用於查詢 select n_name from nation。
for迴圈遍歷：使用 for rec in v_cur do 對遊標結果進行迭代。
條件判斷和跳出：在迴圈內檢查是否遇到 ‘EGYPT’，若是則使用 break; 跳出迴圈。

第16章練習題解答

練習16-1解答

將以下匿名指令碼轉換為名為 rectangle_area() 的儲存程式，帶有數值引數 p_width 和 p_length：

create procedure rectangle_area(p_width number, p_length number)
returns number
language sql
as
begin
    return p_width * p_length;
end;

內容解密：

建立儲存程式：使用 create procedure rectangle_area(p_width number, p_length number) 定義儲存程式及其引數。
傳回計算結果：在程式體內，直接傳回 p_width * p_length 的結果。

練習16-2解答

撰寫一個名為 get_parts_by_type() 的儲存程式，帶有單一引數 p_type_name（型別為 varchar）。該程式應傳回 Part 表中 p_partkey 和 p_name 列的結果集，其中 p_type 列與引數值相符。使用引數值 ‘SMALL PLATED NICKEL’ 或 ‘PROMO BRUSHED STEEL’ 呼叫該程式，均應傳回23行結果。

create procedure get_parts_by_type(p_type_name varchar)
returns table(partkey number, name varchar)
language sql
as
declare
    v_results resultset;
begin
    v_results := (select p_partkey, p_name from part where p_type = :p_type_name);
    return table(v_results);
end;

內容解密：

建立儲存程式：定義儲存程式及其傳回結果集的結構。
查詢和傳回結果：在程式體內，執行查詢並將結果存入 v_results，然後傳回該結果集。

第17章練習題解答

練習17-1解答

撰寫一個名為 fn_circle_area() 的純量使用者定義函式（UDF），接受單一數值引數 p_radius，傳回圓的面積（π * p_radius²）。該UDF應傳回 float 型別。

create function fn_circle_area(p_radius number)
returns float
language sql
as
'pi() * square(p_radius)';

內容解密：

建立UDF：定義函式及其傳回型別。
計算圓面積：使用內建函式 pi() 和 square() 計算圓面積，並直接傳回結果。

練習17-2解答

撰寫一個名為 fn_get_parts_by_type() 的表函式，帶有單一引數 p_type_name（型別為 varchar）。該表函式應傳回 Part 表中 p_partkey 和 p_name 列的結果集，其中 p_type 列與引數值相符。使用引數值 ‘SMALL PLATED NICKEL’ 或 ‘PROMO BRUSHED STEEL’ 呼叫該函式，均應傳回23行結果。

create function fn_get_parts_by_type(p_type_name varchar)
returns table(p_partkey number, p_name varchar)
language sql
as
'select p_partkey, p_name from part where p_type = p_type_name';

內容解密：

建立表函式：定義表函式及其傳回結果集的結構。
查詢和傳回結果：在函式體內，直接執行查詢並傳回符合條件的結果集。

深入解析 Snowflake 中的 JSON 檔案處理與查詢最佳化

在現代資料處理和分析中，JSON 檔案格式因其靈活性和可擴充套件性被廣泛應用於各種資料儲存和交換場景。Snowflake 作為一個領先的雲端資料倉儲平台，提供了強大的 JSON 資料處理能力。本文將結合具體例項，探討如何在 Snowflake 中處理 JSON 檔案，以及如何最佳化相關查詢。

練習 17-3：查詢 `fn_get_parts_by_type` 表函式

在練習 17-3 中，我們首先查詢了 fn_get_parts_by_type 表函式，該函式根據零件型別傳回相關零件資訊。然後，我們將該表函式與 Part 表進行交叉連線，以檢索 p_type 類別似於 'STANDARD % STEEL' 的零件資訊。

原始查詢

PUBLIC>select gpt.*
from (select distinct p_type from part
where p_type like 'STANDARD % STEEL') p
cross join table(fn_get_parts_by_type(p.p_type)) as gpt;

查詢解析

子查詢：首先，從 part 表中檢索出 p_type 類別似於 'STANDARD % STEEL' 的不同值。
表函式呼叫：使用 fn_get_parts_by_type 表函式，並將子查詢結果中的 p_type 值作為輸入引數。
交叉連線：將子查詢結果與表函式傳回的結果進行交叉連線，以取得完整的零件資訊。

執行結果

查詢傳回了包含 P_PARTKEY 和 P_NAME 的結果集，共計若干行資料（部分結果省略）。

練習 18-1：生成包含查詢結果的 JSON 檔案

在練習 18-1 中，我們根據對 Part 表的查詢結果生成了一個 JSON 檔案，該檔案包含了一個名為 Parts 的物件，該物件內部包含了一個陣列，陣列中每個元素代表一個零件的資訊。

原始查詢

PUBLIC>select object_construct('Parts',
array_agg(
object_construct(
'Partkey', p_partkey,
'Name', p_name,
'Brand', p_brand
))) as part_doc
from part
where p_mfgr = 'Manufacturer#1'
and p_type = 'ECONOMY POLISHED STEEL';

查詢解析

條件過濾：首先，從 part 表中篩選出製造商為 'Manufacturer#1' 且零件型別為 'ECONOMY POLISHED STEEL' 的記錄。
物件構建：使用 object_construct 函式為每個篩選出的零件構建一個包含 Partkey、Name 和 Brand 的 JSON 物件。
陣列聚合：使用 array_agg 函式將所有零件物件聚合成一個陣列。
最終 JSON 物件構建：再次使用 object_construct 函式，將聚合後的陣列封裝在一個名為 Parts 的 JSON 物件中。

執行結果

查詢傳回了一個包含 Parts 物件的 JSON 檔案，該物件內部包含了一個陣列，陣列中有 6 個元素，每個元素代表一個零件的詳細資訊。

練習 18-2：將 JSON 檔案插入 `my_docs` 表

在練習 18-2 中，我們將練習 18-1 中生成的 JSON 檔案插入到 my_docs 表中。

原始查詢

PUBLIC>insert into my_docs
select parse_json(
'{
"Parts": [
{
"Brand": "Brand#12",
"Name": "royal thistle floral frosted midnight",
"Partkey": 95608
},
{
"Brand": "Brand#11",
"Name": "azure honeydew grey aquamarine black",
"Partkey": 100308
},
...
]}');

查詢解析

JSON 解析：使用 parse_json 函式將 JSON 字串解析為 JSON 物件。
插入操作：將解析後的 JSON 物件插入到 my_docs 表中。

圖表說明：Snowflake 中 JSON 處理流程圖

@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle

title Snowflake資料函式庫查詢與視窗函式應用

package "資料庫架構" {
    package "應用層" {
        component [連線池] as pool
        component [ORM 框架] as orm
    }

    package "資料庫引擎" {
        component [查詢解析器] as parser
        component [優化器] as optimizer
        component [執行引擎] as executor
    }

    package "儲存層" {
        database [主資料庫] as master
        database [讀取副本] as replica
        database [快取層] as cache
    }
}

pool --> orm : 管理連線
orm --> parser : SQL 查詢
parser --> optimizer : 解析樹
optimizer --> executor : 執行計畫
executor --> master : 寫入操作
executor --> replica : 讀取操作
cache --> executor : 快取命中

master --> replica : 資料同步

note right of cache
  Redis/Memcached
  減少資料庫負載
end note

@enduml

圖表翻譯： 此圖示展示了在 Snowflake 中處理 JSON 資料的流程。首先，從 Part 表中進行查詢並應用過濾條件。接著，根據查詢結果生成 JSON 物件。最後，將生成的 JSON 物件插入到 my_docs 表中，完成整個處理流程。

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。

Snowflake資料函式庫查詢與視窗函式應用

資料函式庫查詢與視窗函式實戰演練

練習題13-1：查詢資料表名稱與列數

內容解密：

練習題13-2：查詢重複建立的檢視

內容解密：

練習題14-1：使用視窗函式進行排名

內容解密：

練習題14-2：篩選特定排名的行

內容解密：

練習題14-3：計算總銷售額

內容解密：

練習題14-4：計算累計銷售額

內容解密：

第15章練習題解答

練習15-1解答

內容解密：

練習15-2解答

內容解密：

練習15-3解答

內容解密：

練習15-4解答

內容解密：

第16章練習題解答

練習16-1解答

內容解密：

練習16-2解答

內容解密：

第17章練習題解答

練習17-1解答

內容解密：

練習17-2解答

內容解密：

深入解析 Snowflake 中的 JSON 檔案處理與查詢最佳化

練習 17-3：查詢 fn_get_parts_by_type 表函式

原始查詢

查詢解析

執行結果

練習 18-1：生成包含查詢結果的 JSON 檔案

原始查詢

查詢解析

執行結果

練習 18-2：將 JSON 檔案插入 my_docs 表

原始查詢

查詢解析

圖表說明：Snowflake 中 JSON 處理流程圖

玄貓 BlackCat

練習 17-3：查詢 `fn_get_parts_by_type` 表函式

練習 18-2：將 JSON 檔案插入 `my_docs` 表