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

この記事は公開されてから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