質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

88.77%

SQLの縦持ちデーターの横持変換の方法

解決済

回答 2

投稿

  • 評価
  • クリップ 0
  • VIEW 1,400

SQLで縦持ちを横持に変換したいのですが、一般的には以下のような記述でできると思います。(引用です)

しかし今回は、indicatorに入る値が不特定多数です。
下記のSQL文では、indicatorの値をCASE WHENで指定してますが、indicatorの値をSQLで自動で分けてくれるような方法はないのでしょうか?
ちなみにMySQL(MariaDB)で行っております。

どうぞよろしくお願いいたします。

参考URL一部引用
https://book.mynavi.jp/manatee/detail/id=72442

行を列に変換する
 最初に、行を列に変換する方法を考えてみましょう。SQLにおける列はクエリによって固定的である必要があるため、列に展開されるデータの種類や数が予め判明している場合にのみ、この手法を利用できます。例えば、下記データ例に示す日次KPIデータは、日付ごとに「インプレッション数」「セッション数」「ユーザー数」の3指標の値を1 レコードごとに保持したテーブルです。

●データ:日次KPIデータ(daily_kpi)テーブル
     dt     | indicator   | val
------------+-------------+------
 2017-01-01 | impressions | 1800
 2017-01-01 | sessions    |  500
 2017-01-01 | users       |  200
 2017-01-02 | impressions | 2000
 2017-01-02 | sessions    |  700
 2017-01-02 | users       |  250

求めている結果
dt     | impressions | sessions | users
------------+-------------+----------+-------
2017-01-01 |        1800 |      500 |   200
2017-01-02 |        2000 |      700 |   250

●コード:行で保存された指標の値を列に変換するクエリ
これでは、indicatorの値を全部指定してあげないといけない、、

SELECT
    dt
  , MAX(CASE WHEN indicator = 'impressions' THEN val END) AS impressions
  , MAX(CASE WHEN indicator = 'sessions' THEN val END) AS sessions
  , MAX(CASE WHEN indicator = 'users' THEN val END) AS users
FROM daily_kpi
GROUP BY dt
ORDER BY dt
;
  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 2

checkベストアンサー

+1

indicatorの値をSQLで自動で分けてくれるような方法はないのでしょうか?

列数を可変にするという事は、SQLを動的にするしかありません。

動的に行を列に変換するMysqlクエリ

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/05/20 16:10

    ありがとうございます。勉強になりました!

    キャンセル

0

こうします

  • 元データ
create table daily_kpi(dt date,indicator varchar(20),val int);
insert into daily_kpi values
('2017-01-01','impressions',1800),
('2017-01-01','sessions',500),
('2017-01-01','users',200),
('2017-01-02','impressions',2000),
('2017-01-02','sessions',700),
('2017-01-02','users',250);
  • 集計
select dt
,sum(val*(indicator='impressions')) as impressions
,sum(val*(indicator='sessions')) as sessions
,sum(val*(indicator='users')) as users
from daily_kpi
group by dt;

追記

indicatorを別途マスター管理し、表示順を確定すればprocedureを利用できます

  • 元テーブル
create table daily_kpi(dt date,id int ,val int);
insert into daily_kpi values
('2017-01-01',1,1800),
('2017-01-01',2,500),
('2017-01-01',3,200),
('2017-01-02',1,2000),
('2017-01-02',2,700),
('2017-01-02',3,250);

create table indicator(id int primary key,name varchar(20));
insert into  indicator values
(1,'impressions'),
(2,'sessions'),
(3,'users');
  • procedure作成
drop procedure if exists yokogaki;
delimiter //
create procedure yokogaki()
begin
declare a int;
declare b varchar(255);
declare done int default 0;
declare cur cursor for
select id,name from indicator order by id;
declare continue handler for sqlstate '02000' set done=1;
set @sql='select dt';
open cur;
repeat
fetch cur into a,b;
if not done then
set @sql=CONCAT(@sql,",sum(val*(id=",a,")) as ",b);
end if;
until done end repeat;
close cur;
set @sql=CONCAT(@sql," from  daily_kpi group by dt order by id");
prepare stmt from @sql;
execute stmt;
end
//
delimiter ;
  • 実行
call yokogaki;

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/05/20 16:11

    こんなやり方あるのですね、質問とはちょっと違ったのですが試して見たいと思います。参考になりました。

    キャンセル

  • 2019/05/20 16:46

    自動処理にはprocedureを利用すれば一発でできます
    daily_kpiの値を絞って処理したい場合はyokogakiプロシージャに
    引数を渡してフィルタをすることで対応できます

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 88.77%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る