UNPIVOT 関数がない場合に、MySQL の動的列を含むデータをアンピボットする方法

動的列を使用したデータのアンピボット

クエリ変換領域では、データのアンピボットはテーブル構造の再形成において重要な役割を果たします。このようなシナリオの 1 つは、動的な列を行に転置することですが、MySQL に UNPIVOT 関数がないことがこの作業を妨げる可能性があります。

問題: 動的列の転置

Table_1 というテーブルを想像してください。次に示すように、さまざまな属性を表す動的列 (abc、brt、ccc など) を使用します。以下:

Table_1
-----------------------------------------
Id       abc  brt ccc ddq eee fff gga hxx
-----------------------------------------
12345     0    1   0   5   0   2   0   0  
21321     0    0   0   0   0   0   0   0   
33333     2    0   0   0   0   0   0   0   
41414     0    0   0   0   5   0   0   1   
55001     0    0   0   0   0   0   0   2   
60000     0    0   0   0   0   0   0   0 
77777     9    0   3   0   0   0   0   0

目標は、Table_1 を、ゼロ以外の値のみを含む行として列をリストする Expected_Result_Table に変換することです:

Expected_Result_Table
---------------------
Id      Word   Qty>0
---------------------
12345    brt    1
12345    ddq    5
12345    fff    2
33333    abc    2
41414    eee    5
41414    hxx    1
55001    hxx    2
77777    abc    9
77777    ccc    3

解決策: 動的 UNION ALL と Prepared Statement

この変換を達成するには、次のことができます。 UNION ALL と準備されたステートメントの組み合わせを活用して、動的列を処理します:

1. 動的 SQL の生成: information_schema.columns を使用すると、各非行に対して行を選択する動的 SQL を生成できます。 -zero column.
2. Prepare Statement: 準備されたステートメントを使用します動的 SQL を実行し、潜在的な SQL インジェクションの脆弱性を回避します。
3. ステートメントの実行: 準備されたステートメントを実行して、ピボットされていないデータを取得します。

コードは次のとおりです。ダイナミックな解決策:

-- Generate dynamic SQL
SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'select id, ''',
      c.column_name,
      ''' as word, ',
      c.column_name,
      ' as qty 
      from yt 
      where ',
      c.column_name,
      ' > 0'
    ) SEPARATOR ' UNION ALL '
  ) INTO @sql
FROM information_schema.columns c
where c.table_name = 'yt'
  and c.column_name not in ('id')
order by c.ordinal_position;

-- Build the final SQL query
SET @sql 
  = CONCAT('select id, word, qty
           from
           (', @sql, ') x  order by id');

-- Prepare and execute the statement
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

結果:

結果のテーブルには目的の行が含まれます:

Id      Word   Qty>0
---------------------
12345    brt    1
12345    ddq    5
12345    fff    2
33333    abc    2
41414    eee    5
41414    hxx    1
55001    hxx    2
77777    abc    9
77777    ccc    3

以上がUNPIVOT 関数がない場合に、MySQL の動的列を含むデータをアンピボットする方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。