查詢折疊範例

本文針對查詢摺疊的三種可能結果中的每一個提供一些範例案例。 它也包含一些建議，說明如何充分利用查詢摺疊機制，以及它在查詢中可能產生的效果。

情境

假設這樣一種案例：使用 Azure Synapse Analytics SQL 資料庫的 Wide World Importers 資料庫，您的任務是在 Power Query 中建立查詢，以連線到 fact_Sale 資料表，並擷取僅包含下列欄位的最後 10 筆銷售：

• 銷售密鑰
• 客戶金鑰
• 發票日期關鍵
• Description
• 數量

備註

為了示範目的，本文會使用將 Wide World Importers 資料庫載入 Azure Synapse Analytics 的教學課程中概述的資料庫。 本文的主要區別在於該 fact_Sale 表僅包含 2000 年的數據，總共有 3,644,356 行。

雖然結果可能與您遵循 Azure Synapse Analytics 檔中的教學課程所取得的結果不完全相符，但本文的目標是展示查詢摺疊可能在查詢中產生的核心概念和影響。

本文示範三種方法，以不同層級的查詢摺疊來達成相同的輸出：

• 無查詢折疊
• 部分查詢摺疊
• 完整查詢摺疊

沒有查詢摺疊例子

這很重要

僅依賴非結構化資料來源或沒有計算引擎的查詢 （例如 CSV 或 Excel 檔案） 沒有查詢折疊功能。 這表示 Power Query 會使用 Power Query 引擎評估所有必要的資料轉換。

連線到資料庫並瀏覽至fact_Sale表格之後，您可以從首頁索引標籤的減少列群組中選擇保留底部列轉換。

選取此轉換之後，會出現新的對話方塊。 在這個新對話方塊中，您可以輸入要保留的列數。 在此情況下，請輸入值 10，然後選取 [確定]。

小提示

在此情況下，執行此作業會產生最近 10 筆銷售的結果。 在大多數情況下，我們建議您藉由在資料表上執行排序操作，明確定義哪些資料列應被視為最終位置。

接下來，選取 [首頁] 索引標籤的 [管理資料行] 群組內的 [選擇資料行] 轉換。然後，您可以從表格中選擇要保留的列並刪除其餘列。

最後，在 [ 選擇資料行 ] 對話方塊中，選取 Sale Key、 Customer Key、 Invoice Date KeyDescription和 Quantity 資料行，然後選取 [ 確定]。

下列程式碼範例是您所建立查詢的完整 M 腳本：

let
    Source = Sql.Database(ServerName, DatabaseName),
    Navigation = Source{[Schema = "wwi", Item = "fact_Sale"]}[Data],
    #"Kept bottom rows" = Table.LastN(Navigation, 10),
    #"Choose columns" = Table.SelectColumns(
        #"Kept bottom rows",
        {"Sale Key", "Customer Key", "Invoice Date Key", "Description", "Quantity"}
    )
in
    #"Choose columns""

無查詢折疊：了解查詢評估

在 Power Query 編輯器的 [套用步驟] 底下，請注意 [保留底部資料列] 和 [選擇資料行] 的查詢摺疊指標會標示為在資料來源外部評估的步驟，換句話說，是由 Power Query 引擎評估的步驟。

您可以以滑鼠右鍵按一下查詢的最後一個步驟，即名為 [選擇資料行] 的步驟，然後選取讀取 [檢視查詢計劃] 的選項。 查詢計劃的目標是為您提供查詢執行方式的詳細檢視。 若要深入瞭解此功能，請移至 查詢方案。

上一個圖像中的每個方塊稱為 節點。 節點代表完成此查詢的作業明細。 代表資料來源的節點，例如上一個範例中的 SQL Server 和 Value.NativeQuery 節點，代表查詢的哪個部分會卸載至資料來源。 其餘節點，即上述圖片的矩形中醒目提示的Table.LastN與Table.SelectColumns兩個節點，由Power Query引擎進行評估。 這兩個節點代表您新增的兩個轉換，保留底部資料列和選擇資料行。 其餘節點代表在資料來源層級發生的作業。

