MySQL 集約関数の使い方

集約関数

 

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

 

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

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

 

記述順序と実行順序

※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';

 

 

 

平均値を調べる

 

 

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

 

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

 

最小値を調べる

 

 

商品の最小値を調べる

 

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

 

 

最大値を調べる

 

 

商品の最大値を調べる

 

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

 

 

対象行の行数を数える

 

 

 

例:全てのユーザー(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 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 
    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)

 

 

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

 

select *from 列名 order by price desc;

 

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

 

select *from products order by price asc;

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

select *from products order by price;

 

 

価格と登録順で並べる

 

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

価格:price

登録順:id

とした場合

 

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