導入

業務アプリ開発でDB操作・SQLは必須ですね。体系性に欠けますが個人的な知見を記したいと思います。
下記エントリの応用編となります。

mara-ashida.hatenablog.com

★テーマ

ざっくり言うと、各グループにおいて、点数などがTOPやWORSTであるようなレコードを「集計結果と併せて」表示する方法について記載します。

正確に言うと、GROUP BYで集計した際、「集計結果と併せて」各グループでAカラムの値が最大/最小となるようなBカラムの値を抽出する方法について記録します。

例を挙げると、GROUP BYで、場所ごとにお店の件数を集計した際、集計結果と併せて、レビュー点数が最大のお店も表示する方法です。

GROUP BY句で指定したカラムにおいて、値が同じ行は1行に集約されてしまい、
「各行の値を参照して、特定の条件を満たすレコードを抽出する」という発想が難しいので記載しようと思いました。

目次・流れ

No	タイトル
1	概要/事例/目的
2	コード/実行結果
3	解説・補足

＜前提＞
・データ操作言語(※DML/Data Manipulation Language)について概要を理解していること。
※SELECT/INSERT/UPDATE/DELETEなどテーブルに対するデータの取得・追加・更新・削除を行うコマンド。
・事例等は極力シンプルな記載にしています。

＜実行環境＞
DBのミドルウェア：PostgreSQL 10.6, compiled by Visual C++ build 1800, 64-bit
※SELECT version();で取得
※Oracle/SQLServerでも有効な記事です。

概要/事例/目的

食べログデータを使用して検証します。

場所ごとに下記①②を調査するイメージです。
(例：銀座とかで１番点数の高いお店を、集計結果と併せて取得する。)

①集計結果：店舗数(COUNT)/最大の点数(MAX)など
②場所ごとの、点数が最も高いお店の名前

集合論的なイメージは下記の通り。
各グループに集約操作を行いつつ、各グループで点数が最大(or最小)のデータを取得します。

コード/実行結果

テーブル定義

食べログのお店データを登録するテーブルです。

アトリビュート名	カラム名	桁	型	PK(主キー)	NOT NULL制約
お店ID	SHOP_ID	integer	-	●	●
店名	SHOP_NAME	varchar	100	-	●
場所	PLACE	varchar	50	-	●
種類	KIND	varchar	100	-	-
点数	REVIEW_SCORE	numeric	-	-	-
レビュー数	REVIEW_CNT	integer	-	-	-

CREATE TABLE t_tabelog_shop_list( 
  shop_id INTEGER NOT NULL
  , shop_name VARCHAR (100) NOT NULL
  , place VARCHAR (50) NOT NULL
  , kind VARCHAR (100)
  , review_score NUMERIC
  , review_cnt INTEGER
  , PRIMARY KEY (shop_id)
) ;

データサンプル

件数が多いので、本ページの一番下に全量を記載します。こちら(サイト内遷移)。

INSERT INTO t_tabelog_shop_list VALUES ( 1, '支那麺 はしご 本店', '銀座駅', '担々麺、ラーメン', 3.59, 857 );

(省略)

INSERT INTO t_tabelog_shop_list VALUES ( 80, 'シンガポール・シーフード・リパブリック 銀座', '銀座一丁目駅', 'シンガポール料理、バイキング、アジア・エスニック料理（その他）', 3.57, 239 );

コード

SELECT
  place 場所
  , COUNT(*) 店舗数
  , MAX(review_score) 最大点数
  , MAX(review_cnt) 最大口コミ数

/*****★POINT★******/
  , MAX( 
    CASE WHEN
    NOT EXISTS (
      SELECT 1 FROM t_tabelog_shop_list B
      WHERE A.place = B.place AND A.review_score < B.review_score) 
    THEN A.shop_name ||'/'|| A.kind ||'/' || A.review_score
  ELSE NULL END
  ) 対象地域で一番点数が高い店
/*****★POINT★******/

FROM
  t_tabelog_shop_list A
GROUP BY place ORDER BY place;

