はてなブックマーク

こちらで書いた記事のご意見が気になったので少し。 目次 目次 ご意見 テーブル設計における消費税率について マスタ類の設計例 トランザクション類の設計例 消費税率の登録の仕方 SQLで処理するとこうなる ゼロにするときには、ゼロを掛ければ良い トランザクションに税率を入れるか？ ご意見 gihyo.jp > 99%は同意するが一つ言わせてほしい。SQLの最大の弱点は時間の扱いに弱いことで、RDBの理論的基盤の数学が時間を考慮してないからしょうがないとはいえ消費税みたいな時間で変化するマスタへの配慮が無さすぎる。 99%は同意するが一つ言わせてほしい。SQLの最大の弱点は時間の扱いに弱いことで、RDBの理論的基盤の数学が時間を考慮してないからしょうがないとはいえ消費税みたいな時間で変化するマスタへの配慮が無さすぎる。 - turanukimaru のブックマーク / はてなブックマーク との

DBオブジェクトについて、Rails などのようにどうしても「外側から管理したい」と考える人たちは、言語側の命名法で処理したいと考えるようですが、DB側から見ると非常に使いにくいです。 そのため、弊社では以下のような命名法で用途に応じて別名を提供しています。 目次 目次 RDBMS は階層構造やグループ化しにくい 接頭辞と名称 キーの命名 主キーはサロゲートキーですべて【ID】 外部キーはテーブルの接頭辞＋ID（＋ 名前） 接頭辞をテーブルのエイリアスに 顧客向けに オブジェクト指向言語向けに まとめ RDBMS は階層構造やグループ化しにくい RDBMS上のオブジェクトは基本的にフラットな関係になりますから、オブジェクト指向言語のように英語を基本とすると、とても管理しにくくなります。 ですから、名前順で並べても意味が分かるように接頭辞を付けるべきでしょう。 接頭辞と名称 弊社では、 1文

前回の続き。 目次 目次 正解はこうなる イメージはこうなる つまりこれはバグ！！ 「Staticオジサン」はStatic決め打ち ちなみにWordPressはこんな変換をしています 正解はこうなる ※ 私は左辺、右辺を揃えますが、揃えなくてもOK。 削除フラグで絞り込みたい訳ではないので、「削除フラグ ＝ 0」という条件は、抽出条件ではなく、結合条件なのです。 イメージはこうなる 削除フラグで抽出してないことに注目してください。 つまりこれはバグ！！ LEFT OUTER JOIN のとき、右に置いたテーブルがWHERE句に入ると、外部結合を打消してしまいます。 ですから、外部結合を期待しているのか、INNER JOIN との使い分けが面倒で決め打ちしているのか、ソースだけでは意図が分からない。 いずれにしても矛盾したバグなのです。 「Staticオジサン」はStatic決め打ち 「St

「艦これ」についてというには、遠いけれど、「艦これ」からソーシャル系のシステムについて考えた話についてまとめました。 一覧 「艦これ」から、ソーシャル系のサーバ構成を考える http://d.hatena.ne.jp/Sikushima/20130901/1378021345 お手製クラスター構成は良くないよというお話。 「艦これ」もう少し思い出して考えてみた http://d.hatena.ne.jp/Sikushima/20130904/1378261621 アニメーションの間、非同期で処理してる？というお話。 もうちょっと「艦これ」からSQLを考えてみる1 http://d.hatena.ne.jp/Sikushima/20131014/1381715465 SQLでするとこんな感じになるというお話。 もうちょっと「艦これ」からSQLを考えてみる2 http://d.hatena.n

