【SQL】「GROUP BY」句&集計関数による集計とHAVING・WHEREでの条件付き集計

Contents

【テーブル説明】

【中身】

・login

user_id|date
3|2020-12-09
4|2020-12-10
~~~~~~~~
5|2020-03-19
6|2020-03-19
7|2020-03-19

・install

user_id|date
1|2020-01-01
2|2020-01-01
3|2020-01-01
~~~~~~~~
36|2020-11-18
37|2020-12-08

基本的にSQLの集計では「GROUP BY」句を用います。そしてこのGROUP BYと「集計関数」を組み合わせることで任意のデータを取得します。

一番馴染み深いのは COUNT() ですね。他にも、最大値と最小値を返す MAX(), MIN()、合計を返す SUM() や平均を返す AVG() はよく使う集計関数で、これらは複数のデータをまとめて一つの値として返してくれます。

今回はこのデータから「各日付ごとの新規登録者の人数」を集計したいと思います。

こういう場合はまずテーブルはインストールテーブルを見ます。そして、集計なので、「GROUP BY」と「COUNT()」を使用します。そして、各日付ごとでデータを集計したいのでGROUP BYのキーは日付(date列)とします。

select date, count(*) from (select * from install) as install group by date;

今回はユーザー数を集計したいので、ユーザーIDをCOUNT()で集計するということで厳密には count(user_id)とするべきですが、基本*でOKです。

<実行結果>

2020-01-01|7
2020-02-04|2
2020-03-04|1
2020-03-05|2
2020-03-07|1
～～～～～～～
2020-11-18|1
2020-12-08|1

ちなみに、日単位のデータを月ごとに集計する場合はdate列の日付データをstrftime()関数で月データとして変換してから集計することで実現できます。日付型の変換は集計では避けては通れない部分ですね。

select strftime('%Y-%m',date) as month ,count(user_id) from install group by month;

<実行結果>

これが集計の基本中の基本です。

「WHERE」と「HAVING」条件を絞って集計する

次は条件を絞った集計を行っていきます。条件付集計の場合は「WHERE」または「HAVING」でデータを絞って集計します。WHEREとHAVINGの違いは以下の通りです

・「WHERE」・・・「GROUP BY」の影響の前に条件が適応される
・「HAVING」・・・「GORUP BY」の影響のあとに条件が適応される

「WHERE」は「グループ化される前に、条件に合うデータ以外を省く」時などに使用することになります。

逆に「HAVING」で指定した検索条件はグループかされたあとに反映されるため「人数の少ないグループを表示から省く」なんてことに利用できます。

例１：４月１日以前に新規登録したユーザーを日付ごとに集計せよ

解答:

 select date,count(user_id) from (select * from install where date<'2020-04-01') as install group by date;

<実行結果>

2020-01-01|7
2020-02-04|2
2020-03-04|1
2020-03-05|2
~~~~~~~~~~~
2020-03-25|1
2020-03-29|1

例２：新規登録したユーザーが２人以上の日のみを集計せよ。

解答：

select date,count(user_id) from (select * from install) as install group by date having count(user_id)>=2;

<実行結果>

2020-01-01|7
2020-02-04|2
2020-03-05|2
2020-03-09|2
2020-03-11|2

以上がGROUP BYによる集計の基本です。SQLでの集計はデータ分析の基本中の基本になるので、こういった知識はデータ分析系の仕事を目指すならまず押さえておきたいところですね。

ミック翔泳社 2018年10月11日