※一見すると分かりづらいですが、単純化すると下記の通り。 SELECT句のMAX関数の引数にCASE式が入っているだけです。
※CASE式の条件●●●にはNOT EXISTS(相関サブクエリ)が記載されています。

SELECT
,(省略)

/*****★POINT★******/

,MAX(CASE WHEN ●●● ELSE NULL END)
--※●●●には【NOT EXISTS(相関サブクエリ)】

/*****★POINT★******/
FROM
  t_tabelog_shop_list A
GROUP BY place ORDER BY place;

実行結果

解説

＜操作＞

基礎知識

・(説明不要だと思いますが)場所ごとに店舗数などを集計しています。

・CASE式の返り値の「||」演算子：
文字列の結合を行います。店名に併せてジャンルと点数も結合して表示しています。

・NOT EXISTS演算子：
副問い合せの結果が存在しないとき真になります。

【今回のPOINT概要】

①MAX/MN関数は文字列を返すことができる！
②MAX/MN関数の中でCASE式を使用する！
③CASE式の条件にNOT EXISTS(相関サブクエリ)を利用する！

コードの概要を再掲します。

SELECT
,(省略)

/*****★POINT★******/

,MAX(CASE WHEN ●●● ELSE NULL END)
--※●●●には【NOT EXISTS(相関サブクエリ)】

/*****★POINT★******/
FROM
  t_tabelog_shop_list A
GROUP BY place ORDER BY place;

★POINT①MAX/MN関数は文字列を返すことができる！

・集計関数の返り値には2種類あります。

①SUM/AVG/COUNTなど：数値を返すのが原則
②MAX/MIN：数値・文字列を返す

⇒今回、特定の店名(文字列)を知りたいのでMAX/MINを使用します。

★POINT②MAX/MN関数の中でCASE式を使用する！

・対象の場所/placeの集合に対し、レコードを1件ずつCASE文で走査してます。具体的には

①NOT EXISTS⇒条件を満たすコードが存在しない場合、店名などを返す
②①以外の場合、NULLを返す(Dead Logic/到達不能コードですが)

--(★コード再掲★)
SELECT
  place 場所
  , COUNT(*) 店舗数
  , MAX(review_score) 最大点数
  , MAX(review_cnt) 最大口コミ数

/*****★POINT★******/
  , MAX( 
    CASE WHEN
    NOT EXISTS (
      SELECT 1 FROM t_tabelog_shop_list B
      WHERE A.place = B.place AND A.review_score < B.review_score) 
    THEN A.shop_name ||'/'|| A.kind ||'/' || A.review_score
  ELSE NULL END
  ) 対象地域で一番点数が高い店
/*****★POINT★******/

FROM
  t_tabelog_shop_list A
GROUP BY place ORDER BY place;

★POINT③CASE式の条件にNOT EXISTS(相関サブクエリ)を利用する！

今回実現したいことは①「場所毎に」②「点数が最大のお店」を求めることです。NOT EXISTSの中身で【相関サブクエリ】を利用し、上記条件を実装します。

①「場所毎に」：
【WHERE A.place = B.place】という条件がこれに相当します。
②「点数が最大のお店」：
「点数が最大」=「該当レコード以外に、より大きい点数の店がない」と考えます。
⇒【NOT EXISTS(SELECT ・・・ WHERE ・・・ A.review_score < B.review_score)】という条件がこれに相当します。

※【相関サブクエリ】のイメージ
・外側のテーブル(別名：A)のレコードが1行ずつ、内側のテーブル(別名：B)と比較されるようなイメージです。
・(正確ではないかもしれませんが)個人的なイメージとして、外側のテーブル(別名：A)のレコードが1行ずつ、内側のテーブル(別名：B)に代入されるようなイメージを持っています。

補足

・点数が最大のお店が2店舗以上あった場合、MAX関数を利用しているため「文字列として最大値」のレコードが選択されます。

例：有楽町駅は最大点数が3.58の店が2店舗ある
・MAX関数を利用した場合(文字列として最大値)：「小洞天有楽町店」　が返る
・MIN関数を利用した場合(文字列として最小値)：「シェイクシャック東京国際フォーラム」　が返る

