DBでSQLのテーブル名に別名を付けるときのルールと複数形
データベースのテーブル名を複数形にするかどうか、作るたびに悩む。
Qiita の記事 https://qiita.com/siinai/items/d4274c95fcdde3fd7295 のコメント欄に良いリンクがあった。
SELECT id, name, description FROM products product WHERE product.name = ‘foo’ AND product.description = ‘bar’https://medium.com/@fbnlsr/the-table-naming-dilemma-singular-vs-plural-dc260d90aaff
過去に書いた記事*1 を見返していて、そこのリンクにも同じような記事があった。
テーブルに別名を付ける場合、テーブル名の単数形を使用する
SQL の命名規約とフォーマット - ぐるぐる~
Railsとは無関係の文脈だと思うけど、こういう派閥も昔からあったのか。このときは流し読みでスルーしていた。
T1 とか tbl_kbn とかは以ての外だけど、この複数形を単数形にする別名のルールは良いかもしれない。
SELECT * FROM Customers AS Customer WHERE Customer.FirstName = 'John' - Customers refers to the whole table while Customer refers to the current row.
visual studio - Database tables naming, plural or singular - Stack Overflow
こちらも古いけど複数形テーブルに単数形の別名を付けている。
sql - Table Naming Dilemma: Singular vs. Plural Names - Stack Overflow
こっちは単数形推しで理由がまとまっている。
自分は最初のQiitaの
「テーブル」自体が「複数」そのものではない
テーブルの名前って複数形?単数形? - Qiita
とか
最後のstackoverflowの
Reason 1 (Concept). You can think of bag containing apples like "AppleBag", it doesn't matter if contains 0, 1 or a million apples, it is always the same bag. Tables are just that, containers, the table name must describe what it contains, not how much data it contains. Additionally, the plural concept is more about a spoken language one (actually to determine whether there is one or more).
sql - Table Naming Dilemma: Singular vs. Plural Names - Stack Overflow
とかと同じで単数形にしている。
かと言って複数形にすることは無いかというと、そうでもない。
例えば何らかの理由でファイルに書くようなアプリケーションの設定項目をDBに保存するときは application_settings というただ一つの行のみを持つテーブルを作るかもしれない。
これは the application settings であり、一つの行に複数の設定が詰まっているので意味的に複数形がしっくりくる。行が複数ということではない。
他にも、category_attributesのように何かの情報を詰め込んだ行を持つテーブルは複数形にするかもしれない。
そう考えると全体的に単数形や複数形に統一するという考えとは相反することになりそうだ。
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
http://d.hatena.ne.jp/uesama99/20070120/1169261175
- テーブルの別名はわかりやすく
- 理由:テーブルがUSER_MSTならumsとか、3文字の略字で表します。他の人から見ても可読性が工場します。3文字と決めているのは、項目を並べたときにツラをあわせるためです。
ちょっと信じられない。別の世界に紛れ込んだようだ。
手元にある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 32008-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値を他の値に置き換えることって出来たっけ?RDBMSはPostgreSQL。
部署情報に支払い済み給料合計を加えた表示の例
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の関数で一気にデータを更新するのもアリかも。