Skip to content

Instantly share code, notes, and snippets.

@aamine aamine/HorizVert.md
Last active Nov 22, 2018

Embed
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
You can’t perform that action at this time.