前回の続き。 前回のパターンであれば、全体を（パフォーマンス・サーバの負荷・開発工数・メンテナンス性）を鑑みて、IF文と四則演算は、DBサーバより、APサーバ。APサーバより、クライアントにある程度キャッシュさせて、処理もクライアントで行った方が良いです。 そう判断するには、設計段階でSQLで処理したときどうなるか、SQLで処理しなかったときどうなるか。が正確に分かってないと判断できません。 例えば、「艦これ」からは随分と離れますが、あるテストの男子と女子、全体の平均と最高点・最低点を求めたいとする。（面倒なので性別は非正規化されているとする） テーブルは以下の通り 成績テーブル テストID 生徒番号 性別（1:男子、2:女子） 得点 1.SQLで処理する SELECT テストID , AVG(CASE WHEN 性別 = 1 THEN 得点 ELSE NULL END) AS 男子平均

かなり前に下書きをしていたんだけれど、バタバタしていてほったらかしになっていたものです。タイミングがズレたけど、まだ「艦これ」をやっている人がいるみたいなのでアップします。 私が「艦これ」の設計をするなら 「軍艦を擬人化する」という発想は、生まれ変わっても私からは出てこないし、企画書を見せられても反応すらできないとは思う。ですから、「企画した人は凄いな〜」とは思うけれど、そんな分からないことを考えても意味ないので、遊んでいる半分以上の時間は、「自分ならどう設計するか」を考えていました。 一番気になったのが、 「何でこんなに遅いのか？」 「10万同時アクセスで落ちるとしたらどんな構造か？」 ということでした。 MySQL Cluster を利用しているとして、10分ほど遊んで考えたのが次のようなモノでした。 APサーバは分割するけれど、DBは分割しない。 更新系はNoSQL（API）を利用す

「艦これ」はあんまり関係ないのですが、スケールアウトについて。 「艦これ」は既に MySQL Cluster を使っているなら関係ないのですが、何度も書きますけれど、「JOIN禁止」はスケールアウトと関係ない。 Twitter規模になれば別の考え方が必要になりますが、取りあえず今は DBサーバ は1台だけれど、「スケールアウトのためにJOIN禁止」は「究極のバッドテクニック」ですのでやらないように！ 単純だから良いも、複雑だから良いもない。 必要なのは効率的かどうかを考えることだけで、それはどんなシステムでも同じ。 「艦これ」は…… 「艦これ」は MySQL Cluster を使っているとのこと。私が始めた頃は、4・5個の鎮守府（サーバ群）があった様に記憶しています。 MySQL Clusterと聞いて、DBは1つのクラスタ構成で処理されていると思ったのですが、障害は鎮守府（サーバ群）単位

私は、ソーシャル系とは縁遠い仕事ばっかりしているのですが、そういう依頼も若干増えてきたので話題になっている「艦これ」をお盆にやってみた。 残念ながら、「艦これ」の魅力は分からなかった。しかし、ミッションを用意されると、「クリアーしたい」という欲求から意地になるのは、何となく理解できました。それより、同時に始めた「Clash of Clans」には嵌まりました。気になっていた「ゲームの中に如何に自然に課金システムを取り入れるか」という課題についても、個人的には「Clash of Clans」の方が上手に解決しているように思います。 「艦これ」は、同時アクセスが10万以上あって、何度かシステム障害があったとのこと（そりゃあるでしょうが……）。私の興味の方向性は、課金システムであったり、システム構成にあるので、「艦これ」のシステム障害の方が強い興味の対象になります（苦笑） というわけで、「ソーシ

SQL文ができない人は、どこまでも文法で考えようとしている。 私が、SQLに違和感を感じずに理解することができたのは、文法を気にしてなかったからで、私の勉強法というか、どうやって習得してきたのかを整理してみました。 例えば IN と EXISTS の違い カラオケで説明します。 カラオケ10万曲から、とある曲リストと一致する曲を抽出するとします。 手続き型では、次のいずれかになるでしょう。（細かいところは端折るよ） Aパターン foreach (string 曲名 in 曲リスト){ 結果セット.add(カラオケ.find(曲名)); }Bパターン foreach (Song 曲 in カラオケ){ if(曲リスト.exists(曲.曲名)){ 結果セット.add(曲); } }　Bパターンなんて使わないと思いますか？ 「とある曲リスト」が「男性ヴォーカリストの曲」でも？ COBOL世代の

