PostgreSQL の tablefunc を使用して複数の列のデータを効果的にピボットするにはどうすればよいですか?

複数列ピボット データには Tablefunc を使用します

複数の属性とメジャーを含むデータを処理する場合、効率的に分析するために、データをロング形式からワイド形式に変換する必要がある場合があります。 PostgreSQL の tablefunc 機能は、このような変換に便利なソリューションを提供します。ただし、複数のピボット列を操作する場合は、その制限を理解することが重要です。

前のクエリへの返信で、ユーザーはピボットに tablefunc を使用する方法についてのガイダンスを求めましたが、複数のピボット列を操作するときに問題が発生しました。 tablefunc は行名ごとに一貫した追加列を期待するため、元のクエリでは不完全なデータが生成されます。

問題は解決しました

この問題を解決するには、tablefunc で指定された順序を必ず遵守してください:

1. 行名: この列は常に最初に来る必要があります。
2. 追加の列 (オプション): 必要に応じて、追加の列を行名列の後に置く必要があります。
3. カテゴリと値 (最後の 2 列): ピボット カテゴリと値の列は、最後の 2 列と同じ順序で指定する必要があります。

実装

指定された例では、目的の出力には 2 つの列 (エンティティとステータス) をピボットする必要があります。これを行うために、クエリは次のように変更されました:

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
      );

エンティティを行名として使用し、timeof とエンティティの順序を入れ替えることにより、クエリは複数の列で正常にピボットされます。

設定が異なるバリエーション

応答に記載されている設定では、データが 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 中国語 Web サイトの他の関連記事を参照してください。