SQL:2003で追加されたfilter句をPostgreSQLとPrestoから使ってみた

quiver

2018.01.23

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

SQL:2003 には FILTER 句が追加され、集約関数の対象を選択することが出来ます。

FILTER 句を使うと、SQL-92 準拠で

select sum(case when condition then clm end)

のように CASE 文で頑張っていた SQL を

select sum(clm) filter (where condition)

とシンプル・直感的に記述出来るようになります。

テーブルの縦→横ヘンカニスト待望の SQL 表現です。

この FILTER 句は SQL:2003 に T612: Advanced OLAP operations として追加され、主要RDBとしては PostgreSQL が 9.4 から対応しており、分散クエリーエンジンの Presto も 0.156 から対応しています。

今回はこの機能を以下の環境で試してみます。

Presto 0.172(Amazon Athena から利用)
PostgreSQL 9.6.5

Presto で試す

サクッと Presto を動作させるために、AWS が提供するサーバーレスのインタラクティブなクエリーサービス Amazon Athena を利用します。Athena はクエリーエンジンに分散クエリーエンジンのPrestoを採用しています。

2018/01/19 に Athena の利用する Presto が 0.172 にアップデートされたため、利用可能になりました。

リリースノートの次の部分が該当します。

Support for filtered aggregations, such as SELECT sum(col_name) FILTER, where id > 0.

https://docs.aws.amazon.com/athena/latest/ug/release-note-2018-01-19.html

テストテーブルの用意

SQL の検証には、Athena が提供するテストデータ、 CDN(CloudFront)のログを利用します。

CREATE EXTERNAL TABLE IF NOT EXISTS cloudfront_logs (
  `Date` DATE,
  Time STRING,
  Location STRING,
  Bytes INT,
  RequestIP STRING,
  Method STRING,
  Host STRING,
  Uri STRING,
  Status INT,
  Referrer STRING,
  os STRING,
  Browser STRING,
  BrowserVersion STRING
  ) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
  WITH SERDEPROPERTIES (
  "input.regex" = "^(?!#)([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+[^\(]+[\(]([^\;]+).*\%20([^\/]+)[\/](.*)$"
  ) LOCATION 's3://athena-examples-ap-northeast-1/cloudfront/plaintext/';

19行目にある S3 のバケット名の「ap-northeast-1」の箇所は Athena を利用するリージョンに合わせて読み替えて下さい。例えば、ドイツのフランクフルトリージョンを利用する場合は、以下の様になります。

LOCATION
  's3://athena-examples-eu-central-1/cloudfront/plaintext

テストデータを少し眺めてみます

 	date	time	location	bytes	requestip	method	host	uri	status	referrer	os	browser	browserversion
1	2014-07-05	15:00:00	LHR3	4260	10.0.0.15	GET	eabcd12345678.cloudfront.net	/test-image-1.jpeg	200	-	Linux	Opera	3.0.9
2	2014-07-05	15:00:00	MIA3	10	10.0.0.15	GET	eabcd12345678.cloudfront.net	/test-image-1.jpeg	304	-	OSX	Firefox	3.0.9
3	2014-07-05	15:00:00	MIA3	4252	10.0.0.15	GET	eabcd12345678.cloudfront.net	/test-image-3.jpeg	200	-	Linux	Lynx	3.0.9
4	2014-07-05	15:00:00	FRA2	4257	10.0.0.8	GET	eabcd12345678.cloudfront.net	/test-image-2.jpeg	200	-	iOS	Firefox	3.0.9
5	2014-07-05	15:00:03	HKG1	4261	10.0.0.15	GET	eabcd12345678.cloudfront.net	/test-image-2.jpeg	200	-	Linux	Opera	3.0.9

CDN エッジロケーションに対して、OS毎のアクセス数を求める

SQL-92

CASE WHEN condition で選択します。

SELECT
  location
 ,sum (CASE WHEN os = 'iOS' THEN 1 END) iOS
 ,sum (CASE WHEN os = 'Android' THEN 1 END) Android
 ,...
