欄式資料庫的分析工作流程

NoSQL 入門

Jake Roach

Data Engineer

在 Snowflake 使用 CTE

Common table expressions（CTE）︰

使用 WITH 關鍵字定義的具名子查詢／暫存資料表
建立可供後續查詢的物件
減少需查詢與（或）JOIN 的資料量
模組化，更易除錯

WITH <cte-name> AS (
    SELECT
        ....
    FROM <table-name>
    [JOIN | WHERE | ...]
)

SELECT
    ...
FROM <cte-name>;

¹ https://docs.snowflake.com/en/user-guide/queries-cte

撰寫 CTE

WITH premium_books AS (
    SELECT
        title,
        author,
        avg_reviews
    FROM books
    WHERE price > 25.00
)
SELECT
    author,
    MIN(avg_reviews) AS min_avg_reviews,
    MAX(avg_reviews) AS max_avg_reviews
FROM premium_books
GROUP BY author;

建立 premium_books 暫存物件
在後續查詢中使用 premium_books

也可同時建立多個暫存物件：

WITH 
    <first-name> AS (...),
    <second-name> AS (...),
    ...
...
;

在 Snowflake 使用 Views

Views（檢視）︰

讓查詢結果可像資料表一樣被存取
分為非實體化與實體化

CREATE VIEW <view-name> AS
  SELECT
      ...
  FROM <table-name>
  [WHERE | JOIN | ...];

在 Snowflake 建立 Views

CREATE VIEW premium_books AS
    SELECT
        title,
        author,
        avg_reviews
    FROM books
    WHERE price >= 25.00;

SELECT * FROM premium_books;

當呼叫 premium_books 時才執行查詢
查詢的「具名定義」

CREATE MATERIALIZED VIEW premium_books AS
    SELECT
        title,
        author,
        avg_reviews
    FROM books
    WHERE price >= 25.00;

SELECT * FROM premium_books;

執行時計算結果並予以儲存
查詢效能較佳，需定期重新整理

一起來練習吧！

NoSQL 入門