- 投稿日：2021-01-20T23:43:03+09:00
[My SQL] 複数のテーブルを1つに結合する方法
複数のテーブルを1つに結合する方法は大きく２つあり、内部結合と外部結合があります。
内部結合とは？
テーブルの両方に結合条件で指定したレコードが存在しないと、結合後のテーブルに表示されないので、どちらか一方にしかないレコードは表示されません。
内部結合の方法
テーブルAとテーブルBを
内部結合する場合、以下のようになります。SELECT テーブルAから抽出するカラム FROM 基準にしたいテーブルAの名前 JOIN 結合させたいテーブルBの名前 ON 結合条件（テーブルAの名前.基準にするカラム名 = テーブルBの名前.基準にするカラム名）外部結合とは？
外部結合には2種類あり、左外部結合と右外部結合があります。全データを表示する範囲をテーブルA（左側）か、テーブルB（右側）かを決められます。
テーブルの両方に結合条件で指定したレコードが存在しなくても、結合後のテーブルに表示されます。存在しない時は、全データを表示するテーブルではない方にNULLが表示されます。左外部結合の方法
テーブルAとテーブルBを
左外部結合する場合、以下のようになります。
この場合、テーブルBに存在しない時はNULLが表示されます。SELECT テーブルAから抽出するカラム FROM 基準にしたいテーブルAの名前 LEFT JOIN 結合させたいテーブルBの名前 USING 結合条件右外部結合の方法
テーブルAとテーブルBを
右外部結合する場合、以下のようになります。
この場合、テーブルAに存在しない時はNULLが表示されます。SELECT テーブルAから抽出するカラム FROM 基準にしたいテーブルAの名前 RIGHT JOIN 結合させたいテーブルBの名前 USING 結合条件
- 投稿日：2021-01-20T23:40:52+09:00
railsで検索機能を実装する方法（複数カラムも対応)
開発環境
Mac OS Catalina 10.15.7
ruby 2.6系
rails 6.0系前提
前提として、現在私が作っている、記事を投稿できるアプリを例に説明します。
私はモデル名はpostモデルとしています。(参考にする際は、自分用に置き換えてください。)ルーティング設定
まずはルーティングを設定します。
今回はsearchアクションという、基本アクション(index,new,create,show,edit,update,destroy)以外のアクションを定義する必要があるため、collectionを使い設定します。routes.rbRails.application.routes.draw do devise_for :users root to: "posts#index" resources :posts do resources :comments, only: [:create, :destroy] collection do get 'search' end end resources :users, only: :show endcollectionを使うと、ルーティングのURLと実行されるコントローラーを任意にカスタムできます。
また、ルーティングにidを含めたいときはmemberが使えます。routes.rbmember do get 'search' end単純にcollectionのところをmemberにするだけです。
今回の私の例ではidは必要ないので、collectionを使っています。
検索フォーム作成
ルーティングができたので、ビューに配置する検索フォームを作っていきます。
index.html.erb<%= form_with(url: search_posts_path, local: true, method: :get, class: "search-form") do |form| %> <%= form.text_field :keyword, placeholder: "投稿を検索する", class: "search-input" %> <%= form.submit "検索", class: "search-btn" %> <% end %>いろいろ書き方があるとは思いますが、自分はこんな感じで習いました。
ポイントは先ほどのルーティング設定で生成したパス(search_posts_path)が入っていること(パスの名前はコンソールで確認しましょう）、それとmethod: :getを忘れないことぐらいですかね。
加えてform_withのtext_fieldのキー名はkeywordとしました。
モデルに検索の処理を記述
それでは実際に検索する際の処理をモデルに書いていきます。
このとき、コントローラーに書いてしまいたくなる気持ちはわかりますが、テーブルとやり取りをするようなメソッドを定義する際は極力モデルに書くようにしましょう。
def self.search以下(最下部のendは除く)が今回記述した内容です。post.rbclass Post < ApplicationRecord extend ActiveHash::Associations::ActiveRecordExtensions belongs_to_active_hash :category belongs_to :user has_one_attached :image has_many :comments, dependent: :destroy with_options presence: true do validates :image validates :title validates :category_id, numericality: { other_than: 1 , message: "は--以外から選んでください"} end def self.search(search) if search != "" Post.where(['title LIKE(?) OR explanation LIKE(?) OR animal_name LIKE(?)', "%#{search}%", "%#{search}%", "%#{search}%"]) else Post.includes(:user).order('created_at DESC') end end end基本的な書き方としては
オブジェクト名.where('検索をかけたいカラム名 LIKE(?)', "%#{search}%")複数のカラムで検索をかけたいときは
オブジェクト名.where(['検索をかけたいカラム名 LIKE(?) OR 検索をかけたいカラム名 LIKE(?)', "%#{search}%", "%#{search}%"])みたいな感じで実行できます。
自分の場合は3つのカラムで検索をかけたいので上記のような記述になります。また、if文で検索欄が空だった場合には、postの一覧が表示するように記述しました。
コントローラー記述
次にコントローラーを記述します。
posts_controller.rbdef search @posts = Post.search(params[:keyword]) end先ほどモデルに定義したsearchメソッドを使っています。
引数のparamsの中の[:keyword]は、検索フォームを作った際に設定したもので、text_fieldのキー名です。ビュー作成
最後にsearchアクションが参照するビューを作成します。
別に作らなくても、renderでindexページに飛ばしたりもできると思います。search.html.erb<%= render "shared/header"%> <%= form_with(url: search_posts_path, local: true, method: :get, class: "search-form") do |form| %> <%= form.text_field :keyword, placeholder: "投稿を検索する", class: "search-input" %> <%= form.submit "検索", class: "search-btn" %> <% end %> <% if @posts == [] %> <h2 class = "seach-result">検索結果はありません</h2> <% else %> <div class = "post-contents"> <ul class = "post-list"> <% @posts.each do |post| %> <li class = "list"> <%= link_to post_path(post.id) do %> <%= image_tag post.image, class: "post-img" %> <h3 class='post-title'><%= post.title %></h3> <p class = "root-show">〜クリックして詳細を見る〜</p> <% end %> </li> <% end %> </ul> </div> <% end %> <%= link_to new_post_path, class: 'post-btn' do %> <span class='post-btn-text'>投稿する</span> <% end %> <%= render "shared/footer" %>以上がrailsにおける検索機能の実装方法です。
参考になれば幸いです。
- 投稿日：2021-01-20T23:29:52+09:00
rails test がデータベースの参照エラーで失敗する
経緯
Docker の中で Rails と MySQL を立て、 rails test でテストを実行しようとしたところ、エラーとなりました。
docker-compose.ymlservices: mysql_test: image: mysql:5.7 environment: MYSQL_ROOT_PASSWORD: root MYSQL_DATABASE: aaa MYSQL_USER: user MYSQL_PASSWORD: password TZ: 'Asia/Tokyo' command: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci$ rails test test/integration/aaa_test.rb Error: AaaTest#aaa: ActiveRecord::ConnectionNotEstablished: Access denied for user 'user'@'%' to database 'aaa-1'エラー内容を見ると
aaa-1を参照しており、そのデータベースはないのでエラーとなっています。解決策
test_helper.rb にある
parallelize(workers: :number_of_processors)の影響なので、 PARALLEL_WORKERS を変更して suffix がつかないようにします。$ PARALLEL_WORKERS=1 rails test test/integration/aaa_test.rbこれで動作します。
- 投稿日：2021-01-20T12:00:44+09:00
Laravelのfactoryを使って、中間テーブルにデータを挿入したりしてみる。
今回はLaravelのfactory機能を使って、ダミーデータを作成したり、中間テーブルにデータを挿入したりします。
ぶっちゃけ、そこまで難しくありません。
ざっくりしたファクトリーを使用する手順は、
1.ファクトリーを作って、テーブルに挿入するデータのルールを記述する。
2.シーダーを作って、実際にデータを挿入する処理を記述する。
3.作ったシーダーをコマンドで実行できるように登録する。
これだけです。今回は、
novelsテーブル（小説）とtagsテーブル（タグ）の中間テーブルnovel_tagテーブルにデータを挿入する設定です。
また、最大で6つまで小説にタグを設定できるという仮定で進めていきます。それではガンガンやっていきましょう。
1.ファクトリーを作って、テーブルに挿入するデータのルールを記述する。
まずは
novelsテーブル（小説）とtagsテーブル（タグ）用のファクトリーを作成します。下記コマンドを実行してください。
php artisan make:factory NovelFactory php artisan make:factory TagFactoryこれで、
app > database > factories直下にNovelFactory.phpとTagFactory.phpが作成されているはず。それぞれに下記の記述を追加します。
NovelFactory.php<?php use App\Model; use Faker\Generator as Faker; $factory->define(App\Novel::class, function (Faker $faker) { //Usersテーブルの中からランダムに1つのレコードのidを取得する。 $user_id = App\User::all()->random(1)[0]->id; return [ 'title'=>$faker->word, 'summary'=>$faker->sentence, 'user_id'=>$user_id, ]; });TagFactory.php<?php use App\Model; use Faker\Generator as Faker; $factory->define(App\Tag::class, function (Faker $faker) { return [ 'name'=>$faker->word, ]; });簡単に解説しておきます。
まずFactoryを使う時は$factory->define(App\Novel::class, function (Faker $faker) {ココ});の中にコードを記述します。
$user_id = App\User::all()->random(1)[0]->id;こちらのコードはUsersテーブルからランダムで1つのレコードを取得。そして、そのレコードのidを取得しています。
randomメソッドを使えば、引数に与えた数値の数だけ、コレクションの中からインスタンスを取得することができます。
ここで取得したusersテーブルのidを'user_id'=>$user_id,で外部キーを実現しています。2.シーダーを作って、実際にデータを挿入する処理を記述する。
次にシーダーを作成します。
php artisan make:seed NovelsTableSeeder php artisan make:seed TagsTableSeederこれで
app > database > seedsの中にNovelsTableSeeder.phpとTagsTableSeeder.phpができているはず。こちらのファイルを下記のように変更します。
NovelsTableSeeder.php<?php use Illuminate\Database\Seeder; use App\Tag; use App\User; class NovelsTableSeeder extends Seeder { public function run() { $tags = Tag::all(); // factoryを利用 factory(App\Novel::class, 100) ->create() ->each(function (App\Novel $novel) use ($tags) { //1~6までの数値をランダムで取得 $ran = rand(1, 6); // 中間テーブルに紐付け $novel->tags()->attach( //tagsテーブルからランダムで1~6個のインスタンスを紐づける。 $tags->random($ran)->pluck('id')->toArray(), //attachの第二引数は他のカラムに挿入したい値を入れることができる。 //今回はtag_numberというカラムにランダムで1~6の数値を挿入。これは僕の都合上やってるだけなので、気にしなくてOK。 ['tag_number'=>$ran] ); }); } }TagsTableSeeder.php<?php use Illuminate\Database\Seeder; class TagsTableSeeder extends Seeder { public function run() { factory(App\Tag::class, 50)->create(); } }上記の通りです。
多対多の中間テーブルへの紐付けは
attachメソッドを使うのが基本です。
第一引数に紐づけるid、第二引数には他のカラムにも値を追加したい場合はその値を指定します。今回の場合は
$novel->tags()->attachとすることで、中間テーブルのnovel_idカラムに$novelのidが保存されます。次にtag_idカラムの値を指定する必要があるので、attachメソッド内に記述します。$tags->random($ran)->pluck('id')->toArray(),
$tagsには$tags = Tag::all();で取得したtagsテーブルの全データが入っています。その中からランダムで1~6個のインスタンスを取得して、それらのidを配列にしています。$ranは$ran = rand(1, 6);の部分でランダムで1~6の値を挿入しています。このようにしているのは、小説には1~6個までのタグを指定できるという設定にしているからです。
別に1つしかタグ付けしないなら、下記でOK。$tags->random(1)->value('id'),3.作ったシーダーをコマンドで実行できるように登録する。
最後に
app > database > seeds > DatabaseSeeder.phpにて、今作ったシーダーを登録します。<?php use Illuminate\Database\Seeder; class DatabaseSeeder extends Seeder { public function run() { $this->call([ TagsTableSeeder::class, //先にコッチ。 NovelsTableSeeder::class, ]); } }先にタグのシーダーを記述しておかないとエラーになるので注意してください。
あとは下記コマンドを実行すればOkです。
php artisan db:seed駆け足でしたが、これにて終了です。
factoryを使えば簡単に大量のデータを作れます。
慣れないうちは大変だと思いますが、ぜひ使ってみてください。
- 投稿日：2021-01-20T01:43:47+09:00
[MySQL]window関数を紐解く
はじめに
Cloud SQLでもMySQL8がサポートされ、window関数が使えるようになりました。
window関数の基礎的な概念をまとめようと思います。公式ドキュメントはこちらです。https://dev.mysql.com/doc/refman/8.0/en/window-functions.html
環境
MySQL: ^8.0.0
まずはふわっとwindow関数を使ってみる
window関数の定義を公式ドキュメントから引用すると、以下のような定義だそうです。
ウィンドウ関数は、一連のクエリ行に対して集計のような操作を実行します。ただし、集計操作はクエリ行を1つの結果行にグループ化しますが、ウィンドウ関数はクエリ行ごとに結果を生成します。
集計のような操作を行うそうですが、定義だけではわかりにくいので実際にwindow関数を使ってみましょう。
使うデータは以下の通りです。
従業員のID,名前,部署,役職、給与を保存しているテーブルです。
employee_id employee_name department position salary 1 Nakajima Atsushi human reso Manager 8000000 2 Nakamura Mai human reso Manager 65000000 3 Yamamoto Youko human reso Director 6000000 4 Hukuda Kyoko human reso Director 5000000 5 Kobayashi Takahiro human reso Director 45000000 6 Satou Osamu human reso Member 4300000 7 Suzuki Haruka human reso Member 4000000 8 Matsuda Kana human reso Member 4100000 9 Kimura Sayuri human reso Member 4400000 10 Endo Syota Legal Manager 9000000 11 Suzuki Yuka Legal Director 7000000 12 Tanaka Maya Legal Director 7600000 13 Hayasi Asuka Legal Member 5500000 14 Takahasi Noboru Legal Member 5400000 15 Tezuka Kei Legal Member 5300000 各従業員は以下のようになっています
- 各従業員はhuman reso, Legalのいずれかの部署に所属する。
- 各従業員はManager, Director, Memberのいずれかの役職に付いている
window関数を使って見ます。
SELECT employee_id, employee_name, department, position, salary, AVG(salary) OVER(PARTITION BY department) AS salary_average FROM employee; | employee_id | employee_name | department | position | salary | salary_average | | ----------- | ------------------ | ---------- | -------- | -------- | -------------- | | 1 | Nakajima Atsushi | human reso | Manager | 8000000 | 16200000 | | 2 | Nakamura Mai | human reso | Manager | 65000000 | 16200000 | | 3 | Yamamoto Youko | human reso | Director | 6000000 | 16200000 | | 4 | Hukuda Kyoko | human reso | Director | 5000000 | 16200000 | | 5 | Kobayashi Takahiro | human reso | Director | 45000000 | 16200000 | | 6 | Satou Osamu | human reso | Member | 4300000 | 16200000 | | 7 | Suzuki Haruka | human reso | Member | 4000000 | 16200000 | | 8 | Matsuda Kana | human reso | Member | 4100000 | 16200000 | | 9 | Kimura Sayuri | human reso | Member | 4400000 | 16200000 | | 10 | Endo Syota | Legal | Manager | 9000000 | 6633333.333 | | 11 | Suzuki Yuka | Legal | Director | 7000000 | 6633333.333 | | 12 | Tanaka Maya | Legal | Director | 7600000 | 6633333.333 | | 13 | Hayasi Asuka | Legal | Member | 5500000 | 6633333.333 | | 14 | Takahasi Noboru | Legal | Member | 5400000 | 6633333.333 | | 15 | Tezuka Kei | Legal | Member | 5300000 | 6633333.333 |window関数をsalary_averageで使っていますが、各行にまるで部署でGROUP BYしAVG関数を使ったような値が入っています。
このように
集合関数をGROUP BYをせずに各行に結果を返す処理が冒頭で引用したウィンドウ関数は、一連のクエリ行に対して集計のような操作を実行します。ただし、集計操作はクエリ行を1つの結果行にグループ化しますが、ウィンドウ関数はクエリ行ごとに結果を生成しますというものの実態です。同様の結果は以下のようなGROUP BY句を使ったSQLで取得することもできます。
window関数を使った方が簡単にかけますね。SELECT employee_id, employee_name, t.department, position, salary, salary_average FROM employee JOIN ( SELECT department, AVG(salary) AS salary_average FROM employee GROUP BY department ) AS t ON employee.department = t.departmentなんとなく、window関数は集合関数を各行に返すものと理解していただけたと思いますが、まだまだふわっとしていると思いますので、詳しく見ていきましょう。
window関数にはOVER句が必要
window関数のシンタックスを全て書くと以下のようになります。
window_function: [ existing_window_name ] [ over_clause ] over_clause: {OVER (window_spec) | OVER window_name} window_spec: [window_name] [partition_clause] [order_clause] [frame_clause] partition_clause: PARTITION BY expr [, expr] ... order_clause: ORDER BY expr [ASC|DESC] [, expr [ASC|DESC]] ... frame_clause: frame_units frame_extent frame_units: {ROWS | RANGE} frame_extent: {frame_start | frame_between} frame_between: BETWEEN frame_start AND frame_end frame_start, frame_end: { CURRENT ROW | UNBOUNDED PRECEDING | UNBOUNDED FOLLOWING | expr PRECEDING | expr FOLLOWING }呪文のようですね。。。重要そうなところを見ていきましょう。
window_functionがwindow関数のシンタックスです。
window_function: [ existing_window_name ] [ over_clause ]window関数はexisting_window_nameとexisting_window_nameの二つの構文があれば成り立ちます。
- existing_window_name: 実行される関数です。最初のSQLではAVGを使いました。
over_clause: {OVER (window_spec) | OVER window_name}
- over_clause: window関数を構成する重要な構文の一つ。書き方は
OVER ()と名前付きウィンドウをという2つの書き方がある。ここでは主にOVER ()で書く方法について触れる。window_spec: [window_name] [partition_clause] [order_clause] [frame_clause]
- window_spec:
OVER ()の()の中で使われる。フレームを定義する。ここまでの内容をまとめると、window関数は以下のような構文で成り立つことがわかります。
window関数() OVER (フレーム定義)実際、一番最初に使ったSQLを例に出すと
AVG(salary) OVER(PARTITION BY department) AS salary_average ^ ^ window関数 フレーム定義
のようになっています。さて、謎の言葉
フレームが出てきました。
次はフレームについて触れていきます。window関数の勘所,フレームの感覚を掴む
window関数にはフレームという概念があり、そのフレームに対してwindow関数が適用されます。
感覚を掴むために最初に使ったSQLのフレーム定義が実際にどのような動きをしているのか見ていきます。window関数部分のSQLは以下です。
AVG(salary) OVER(PARTITION BY department) AS salary_average
PARTITION BY departmentは以下のようなフレームごとにデータを分割します。| employee_id | employee_name | department | position | salary | | ----------- | ------------------ | ---------- | -------- | -------- | --------- | 1 | Nakajima Atsushi | human reso | Manager | 8000000 | | 2 | Nakamura Mai | human reso | Manager | 65000000 | | 3 | Yamamoto Youko | human reso | Director | 6000000 | | 4 | Hukuda Kyoko | human reso | Director | 5000000 | | 5 | Kobayashi Takahiro | human reso | Director | 45000000 | フレーム① | 6 | Satou Osamu | human reso | Member | 4300000 | | 7 | Suzuki Haruka | human reso | Member | 4000000 | | 8 | Matsuda Kana | human reso | Member | 4100000 | | 9 | Kimura Sayuri | human reso | Member | 4400000 | | ----------- | ------------------ | ---------- | -------- | -------- | --------- | 10 | Endo Syota | Legal | Manager | 9000000 | | 11 | Suzuki Yuka | Legal | Director | 7000000 | | 12 | Tanaka Maya | Legal | Director | 7600000 | フレーム② | 13 | Hayasi Asuka | Legal | Member | 5500000 | | 14 | Takahasi Noboru | Legal | Member | 5400000 | | 15 | Tezuka Kei | Legal | Member | 5300000 |そしてフレーム毎に
AVG(salary)が実行され、それぞれの行に値が返されます。| employee_id | employee_name | department | position | salary | salary_average | | ----------- | ------------------ | ---------- | -------- | -------- | -------------- | | 1 | Nakajima Atsushi | human reso | Manager | 8000000 | 16200000 | | 2 | Nakamura Mai | human reso | Manager | 65000000 | 16200000 | | 3 | Yamamoto Youko | human reso | Director | 6000000 | 16200000 | | 4 | Hukuda Kyoko | human reso | Director | 5000000 | 16200000 | | 5 | Kobayashi Takahiro | human reso | Director | 45000000 | 16200000 | | 6 | Satou Osamu | human reso | Member | 4300000 | 16200000 | | 7 | Suzuki Haruka | human reso | Member | 4000000 | 16200000 | | 8 | Matsuda Kana | human reso | Member | 4100000 | 16200000 | | 9 | Kimura Sayuri | human reso | Member | 4400000 | 16200000 | | 10 | Endo Syota | Legal | Manager | 9000000 | 6633333.333 | | 11 | Suzuki Yuka | Legal | Director | 7000000 | 6633333.333 | | 12 | Tanaka Maya | Legal | Director | 7600000 | 6633333.333 | | 13 | Hayasi Asuka | Legal | Member | 5500000 | 6633333.333 | | 14 | Takahasi Noboru | Legal | Member | 5400000 | 6633333.333 | | 15 | Tezuka Kei | Legal | Member | 5300000 | 6633333.333 |このようにwindow関数はOVER句の中のフレーム定義によって、行を拡張しフレームとしてデータを扱います。そしてフレームに関数を適用されることで、結果
AVG(salary) FROM employee GROUP GY departmentを使ったかのような値がsalary_averageカラムに入ります。フレームについてなんとなくイメージできましたでしょうか？
重要なのは
行を拡張しフレームとしてデータを扱うということです。window関数は
フレームの切り方とフレームに適用させる関数の2つを自由に操作できるようになれば怖くありません。フレームの分割過程
実はフレームは、2つの過程を経て、分割されます。
その過程は以下の通りです。
- パーティション分割
- フレーム分割
フレーム定義のシンタックスを見てみると、
window_spec: [window_name] [partition_clause] [order_clause] [frame_clause]
- window_name: こちらは名前付きウィンドウで使われます。今回は
OVER (フレーム定義)のような構文でフレーム定義をするので、window_nameは使いません。
partition_clause,order_clause,frame_clauseの3つの構文で構成されることがわかります。フレームの分割過程と上記3つの構文を紐づけると
- パーティション分割
- partition_clause
- PARTITION BY句を使う
- order_clause
- ORDER BY句を使う
- フレーム分割
- frame_clause
- ROWS BETWEENなどを使う。
- フレーム定義の最後に使う
のようになります。
3つの構文を全てを使ったSQLを実行して、その処理過程を見て、見ましょう。
実行するSQLは下記です。SELECT employee_id, employee_name, department, position, salary, SUM(salary) OVER( PARTITION BY department ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS salary_sum FROM employee;window関数部分はこちらです。
SUM(salary) OVER( PARTITION BY department ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS salary_sum ^ ^ ^ partition_clause order_clause frame_clause元のデータはこちらです
| employee_id | employee_name | department | position | salary | | ----------- | ------------------ | ---------- | -------- | -------- | | 1 | Nakajima Atsushi | human reso | Manager | 8000000 | | 2 | Nakamura Mai | human reso | Manager | 65000000 | | 3 | Yamamoto Youko | human reso | Director | 6000000 | | 4 | Hukuda Kyoko | human reso | Director | 5000000 | | 5 | Kobayashi Takahiro | human reso | Director | 45000000 | | 6 | Satou Osamu | human reso | Member | 4300000 | | 7 | Suzuki Haruka | human reso | Member | 4000000 | | 8 | Matsuda Kana | human reso | Member | 4100000 | | 9 | Kimura Sayuri | human reso | Member | 4400000 | | 10 | Endo Syota | Legal | Manager | 9000000 | | 11 | Suzuki Yuka | Legal | Director | 7000000 | | 12 | Tanaka Maya | Legal | Director | 7600000 | | 13 | Hayasi Asuka | Legal | Member | 5500000 | | 14 | Takahasi Noboru | Legal | Member | 5400000 | | 15 | Tezuka Kei | Legal | Member | 5300000 |まずpartition_clauseとorder_clauseによって
パーティション分割されます。
- partition_clauseは
PARTITION BY departmentで表されています- order_clauseは
ORDER BY salaryで表されています| employee_id | employee_name | department | position | salary | | ----------- | ------------------ | ---------- | -------- | -------- | ---------- | 7 | Suzuki Haruka | human reso | Member | 4000000 | | 8 | Matsuda Kana | human reso | Member | 4100000 | | 6 | Satou Osamu | human reso | Member | 4300000 | | 9 | Kimura Sayuri | human reso | Member | 4400000 | | 4 | Hukuda Kyoko | human reso | Director | 5000000 | パーティション① | 3 | Yamamoto Youko | human reso | Director | 6000000 | | 1 | Nakajima Atsushi | human reso | Manager | 8000000 | | 5 | Kobayashi Takahiro | human reso | Director | 45000000 | | 2 | Nakamura Mai | human reso | Manager | 65000000 | | ----------- | ------------------ | ---------- | -------- | -------- | ---------- | 15 | Tezuka Kei | Legal | Member | 5300000 | | 14 | Takahasi Noboru | Legal | Member | 5400000 | | 13 | Hayasi Asuka | Legal | Member | 5500000 | パーティション② | 11 | Suzuki Yuka | Legal | Director | 7000000 | | 12 | Tanaka Maya | Legal | Director | 7600000 | | 10 | Endo Syota | Legal | Manager | 9000000 |
employeeテーブルのデータが
PARTITION BY句で指定されたdepartmentと、ORDER BY句で指定されたsalaryを使ってORDER BY department, salaryのようにソートされます。
PARTITION BY句で指定されたdepartment列の値でパーティションが分割されます。続いて、frame_clauseによってフレーム分割されます
- frame_clauseは
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWで表されています。簡略化のために左側のカラムを省略しています。
~ | department | position | salary | ~ | ---------- | -------- | -------- | -------------------------------------- パーティション①の開始点 ~ | human reso | Member | 4000000 | フレーム1 | | | ~ | ---------- | -------- | -------- | ------- | | | ~ | human reso | Member | 4100000 | フレーム2 | | ~ | ---------- | -------- | -------- | ------------------- | | ~ | human reso | Member | 4300000 | フレーム3 | ~ | ---------- | -------- | -------- | ------------------------------- | ~ | human reso | Member | 4400000 | | . . . | . . . | . . . | ~ | human reso | Manager | 65000000 | フレーム9 ~ | ---------- | -------- | -------- | -------------------------------------- パーティション①の終了点&パーティション②の開始点 ~ | Legal | Member | 5300000 | フレーム10 | | | ~ | ---------- | -------- | -------- | ------- | | | ~ | Legal | Member | 5400000 | フレーム11 | | ~ | ---------- | -------- | -------- | ------------------- | | ~ | Legal | Member | 5500000 | フレーム12 | ~ | ---------- | -------- | -------- | ---------------------------------- | ~ | Legal | Director | 7000000 | | . . . | . . . | . . . | ~ | Legal | Manager | 9000000 | フレーム15 ~ | ---------- | -------- | -------- | -------------------------------------- パーティション②の終了点以下の評価基準でフレームを分割します
ROWSなので行を評価しますBETWEEN UNBOUNDED PRECEDINGなのでパーティションの開始行からフレームを始めますAND CURRENT ROWなので評価されている行でフレームを終了します最後にフレームごとに関数を適用します。
- 関数は
SUM(salary)です| employee_id | employee_name | department | position | salary | salary_sum | | ----------- | ------------------ | ---------- | -------- | -------- | ---------- | | 7 | Suzuki Haruka | human reso | Member | 4000000 | 4000000 | | 8 | Matsuda Kana | human reso | Member | 4100000 | 8100000 | | 6 | Satou Osamu | human reso | Member | 4300000 | 12400000 | | 9 | Kimura Sayuri | human reso | Member | 4400000 | 16800000 | | 4 | Hukuda Kyoko | human reso | Director | 5000000 | 21800000 | | 3 | Yamamoto Youko | human reso | Director | 6000000 | 27800000 | | 1 | Nakajima Atsushi | human reso | Manager | 8000000 | 35800000 | | 5 | Kobayashi Takahiro | human reso | Director | 45000000 | 80800000 | | 2 | Nakamura Mai | human reso | Manager | 65000000 | 145800000 | | 15 | Tezuka Kei | Legal | Member | 5300000 | 5300000 | | 14 | Takahasi Noboru | Legal | Member | 5400000 | 10700000 | | 13 | Hayasi Asuka | Legal | Member | 5500000 | 16200000 | | 11 | Suzuki Yuka | Legal | Director | 7000000 | 23200000 | | 12 | Tanaka Maya | Legal | Director | 7600000 | 30800000 | | 10 | Endo Syota | Legal | Manager | 9000000 | 39800000 |結果salary_sumが演算され各行に返されます。
まとめ
フレームとフレームに適用する関数という2つの概念がwindow関数の根幹をなす概念です。
- 投稿日：2021-01-20T01:43:47+09:00
[MySQL]window関数を紐解くexisting_window_name
はじめに
Cloud SQLでもMySQL8がサポートされ、window関数が使えるようになりました。
window関数の基礎的な概念をまとめようと思います。公式ドキュメントはこちらです。https://dev.mysql.com/doc/refman/8.0/en/window-functions.html
環境
MySQL: ^8.0.0
まずはふわっとwindow関数を使ってみる
window関数の定義を公式ドキュメントから引用すると、以下のような定義だそうです。
ウィンドウ関数は、一連のクエリ行に対して集計のような操作を実行します。ただし、集計操作はクエリ行を1つの結果行にグループ化しますが、ウィンドウ関数はクエリ行ごとに結果を生成します。
集計のような操作を行うそうですが、定義だけではわかりにくいので実際にwindow関数を使ってみましょう。
使うデータは以下の通りです。
従業員のID,名前,部署,役職、給与を保存しているテーブルです。
employee_id employee_name department position salary 1 Nakajima Atsushi human reso Manager 8000000 2 Nakamura Mai human reso Manager 65000000 3 Yamamoto Youko human reso Director 6000000 4 Hukuda Kyoko human reso Director 5000000 5 Kobayashi Takahiro human reso Director 45000000 6 Satou Osamu human reso Member 4300000 7 Suzuki Haruka human reso Member 4000000 8 Matsuda Kana human reso Member 4100000 9 Kimura Sayuri human reso Member 4400000 10 Endo Syota Legal Manager 9000000 11 Suzuki Yuka Legal Director 7000000 12 Tanaka Maya Legal Director 7600000 13 Hayasi Asuka Legal Member 5500000 14 Takahasi Noboru Legal Member 5400000 15 Tezuka Kei Legal Member 5300000 各従業員は以下のようになっています
- 各従業員はhuman reso, Legalのいずれかの部署に所属する。
- 各従業員はManager, Director, Memberのいずれかの役職に付いている
window関数を使って見ます。
SELECT employee_id, employee_name, department, position, salary, AVG(salary) OVER(PARTITION BY department) AS salary_average FROM employee; | employee_id | employee_name | department | position | salary | salary_average | | ----------- | ------------------ | ---------- | -------- | -------- | -------------- | | 1 | Nakajima Atsushi | human reso | Manager | 8000000 | 16200000 | | 2 | Nakamura Mai | human reso | Manager | 65000000 | 16200000 | | 3 | Yamamoto Youko | human reso | Director | 6000000 | 16200000 | | 4 | Hukuda Kyoko | human reso | Director | 5000000 | 16200000 | | 5 | Kobayashi Takahiro | human reso | Director | 45000000 | 16200000 | | 6 | Satou Osamu | human reso | Member | 4300000 | 16200000 | | 7 | Suzuki Haruka | human reso | Member | 4000000 | 16200000 | | 8 | Matsuda Kana | human reso | Member | 4100000 | 16200000 | | 9 | Kimura Sayuri | human reso | Member | 4400000 | 16200000 | | 10 | Endo Syota | Legal | Manager | 9000000 | 6633333.333 | | 11 | Suzuki Yuka | Legal | Director | 7000000 | 6633333.333 | | 12 | Tanaka Maya | Legal | Director | 7600000 | 6633333.333 | | 13 | Hayasi Asuka | Legal | Member | 5500000 | 6633333.333 | | 14 | Takahasi Noboru | Legal | Member | 5400000 | 6633333.333 | | 15 | Tezuka Kei | Legal | Member | 5300000 | 6633333.333 |window関数をsalary_averageで使っていますが、各行にまるで部署でGROUP BYしAVG関数を使ったような値が入っています。
このように
集合関数をGROUP BYをせずに各行に結果を返す処理が冒頭で引用したウィンドウ関数は、一連のクエリ行に対して集計のような操作を実行します。ただし、集計操作はクエリ行を1つの結果行にグループ化しますが、ウィンドウ関数はクエリ行ごとに結果を生成しますというものの実態です。同様の結果は以下のようなGROUP BY句を使ったSQLで取得することもできます。
window関数を使った方が簡単にかけますね。SELECT employee_id, employee_name, t.department, position, salary, salary_average FROM employee JOIN ( SELECT department, AVG(salary) AS salary_average FROM employee GROUP BY department ) AS t ON employee.department = t.departmentなんとなく、window関数は集合関数を各行に返すものと理解していただけたと思いますが、まだまだふわっとしていると思いますので、詳しく見ていきましょう。
window関数にはOVER句が必要
window関数のシンタックスを全て書くと以下のようになります。
window_function: [ existing_window_name ] [ over_clause ] over_clause: {OVER (window_spec) | OVER window_name} window_spec: [window_name] [partition_clause] [order_clause] [frame_clause] partition_clause: PARTITION BY expr [, expr] ... order_clause: ORDER BY expr [ASC|DESC] [, expr [ASC|DESC]] ... frame_clause: frame_units frame_extent frame_units: {ROWS | RANGE} frame_extent: {frame_start | frame_between} frame_between: BETWEEN frame_start AND frame_end frame_start, frame_end: { CURRENT ROW | UNBOUNDED PRECEDING | UNBOUNDED FOLLOWING | expr PRECEDING | expr FOLLOWING }呪文のようですね。。。重要そうなところを見ていきましょう。
window_functionがwindow関数のシンタックスです。
window_function: [ existing_window_name ] [ over_clause ]window関数はexisting_window_nameとover_clauseの二つの構文があれば成り立ちます。
- existing_window_name: 実行される関数です。最初のSQLではAVGを使いました。
over_clause: {OVER (window_spec) | OVER window_name}
- over_clause: window関数を構成する重要な構文の一つ。書き方は
OVER ()と名前付きウィンドウをという2つの書き方がある。ここでは主にOVER ()で書く方法について触れる。window_spec: [window_name] [partition_clause] [order_clause] [frame_clause]
- window_spec:
OVER ()の()の中で使われる。フレームを定義する。ここまでの内容をまとめると、window関数は以下のような構文で成り立つことがわかります。
window関数() OVER (フレーム定義)実際、一番最初に使ったSQLを例に出すと
AVG(salary) OVER(PARTITION BY department) AS salary_average ^ ^ window関数 フレーム定義
のようになっています。さて、謎の言葉
フレームが出てきました。
次はフレームについて触れていきます。window関数の勘所,フレームの感覚を掴む
window関数にはフレームという概念があり、そのフレームに対してwindow関数が適用されます。
感覚を掴むために最初に使ったSQLのフレーム定義が実際にどのような動きをしているのか見ていきます。window関数部分のSQLは以下です。
AVG(salary) OVER(PARTITION BY department) AS salary_average
PARTITION BY departmentは以下のようなフレームごとにデータを分割します。| employee_id | employee_name | department | position | salary | | ----------- | ------------------ | ---------- | -------- | -------- | --------- | 1 | Nakajima Atsushi | human reso | Manager | 8000000 | | 2 | Nakamura Mai | human reso | Manager | 65000000 | | 3 | Yamamoto Youko | human reso | Director | 6000000 | | 4 | Hukuda Kyoko | human reso | Director | 5000000 | | 5 | Kobayashi Takahiro | human reso | Director | 45000000 | フレーム① | 6 | Satou Osamu | human reso | Member | 4300000 | | 7 | Suzuki Haruka | human reso | Member | 4000000 | | 8 | Matsuda Kana | human reso | Member | 4100000 | | 9 | Kimura Sayuri | human reso | Member | 4400000 | | ----------- | ------------------ | ---------- | -------- | -------- | --------- | 10 | Endo Syota | Legal | Manager | 9000000 | | 11 | Suzuki Yuka | Legal | Director | 7000000 | | 12 | Tanaka Maya | Legal | Director | 7600000 | フレーム② | 13 | Hayasi Asuka | Legal | Member | 5500000 | | 14 | Takahasi Noboru | Legal | Member | 5400000 | | 15 | Tezuka Kei | Legal | Member | 5300000 |そしてフレーム毎に
AVG(salary)が実行され、それぞれの行に値が返されます。| employee_id | employee_name | department | position | salary | salary_average | | ----------- | ------------------ | ---------- | -------- | -------- | -------------- | | 1 | Nakajima Atsushi | human reso | Manager | 8000000 | 16200000 | | 2 | Nakamura Mai | human reso | Manager | 65000000 | 16200000 | | 3 | Yamamoto Youko | human reso | Director | 6000000 | 16200000 | | 4 | Hukuda Kyoko | human reso | Director | 5000000 | 16200000 | | 5 | Kobayashi Takahiro | human reso | Director | 45000000 | 16200000 | | 6 | Satou Osamu | human reso | Member | 4300000 | 16200000 | | 7 | Suzuki Haruka | human reso | Member | 4000000 | 16200000 | | 8 | Matsuda Kana | human reso | Member | 4100000 | 16200000 | | 9 | Kimura Sayuri | human reso | Member | 4400000 | 16200000 | | 10 | Endo Syota | Legal | Manager | 9000000 | 6633333.333 | | 11 | Suzuki Yuka | Legal | Director | 7000000 | 6633333.333 | | 12 | Tanaka Maya | Legal | Director | 7600000 | 6633333.333 | | 13 | Hayasi Asuka | Legal | Member | 5500000 | 6633333.333 | | 14 | Takahasi Noboru | Legal | Member | 5400000 | 6633333.333 | | 15 | Tezuka Kei | Legal | Member | 5300000 | 6633333.333 |このようにwindow関数はOVER句の中のフレーム定義によって、行を拡張しフレームとしてデータを扱います。そしてフレームに関数を適用されることで、結果
AVG(salary) FROM employee GROUP GY departmentを使ったかのような値がsalary_averageカラムに入ります。フレームについてなんとなくイメージできましたでしょうか？
重要なのは
行を拡張しフレームとしてデータを扱うということです。window関数は
フレームの切り方とフレームに適用させる関数の2つを自由に操作できるようになれば怖くありません。フレームの分割過程
実はフレームは、2つの過程を経て、分割されます。
その過程は以下の通りです。
- パーティション分割
- フレーム分割
フレーム定義のシンタックスを見てみると、
window_spec: [window_name] [partition_clause] [order_clause] [frame_clause]
- window_name: こちらは名前付きウィンドウで使われます。今回は
OVER (フレーム定義)のような構文でフレーム定義をするので、window_nameは使いません。
partition_clause,order_clause,frame_clauseの3つの構文で構成されることがわかります。フレームの分割過程と上記3つの構文を紐づけると
- パーティション分割
- partition_clause
- PARTITION BY句を使う
- order_clause
- ORDER BY句を使う
- フレーム分割
- frame_clause
- ROWS BETWEENなどを使う。
- フレーム定義の最後に使う
のようになります。
3つの構文を全てを使ったSQLを実行して、その処理過程を見て、見ましょう。
実行するSQLは下記です。SELECT employee_id, employee_name, department, position, salary, SUM(salary) OVER( PARTITION BY department ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS salary_sum FROM employee;window関数部分はこちらです。
SUM(salary) OVER( PARTITION BY department ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS salary_sum ^ ^ ^ partition_clause order_clause frame_clause元のデータはこちらです
| employee_id | employee_name | department | position | salary | | ----------- | ------------------ | ---------- | -------- | -------- | | 1 | Nakajima Atsushi | human reso | Manager | 8000000 | | 2 | Nakamura Mai | human reso | Manager | 65000000 | | 3 | Yamamoto Youko | human reso | Director | 6000000 | | 4 | Hukuda Kyoko | human reso | Director | 5000000 | | 5 | Kobayashi Takahiro | human reso | Director | 45000000 | | 6 | Satou Osamu | human reso | Member | 4300000 | | 7 | Suzuki Haruka | human reso | Member | 4000000 | | 8 | Matsuda Kana | human reso | Member | 4100000 | | 9 | Kimura Sayuri | human reso | Member | 4400000 | | 10 | Endo Syota | Legal | Manager | 9000000 | | 11 | Suzuki Yuka | Legal | Director | 7000000 | | 12 | Tanaka Maya | Legal | Director | 7600000 | | 13 | Hayasi Asuka | Legal | Member | 5500000 | | 14 | Takahasi Noboru | Legal | Member | 5400000 | | 15 | Tezuka Kei | Legal | Member | 5300000 |まずpartition_clauseとorder_clauseによって
パーティション分割されます。
- partition_clauseは
PARTITION BY departmentで表されています- order_clauseは
ORDER BY salaryで表されています| employee_id | employee_name | department | position | salary | | ----------- | ------------------ | ---------- | -------- | -------- | ---------- | 7 | Suzuki Haruka | human reso | Member | 4000000 | | 8 | Matsuda Kana | human reso | Member | 4100000 | | 6 | Satou Osamu | human reso | Member | 4300000 | | 9 | Kimura Sayuri | human reso | Member | 4400000 | | 4 | Hukuda Kyoko | human reso | Director | 5000000 | パーティション① | 3 | Yamamoto Youko | human reso | Director | 6000000 | | 1 | Nakajima Atsushi | human reso | Manager | 8000000 | | 5 | Kobayashi Takahiro | human reso | Director | 45000000 | | 2 | Nakamura Mai | human reso | Manager | 65000000 | | ----------- | ------------------ | ---------- | -------- | -------- | ---------- | 15 | Tezuka Kei | Legal | Member | 5300000 | | 14 | Takahasi Noboru | Legal | Member | 5400000 | | 13 | Hayasi Asuka | Legal | Member | 5500000 | パーティション② | 11 | Suzuki Yuka | Legal | Director | 7000000 | | 12 | Tanaka Maya | Legal | Director | 7600000 | | 10 | Endo Syota | Legal | Manager | 9000000 |
employeeテーブルのデータが
PARTITION BY句で指定されたdepartmentと、ORDER BY句で指定されたsalaryを使ってORDER BY department, salaryのようにソートされます。
PARTITION BY句で指定されたdepartment列の値でパーティションが分割されます。続いて、frame_clauseによってフレーム分割されます
- frame_clauseは
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWで表されています。簡略化のために左側のカラムを省略しています。
~ | department | position | salary | ~ | ---------- | -------- | -------- | -------------------------------------- パーティション①の開始点 ~ | human reso | Member | 4000000 | フレーム1 | | | ~ | ---------- | -------- | -------- | ------- | | | ~ | human reso | Member | 4100000 | フレーム2 | | ~ | ---------- | -------- | -------- | ------------------- | | ~ | human reso | Member | 4300000 | フレーム3 | ~ | ---------- | -------- | -------- | ------------------------------- | ~ | human reso | Member | 4400000 | | . . . | . . . | . . . | ~ | human reso | Manager | 65000000 | フレーム9 ~ | ---------- | -------- | -------- | -------------------------------------- パーティション①の終了点&パーティション②の開始点 ~ | Legal | Member | 5300000 | フレーム10 | | | ~ | ---------- | -------- | -------- | ------- | | | ~ | Legal | Member | 5400000 | フレーム11 | | ~ | ---------- | -------- | -------- | ------------------- | | ~ | Legal | Member | 5500000 | フレーム12 | ~ | ---------- | -------- | -------- | ---------------------------------- | ~ | Legal | Director | 7000000 | | . . . | . . . | . . . | ~ | Legal | Manager | 9000000 | フレーム15 ~ | ---------- | -------- | -------- | -------------------------------------- パーティション②の終了点以下の評価基準でフレームを分割します
ROWSなので行を評価しますBETWEEN UNBOUNDED PRECEDINGなのでパーティションの開始行からフレームを始めますAND CURRENT ROWなので評価されている行でフレームを終了します最後にフレームごとに関数を適用します。
- 関数は
SUM(salary)です| employee_id | employee_name | department | position | salary | salary_sum | | ----------- | ------------------ | ---------- | -------- | -------- | ---------- | | 7 | Suzuki Haruka | human reso | Member | 4000000 | 4000000 | | 8 | Matsuda Kana | human reso | Member | 4100000 | 8100000 | | 6 | Satou Osamu | human reso | Member | 4300000 | 12400000 | | 9 | Kimura Sayuri | human reso | Member | 4400000 | 16800000 | | 4 | Hukuda Kyoko | human reso | Director | 5000000 | 21800000 | | 3 | Yamamoto Youko | human reso | Director | 6000000 | 27800000 | | 1 | Nakajima Atsushi | human reso | Manager | 8000000 | 35800000 | | 5 | Kobayashi Takahiro | human reso | Director | 45000000 | 80800000 | | 2 | Nakamura Mai | human reso | Manager | 65000000 | 145800000 | | 15 | Tezuka Kei | Legal | Member | 5300000 | 5300000 | | 14 | Takahasi Noboru | Legal | Member | 5400000 | 10700000 | | 13 | Hayasi Asuka | Legal | Member | 5500000 | 16200000 | | 11 | Suzuki Yuka | Legal | Director | 7000000 | 23200000 | | 12 | Tanaka Maya | Legal | Director | 7600000 | 30800000 | | 10 | Endo Syota | Legal | Manager | 9000000 | 39800000 |結果salary_sumが演算され各行に返されます。
まとめ
フレームとフレームに適用する関数という2つの概念がwindow関数の根幹をなす概念です。