sql oracle Advent Calendar 2018の**11日目です。

枠が余っていたので、思うままに書いてみました。19年のが始まるまでには埋めたい。。
定義ファイルチックに実装できないかなーと思って書いてみたら、案外それっぽくできた。cfgファイル的な感じで。

sql oracle Advent Calendar 2018

はじめに〜書こうと思った理由について〜

SQLの学習を継続的にやっているのですが、
まだ理解していない部分を理解して、アウトプットしておこうと思い、
記事を書きました。

対象とする人

・SQLの学習をしはじめて間もない人
・SQLのデータ型についてイマイチ分かっていない人

この記事の流れ

・データ型について
・固定長？可変長？
・固定長（CHAR型）
・可変長(VARCHAR型）
・まとめ

データ型について

データベースのテーブルって、列ごとに格納できる「データの種類」というのが決められているんですよね。
そのデータの種類をあらわすものを「データ型」と呼びます。

代表的なデータ型は下記となります。

データ種別	区分	代表的なデータ型名前
文字列	固定長	CHAR型
文字列	可変長	VARCHAR型
数値	整数値	INTEGER型
数値	小数	DEMICAL型,REAL型
日付と時刻	--	DATETIME型,DATE型,TIME型

固定長？可変長？

上に書かれた表を見て、
整数値
小数
日付と時刻
は意味が分かるけど、
固定長、可変長ってなに？？聞いたこと無い・・・。

と思った人がいるかと思いますので、解説します。

固定長（CHAR型）

固定長とは、一言で言うと、「入るデータの長さが決まっている」、という意味です。
また、CHAR型というのは、固定長の文字列データを扱うデータ型のことです。

例えば、CHAR（10）とかかれていたら、

「10バイトまで文字をきちんと入れてねー」

ということになります。
そして、もし、格納しようとしている文字列が10バイトではなく8バイトだとします。

8バイトだと10バイトに満たないですよね。
その場合だと、文字列の右側に空白が2バイト追加されます。
そして、10バイトぴったりに調整された後、格納されます。

CHAR型は社員番号や郵便番号といった、格納するデータの桁数が決まっているものに対して使われるデータ型です。

可変長(VARCHAR型）

可変長（VARCHAR型）とは、一言で言うと、「中に入るデータの長さが決まっていない」という意味です。
また、VARCHAR型というのは、可変長の文字列データを扱うデータ型のことです。

VARCHAR型では、もし格納する文字列の長さが足りていなくても、右側に空白を追加する、といった調整はありません。

例えば、VARCHAR（10)とかかれていたら、

「10バイトまで文字を入れてねー、10バイトまで８バイトでも3バイトでも大丈夫だよー」
ということになります。

格納しようとしている文字列が8バイトでも3バイトでも、そのままの長さで格納することが可能です。

そのため、

VARCHAR型は氏名や書籍名といった、格納するデータの桁数が変動するものに対して使われるデータ型です。

まとめ

固定長（CHAR型）とは、「入るデータの長さが決まっている」ということ。
例として、社員番号や郵便番号の文字列を格納する時に使われる。

可変長（VARCHAR型）とは、「中に入るデータの長さが決まっていない」ということ。
例として、氏名や書籍名といった文字列を格納する時に使われる。

というわけで・・・

お読みいただきありがとうございました。
間違い等ございましたら、ご指摘いただけると嬉しいです！

参考図書 : スッキリわかるSQL入門

はじめに

本記事はRails5.2にて作成されています、他のバージョンでの互換性を保証しません。

Table構成

Model構成

#### item.rb ####
class Item < ActiveRecord::Base
  belongs_to :order
end

#### order.rb ####
class Order < ActiveRecord::Base
  has_many :items
  has_many :reviews
end

#### review.rb ####
class Review < ActiveRecord::Base
  belongs_to :order
  has_many :themes
end

#### theme.rb ####
class Theme < ActiveRecord::Base
  belongs_to :review
end

各種実装

多段Association

orderからthemeを二段has_manyしたい & themeからorderを二段belongs_toしたい

#### order.rb ####
class Order < ActiveRecord::Base
  # ...
  has_many :themes, through: :reviews
end

#### theme.rb ####
class Theme < ActiveRecord::Base
  # ...
  delegate :order, to: :reviews
end

注意: has_many, throughの逆はdelegate, toを使います

こんなSQLが発行されます:

pry(main)> Theme.last.order
  Theme Load (0.4ms)  SELECT  `reviews`.* FROM `reviews` ORDER BY `reviews`.`id` DESC LIMIT 1
  Review Load (0.3ms)  SELECT  `reviews`.* FROM `reviews` WHERE `reviews`.`id` = 1 LIMIT 1
  Order Load (0.5ms)  SELECT  `orders`.* FROM `orders` INNER JOIN `reviews` ON `reviews`.`id` = `orders`.`review_id` WHERE `orders`.`review_id` = 1 LIMIT 1

多段Scope(多段Joins)

itemをreview.textでsortしたい

#### item.rb ####
class Item < ActiveRecord::Base
  # ...
  scope :join_reviews, -> { joins(:order, order: :reviews) }
end

こんなSQLが発行されます:

pry(main)> Item.all.order("review.theme_id desc")
  Item Load (7.0ms)  SELECT  `items`.* FROM `items` INNER JOIN `orders` ON `orders`.`id` = `items`.`order_id` INNER JOIN `reviews` ON `reviews`.`order_id` = `orders`.`id` ORDER BY reviews.text DESC

参考

https://blog.toshimaru.net/belongs_to-through/
https://qiita.com/takashisite/items/9d1484b88708e2872134
https://qiita.com/ishidamakot/items/7dba557d764362a828ff

レコードの縦横変換

やりたいこと

下記の表のように発注ごとに商品の売り上げ金額を持っているような場合に、発注IDごとの集計をするために縦横変換を行いたい。

元データ

発注ID	商品	金額
TK001	コーヒー	200
TK001	緑茶	300
TK001	ほうじ茶	400
TK002	コーヒー	500
TK002	ほうじ茶	600

縦横変換後

発注ID	コーヒー	緑茶	ほうじ茶
TK001	200	300	400
TK002	500	0	600

サンプルコード

発注IDごとにデータをGROUP BYを行い、その合計額を取ります。
合計額にする必要がない場合はSUMをMAXなどに変え、1レコードのみ取得するなどの方法が取れます。

SELECT 発注ID,
       SUM(CASE WHEN 商品 = 'コーヒー' THEN 金額 END) AS コーヒー,
       SUM(CASE WHEN 商品 = '緑茶'     THEN 金額 END) AS 緑茶,
       SUM(CASE WHEN 商品 = 'ほうじ茶' THEN 金額 END) AS ほうじ茶
FROM 元データ
GROUP BY 発注ID
;