Open4

LeetCodeのSQL140本ノックを5日で完走したら結構良かったのでメモ

50時間くらいかかりました。5日間完全に寝なかったけどやれば人はできる。

ぶっちゃけDMLスムーズに書けない力不足を感じていたので「一度ボコボコにされて鍛えたいなぁ」と思ってたらLeetCodeがとてもいいSQL問題集を抱えていました。

SQL140本ノックはこちら↓。

https://leetcode.com/problemset/database/

何がいいのか

同様のサービスにsqlzooとかありますが、leetcodeはもっとすごいです。


回答画面


回答詳細画面


掲示板画面

特に掲示板が最高で、「あぁこれってアンチパターンなんや・・・」とか「そんな裏技できるんか・・・」というのをコメント見て気づいたりできます。LeetCodeのサイトの目指してるゴール上、結構ユースケースに近い問題が多くてそれもまた勉強になりました。

注意として有料問題があります。自分は教科書代とおもってお金払いましたが、無料では40問くらいしかないと思います。でも結構十分な気がする。そこで、無料問題の範囲で良い問題をピックアップしてみました。

前提知識

極論前提知識0でもやってるうちに体得してくようにできてるように感じてます(だからこれ、非SQL経験者への研修にめちゃくちゃ使える気がする)。ただ一応知っといたら楽になるんじゃないかという知識は以下の通り。

  • 基本的なDML(selectとかdelete)。
  • (inner) join, left (outer) join, right (outer) join, natural join, union, union allなどのオペレータ。
  • windows functions (dense_rank() over (partition by id oder by date desc) とかavg(cnt) over(order by id rows between current row and 2 following))とか)。これはMySQL Ver8からの機能なので多用してはいけないが、使える所では練習としてwindows functionでも別解してみるといい気がする。パフォーマンス超絶良いしコード綺麗になる。
  • CTE (withとか)。これもMySQL Ver8からの機能だし多用してはいけないが、with recursiveだけは別。1-100までの連番がほしい時にselect 1 union select ....ってやってたら日が暮れるし。
  • UserDefinedVariable。inner join (select @cnt:=0)など。row_numberとかcumulative_sumとかやる時、Windows Functionを使わないなら確実にお世話になるであろう機能。
  • 相関サブクエリは死んでも使わない事。MySQL5.6で自動最適化が入って高速になったが、使ってると周りから「おいw」って言われるのでやめましょう。遅いです。この記事が10年前だけど本当に詳しいです。

このあたりが「あるんだな」って気持ち止めておくと楽になると思います。難易度としては経験者なら大体Easyは1,2分で解けますが、Mediumが下手したら10分、Hardは初見で詰まったら余裕で1,2時間くらいかかったりします。

良かった問題

1: 各グループのN番目を取得する

184. Department Highest Salary

頻出ユースケース。windows functionを用いると

from (
    select name,
           departmentid,
           rank() over (partition by departmentid order by salary desc) as r
    ----
) as hoge
where r=1

で簡単に解けてしまいますが、rank() over (partition by DepartmentId)を使わずにいかに生SQLで取得できるかが鍵です。また二種類のjoinが必要になるので、基礎知識の復習にもなります。

これの発展型が185. Department Top Three Salaries

2: rowが連続しているかどうかを検知する

601. Human Traffic of Stadium

頻出ユースケース。このケースでは3つだけなので、正直joinを2つずつかけてあげれば解けるが、これが例えば100個以上連続してる時でもスマートに解けれないか試してみましょう。

ヒントとしてはwindows functionを用いるならば

    id - row_number() over (order by s.id) as grp
とした上で
    group by grp
    having count(grp)>=3
とすると。。。

3: 特定条件の場合のパーセンテージを計算する

262. Trips and Users

「特定の日のキャンセル率を計算する」というユースケース。いかに分母と分子をsmartに、かつ相関サブクエリを使わないで答えるかがポイント。
countを別々にjoinしてもいいが、実は一発で計算できる方法がある。

4: 各スコアのランク付けをする

178. Rank Scores

windows functionを使うと一発で解けてしまうのだが、user defined variableと、variableも使わない生SQLの3パターンで解いてみると良さそう。

Snippet

mycliFavorite Query記法で一覧していきます。下な感じで使えます(ぶっちゃけ事故る可能性もあるからメモ程度のつもりがいい)。

1: 累積和

cumulativesum = '''
select $1,
       (@csum := @csum + 1) as csum
from $2
inner join (select @csum:=0) as t
'''

めんどくなった

あとはここ見た方が速いと思います。自分のmyclircです。

https://github.com/ulwlu/dotfiles/blob/master/dotfiles/.trashrc/.myclirc
ログインするとコメントできます