前回(MySQLで全文検索1)の続き。 my.cnf（my.ini）の調整 FullTextIndex でインデックスされる最小の文字数を確認する。 MySQL5.5以下 mysql> SHOW VARIABLES like 'ft_min_word_len'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | ft_min_word_len | 4 | +-----------------+-------+MySQL5.6 では innodb_ft_min_token_size に変更されている様子（デフォルトは3）。 mysql> SHOW VARIABLES like 'innodb_ft_min_token_size'; +-----------------+---

私は、MySQLをほとんど使わないのであまり考えたこともないのですが、MySQLの全文検索はブランクやカンマで区切られた単語単位でしかインデックスしてくれないので、単語の区切れのない日本語ではほぼ使えません。 そこで nGram でカットするファンクションを作ってみました。 nGramにカッとしたりするのは外側の言語でやれば良い。という考え方もあるでしょうが、私は RDBMS で完結するべきと考えています。 アプリ側に任せてしまうと、例えば、PHPで作ったモジュールを Javaで呼ばないと行けなくなったり、Javaのプログラムに書き直したりということが起こり得ます。 RDBMSを引っ越すよりも、複数言語を扱うことの方が圧倒的に確率は高いですし、SQL側で行った方がパフォーマンスも良くなります。 nGramにカットするのは、確かに外側の言語で行った方が RDBMS の負荷は小さくなりますが、

