MySQL でテーブルを動的にピボットする方法?

MySQL 動的ピボット: 行を列に変換

MySQL では、行を列に変換するためにピボット テーブルが必要になる場合があります。これは、データをさまざまな方向で表現する必要がある場合に特に便利です。 MySQL は PIVOT 関数をネイティブに提供しませんが、このタスクを実行するにはいくつかの方法があります。

チャレンジ: ダイナミック ピボット テーブル

Products、Partners、Sales という 3 つのテーブルがあるとします。行としてパートナー、列として製品を含むテーブルを作成し、それぞれの組み合わせに関連付けられた販売数量を計算する必要があります。

CASE ステートメントを使用します

1 つの方法は、CASE ステートメントで集計関数を使用することです。複数の CASE ステートメントを作成すると、各製品の売上を計算し、結果をパートナー名ごとにグループ化できます。

SELECT pt.partner_name,
  COUNT(CASE WHEN pd.product_name = 'Product A' THEN 1 END) AS ProductA,
  COUNT(CASE WHEN pd.product_name = 'Product B' THEN 1 END) AS ProductB,
  COUNT(CASE WHEN pd.product_name = 'Product C' THEN 1 END) AS ProductC,
  COUNT(CASE WHEN pd.product_name = 'Product D' THEN 1 END) AS ProductD,
  COUNT(CASE WHEN pd.product_name = 'Product E' THEN 1 END) AS ProductE
FROM partners pt
LEFT JOIN sales s ON pt.part_id = s.partner_id
LEFT JOIN products pd ON s.product_id = pd.prod_id
GROUP BY pt.partner_name;

準備されたステートメントを使用して動的パースペクティブを実装します

製品の数が不明で動的に変化する状況では、準備されたステートメントを使用できます。 SQL クエリを動的に生成することで、利用可能な製品に基づいて列の数を調整できます。

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'COUNT(CASE WHEN Product_Name = ''',
      Product_Name,
      ''' THEN 1 END) AS ',
      REPLACE(Product_Name, ' ', '')
    )
  ) INTO @sql
FROM products;

SET @sql = CONCAT('SELECT pt.partner_name, ', @sql, ' FROM partners pt
LEFT JOIN sales s ON pt.part_id = s.partner_id
LEFT JOIN products pd ON s.product_id = pd.prod_id
GROUP BY pt.partner_name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

制限事項と注意事項

GROUP_CONCAT デフォルトのバイト制限は 1024 であることに注意してください。連結された文字列がこの制限を超える場合は、SET @@group_concat_max_len = 32000; を使用して制限を増やす必要がある場合があります。

以上がMySQL でテーブルを動的にピボットする方法?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。