FROM cloudfront_logs
GROUP BY location
ORDER BY location

FILTER 句

FILTER(WHERE condition) で選択します。

SELECT
  location
 ,count(*) FILTER(WHERE os = 'iOS') iOS
 ,count(*) FILTER(WHERE os = 'Android') Android
 ,count(*) FILTER(WHERE os = 'MacOS') MacOS
 ,count(*) FILTER(WHERE os = 'OSX') OSX
 ,count(*) FILTER(WHERE os = 'Windows') Windows
 ,count(*) FILTER(WHERE os = 'Linux') Linux
 ,count(*) FILTER(WHERE os = 'Windows') Windows
FROM cloudfront_logs
GROUP BY location
ORDER BY location

結果

location	iOS	Android	MacOS	OSX	Windows	Linux	Windows
AMS1	54	56	74	66	78	52	78
DFW3	57	70	68	67	48	61	48
DUB2	61	67	64	57	67	62	67
EWR2	55	67	60	60	62	70	62
FRA2	66	72	60	57	57	69	57
HKG1	58	77	63	56	64	57	64
IAD2	68	58	67	60	62	65	62
LAX1	22	24	30	22	18	21	18
LHR3	71	64	55	55	61	70	61

...

PostgreSQL で試す

PostgreSQL は 9.4 から FILTER 句が利用可能になりました。

リリースノートの次の部分が該当します。

Add control over which rows are passed into aggregate functions via the FILTER clause (David Fetter)

https://www.postgresql.org/docs/9.4/static/release-9-4.html

テストテーブルの用意

Leo S. Hsu, Regina O. Obe 著の "PostgreSQL: Up and Running, 3rd Edition", Chapter 7 からデータをお借りします。

生徒の過去の試験結果を保存するテーブルを用意します。生徒は試験を過去に複数回受けており、物理(physics)、化学(chemistry)など複数の科目が存在します。

CREATE TABLE test_scores (
    student character varying(100) NOT NULL,
    subject character varying(100) NOT NULL,
    score numeric(5),
    test_date date NOT NULL,
	CONSTRAINT pk_test_scores PRIMARY KEY (student, subject, test_date)
);

テストデータを少し眺めてみます

dbname=> select * from test_scores limit 5;
 student |  subject  | score | test_date
---------+-----------+-------+------------
 regina  | algebra   |    68 | 2014-01-15
 regina  | physics   |    83 | 2014-01-15
 regina  | chemistry |    71 | 2014-01-15
 regina  | calculus  |    68 | 2014-01-15
 regina  | scheme    |    90 | 2015-01-15
(5 rows)

集約関数をより複雑に適用していく

PostgreSQL は Presto に比べてより複雑な SQL を記述できるため、シンプルなものから FILTER 句を活用したより複雑な SQL に拡張していきます。

まずは、生徒の全試験を対象にした平均点を求めます

試験を受けた時期や科目を区別しません。

SELECT
  student,
  AVG(score)
FROM test_scores
GROUP BY student;

student |         avg
---------+---------------------
sonia   | 75.0000000000000000
regina  | 75.4444444444444444
leo     | 73.7500000000000000
alex    | 77.6250000000000000
(4 rows)

生徒はどの科目が得意なのか知りたいことがあるかもしれません。その時は、 FILTER 句の出番です。

SELECT student,
  AVG(score) FILTER (WHERE subject ='algebra') As algebra,
  AVG(score) FILTER (WHERE subject ='physics') As physics
FROM test_scores
GROUP BY student;

 student |       algebra       |       physics
---------+---------------------+---------------------
 sonia   | 76.5000000000000000 | 72.0000000000000000
 regina  | 72.5000000000000000 | 84.0000000000000000
 leo     | 82.0000000000000000 | 72.0000000000000000
 alex    | 74.0000000000000000 | 81.0000000000000000