問題を少し変更 問題）部品在庫から、作成可能な製品の情報をとる。 ※本来はマスタテーブルと組み合わすべきですが、ツールの関係上2テーブルしか同時に表示出来ないので名称で結合する形になります。 SQLで考えるなら という風に考えます。 言い換える 『在庫数が必要量を満みたす製品のみ』 → 『在庫数が必要量を満たさないレコードがある製品を削る』 『満たさない』＝ NOT EXISTS（あるいは、NOT IN） 答えは SELECT * -- 本当は必要なもののみ FROM 部品表 AS bh INNER JOIN 在庫 AS zk ON bh.材料名 = zk.材料名 WHERE NOT EXISTS (SELECT * FROM 部品表 AS bh_s INNER JOIN 在庫 AS zk_s ON bh_s.材料名 = zk_s.材料名 WHERE bh_s.製品名 = bh.製品名 -

もう一度問題 問題）部品在庫から、作成可能な製品名をとる。 ※本来はマスタテーブルと組み合わすべきですが、ツールの関係上2テーブルしか同時に表示出来ないので名称で結合する形になります。 SQLで考えるなら 答えは SELECT 製品名 FROM 部品表 INNER JOIN 在庫 ON 部品表.材料名 = 在庫.材料名 GROUP BY 製品名 HAVING MIN(在庫.数量 - 部品表.必要量) >= 0; 三枚の図になっていますが、考え方さえ理解していれば数十秒で答えまでたどり着けます。パターン認識じゃないので忘れてもどってことはないのです。 考え方としてはこんな感じになります。 必要なテーブルを引っ付け、ゴールを目指して削り出して行けば良い。 プラモデルと、彫刻の違いです。 言い換えは、他の言語より重要？ プログラムも翻訳と同じですから、言い換えは必要です。 上の図の「SQL的思考

昨日、SQLWorldの「みんなでSQLを書いてみよう」というハンズオンの企画の勉強会に参加した。 最初にお知らせ 次回の企画のために、「実務で困っているSQL」を募集するそうです。 「こんなのSQLで出来るの？」という問題があれば、コメントやメール [email protected] でご相談ください。 やっぱり、できた方が良いと思うよ 私の興味はSQLとは全く違うところに向いたけれど（苦笑）SQLについて、これだけマシンが速くなったら、パフォーマンスはあんまり拘らなくても良いかなとも思っているのですが、それでもできた方が工数的にもメリットが大きいと実感した。 特に、最後にやった問題は、実務ではもっとかなり複雑だけれど、その複雑な実務でもSQLで恐らく可能です。在庫とか、生産管理の山積み、山崩しなどの複雑な業務も、数個のSQLで可能ですが……。 しかし、現実にはグリグリ回してやることになる

MySQLの Cluster: NoSQL がなかなか良さそうです。 私自身は、それを使う様な案件に恵まれてないので使わないとは思うけれど、チャンスがあれば使ってみたい。所謂、NoSQLは半端すぎて使いにくい。NoSQLという新たなモノを作るよりも、RDBMS が NoSQL を飲み込む、つまり、DB Engineを通さずにダイレクトアクセスできる API を提供して欲しいと考えて来たが、MySQLがその先駆けとしてやってくれました。この先、PostgreSQL も同様の API の提供を予定しているようです。 この流れが商用RDBMSにも起きてくれたらと思います。 O/Rマッパとどう違うのか？ 要するに、O/RマッパはDB Engine、SQLというボトルネックの上に、抽象度が逆転するラッパーとなっているため、必ず非効率になる。 DB Engineを利用するならば、効率的なSQLを書いて

私は基本的に、インターネット上の議論では個別の事情は挟むべきではない。と考えています。 私が個別の事情を挟んでいるとか、感情的とかいう人がいるけれど、全く逆です。 相手が【詐欺師レベル】と分かったとき、議論を打ち切って感情的に怒っているだけの話です。 できない人がいるは個別の事情。 私がSQLが得意だから推す。と考える人は、自分自身が得意なものを推そうとする人でしょう。ですから、そんなこと全く言ってない私の感情を、自分の感情で補完しているだけでしょう。自分の心の醜さが出ているだけです。 私はRDBMSを使う以上、SQLを使いきることが効率的であると言ってるに過ぎません。 ただし、以前にも書きましたが過ぎたるは及ばざるがごとし。例えば、【闇雲に】トリガーを仕込みまくったりしたら効率は確かに落ちます。【闇雲に】が重要であって【使ってはいけない】ではないのです。 私は当たり前のことしか言ってない

私は妥協点をほとんど出しません。なぜそんなに頑ななのか？その答えは単純です。 SQLを使うか、使わないかでは以下の様なグラフになります。 しかし、多くの人達は以下の様なイメージ上で、議論を行っています。 皆さんがある程度許せるという妥協点は赤丸のあたりになってしまいます。 普段、O/Rマッパーを使ってどうしてもパフォーマンスが出ないので一部にSQLを使うとか、パフォーマンスのために複雑なところをストアドプロシージャにするというのが、この辺の話になります。ハードが良くなったとか、ナンチャラというフレームワークができたとかも、ほぼ、このグラフの範囲を抜けていません。 その範囲を抜けてないと、パフォーマンスは上がっても工数が落ちたりします。その先は全く見えてないから、とてつもなく落ち込んで見えてしまいますし、全体として効率は落ち込むと感じてしまうわけです。 結果として、私の考える効率のグラフとは

前回の続き。 非正規化した項目の整合性を維持するトリガーをかいてみます。 トランザクションテーブルにトリガーを設定する。 例として、以下の様な受注明細があったとして、その非正規化項目の整合性を維持するトリガーを書いてみます。 CREATE TABLE T010_受注明細 ( ID　　　　　　　　　　　　　int IDENTITY(1, 1) NOT NULL, T000_ID　　　　　　　　　　 int NOT NULL, 受注CD　　　　　　　　　　　nchar(10) NOT NULL, 行番号　　　　　　　　　　　int NOT NULL, M000_ID　　　　　　　　　　 int, 製品CD　　　　　　　　　　　nvarchar(20) NOT NULL, 製品区分　　　　　　　　　　nvarchar(10) NOT NULL, 製品基準日　　　　　　　　　datetime NO

トリガーの適応範囲はエクセルで出力できるところまでとすると、必然的にできることは限られます。関連チェックや整合性の維持に限られるでしょう。 まずは書いてみる。 以前書いたトリガーを少し修正してみましょう。（SQLServerで書きました。） トリガーを仕込むテーブル。 CREATE TABLE M000_製品 ( ID　　　　　　　　　　　　　　int IDENTITY(1, 1) NOT NULL, CD　　　　　　　　　　　　　　nvarchar(20) NOT NULL, 区分　　　　　　　　　　　　　nvarchar(10) NOT NULL, 名前　　　　　　　　　　　　　nvarchar(255) NOT NULL, ステータス　　　　　　　　　　int NOT NULL, 有効期限開始　　　　　　　　　datetime NOT NULL, 有効期限終了　　　　　　　　　dat

トリガーを嫌う人は非常に多い。 トリガーはレコードの更新を起点（トリガーにして）に自動的に動くので、アプリケーション側のプログラムソースからは追いかけられず、プログラマが意図しない結果になることがある。というのが嫌われる一番の理由でしょう。 しかし、「自動的に動く」というのは非常に便利で、多くのアプリケーションや、OS、IDE、（メタ）プログラミング言語が目指すところです。 では、なぜトリガーは異常に嫌われるのでしょうか。 「自動的にやってくれる」と「勝手に変わった」 トリガーは自動的に動くので非常に便利で、当たり前の制限はありますが何でもできます。「何でもできる」というのは曲者で、トリガーは作っている（コーディングしている）ときは非常に便利ですが「後で訳が分からなくなる」という「諸刃の剣」になる典型のテクニックです。 実際にプロジェクトで使わなくても、試しに使ったぐらいで「諸刃の剣」ぶり

正規化の効率を考えてみる。 正規化は基本的な技術ですが、COBOLから抜けきれていないと正規化すると遅くなると感じる様になる。 これはおそらく私もだろうと思うけれど、効率をグラフ化して最初に訪れた山が最高と感じるものです。それはつまり、落ち始めたらそれ以上は危険と判断するからで、その先に進めなくなる人が非常に多い。 イメージとしてはこんな感じになります。 COBOLから始めた人は、最初に訪れる効率の山が全体としては極めて低くても、それが最高と判断してしまう。実際にCOBOLから始めた人が大半を占めるプロジェクトでは決して珍しくない光景です。 壁から先は見えてないから、COBOLからスタートした人達が見ているのはここまでです。その先は見えていません。 この曲線は、スポーツでも、勉強でも、何にでもあるものですが、いわゆる『壁』ってヤツです。最初に訪れた効率の山をくだるのは非常に怖く辛い。その先

そもそも、私はプログラムを習ったことがないから、一般的な専門学校などでやっている様な講義は知らないしやりようがない。私が講師をやると厳しすぎるので、新人教育を担当することはないのですけれど……。 新人を相手するときに、私が最初に説明するのは「無意識を意識しなさい」ということです。 朝のトイレをプログラムする。 新人に言語の文法を教えても、大概使い物になりません（苦笑）。 で、私が最も重要と考える、無意識を意識するという感覚を掴むために、トイレ（う●ち）をするのをプログラムを書くとこうなる。というのをやります。 １．ドアを開けてトイレに入る。 ２．便器のふたを開ける。 ３．パンツをおろして座る。 ４．LOOP{ ４．１．気張る。 ４．２．IF 時間がもうないとき。{ ４．２．１．IF 我慢できるとき。{ ４．２．１．１．LOOPを抜ける。 } } ４．３．ELSE（時間があるとき）{ ４．３

前回の続きです。やっと完成です。 ざっくりとした考え方（毎回） GROUP BY は集約するので、結果が（集約キーを出力すれば）一意になる。つまり、出力される結果が一意になるまで集約される。 しかし、OLAP（分析）関数は、SELECTされた結果を区切って処理する。そのため、レコード数に変化はない。 集約する → GROUP BY 区切る → PARTITION BY となる。これだけが分かっていれば実は簡単です。 SQLの完成。 拡大する 前回説明したように、OLAP（分析）関数を使ってイメージの通り順に考えて行くと、サブクエリーの中には不要なものも入っていますが、以下の様なSQLが完成します。 SELECT 納品書番号, 行, 商品ID, 単価, 数量, 消費税率 , 明細計, 納品書合計金額, 納品書消費税額 , CASE WHEN ABS(消費税誤差) >= 金額の多い順 THEN

前回の続きです。やっとOLAP（分析）関数までたどり着きました。 ざっくりとした考え方（毎回） GROUP BY は集約するので、結果が（集約キーを出力すれば）一意になる。つまり、出力される結果が一意になるまで集約される。 しかし、OLAP（分析）関数は、SELECTされた結果を区切って処理する。そのため、レコード数に変化はない。 集約する → GROUP BY 区切る → PARTITION BY となる。これだけが分かっていれば実は簡単です。 納品書合計金額までをOLAP（分析）関数で計算する。 OLAP（分析）関数で処理すると SELECT 納品書番号, 行, 商品ID, 単価, 数量, 消費税率 , 単価 * 数量 AS 合計 , ROUND(単価 * 数量 * 消費税率) AS 明細毎の消費税 , SUM(単価 * 数量) OVER (PARTITION BY 納品書番号) AS

OLAP（分析）関数は考え方としては、SQLの他の構文よりも手続き型言語と差が小さい。 考え方ではなく、文法から入る人にとってはとんでもない違いに感じるかも知れませんが、答えは同じなのですから違いはないのです。 長くなるので数回に分けて書こうと思う。 ざっくりとした考え方（毎回） GROUP BY は集約するので、結果が（集約キーを出力すれば）一意になる。つまり、出力される結果が一意になるまで集約される。 しかし、OLAP（分析）関数は、SELECTされた結果を区切って処理する。そのため、レコード数に変化はない。 集約する → GROUP BY 区切る → PARTITION BY となる。これだけが分かっていれば実は簡単です。 イメージしてみましょう。 いつも例題に使っているのは少しややこしいので、以下の条件で考えてみましょう。 ■要望 ■納品書明細（テーブル） 納品書番号 行 商品ID

業務システムのほとんどはナチュラルキーで構築されていると思います。 しかし、シノニムやトリガーを利用すれば、既存システムを変更することなくサロゲートキーを追加して、それ以降、サロゲートキーによる運用も可能になります。手順は以下の通りです。 元のテーブル 以下の３つのテーブルをサロゲートキーによる運用に変更します。 ■ Foods 料理マスタ 物理名論理名備考 CD料理CD主キー Name名前 Price価格 …… ■ Ingredients 材料マスタ 物理名論理名備考 CD材料CD主キー Name名前 Cost_Price材料費 …… ■ Recipes レシピマスタ 物理名論理名備考 Food_CD料理CD主キー（複合） Ingredient_CD材料CD主キー（複合） Quantity使用量 …… IDカラムを付加する。 現在のテーブル名にIDを採番したシノニムを作成し、全部のテーブ

完全に新規の案件というのは本当に少ないので、実践できることはほぼない理想論ですが、私の理想とするテーブル構造と命名法です。 まずは、サロゲートキーについて サロゲートキーというのは業務上意味のないキーのことです。 例えば、生徒テーブルは、年次・クラスID・生徒番号で一意になるとしましょう。 この「年次・クラスID・生徒番号」の複合キーを主キーとせずに、システム側で採番した一意な値を主キーとすることをサロゲートキーといいます。 「年次・クラスID・生徒番号」はナチュラルキーといいます。 基本は全テーブルをサロゲートキーにする方が効率的です。 サロゲートキーを使わない例外 私なりの基準は関係テーブルの場合、他に情報がない場合サロゲートキーは使いません。 例えば ■ 生徒テーブル ID 年次 クラスID 生徒番号 名前 生年月日 …… ■ 科目マスタ ID 名前 …… ■ 履修マスタ（サロゲート