若要查看傳送至資料來源的確切要求，請在節點中選取 Value.NativeQuery]。

此資料來源請求使用的是資料來源的原始語言。 在此情況下，該語言是 SQL，而此陳述式代表表格中 fact_Sale 所有列及欄位的要求。

查閱此資料來源要求可協助您更瞭解查詢計劃嘗試傳達的故事：

• Sql.Database：此節點代表資料來源存取。 連接到資料庫並發送元數據請求以了解其功能。
• Value.NativeQuery：代表 Power Query 所產生的要求，以完成查詢。 Power Query 會將原生 SQL 陳述式中的資料要求提交至資料來源。 在此情況下，這代表資料表中的所有 fact_Sale 記錄和欄位 （資料行）。 在此案例中，此案例是不受歡迎的，因為資料表包含數百萬列，且興趣僅在最後 10 列中。
• Table.LastN：一旦 Power Query 從資料表收到 fact_Sale 所有記錄，它就會使用 Power Query 引擎來篩選資料表，並只保留最後 10 個資料列。
• Table.SelectColumns：Power Query 會使用節點的 Table.LastN 輸出，並套用名為 Table.SelectColumns的新轉換，以選取您要從資料表保留的特定資料行。

為了進行評估，此查詢必須從資料表下載 fact_Sale 所有資料列和欄位。 此查詢在 Power BI 資料流程的標準執行個體中處理平均需要 6 分鐘 1 秒，這包括資料的評估及載入至資料流程的時間。

部分查詢折疊範例

連線到資料庫並導覽至 fact_Sale 資料表之後，您可以先選取要從資料表中保留的資料行。 從 [首頁] 索引標籤選取 [管理資料行] 群組內的 [選擇資料行] 轉換。此轉換可協助您明確選取要從資料表中保留的資料行，並移除其餘資料行。

在 [ 選擇資料行 ] 對話方塊中，選取 Sale Key、 Customer Key、 Invoice Date KeyDescription和Quantity資料行，然後選取 [確定]。

您現在建立邏輯來排序表格，讓最後的銷售額位於表格底部。 選取 Sale Key 欄，這是資料表的主鍵，並作為遞增序列或索引。 僅使用此欄位從欄的操作功能表中按遞增順序排序表格。

接下來，選取表格內容選單，然後選擇 保留底部資料列 轉換。

在 [保留底部資料列] 中，輸入值 10，然後選取 [確定]。

下列程式碼範例是您所建立查詢的完整 M 腳本：

let
    Source = Sql.Database(ServerName, DatabaseName),
    Navigation = Source{[Schema = "wwi", Item = "fact_Sale"]}[Data],
    #"Choose columns" = Table.SelectColumns(
        Navigation, 
        {"Sale Key", "Customer Key", "Invoice Date Key", "Description", "Quantity"}
    ),
    #"Sorted rows" = Table.Sort(#"Choose columns", {{"Sale Key", Order.Ascending}}),
    #"Kept bottom rows" = Table.LastN(#"Sorted rows", 10)
in
    #"Kept bottom rows"

部分查詢折疊範例：了解查詢評估

檢查套用的步驟窗格時，您會注意到查詢摺疊指標顯示您新增的最後一個轉換 Kept bottom rows，已標示為在資料來源外部評估的步驟，也就是由 Power Query 引擎評估的步驟。

您可以以滑鼠右鍵按一下查詢的最後一個步驟，即名為 Kept bottom rows的步驟，然後選取 [查詢計劃] 選項，以進一步瞭解如何評估您的查詢。

上一個圖像中的每個方塊稱為 節點。 節點代表需要發生的每個進程 （從左到右） 才能評估您的查詢。 其中一些節點可以在您的資料來源進行評估，而其他節點 （例如 的節點 Table.LastN） 是由 [ 保留底部資料列 ] 步驟所代表，則會使用 Power Query 引擎進行評估。

