動的 SQL テクニックを使用して SQL でデータを効率的にピボットするにはどうすればよいですか?

動的 SQL を使用して SQL データ ピボットを効率的に実装する

この記事では、データ変換の効率と柔軟性の向上に焦点を当てて、動的 SQL テクノロジーを使用して表形式データをピボット テーブル形式に変換する方法について説明します。

初期クエリ

次のクエリは CASE ステートメントと GROUP BY を使用してデータ ピボットを実装します。

SELECT bar, 
   MAX(CASE WHEN abc."row" = 1 THEN feh ELSE NULL END) AS "val1",
   MAX(CASE WHEN abc."row" = 2 THEN feh ELSE NULL END) AS "val2",
   MAX(CASE WHEN abc."row" = 3 THEN feh ELSE NULL END) AS "val3"
FROM
(
  SELECT bar, feh, row_number() OVER (partition by bar) as row
  FROM "Foo"
 ) abc
GROUP BY bar 

この方法はデータのピボットには効果的ですが、大規模なデータセットを扱う場合は効率も柔軟性も低くなります。

クロス集計機能を使用した改善されたソリューション

PostgreSQL の tablefunc モジュールによって提供される crosstab() 関数は、より効率的で動的なソリューションを提供できます。

tablefunc モジュールをインストールします

crosstab() を使用する前に、tablefunc モジュールが PostgreSQL データベースにインストールされていることを確認する必要があります。次のコマンドはデータベースごとに 1 回だけ実行する必要があります:

CREATE EXTENSION tablefunc;

クロス集計の実装

次の crosstab クエリはピボットに使用できます:

SELECT * FROM crosstab(
  'SELECT bar, 1 AS cat, feh
   FROM   tbl_org
   ORDER  BY bar, feh')
 AS ct (bar text, val1 int, val2 int, val3 int);  -- 可根据需要增加列

手順:

• tbl_org は入力テーブルです。
• crosstab() のサブクエリは、bar、ダミー カテゴリ cat、および feh を選択します。 cat 列はプレースホルダーとしてのみ機能し、無視されます。
• ORDER BY 句は、値が正しい順序であることを保証します。

このクエリは、目的のピボット テーブル形式でデータを返します。

動的クロス集計

より動的なアプリケーションを実現するために、ウィンドウ関数を使用してカテゴリ列を合成できます。

SELECT * FROM crosstab(
   $$
   SELECT bar, val, feh
   FROM  (
      SELECT *, 'val' || row_number() OVER (PARTITION BY bar ORDER BY feh) AS val
      FROM tbl_org
      ) x
   ORDER BY 1, 2
   $$
 , $$VALUES ('val1'), ('val2'), ('val3')$$         -- 可根据需要增加列
) AS ct (bar text, val1 int, val2 int, val3 int);  -- 可根据需要增加列

このクエリは、テーブル内の値に基づいてカテゴリ列を動的に作成します。

結論

全体として、crosstab() 関数は SQL でのピボットのためのより効率的で柔軟なソリューションを提供し、クエリを簡素化し、ニーズに応じた柔軟なカスタマイズを可能にします。

以上が動的 SQL テクニックを使用して SQL でデータを効率的にピボットするにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。