MySQL で列を行に動的にアンピボットするにはどうすればよいですか?

動的列を行に転置する

列を行に転置することは、多くの場合「アンピボット」と呼ばれ、データの再形成において貴重なテクニックとなります。 。複数の列を含む幅の広いテーブルを列数の少ない狭いテーブルに変換することで、データの分析と操作が容易になります。

MySQL での動的アンピボット

MySQL では、組み込みの UNPIVOT 関数はありません。ただし、SQL ステートメントと動的 SQL 生成を組み合わせて使用​​すると、アンピボットを実現できます。

例: Table_1 のアンピボット

Table_1 をアンピボットする次の例を考えてみましょう。より大きい値のみを考慮して、Expected_Result_Table に変換します。 0:

Table1
-----------------------------------------
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

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

ピボット解除のための動的 SQL 生成

Table_1 をアンピボットするには、まず Table_1 の列名に基づいて動的 SQL ステートメントを生成する必要があります。表_1。これは、次の手順を使用して実現できます。

1. 次のクエリを使用して、「Id」列を除く個別の列名のリストを生成します。

   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;

2. 生成された SQL 文字列を連結して、最終的な動的 SQL を形成しますステートメント:

   SET @sql = CONCAT('select id, word, qty
               from
               (', @sql, ') x  order by id');

3. 次のコードを使用して動的 SQL ステートメントを準備して実行します:

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

結果:

動的 SQL ステートメントを実行すると、期待結果テーブル:

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

以上がMySQL で列を行に動的にアンピボットするにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。