若要查看傳送至資料來源的確切要求，請在節點中選取 Value.NativeQuery]。

此請求使用您資料來源的母語。 在此情況下，該語言是 SQL ，且此陳述式代表所有列的要求，只有表格中 fact_Sale 要求的欄位依欄位 Sale Key 排序。

查閱此資料來源要求可協助您進一步瞭解完整查詢計劃嘗試傳達的故事。 節點的順序是一個循序過程，首先從資料來源請求資料：

• Sql.Database：連線到資料庫並傳送中繼資料請求以了解其功能。
• Value.NativeQuery：代表 Power Query 所產生的要求，以完成查詢。 Power Query 會將原生 SQL 陳述式中的資料要求提交至資料來源。 在此情況下，這代表從資料庫中fact_Sale表格中請求的欄位，這包括所有的紀錄，並依Sales Key欄位遞增排序。
• Table.LastN：一旦 Power Query 從資料表收到 fact_Sale 所有記錄，它就會使用 Power Query 引擎來篩選資料表，並只保留最後 10 個資料列。

為了進行評估，此查詢必須從表格中 fact_Sale 下載所有資料列，且僅下載必要欄位。 在 Power BI 資料流程的標準執行個體中，處理平均需要 3 分 4 秒的時間（此過程包括資料的評估與載入至資料流程）。

完整查詢摺疊範例

連線到資料庫並導覽至 fact_Sale 資料表之後，請先選取您要從資料表中保留的資料行。 從 [首頁] 索引標籤選取 [管理資料行] 群組內的 [選擇資料行] 轉換。此轉換可協助您明確選取要從資料表中保留的資料行，並移除其餘資料行。

在 [選擇資料行] 中，選取 Sale Key、 Customer Key、 Invoice Date KeyDescription和Quantity資料行，然後選取 [確定]。

您現在會建立邏輯來排序表格，讓最後的銷售額位於表格頂端。 選取 Sale Key 欄，這是資料表的主鍵，並作為遞增序列或索引。 僅使用此欄位，從欄位的右鍵選單中以遞減順序對表格排序。

接下來，選取表格內容功能表，然後選擇 [保留最上層列轉換 ]。

在 [保留最上層資料列] 中，輸入值 10，然後選取 [確定]。

下列程式碼範例是您所建立查詢的完整 M 腳本：

let
    Source = Sql.Database(ServerName, DatabaseName),
    Navigation = Source{[Schema = "wwi", Item = "fact_Sale"]}[Data],
    #"Choose columns" = Table.SelectColumns(
        Navigation, 
        {"Sale Key", "Customer Key", "Invoice Date Key", "Description", "Quantity"}
    ),
    #"Sorted rows" = Table.Sort(#"Choose columns", {{"Sale Key", Order.Descending}}),
    #"Kept top rows" = Table.FirstN(#"Sorted rows", 10)
in
    #"Kept top rows"

完整查詢折疊範例：了解查詢評估

檢查套用的步驟窗格時，請注意，查詢摺疊指標會顯示您新增的轉換 [ 選擇資料行]、[ 已排序的資料列] 和 [保留最上層資料列] 會標示為在資料來源評估的步驟。

您可以以滑鼠右鍵按一下查詢的最後一個步驟，即名為 [保留最上層列] 的步驟，然後選取讀取 [查詢計劃] 的選項。

查詢計劃的螢幕擷取畫面，其中包含 'Value.NativeQuery' 內的 SQL 陳述式，代表使用 'Sale Key' 欄位排序的 'fact_Sale' 資料表前 10 筆記錄的要求，且僅包含 'Sale Key'、'Customer Key'、'Invoice Date Key'、'Description' 和 'Quantity' 欄位。

此請求使用您資料來源的母語。 在此情況下，該語言是 SQL，而此陳述式代表表格中 fact_Sale 所有列及欄位的要求。

查閱此資料來源查詢可協助您更瞭解完整查詢計劃嘗試傳達的故事：

