Create a gist now

Instantly share code, notes, and snippets.

@aamine /HorizVert.md
Last active Feb 26, 2016

What would you like to do?
RDBの縦持ちテーブルと横持ちテーブル、およびその変換について

テーブルの縦持ち横持ちについて

横持ちテーブルと縦持ちテーブル

横持ちはいわゆる「普通の」データの持ちかたのこと。 例えばレコードごとにa, b, c, dの4つの属性を持つ テーブルを作る場合、次のようなテーブルが横持ちテーブルである。

create table horiz ( rowId int, a int, b int, c int, d int );
insert into horiz values ( 1, 11, 22, 33, 44 );

※rowIdが関数の場合は適当に名前を変えてね

一方、縦持ちテーブルでは行IDとカラムIDを追加して レコードごと・カラムごとに1行を作る。 テーブル定義は以下のようになる。

create table vert ( rowId int, columnId int, value int )
insert into vert values ( 1, 1, 11 );
insert into vert values ( 1, 2, 22 );
insert into vert values ( 1, 3, 33 );
insert into vert values ( 1, 4, 44 );

縦持ちテーブルの利点

フツーRDBを使う場合はまず横持ちを考えると思うので 縦持ちナニソレおいしいの、という気になるわけだが、 次のような条件を満たすデータの場合には縦持ちのほうが 便利かつ高速になることがある。

  • 異常にカラムが多い(500カラムとか)
  • ほとんどのカラムがnull
  • レコードごとにカラムが異なる

横→縦の変換

横持ちテーブルを縦持ちテーブルに変換する場合は、 カラムIDだけを持った補助テーブルを作って横持ちテーブルに クロスジョインしてやる。

create table columnId ( value int );
insert into columnId values (1), (2), (3), (4);

select
    h.rid
    , c.value as cid
    , case cid
      when 1 then a
      when 2 then b
      when 3 then c
      when 4 then d
      end
from
    horiz h cross join columnId c
;

縦→横の変換

縦持ちテーブルを横持ちテーブルに変換する場合はもうちょっとややこしい。 論理的に同じレコードに属する行(カラム数だけあるはず)を1行にする必要があるので、 case文で適切なカラムに値を配置したうえで、maxでnullを潰す。

select 
    rowId
    , max(case columnId when 1 then value end)
    , max(case columnId when 2 then value end)
    , max(case columnId when 3 then value end)
    , max(case columnId when 4 then value end)
from
    vert
group by
    rowId
;

この変換の理屈は以下のような処理過程を見るとわかると思う。

-- 元の縦持ちテーブル
(1, 1, 11)
(1, 2, 22)
(1, 3, 33)
(1, 4, 44)

-- case文で対角線上に値を配置
(1, 11, null, null, null)
(1, null, 22, null, null)
(1, null, null, 33, null)
(1, null, null, null, 44)

-- カラムごとにmaxを取るとnullが消える
(1, 11, 22, 33, 44)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment