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

この記事は約5分で読めます。

 

事前準備

 

ファイルの場所→https://github.com/beginerSE/sql_practice

 

 

【テーブル説明】

  • login→ログインしたユーザーのユーザーIDとログイン時間
  • install→新規登録したユーザーのユーザーIDと登録時間

 

【中身】

・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

 

 

「GROUP BY」句で集計する

 

基本的に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;

 

<実行結果>

2020-01|7
2020-02|2
2020-03|19
2020-04|1
2020-06|1
2020-07|2
2020-09|1
2020-10|1
2020-11|1
2020-12|1

 

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

 

 

スポンサーリンク
スポンサーリンク

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

 

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

 

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

 

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

 

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

 

 

例1:4月1日以前に新規登録したユーザーを日付ごとに集計せよ

解答:

 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

 

 

 

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

解答:

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での集計はデータ分析の基本中の基本になるので、こういった知識はデータ分析系の仕事を目指すならまず押さえておきたいところですね。

 

 

 

 

コメント

タイトルとURLをコピーしました