【初心者向け】集計クエリの書き方

投稿者 投稿日:

select,from,where order byまではすんなり理解できたものの、
group byでつまづいた方向けの記事になります。

group by難しいですよね。

私自身も同じころに学ぶorder byに比べて、直感的に理解できないなと感じました。
それに、後輩エンジニアの方からのクエリに関する質問は、group byが多いです。

今でこそgroup byを使ったクエリを自由に書けるようになりましたが、
しかし、随分と時間をかけたことを覚えています。

そこで今回はgroup byが難しいと感じている方向けに

  • selectの書き方
  • group byの選び方
  • 間違っているときの確認方法

を紹介したいと思います。

selectは集計関数を使うかgroup byと同じものを使うかで考える

私は、group byを使ったクエリがうまく書けない理由の一つに
「selectの書き方がわからない」があると考えています。

というのも、selectとgroup byはかなり密接に連携しているからです。

group byを使い方を調べると
Sum()、Max()、Min()、AVG()、Count()などの集計関数と一緒に使う
と、でてくるので間違いではないでしょう。

しかし、group byを使うとき必ずしもselectは集計関数だけではないですよね。

そこで、selectを書くときのポイントとして
集計関数を使うか、group byを使うかを提案したいと思います。

どういう事か例を見ていきましょう。使用する表は下記です。

クエリがメインなので表は参考程度にお考えください。

では、本題の集計関数Max()とgroup byの関係を表す四つのクエリです。

  1. select Max(item_id) , Max(category_id) , Max(price)
    from item;
    　
  2. select item_id , Max(category_id), Max(price)
    from item
    group by item_id;
    　
  3. select item_id , category_id, Max(price)
    from item
    group by item_id, category_id;
    　
  4. select item_id , category_id, price
    from item
    group by item_id, category_id, price;

四つのクエリを見ていただくと、selectにMax()などの集計関数を書くときは
「集計関数を使用するか」もしくは「集計関数を外してgroup byと同じものを使用するか」の
どちらかを選ぶことになります。

これは集計関数を使用するときの絶対のルールですので覚えて損はないです。

group byに指定する列は○○ごとと言い換えてみる

selectの書き方がわかったところで、次にgroup byにフォーカスします。

group byの選び方は重要ですね。
group byは集計する「単位」を決めるものです。

集計するとき、

  • 〇〇ごとの◇◇の最大値
  • 〇〇ごとの件数
  • 〇〇ごとの◇◇平均

というように集計をします。
この「〇〇ごと」が集計する単位になります。

今回は「カテゴリーごと」の「最大」クエリを書いてみます。

select Max(item_id) , category_id, Max(price) from item
group by category_id

「category_id」がgroup byに指定されるため集計関数は使えません。
逆に、priceとitem_idに集計関数をつかいます。

もし、カテゴリーごとの最大金額を出力するなら、
ここで　「Max(item_id) 」は使いませんので

select category_id, Max(price) from item
group by category_id;

となります。

group byを外してorder byで確認しよう

集計するためのクエリを作ったからといって
思った通りの集計結果になるとは限りません。

そこで、思った結果にならないときのお勧めの確認方法を紹介します。

それはがorder byを使用することです。

先ほどの例にとると

select category_id, Max(price) from item
group by category_id;

であれば、集計関数をはずして

select category_id , price from item
order by category_id;

に変更してみてください。

すると、集計するべき対象ごとに並べ替えられた表が出力され、
目視で電卓やエクセルで確認できるようになります。

手計算した数値と見比べることで、group byで集計の仕方が間違っているのか、
それとも別の要因があるかの切り分けができます。

世にある集計クエリは単純なものばかりではありません。
joinをしていたりクエリ中で計算をしていたりします。

そうした時、group byに過不足があるのか、
はたまた、joinが間違っているのかなどを判別することができます。

集計が間違っているときの打ち手としてはかなりおすすめです。

まとめ

group byを使ったクエリの書き方と結果の確認方法は以上になります。

  • selectには集計関数を使用するかgroup byと同じものを使用するかの2択
  • group byには日本語にしたとき「○○ごと」となる項目を指定する
  • 集計があっているかを確認するときはorder byに変えて確認する

つまづくところは人それぞれですけれども、
私と似たポイントで苦しんでいる方の参考になれば嬉しいです。

投稿者: nry