MySQL 集約関数の使い方

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

集約関数

SQLでテーブルの値を集計するために使います。

演算子 意味
sum(expr) 合計値を返します
avg(expr) 平均値を返します
min(expr) 最小値を返します
max(expr) 最大値を返します
count(expr) 行数のカウントを返します

(expr)内は、引数と呼ばれ呼び出しごとに異なる値を関数に渡すために使用されます。

集約関数では、基本的にnullが無視されます。

記述順序と実行順序

記述順序

  1. select・・取得行(カラム)の指定
  2. from・・対象テーブルの指定
  3. where・・・絞り込み条件の指定
  4. group by ・・・グループ化の条件を指定
  5. having・・・グループ化した後の絞り込み条件を指定
  6. order by・・・並び替え条件を指定
  7. limit・・・取得する行数の制限

実行順序

  1. from・・・対象テーブルの指定
  2. where・・・絞り込み条件の指定
  3. group by・・・グループ化の条件を指定
  4. select・・・取得行(カラム)の指定
  5. having・・・グループ化した後の絞り込み条件を指定
  6. order by・・・並び替え条件を指定
  7. limit・・・取得する行の制限

※selectの記述順序と実行順序が異なります。

合計金額を調べる

構文:sum(expr)

2017年11月の売上合計金額を求める

2017年11月の全てのデータを以下のように抽出した場合。

select 
	* 
from 
	テーブル名 
where 
	order_time >= '2017-11-01 00:00:00'
	and order_time < '2017-12-01 00:00:00';

全てのデータからsum関数を使い(amount)の合計値を返します。

結果以下のように合計値が出力されます。

select 
	sum(amount) 
from 
	テーブル名 
where 
	order_time >= '2018-01-01 00:00:00'
	and order_time < '2018-02-01 00:00:00';

平均値を調べる

構文:avg(expr)

全商品の平均価格を求めるには

select avg(price) from テーブル名;

最小値を調べる

構文:min(expr)

商品の最小値を調べる

select min(price) from テーブル名;

最大値を調べる

構文:max(expr)

商品の最大値を調べる

select max(price) from テーブル名;

対象行の行数を数える

構文:count(expr)

・exprの数をカウントします。
・count(*)とすると、テーブルの行数を取得できる。
・テーブルの行が、ユーザー数となります。

例:全てのユーザー(users)の人数を求めます。

select count(*) from テーブル名;

例:男性のユーザーの人数を調べる

列名:gender

性別:genderのデータが(※1:男性 2:女性とした場合)

select count(*) from テーブル名 where gender = 1;

ユニークユーザー数を調べる

11月にアクセスしたユニークユーザーを調べる

※あるユーザーAさんが決まった期間内に複数アクセスしたとしても1と数えます。

count(distinct expr)の形でcount関数は、重複を排除し個数を取得できます。

where句と集約関数 count (distinct expr)を組み合わせて使います

select 
	count(distinct user_id) 
from 
	テーブル名 
where 列名 = '2017-11-01';

データをグループ化して都道県別のユーザーを調べてみます。

データをグループ化するには group by句を使います。

・group byで指定する列名によって、グループ化されます。

構文: select 列名 from テーブル名 group by 列名;

その前に分かりやすいようにサンプルを用意しました。

select *from テーブル名 group by prefecture_id;

で出力した結果以下の画像だったとすると・・

prefecture_idとそのprefecture_idに所属しているユーザー数をカウントするためにformの前にprefecture_id, count(*)を付与します。

・countを使ってデータを集計する時にグループ単位で集計が行われます。

select prefecture_id, count(*) from テーブル名 group by prefecture_id;

すると以下のように各都道県がグループ化され、集計されます。

期間ごとに集計する

以下ようなテーブルがあった場合に

select 
	* 
from 
	テーブル名;

2017年の月別ユニークユーザーを調べるには

group by句を使用しテーブルのデータをrequest_monthによってグループ分けすることにより月別のユニークユーザー数が求められます。

select 
    request_month,
    count(distinct user_id)
from 
    access_logs
where
    request_month >= '2017-01-01'
    and request_month < '2018-01-01'
group by
    request_month;

count(distinct)をuser_idに指定することにより、重複なしのユニークユーザー数が求められます。

上記のコード実行結果は以下のようになります。

集約結果をさらに絞り込む

having句を使って月間ユニークユーザー数が640人以上の月を絞り込む

having句はwhere句と同様、条件に合致する行に絞り込めます。

テーブルのデータを集約した結果に対して、条件式を適用する場合に使います。

構文:select 列1,… from テーブル名 where 条件式 group by 列1,… having 条件式;

記述順序:select→from→where→group byhaving

select 
    request_month,
    count(distinct user_id)
from 
    access_logs
where
    request_month >= '2017-01-01'
    and request_month < '2018-01-01' 
group by 
    request_month 
having 
    count(distinct user_id) >= 640;

すると以下のように640人以上のユニークユーザーが絞り込めました。

複数の並び替え条件を指定する

asc・・昇順(ascending) ※デフォルト

desc・・降順(descending)

構文: order by 列名や式 並び順,…

商品一覧を価格が高い順に並べる

select *from 列名 order by price desc;

商品一覧を価格が低い順に並べる

select *from products order by price asc;

低い順の時は以下のようにascを省略できます。

select *from products order by price;

order byで並び順を指定しないと、将来バージョンアップした時に同じ並び順になることは保証されていないため取得する並び順が重要な場合は 明示的に並び順を指定しましょう。

価格と登録順で並べる

商品一覧を価格が高い順に並べ、価格が同じ時は登録順で並び替えをする。

価格:price

登録順:id

とした場合

select *from テーブル名 order by price desc,id asc;

にほんブログ村 IT技術ブログへ

スポンサーリンク
スポンサーリンク
SNSフォローボタン

フォローする

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