• Sql.Database：連線到資料庫並傳送中繼資料請求以了解其功能。
• Value.NativeQuery：代表 Power Query 所產生的要求，以完成查詢。 Power Query 會將原生 SQL 陳述式中的資料要求提交至資料來源。 在此情況下，這表示請求僅檢索資料表的 fact_Sale 前 10 筆記錄，這些記錄先使用 Sale Key 欄位以遞減順序排序，然後只顯示必要的欄位。

備註

雖然沒有子句可用來在 T-SQL 語言中選取資料表的底部資料列，但有一個 TOP 子句可擷取資料表的頂端資料列。

為了進行評估，此查詢只會從表格 fact_Sale 下載 10 列，僅有您要求的欄位。 此查詢在 Power BI 資料流程的標準執行個體中平均需要 31 秒完成處理（其中包含資料的評估及載入至資料流程的過程）。

表現比較

若要進一步瞭解查詢摺疊在這些查詢中的效果，您可以重新整理查詢、記錄完全重新整理每個查詢所需的時間，並比較它們。 為了簡單起見，本文提供使用 Power BI 資料流程重新整理機制擷取的平均重新整理時間，同時連線到以 DW2000c 作為服務層級的專用 Azure Synapse Analytics 環境。

每個查詢的重新整理時間如下：

Example	標籤	時間（以秒為單位）
無查詢折疊	None	361
部分查詢摺疊	部分的	184
完整查詢摺疊	完整	31

通常情況下，完全折疊回資料來源的查詢效能優於未完全折疊回資料來源的類似查詢。 造成這種情況的原因可能有很多。 這些原因包括查詢執行的轉換的複雜性，以及在資料來源上實作的查詢最佳化，例如索引和專用運算，以及網路資源。 不過，查詢摺疊會嘗試使用兩個特定的關鍵進程，以將這兩個進程對 Power Query 的影響降到最低：

• 傳輸中的數據
• Power Query 引擎所執行的轉換

下列各節說明這兩個進程在先前提及的查詢中所具有的效果。

傳輸中的數據

執行查詢時，它會嘗試從資料來源擷取資料，作為其第一步之一。 從資料來源擷取哪些資料是由查詢摺疊機制所定義。 此機制會識別查詢中可卸載至資料來源的步驟。

下表列出了從資料庫資料表fact_Sale要求的列數。 此表格也包含傳送以從資料來源要求這類資料的 SQL 陳述式的簡短說明。

Example	標籤	請求的資料列	Description
無查詢折疊	None	3644356	查詢 fact_Sale 資料表中的所有欄位和所有記錄
部分查詢摺疊	部分的	3644356	請求表格fact_Sale中所有記錄的必要欄位，但僅在按Sale Key欄位排序後
完整查詢摺疊	完整	10	從fact_Sale表格中提取前10筆記錄，並按Sale Key欄位遞減排序，只要求必填欄位。

當您從資料來源請求資料時，資料來源需要計算請求的結果，然後將資料傳送給請求者。 雖然已提及計算資源，但將數據從數據源移動至 Power Query，然後讓 Power Query 能夠有效地接收數據，並為本機發生的轉換做好準備的網路資源可能需要一些時間，視數據大小而定。

針對展示的範例，Power Query 必須從資料來源要求超過 3,600,000 個資料列，才能取得無查詢摺疊和部分查詢摺疊範例。 對於完整的查詢折疊範例，它只要求 10 列。 對於要求的欄位，無查詢摺疊範例會要求資料表中的所有可用欄位。 部分查詢折疊和完整查詢折疊範例都只針對它們所需的欄位提交了查詢。

謹慎

建議您實作使用查詢摺疊技術的增量資料重新整理解決方案，以處理具有大量資料的查詢或資料表。 Power Query 不同的產品整合方式會實作逾時機制來終止持續運行的查詢。 一些資料來源也會對長時間執行的會話實作逾時，因為他們嘗試在其伺服器上執行高成本的查詢。 其他資訊：搭配資料流程使用增量式更新 和 語意模型的增量式更新

Power Query 引擎所執行的轉換

