SELECT 後の抽出

SQL Serverでクエリ性能を改善する

Dean Smith

Founder, Atamai Analytics

SELECT 後の処理順序

1.  FROM

2.  ON

3.  JOIN

4.  WHERE

5.  GROUP BY

6.  HAVING


7.  SELECT


8.  DISTINCT

9.  ORDER BY

10. TOP

ALL は常に良いわけではない

SELECT *
FROM PlayerStats;

SELECT * は探索には便利だが、性能面では悪影響の可能性

PlayerName	Team	Position	...	...
Alex Abrines	OKC	SG	...	...
Quincy Acy	BRK	PF	...	...
Steven Adams	OKC	C	...	...
Bam Adebayo	MIA	C	...	...
...	...	...	...	...

必要な分だけが最善

SELECT PlayerName, 
        Team, 
        TotalPoints
FROM PlayerStats;

必要な列だけを選択する

PlayerName	Team	TotalPoints
Alex Abrines	OKC	353
Quincy Acy	BRK	411
Steven Adams	OKC	1056
Bam Adebayo	MIA	477
...	...	...

JOIN での ALL

SELECT * 
FROM Players p
  INNER JOIN PlayerStats ps
  ON ps.PlayerName = p.PlayerName;

結合での SELECT * は結合列を重複して返す

PlayerName	Age	PlayerName	Team	...
Alex Abrines	24	Alex Abrines	OKC	...
Quincy Acy	27	Quincy Acy	BRK	...
Steven Adams	24	Steven Adams	OKC	...
Bam Adebayo	20	Bam Adebayo	MIA	...
...	...	...	...	...

JOIN で必要なこと

SELECT p.PlayerName, 
       ps.Team, 
       p.Country, 
       ps.Position
FROM Players p
  INNER JOIN PlayerStats ps
  ON ps.PlayerName = p.PlayerName;

返す列と出所のテーブルを明示する

PlayerName	Country	Team	Position
Alex Abrines	Spain	OKC	SG
Quincy Acy	USA	BRK	PF
Steven Adams	New Zealand	OKC	C
Bam Adebayo	USA	MIA	C
...	...	...	...

先頭の行を取得（TOP）

SELECT TOP 5 PlayerName, 
        Team, 
        TotalPoints
FROM PlayerStats;

PlayerName	Team	TotalPoints
Alex Abrines	OKC	353
Quincy Acy	BRK	411
Steven Adams	OKC	1056
Bam Adebayo	MIA	477
Arron Afflalo	ORL	179

先頭の割合（TOP）

SELECT TOP 1 PERCENT PlayerName, 
        Team, 
        TotalPoints
FROM PlayerStats;

PlayerName	Team	TotalPoints
Alex Abrines	OKC	353
Quincy Acy	BRK	411
Steven Adams	OKC	1056
Bam Adebayo	MIA	477
Arron Afflalo	ORL	179
Cole Aldrich	MIN	12
LaMarcus Aldridge	SAS	1735

上も下もない

SELECT TOP 5 PlayerName, 
        Team, 
        TotalPoints
FROM PlayerStats
ORDER BY TotalPoints DESC

PlayerName	Team	TotalPoints
LeBron James	CLE	2251
James Harden	HOU	2191
Anthony Davis	NOP	2110
Russell Westbrook	OKC	2028
Giannis Antetokounmpo	MIL	2014

他の行制限子

行制限子	並び替え句	データベース
TOP	ORDER BY	Microsoft SQL Server
ROWNUM	ORDER BY	Oracle
LIMIT	ORDER BY	PostgreSQL

ORDER BY はどこで使うか

クエリで ORDER BY を使う目的:

データを検証するため
最終結果を並べる妥当な理由があるとき

Ayo berlatih!

SQL Serverでクエリ性能を改善する