・通常、最大点数の一覧をサブクエリに書いて、 INNER JOINで店名を抽出するといった操作をすると思いますが、当BlogのようにCASE式1つで記載することが可能です。

参考サイト

【同一グループの中で最大のレコードを取得する SQL を書く】
http://labs.timedia.co.jp/2014/10/selecting-max-record-in-group-by.html
【同一グループの中で最大のレコードを取得する SQL を書く】
https://note.miyabis.jp/2009/02/26327410.html
【第9回　SQLでループ！相関サブクエリの使い方～切れ過ぎるナイフにご用心～　（2）相関サブクエリ】
http://gihyo.jp/dev/serial/01/sql_academy2/000902

万一、誤った理解をしている場合はTwitterまたは本Blogにてご指摘いただけると幸いです。以上。

Sample Data

INSERT INTO t_tabelog_shop_list VALUES ( 1, '支那麺はしご本店', '銀座駅', '担々麺、ラーメン', 3.59, 857 );
INSERT INTO t_tabelog_shop_list VALUES ( 2, 'グルガオン', '銀座一丁目駅', 'インド料理、インドカレー', 3.64, 760 );
INSERT INTO t_tabelog_shop_list VALUES ( 3, '銀座久兵衛銀座本店', '新橋駅', '寿司', 3.79, 718 );
INSERT INTO t_tabelog_shop_list VALUES ( 4, 'ラデュレサロン・ド・テ銀座三越店', '銀座駅', 'マカロン、カフェ、フレンチ', 3.60, 605 );
INSERT INTO t_tabelog_shop_list VALUES ( 5, '煉瓦亭', '銀座駅', '洋食、オムライス、その他肉料理', 3.15, 576 );
INSERT INTO t_tabelog_shop_list VALUES ( 6, 'アーンドラ・ダイニング銀座', '銀座一丁目駅', 'インド料理、インドカレー、ダイニングバー', 3.75, 517 );
INSERT INTO t_tabelog_shop_list VALUES ( 7, 'ロオジエ', '銀座駅', 'フレンチ', 4.41, 512 );
INSERT INTO t_tabelog_shop_list VALUES ( 8, '大衆割烹三州屋銀座店 ', '銀座一丁目駅', '定食・食堂、居酒屋', 3.59, 489 );
INSERT INTO t_tabelog_shop_list VALUES ( 9, 'カーン・ケバブ・ビリヤニ', '新橋駅', 'インド料理、インドカレー、パキスタン料理', 3.61, 457 );
INSERT INTO t_tabelog_shop_list VALUES ( 10, 'びすとろ UOKIN', '新橋駅', 'ビストロ、居酒屋、バル・バール', 3.54, 444 );
INSERT INTO t_tabelog_shop_list VALUES ( 11, 'デリー銀座店', '銀座駅', 'インド料理、インドカレー、居酒屋', 3.60, 441 );
INSERT INTO t_tabelog_shop_list VALUES ( 12, 'RIGOLETTO KITCHEN', '内幸町駅', 'イタリアン、スペイン料理、パスタ', 3.53, 440 );
INSERT INTO t_tabelog_shop_list VALUES ( 13, 'シクスバイオリエンタルホテル', '有楽町駅', 'イタリアン、カフェ、バー', 3.58, 435 );
INSERT INTO t_tabelog_shop_list VALUES ( 14, 'アピシウス', '日比谷駅', 'フレンチ', 4.34, 431 );
INSERT INTO t_tabelog_shop_list VALUES ( 15, '銀座うかい亭', '東銀座駅', '鉄板焼き、ステーキ', 4.23, 430 );
INSERT INTO t_tabelog_shop_list VALUES ( 16, 'ラ・メール・プラール', '有楽町駅', 'ビストロ、西洋各国料理（その他）、カフェ', 3.53, 420 );
INSERT INTO t_tabelog_shop_list VALUES ( 17, 'てんぷら近藤', '銀座駅', '天ぷら', 4.02, 417 );
INSERT INTO t_tabelog_shop_list VALUES ( 18, '銀座千疋屋銀座本店　フルーツパーラー', '銀座駅', 'フルーツパーラー、パフェ、ケーキ', 3.59, 392 );
INSERT INTO t_tabelog_shop_list VALUES ( 19, '銀座しまだ', '新橋駅', '割烹・小料理、魚介料理・海鮮料理、居酒屋', 3.92, 371 );
INSERT INTO t_tabelog_shop_list VALUES ( 20, 'インペリアルバイキングサール', '日比谷駅', 'バイキング、洋食・欧風料理（その他）、西洋各国料理（その他）', 3.63, 367 );
INSERT INTO t_tabelog_shop_list VALUES ( 21, 'ダルマサーガラ', '東銀座駅', 'インドカレー、アジア・エスニック料理（その他）、野菜料理', 3.65, 361 );
INSERT INTO t_tabelog_shop_list VALUES ( 22, 'Peter', '日比谷駅', 'ステーキ、洋食、バー', 3.59, 359 );
INSERT INTO t_tabelog_shop_list VALUES ( 23, 'ぴょんぴょん舎 GINZA UNA', '銀座駅', '焼肉、冷麺、韓国料理', 3.58, 354 );
INSERT INTO t_tabelog_shop_list VALUES ( 24, '魚金本店', '新橋駅', '魚介料理・海鮮料理、居酒屋、日本酒バー', 3.55, 351 );
INSERT INTO t_tabelog_shop_list VALUES ( 25, '俺の割烹銀座本店', '新橋駅', '割烹・小料理、日本酒バー、居酒屋', 3.60, 350 );
INSERT INTO t_tabelog_shop_list VALUES ( 26, 'エスキス', '銀座駅', 'フレンチ、モダンフレンチ', 4.57, 348 );
INSERT INTO t_tabelog_shop_list VALUES ( 27, '焼肉の名門天壇銀座店', '東銀座駅', '焼肉', 3.59, 345 );
INSERT INTO t_tabelog_shop_list VALUES ( 28, 'サーラアマービレ', '銀座一丁目駅', 'イタリアン', 3.94, 343 );
INSERT INTO t_tabelog_shop_list VALUES ( 29, 'パラダイスダイナシティ', '銀座駅', '中華料理、飲茶・点心、シンガポール料理', 3.58, 342 );
INSERT INTO t_tabelog_shop_list VALUES ( 30, 'パークサイドダイナー', '日比谷駅', '洋食、パンケーキ、ハンバーガー', 3.60, 338 );
INSERT INTO t_tabelog_shop_list VALUES ( 31, 'うち山', '銀座一丁目駅', '懐石・会席料理', 3.94, 337 );
INSERT INTO t_tabelog_shop_list VALUES ( 32, 'ベージュアラン・デュカス東京', '銀座一丁目駅', 'フレンチ', 3.73, 332 );
INSERT INTO t_tabelog_shop_list VALUES ( 33, '銀座シェ・トモ', '銀座一丁目駅', 'フレンチ', 3.63, 330 );
INSERT INTO t_tabelog_shop_list VALUES ( 34, '資生堂パーラー銀座本店', '新橋駅', '洋食、ハヤシライス、オムライス', 3.67, 328 );
INSERT INTO t_tabelog_shop_list VALUES ( 35, '筑紫樓銀座店', '銀座駅', '中華料理', 3.78, 327 );
INSERT INTO t_tabelog_shop_list VALUES ( 36, 'ビヤホールライオン銀座七丁目店', '銀座駅', 'ビアホール・ビアレストラン、ビアバー、ステーキ', 3.58, 327 );
INSERT INTO t_tabelog_shop_list VALUES ( 37, 'RESTAURANT DAZZLE', '銀座一丁目駅', 'イタリアン、ワインバー、バー', 3.59, 326 );
INSERT INTO t_tabelog_shop_list VALUES ( 38, 'ビーフン東', '新橋駅', '台湾料理', 3.58, 317 );
INSERT INTO t_tabelog_shop_list VALUES ( 39, 'ブラッスリーオザミ丸の内', '有楽町駅', 'フレンチ、ビストロ、ワインバー', 3.58, 311 );
INSERT INTO t_tabelog_shop_list VALUES ( 40, 'ブラッスリーポール・ボキューズ銀座', '銀座一丁目駅', 'ビストロ、フレンチ', 3.58, 310 );
INSERT INTO t_tabelog_shop_list VALUES ( 41, '梅丘寿司の美登利総本店銀座店', '内幸町駅', '寿司', 3.57, 309 );
INSERT INTO t_tabelog_shop_list VALUES ( 42, '竹葉亭銀座店', '銀座駅', 'うなぎ、割烹・小料理、懐石・会席料理', 3.60, 307 );
INSERT INTO t_tabelog_shop_list VALUES ( 43, 'ジョーズシャンハイニューヨーク銀座店', '銀座一丁目駅', '中華料理、飲茶・点心、広東料理', 3.58, 293 );
INSERT INTO t_tabelog_shop_list VALUES ( 44, '銀座鮨かねさか本店', '新橋駅', '寿司', 4.08, 292 );
INSERT INTO t_tabelog_shop_list VALUES ( 45, '俺のフレンチ TOKYO', '銀座一丁目駅', 'ビストロ、フレンチ、バル・バール', 3.58, 289 );
INSERT INTO t_tabelog_shop_list VALUES ( 46, 'シェイクシャック東京国際フォーラム', '有楽町駅', 'ハンバーガー、カフェ、ダイニングバー', 3.58, 288 );
INSERT INTO t_tabelog_shop_list VALUES ( 47, '新ばししみづ', '新橋駅', '寿司', 4.13, 285 );
INSERT INTO t_tabelog_shop_list VALUES ( 48, 'エール', '銀座駅', 'イノベーティブ・フュージョン、フレンチ、モダンフレンチ', 4.17, 282 );
INSERT INTO t_tabelog_shop_list VALUES ( 49, '銀之塔', '東銀座駅', 'シチュー', 3.59, 282 );
INSERT INTO t_tabelog_shop_list VALUES ( 50, 'フィッシュバンクトーキョー', '新橋駅', 'フレンチ、イタリアン、ステーキ', 3.59, 282 );
INSERT INTO t_tabelog_shop_list VALUES ( 51, 'バー＆ラウンジトゥエンティエイトコンラッド東京', '汐留駅', 'バー、ラウンジ、カフェ', 3.30, 276 );
INSERT INTO t_tabelog_shop_list VALUES ( 52, '焼肉うしごろ銀座店', '銀座一丁目駅', '焼肉、ホルモン', 4.15, 273 );
INSERT INTO t_tabelog_shop_list VALUES ( 53, '一味玲玲新橋本店', '新橋駅', '中華料理、餃子', 3.59, 274 );
INSERT INTO t_tabelog_shop_list VALUES ( 54, 'ザ・ロビー', '日比谷駅', '洋食・欧風料理（その他）、カフェ、ラウンジ', 3.59, 272 );
INSERT INTO t_tabelog_shop_list VALUES ( 55, 'イタリア料理フィオレンツァ', '銀座一丁目駅', 'イタリアン、ステーキ、パスタ', 3.59, 272 );
INSERT INTO t_tabelog_shop_list VALUES ( 56, '俺のやきとり銀座9丁目', '新橋駅', '焼鳥', 3.58, 268 );
INSERT INTO t_tabelog_shop_list VALUES ( 57, 'イタリアンバル UOKIN 新橋店', '新橋駅', 'イタリアン、バル・バール、ワインバー', 3.53, 268 );
INSERT INTO t_tabelog_shop_list VALUES ( 58, '舞浜', '新橋駅', '居酒屋、割烹・小料理、魚介料理・海鮮料理', 3.60, 265 );
INSERT INTO t_tabelog_shop_list VALUES ( 59, '佐賀牛季楽銀座 ', '銀座駅', '鉄板焼き、ステーキ', 4.06, 263 );
INSERT INTO t_tabelog_shop_list VALUES ( 60, '遊食豚彩いちにいさん日比谷店', '日比谷駅', '豚しゃぶ、とんかつ、かき氷', 3.58, 261 );
INSERT INTO t_tabelog_shop_list VALUES ( 61, 'コカレストラン＆マンゴツリーカフェ有楽町', '有楽町駅', 'タイ料理、タイスキ、カフェ', 3.51, 259 );
INSERT INTO t_tabelog_shop_list VALUES ( 62, 'パリのワイン食堂', '東銀座駅', 'バル・バール、ステーキ、フレンチ', 3.58, 258 );
INSERT INTO t_tabelog_shop_list VALUES ( 63, '岡半本店', '銀座駅', '鉄板焼き、すき焼き、ステーキ', 3.58, 257 );
INSERT INTO t_tabelog_shop_list VALUES ( 64, 'ヘイフンテラス', '日比谷駅', '広東料理、中華料理、飲茶・点心', 3.61, 255 );
INSERT INTO t_tabelog_shop_list VALUES ( 65, '小洞天有楽町店', '有楽町駅', '中華料理、担々麺、飲茶・点心', 3.58, 254 );
INSERT INTO t_tabelog_shop_list VALUES ( 66, '銀座吉宗', '新橋駅', '和食（その他）、ちゃんぽん、郷土料理（その他）', 3.58, 254 );
INSERT INTO t_tabelog_shop_list VALUES ( 67, 'ジリオ', '汐留駅', 'イタリアン', 3.69, 251 );
INSERT INTO t_tabelog_shop_list VALUES ( 68, '蕎麦流石', '東銀座駅', 'そば、懐石・会席料理', 3.63, 250 );
INSERT INTO t_tabelog_shop_list VALUES ( 69, '末げん', '新橋駅', '鳥料理、懐石・会席料理、親子丼', 3.58, 249 );
INSERT INTO t_tabelog_shop_list VALUES ( 70, 'インペリアルラウンジアクア', '日比谷駅', 'ラウンジ、バー、カフェ・喫茶（その他）', 3.54, 249 );
INSERT INTO t_tabelog_shop_list VALUES ( 71, '桃花源', '新橋駅', '四川料理、中華料理、スイーツ（その他）', 3.62, 247 );
INSERT INTO t_tabelog_shop_list VALUES ( 72, '俺のイタリアンJAZZ', '新橋駅', 'イタリアン、バル・バール、ピザ', 3.58, 247 );
INSERT INTO t_tabelog_shop_list VALUES ( 73, '銀座天ぷら阿部', '銀座駅', '天ぷら', 3.58, 247 );
INSERT INTO t_tabelog_shop_list VALUES ( 74, 'ジムトンプソンズテーブルタイランド銀座', '銀座一丁目駅', 'タイ料理、ダイニングバー、バイキング', 3.58, 246 );
INSERT INTO t_tabelog_shop_list VALUES ( 75, '銀座天國本店', '新橋駅', '天ぷら、天丼・天重、懐石・会席料理', 3.57, 246 );
INSERT INTO t_tabelog_shop_list VALUES ( 76, 'アロッサ銀座店', '銀座一丁目駅', 'ビストロ、バル・バール、オセアニア料理', 3.58, 242 );
INSERT INTO t_tabelog_shop_list VALUES ( 77, '焼肉銀座コバウ', '銀座駅', '焼肉、韓国料理、牛料理', 4.12, 241 );
INSERT INTO t_tabelog_shop_list VALUES ( 78, 'レストランタテルヨシノ銀座', '東銀座駅', 'フレンチ、モダンフレンチ', 4.04, 241 );
INSERT INTO t_tabelog_shop_list VALUES ( 79, 'ヌガ', '東銀座駅', 'ビストロ、ワインバー、フレンチ', 3.60, 241 );
INSERT INTO t_tabelog_shop_list VALUES ( 80, 'シンガポール・シーフード・リパブリック銀座', '銀座一丁目駅', 'シンガポール料理、バイキング、アジア・エスニック料理（その他）', 3.57, 239 );

仕事と婚活と私～ITエンジニアの末路～

仕事(IT/SE)・婚活・ツイッターの話とか、いろいろ　TW:@mara_ashida_

【IT】SQL/同一グループの中で最大/最小のレコードと集計結果を同時に取得する #SQL/GROUP BY/CASE式

導入