ログ日記

作業ログと日記とメモ

SQLテーブル名の別名、命名規則、T1とか

ときどき

SELECt * FROM item as T1

みたいな連番別名テーブルを見るんだが。
どこかで推奨でもされているんだろうか。


プログラムの変数名に無意味な連番は有り得ないっていうのは共通認識としてあると思うけど、SQLはそうじゃないんだろうか。


検索して上から順に見ていった。

オブジェクト(主にテーブル)のエイリアスは大抵つける、その際、テーブル名を略したエイリアスをつけない(TOKUISAKI_MST TOK, USER_MST USR みたいな)。理由は、略文字はテーブル名が増えたり、似たような名前のテーブルが並ぶと、ぱっと見で判断しにくいから。その点数字だとすぐ見分けが付く。(from 句を参照しないとだめだが) 頭文字の f というのは特に意味なし。

http://d.zeromemory.info/2007/01/19/coding-rule-sql.html

あと、テーブルには必ず別名を付けてて(参照するテーブルが1つの場合でも)、別名は小文字で付けてます。昔は、アルファベット1〜2文字で付けてました。

http://d.hatena.ne.jp/shimooka/20100426/1272291486

テーブルに別名を付ける場合、テーブル名の単数形を使用する

http://bleis-tift.hatenablog.com/entry/20090413/1239604802
  • テーブルの別名はわかりやすく
    • 理由:テーブルがUSER_MSTならumsとか、3文字の略字で表します。他の人から見ても可読性が工場します。3文字と決めているのは、項目を並べたときにツラをあわせるためです。
http://d.hatena.ne.jp/uesama99/20070120/1169261175

ちょっと信じられない。別の世界に紛れ込んだようだ。



手元にあるSQLが書いている本はアート・オブ・SQLしか無かったので読んでみた。
自己参照以外はだいたいテーブル名をそのまま使っている。


だいたいどの言語でも意味が伴わない型の再定義はよくないし、カーネルコーディング規約では typedef struct は禁止だ。
テーブル名にT1とか書かれても難読化しているようにしか思えない。



もうちょっと検索したら真面目に調べてる人が居た。
http://d.hatena.ne.jp/trshugu/20121219/1355883637
なるほど。速くなると思ってる人が居て、それが広まってしまって、さらに理由も忘れられて習慣化したのかな。

相関サブクエリの書き方が分かってきた

やっとできた。




FROM句に相関副問い合わせは指定できない*1 し、where句での指定では選択した値を持って来れない。


相関サブクエリでひたすら検索していたら
http://jutememo.blogspot.com/2010/11/sql-4-select.html
このページが見つかった。


なるほど、from句に書かずにselect句に直接書けばいいらしい。





で、昨日の続き *2 。同じテーブル構造で同じ結果を出力するSQLはこうなった。

select B.day, (
  select sum(
  (
    select col3
    from sample
    where col2 <= B.day
    and col1 = c1s.v -- ※
    order by col2 desc
    limit 1
  )) as col3
  from (select * from (values('A'), ('B')) as c1(v)) as c1s
) as col3
from
(
  select '2011-05-01'::Timestamp + ((days.d || ' days')::interval) as day
  from generate_series(0, 100) as days(d)
) as B
where B.day < '2011-06-01'
and B.day >= '2011-05-15'
group by B.day
order by B.day
;
         day         | col3 
---------------------+------
 2011-05-15 00:00:00 |    2
 2011-05-16 00:00:00 |    5
 2011-05-17 00:00:00 |    5
 2011-05-18 00:00:00 |    8
 2011-05-19 00:00:00 |    8
 2011-05-20 00:00:00 |    7
 2011-05-21 00:00:00 |    7
 2011-05-22 00:00:00 |    7
 2011-05-23 00:00:00 |    7
 2011-05-24 00:00:00 |    7
 2011-05-25 00:00:00 |    5
 2011-05-26 00:00:00 |    5
 2011-05-27 00:00:00 |    5
 2011-05-28 00:00:00 |    5
 2011-05-29 00:00:00 |    5
 2011-05-30 00:00:00 |    5
 2011-05-31 00:00:00 |    5
(17 行)

※の箇所、ここではべた書きだけれど、コストは{col1の種類数 * selectサブクエリの行数}になり、selectサブクエリの中の検索でインデックスが効いていればだいぶ早くなる。

SQLが分からない

2日ほど悩んでる。


おかげで変な技術は身に付いた。
連番生成とか。
例えば

次のような一行だけデータを持つテーブルがあります。データの中身は重要ではありません。

CREATE TABLE OneRow
(col1 VARCHAR(8) NOT NULL,
 col2 VARCHAR(8) NOT NULL,
 col3 VARCHAR(8) NOT NULL,
 PRIMARY KEY (col1));

INSERT INTO OneRow VALUES('test', 'data', 'col')
問題は、このテーブルから取得したレコードを、次のように3行で表示したい、ということ。