本文示範如何使用 查詢計劃 ，以進一步瞭解如何評估查詢。 在查詢計劃中，您可以看到 Power Query 引擎所執行轉換作業的確切節點。

下表顯示先前查詢的查詢計劃中的節點，這些節點會由 Power Query 引擎評估。

Example	標籤	Power Query 引擎轉換節點
無查詢折疊	None	Table.LastN、Table.SelectColumns
部分查詢摺疊	部分的	Table.LastN
完整查詢摺疊	完整	—

針對本文中展示的範例，完整的查詢折疊範例不需要在 Power Query 引擎內進行任何轉換，因為所需的輸出資料表直接來自資料來源。 相反地，其他兩個查詢需要在 Power Query 引擎上進行一些計算。 由於這兩個查詢需要處理的資料量，因此這些範例的處理程序比完整查詢折疊範例花費更多的時間。

轉換可以分組為下列類別：

操作員類型	Description
遠端	運算子，即資料來源節點。 這些運算子的評估會在 Power Query 外部進行。
串流	運算子是透傳運算子。 例如，Table.SelectRows 使用簡單的篩選器通常可以在結果通過運算子時篩選出結果，而不需要在移動資料之前收集所有資料列。 Table.SelectColumns 和 Table.ReorderColumns 是這種運算子的其他例子。
完整掃描	在資料可以移動到鏈結中的下一個運算子之前，需要收集所有資料列的運算元。 例如，若要排序資料，Power Query 需要收集所有資料。 完整掃描運算子的其他範例包括 Table.Group、 Table.NestedJoin和 Table.Pivot。

小提示

雖然從效能的角度來看，並非每個轉換都相同，但在大多數情況下，轉換較少通常更好。

考慮因素和建議

• 建立新查詢時，請遵循最佳做法，如 Power Query 中的最佳做法中所述。
• 使用 查詢摺疊指標 來檢查哪些步驟會防止查詢摺疊。 如有必要，可重新排序以提高摺疊效果。
• 使用查詢計劃來判斷特定步驟的 Power Query 引擎正在進行哪些轉換。 請考慮重新排列步驟來修改現有查詢。 然後再次檢查查詢最後一個步驟的查詢計劃，並查看查詢計劃看起來是否比前一個更好。 例如，新的查詢計劃的節點比前一個少，而且大部分的節點都是「串流」節點，而不是「完整掃描」。 對於支援摺疊的資料來源，查詢計劃中除 和資料來源存取節點以外的 Value.NativeQuery 任何節點都代表未摺疊的轉換。
• 如果可用，您可以使用 檢視原生查詢 （或 檢視資料來源查詢） 選項，以確保您的查詢可以折疊回資料來源。 如果在您的步驟中禁用了此選項，而您所使用的來源通常會啟用它，那麼您已建立了一個步驟來停止查詢摺疊。 如果您使用的來源不支援此選項，您可以依賴查詢摺疊指標和查詢計劃。
• 使用查詢診斷工具，當連接器支援查詢摺疊功能時，能更好地瞭解傳送至資料來源的請求。
• 當您結合來自多個連接器的數據時，Power Query 會嘗試將盡可能多的工作推送至這兩個數據源，同時遵守為每個數據源定義的隱私權層級。
• 閱讀有關 隱私權層級 的文章，以保護您的查詢免於因資料隱私權防火牆錯誤而執行。
• 使用其他工具，從資料來源收到的請求的角度檢查查詢折疊。 根據本文中的範例，您可以使用 Microsoft SQL Server 分析工具來檢查 Power Query 所傳送和 Microsoft SQL Server 所接收的要求。
• 如果您將新增步驟加入已摺疊的查詢中，且該新增步驟也會摺疊，Power Query 可能會將新的要求傳送至資料來源，而不是使用先前結果的快取版本。 實際上，此程式可能會導致對少量資料進行看似簡單的作業，但在預覽中重新整理的時間比預期長。 此較長的重新整理是因為 Power Query 會再次查詢資料來源，而不是使用資料的本地複本。