Movatterモバイル変換

PgDay2012 LightningTalkSQL上級者にこそ知って欲しいなぜO/Rマッパーが重要か？ makoto kuwata <kwa@kuwata-lab.com> http://www.kuwata-lab.com/

Ruby## 販売実績上位Nコを抽出def sales_top(n, rows) xs = rows.map {|row| Sale.new(*row) } xs = xs.select {|x| ! x.deleted_at } xs = xs.sort_by {|x| - x.amount } xs = xs[0, 10] return xsend copyright© 2012 kuwata-lab.com all rights reserved

Ruby## 販売実績上位Nコを抽出def sales_top(n, rows) xs = rows.map {|row| Sale.new(*row) } xs = xs.select {|x| ! x.deleted_at } xs = xs.sort_by {|x| - x.amount } xs = xs[0, 10] select from相当 return xsend copyright© 2012 kuwata-lab.com all rights reserved

Ruby## 販売実績上位Nコを抽出def sales_top(n, rows) xs = rows.map {|row| Sale.new(*row) } xs = xs.select {|x| ! x.deleted_at } xs = xs.sort_by {|x| - x.amount } xs = xs[0, 10] return xs where相当end copyright© 2012 kuwata-lab.com all rights reserved

Ruby## 販売実績上位Nコを抽出def sales_top(n, rows) xs = rows.map {|row| Sale.new(*row) } xs = xs.select {|x| ! x.deleted_at } xs = xs.sort_by {|x| - x.amount } xs = xs[0, 10] return xs order by相当end copyright© 2012 kuwata-lab.com all rights reserved

Ruby## 販売実績上位Nコを抽出def sales_top(n, rows) xs = rows.map {|row| Sale.new(*row) } xs = xs.select {|x| ! x.deleted_at } xs = xs.sort_by {|x| - x.amount } xs = xs[0, 10] return xs limit相当end copyright© 2012 kuwata-lab.com all rights reserved

Ruby## 販売実績上位Nコを抽出def sales_top(n, rows) xs = rows.map {|row| Sale.new(*row) } xs = xs.select {|x| ! x.deleted_at } xs = xs.sort_by {|x| - x.amount } xs = xs[0, 10] return xsend 1つの関数でいろんなことをやりすぎている copyright© 2012 kuwata-lab.com all rights reserved

Rubydef to_sales(rows) rows.map {|row| Sale.new(*row) }enddef active(sales) sales.select {|x| ! x.deleted_at }enddef top(n, sales) sales = sales.sort_by {|x| - x.amount } return sales[0, 10]end copyright© 2012 kuwata-lab.com all rights reserved

Rubyclas Sales def initialize(rows) @all = rows.map {|row| Sales.new(*row) } end attr_reader :all def active @all = @all.select {|x| ! x.deleted } return self end def top(n) @all = @all.sort_by {|x| - x.amount } @all = @all[0, n] return self endend copyright© 2012 kuwata-lab.com all rights reserved

Ruby where deleted = falseclass Sales < ActiveReocrd::Base に相当する「部分」 named_scope :active, {:conditions=>"deleted = false"} named_scope :top, lambda do |n| {:order=>"amount desc", :limit=>n} end order by amount desc limit nend に相当する「部分」 copyright© 2012 kuwata-lab.com all rights reserved

「部分」を組み合わせて Ruby 「全体」を構築Sales.active().top(10).all()## これは## select * from sales## where deleted = false## order by amount desc limit 10## を生成して実行する ※(2012-12-19) 「と同じ」を「を生成して実行する」に修正 copyright© 2012 kuwata-lab.com all rights reserved

Rubyclass Sales where deleted = false include DataMapper::Resource に相当する「部分」 def self.active where(:deleted => false) end def self.top(n) order(:amount.desc).limit(n) endend order by amount desc limit n に相当する「部分」 copyright© 2012 kuwata-lab.com all rights reserved

「部分」を組み合わせて Ruby 「全体」を構築Sales.active().top(10).all()## これも## select * from sales## where deleted = false## order by amount desc limit 10## を生成して実行する ※(2012-12-19) 「と同じ」を「を生成して実行する」に修正 copyright© 2012 kuwata-lab.com all rights reserved

SQL-- 年齢が20歳の社員が所属する部署一覧select * from departmentswhere id in (select dept_id from employees where age = 20)order by name SQLに「部品化」の機能がないことを示す典型例 ※(2012-12-13) "select id" を "select dept_id" に修正 copyright© 2012 kuwata-lab.com all rights reserved

副問い合わせを切り出して名前を付ける Rubydept_ids = Employee.select(:dept_id) .filter(:age=>20)Department.filter(:id=>dept_ids).all() SQL構築が簡潔になる SQLselect * from departmentswhere id in (select dept_id from employees where age = 20) copyright© 2012 kuwata-lab.com all rights reserved

Rubydept_ids = Employee.select(:dept_id) .filter(:age=>20)Department.filter(:id=>dept_ids).all()Budget.filter(:dept_id=>dept_ids).all() 部品化した副問い合わせを複数の SQLで共用できる(with句より便利) copyright© 2012 kuwata-lab.com all rights reserved

SQL-- 誕生日をもとに年齢を計算select date_part('year', age(birth)) as age, count(*)from userswhere date_part('year', age(birth)) < 20group by date_part('year', age(birth))order by age 同じ式が重複して出現 copyright© 2012 kuwata-lab.com all rights reserved

年齢の計算式を表す構文木を作り、 Pythonfrom sqlalchemy.sql import func as fnage = fn.date_part('year', fn.age(User.birth))rows = DBSession .query(age, fn.count('*')) .select_from(User) .filter(age < 20) .group_by(age) .order_by(age) .all() それを複数箇所で使用できる copyright© 2012 kuwata-lab.com all rights reserved

Movatterモバイル変換

Change Language

SQL上級者こそ知って欲しい、なぜO/Rマッパーが重要か？

Embed presentation

Recommended

More Related Content

What's hot

Viewers also liked

Similar to SQL上級者こそ知って欲しい、なぜO/Rマッパーが重要か？

More from kwatch

SQL上級者こそ知って欲しい、なぜO/Rマッパーが重要か？