(4 rows)

次に中央値を求めてみます。

PostgreSQL には median(中央値) に特化した関数は存在しませんが、9.4 からパーセンタイル用の関数(percentile_disc)が追加されたため、50% の位置を指定することで median として利用出来ます。

percentile_disc(fraction) WITHIN GROUP (ORDER BY sort_expression)

ORDER BY があるように、パーセンタイルを求めたい順序集合を WITHIN GROUP (ORDER BY sort_expression) の構文で渡します。

percentile_disc を使って中央値を求めたのが次の SQL です。デバッグ目的で、テストの点数を array_agg でダンプしています。

SELECT
    student,
    array_agg(score order by score) AS scores,
    percentile_disc(0.5) WITHIN GROUP (ORDER BY score) AS disc_median
FROM test_scores
GROUP BY student
ORDER BY student;

student |            scores            | disc_median
---------+------------------------------+-------------
alex    | {70,74,74,77,79,80,83,84}    |          77
leo     | {62,69,71,72,72,80,80,84}    |          72
regina  | {61,68,68,71,76,77,83,85,90} |          76
sonia   | {65,70,72,72,75,78,82,86}    |          72
(4 rows)

なお順序集合を集約する Ordered-Set Aggregate Functions は 9.4 から追加され、以下のものがあります。

mode() WITHIN GROUP (ORDER BY sort_expression)
percentile_cont(fraction) WITHIN GROUP (ORDER BY sort_expression)
percentile_cont(fractions) WITHIN GROUP (ORDER BY sort_expression)
percentile_disc(fraction) WITHIN GROUP (ORDER BY sort_expression)
percentile_disc(fractions) WITHIN GROUP (ORDER BY sort_expression)

この WITHIN GROUP 型の集約関数は FILTER 句と組み合わせることも出来ます。

aggregate_name ( [ expression [ , ... ] ] ) WITHIN GROUP ( order_by_clause ) [ FILTER ( WHERE filter_clause ) ]

WITHIN GROUP と FILTER 句を利用して、生徒の科目ごとの中央値を求めたのが次の SQL です。

SELECT
  student,
  percentile_disc(0.5) WITHIN GROUP (ORDER BY score)
    FILTER (WHERE subject = 'algebra') algebra,
  percentile_disc(0.5) WITHIN GROUP (ORDER BY score)
    FILTER (WHERE subject = 'physics') physics
FROM test_scores
GROUP BY student
ORDER BY student;

student | algebra | physics
---------+---------+---------
alex    |      74 |      79
leo     |      80 |      72
regina  |      68 |      83
sonia   |      75 |      72
(4 rows)

まとめ

SQL:2003 で追加され、まだそれほど浸透していない FILTER 句の使い方を紹介しました。

集約関数を利用することが多いエンジニアが恩恵を受けやすく、とりわけ、縦持ちテーブルを横持ちテーブルに変換するケースで、このFILTER 句を適用しやすいと感じました。

FILTER 句に対応した SQL エンジンは

PostgreSQL 9.4〜
Presto 0.156

などまだ限定的な点にはご注意下さい。

PostgreSQL 8系がベースの Amazon Redshift でも現時点では利用できません。

なお、このブログを執筆するにあたって大いに参考にしたサイト「modern SQL : a lot has changed since SQL-92」(まさにその通り!)のオーナーは、「Use the Index, Luke」と同一人物です。

ではでは。

参照

https://docs.aws.amazon.com/athena/latest/ug/release-note-2018-01-19.html
http://modern-sql.com/feature/filter
https://www.postgresql.org/docs/9.4/static/release-9-4.html
https://www.postgresql.org/docs/current/static/sql-expressions.html
https://github.com/prestodb/presto/issues/5085
https://github.com/prestodb/presto/pull/6404/files
Leo S. Hsu, Regina O. Obe : "PostgreSQL: Up and Running, 3rd Edition", Chapter 7. SQL: The PostgreSQL Way