如何使用 PostgreSQL 的 tablefunc 有效地透視多個欄位上的資料？

使用Tablefunc進行多列資料透視

處理包含多個屬性和度量的資料時，可能需要將其從長格式轉換為寬格式以進行高效分析。 PostgreSQL 的 tablefunc 功能為此類轉換提供了一個方便的解決方案。但是，在處理多個透視列時，請務必了解其限制。

在先前查詢的回覆中，一位使用者尋求有關使用 tablefunc 進行資料透視的指導，但在處理多個透視列時遇到了挑戰。由於 tablefunc 期望每行名稱都有一致的額外列，因此原始查詢導致資料不完整。

問題解決

要解決此問題，請務必遵守 tablefunc 指定的順序：

1. 行名稱：此欄位必須永遠位於第一位。
2. 額外欄位（可選）：如果需要，任何其他欄位都應位於行名稱列之後。
3. 類別和值（最後兩列）：透視類別和值列必須以此順序作為最後兩列。

實作

在給定的範例中，所需的輸出需要對兩列（實體和狀態）進行透視。為此，查詢進行瞭如下修改：

SELECT *
FROM crosstab(
   'SELECT entity, timeof, status, ct
    FROM t4
    ORDER BY 1'
 , 'VALUES (1), (0)'
   ) AS ct (
      "Attribute" character
    , "Section" timestamp
    , "status_1" int
    , "status_0" int
      );

透過將 entity 作為行名稱並將 timeof 和 entity 的順序互換，查詢成功地對多列進行了透視。

不同設定的變體

對於回應中提到的設置，其中資料按 localt 和 entity 排序，修改後的查詢如下：

SELECT localt, entity
     , msrmnt01, msrmnt02, msrmnt03, msrmnt04, msrmnt05  -- , more?
FROM crosstab(
  'SELECT dense_rank() OVER (ORDER BY localt, entity)::int AS row_name
        , localt, entity -- additional columns
        , msrmnt, val
   FROM test
-- WHERE  ???   -- instead of LIMIT at the end
   ORDER BY localt, entity, msrmnt
-- LIMIT ???'   -- instead of LIMIT at the end
, 'SELECT generate_series(1,5)'  -- more?
   ) AS ct (row_name int, localt timestamp, entity int
          , msrmnt01 float8, msrmnt02 float8, msrmnt03 float8, msrmnt04 float8, msrmnt05 float8 -- , more?
            )
LIMIT 1000  -- ?!

此查詢使用 dense_rank() 產生代理行名稱，並包含可選的 WHERE 子句以過濾處理先前的資料。此外，從子查詢中刪除了 LIMIT 條件，以透過僅處理必要的行來提高效能。

結論

透過了解限制並遵循 tablefunc 指定的順序，即使對於大型資料集，也可以有效地對多列進行資料透視。請記住，透過使用適當的 WHERE 子句或 LIMIT 條件來最佳化查詢，以避免不必要的處理。

以上是如何使用 PostgreSQL 的 tablefunc 有效地透視多個欄位上的資料？的詳細內容。更多資訊請關注PHP中文網其他相關文章！