col1 col2 col3 seq
==== ==== ==== ===
test data col    1
test data col    2
test data col    3
2008-02-07

これのPostgreSQLでのやり方。
PostgreSQL 8からは集合を返す関数が使えるようになっているらしい。


答えその1。

select * from onerow, generate_series(1,3) as seq;

答えその2。三行ぐらいならべた書きでもいい。

select * from onerow, (values(1), (2), (3)) as foo(seq);

valuesってinsert以外でも使えるんだね。

select * from (values(1, 'test'), (2, 'a'), (3, 'b')) as foo(seq, col1);
 seq | col1 
-----+------
   1 | test
   2 | a
   3 | b
(3 行)


で、今悩んでいるSQLはまだ解決していない。

CREATE TABLE sample                                                                                                              (
  col1 VARCHAR(8) NOT NULL,
  col2 TIMESTAMP NOT NULL,
  col3 INT NOT NULL,
  unique(col1, col2)
);


INSERT INTO sample VALUES('A', '2011-05-25', 3);
INSERT INTO sample VALUES('A', '2011-05-15', 2);
INSERT INTO sample VALUES('A', '2011-05-18', 5);

INSERT INTO sample VALUES('B', '2011-05-15', 0);
INSERT INTO sample VALUES('B', '2011-05-16', 3);
INSERT INTO sample VALUES('B', '2011-05-20', 2);

この表で、col1でグループ化した中でcol2がmaxになる行のcol3の値を全てのcol1分合計し、それを一定間隔のシーケンスで出したい。

select B.day, sum(A.col3) as col3
from sample as A
cross join (
  select '2011-05-01'::Timestamp + ((days.d || ' days')::interval) as day
  from generate_series(0, 100) as days(d)
) as B
where B.day < '2011-06-01'
and B.day >= '2011-05-15'
and A.col2 = (
  select col2
  from sample
  where col2 <= B.day
  and col1 = A.col1 -- (※)
  order by col2 desc
  limit 1
)
group by B.day
order by B.day
;

         day         | col3 
---------------------+------
 2011-05-15 00:00:00 |    2
 2011-05-16 00:00:00 |    5
 2011-05-17 00:00:00 |    5
 2011-05-18 00:00:00 |    8
 2011-05-19 00:00:00 |    8
 2011-05-20 00:00:00 |    7
 2011-05-21 00:00:00 |    7
 2011-05-22 00:00:00 |    7
 2011-05-23 00:00:00 |    7
 2011-05-24 00:00:00 |    7
 2011-05-25 00:00:00 |    5
 2011-05-26 00:00:00 |    5
 2011-05-27 00:00:00 |    5
 2011-05-28 00:00:00 |    5
 2011-05-29 00:00:00 |    5
 2011-05-30 00:00:00 |    5
 2011-05-31 00:00:00 |    5
(17 行)

これで一応目的の結果が得られているのだが、(※)の箇所の相関サブクエリが良くない。
この方法だと結合時に {sampleの行数 * whereサブクエリの行数} のコストがかかる。
この表は次々にデータが溜まっていくが、col1の種類は滅多に増えない。
サブクエリの中はcol1でグループ化したいだけなので、{col1の種類数 * whereサブクエリの行数}のコストに抑えたい。


しかし方法が思い付かず…。
cross join で 'A', 'B' のデータだけ持つテーブルを結合しても、クロス結合されている別のテーブルのカラムはwhere句から参照できないらしい。


Head First SQL: atama to karada de oboeru esukyūeru no kihon - Google ブックス
この辺?本あったかなぁ…。

まともなSQLを書く

メモ。


OTN Japan - 404 Error
SQLを速くするぞ―お手軽パフォーマンス・チューニング



柔軟なテーブル構成にすると結合が増える。
多重ビューとかついついやってしまう。
期間をおいて自分の書いたSQLを見直すとなんだこりゃってなるのはプログラムと一緒。

外部結合のときのNULL値

外部結合の時のNULL値を他の値に置き換えることって出来たっけ?RDBMSPostgreSQL


部署情報に支払い済み給料合計を加えた表示の例

      dept      salary       emp
----------   ---------   -------
   dept_id   salary_id    emp_id
 dept_name      emp_id   dept_id
       ...      salary       ...
                   ...
create view dept_ex as 
select *, case
            when salary_sum is null then 0
            else salary_sum
          end as dept_salary
from dept left outer join(
  select dept_id, sum(salary) as salary_sum
  from salary join emp using(emp_id)
  group by dept_id
) as foo using(dept_id);

分からなかったのでcase文を使うことにした。
この例だと、外部結合時のNULLを自動的に0に置き換えたい。


フレームワークを使ってるとビューを多用するようになってきた。
取り敢えずビューさえ定義しておけばフレームワークが単一の表と同じように扱ってくれる。
これは便利だ。
以前某所でストアドプロシージャを使う使わないの議論があったけど、$dao->save($vo) の決まり文句を使うためにはSQLの関数で一気にデータを更新するのもアリかも。