まえがき
最近、Mysqlにてデータをこねこねすることが多いのですが、ちょっとSQLに詰まってしまったので記事にしておきます。
今回も備忘録ですが、調べるのに時間がかかったので、誰かの役に立てればよいなと思い書き留めておきます。
目的
とあるテーブルのデータを抽出しカウント。
そのカウントした結果をさらに条件で絞り出力する。
・・・と言ってもわかりづらいと思うので、例を以下に置いておきます。
例
以下のようなテーブルがあるとします。
上記のテーブルについて、dateとsportsをキーとしてユニークを取ります。
その出力結果を参照して、count(*)が1のものを抽出する。
ということをSQLのみでやりたかったのです。
簡単だと思ったのですが、SQLnoobだったので以外と時間がかかってしまいました。
やったこと
こういうのは結論から書くのが定石ですが、それだとブログに書くことがなくなってしまうので、自分がやっていったことを順に書いていきます。
お付き合いお願いします・・・
(1) 図1から図2を抽出するSQLを考える
さすがのSQLnoobでもこれはすぐにできました。
group byでまとめて、それをcount関数で数えてあげればよいのです。
というわけで作ったSQLが以下。
select
date,
sports,
count(*)
from
m_sports
group by
date,
sports
;
これで図2の出力結果は得られました。
(2) 図2の結果をSQLにて図3に変換する方法を考える
noobはここでつまりました。
出力結果をSQLに直接次のSQLにくわせる方法が思い浮かばなかったのです。
Linuxでいうxarge的なコマンドがあるかなーと思ったのですが、よくわからず・・。
UNION？がそれっぽかったのですが、SQLが長くなりそうだったので脳が拒否反応を起こしました。
で、よく調べてみたら、SQLはネストすることで出力結果をSELECTにくわせることができることを発見。
関数に関数食わせる感じですね。
というわけで以下のSQLを作ってみました。
select
*
from
(
select
date,
sports,
count(*)
from
m_sports
group by
date,
sports
)
where
count(*) = 1
;
実行！
エラー！
なんでや！
(3) エラーの原因を調べる
といっても、大体のめどは立っておりました。
where
count(*) = 1
この部分ですね。
まあこの書き方は無理だろうな、と思いつつ実行して、やはり無理でしたーって感じです。
さて、ではどうやってcount行をwhereにくわせようか、と考えていたところ、
AS
というものがあることを知ります。
これは、テーブル名やカラム名に名前を与えてあげて、以降その名前でいろいろできるようにするもの（らしい）です。
というわけで、ネスト内のcount(*)に名前を与えてあげて、それを使ってwhereにしてあげるようにします。
select
*
from
(
select
date,
sports,
count(*) as cnt
from
m_sports
group by
date,
sports
)
where
cnt = 1
;
実行！
エラー！
なんでや！！（本気）
(4) エラーの原因を調べる その2
とりあえずエラーメッセージを読んでみます。
Every derived table must have its own alias
Google翻訳にかけると
すべての派生テーブルには独自のエイリアスが必要です。
とのこと。
ここで言う派生テーブルってなんや？
ってなりましたが、調べていくと以下箇所が悪かったと判明
from
(
select
date,
sports,
count(*) as cnt
from
m_sports
group by
date,
sports
)
さっき直したじゃん！って思ったのですが、どうやらfromで指定するテーブルは固定名（エイリアス）がないといけないらしいです。
今回はselectの出力結果をそのままfromに指定しているので、その部分が派生テーブルとなっていたようです。
(5) リトライ
というわけで、上記の問題を解決するべく、派生テーブルに名前をつけてあげました。
そのSQLがこちら
select
*
from
(
select
date,
sports,
count(*) as cnt
from
m_sports
group by
date,
sports
) as date_sports
where
cnt = 1
;
fromの指定先のselect分に、「data_sports」という名前を付けてあげました。
これで実行してみます。
・・・無事出力！
というわけで無事に欲しい情報を抽出することができました。
あとがき
「こんなんで詰まってるとか本当にIT従事者か？」
って思われそうですが、そうなのだから仕方がない・・・。
反論はできませんが・・
でも、同じところで詰まっている人も多分少ながらずはいると思うのです。
そのような人たち、そして自分自身にも何か残せたら、と思い記事にしてみました。
あとネタに困ってたので
それでは！
コメント