次のような一覧があり、これを集計して小計や総計を取得したい
| 名前 | 言語 | 国名 | 都市名 |
|---|---|---|---|
| Apple | EN | United States | Florida |
| Banana | EN | Japan | Tokyo |
| Chocolate | EN | Japan | Tokyo |
| Grape | EN | Japan | Kyoto |
| アップル | JA | 日本 | 東京 |
| チョコレート | JA | 日本 | 東京 |
| ぶどう | JA | 日本 | 京都 |
| ... | ... | ... | ... |
小計や総計を取得するためにROLLUPを使用したが、想定外の結果になったので原因調査に苦労した
結果的にデータの問題で、単純な話だったがなるほどと思ったので書き残す
ROLLUP
ROLLUPを使うと次のような小計や総計が得られるので、やりたいことにぴったりだった
SELECT 言語, 国名, 都市名, COUNT( 言語 ) AS count FROM MY_TABLE GROUP BY ROLLUP ( 言語 , 国名, 都市名) +-----+---------------+---------+-------+ |lang | country | state | count | +-----+---------------+---------+-------+ | EN | null | null | 4 | ←ENの総計 | EN | Japan | null | 3 | ←EN, Japanの小計 | EN | United States | null | 1 | | EN | Japan | Tokyo | 2 | | EN | Japan | Kyoto | 1 | | JA | null | null | 3 | ←JAの総計 | JA | 日本 | null | 3 | ←JA,日本の小計 | JA | 日本 | 東京 | 2 | | JA | 日本 | 京都 | 1 | +-----+---------------+---------+-------+
ところが、このテーブルを保存し、ある条件で検索をかけたところ
次のように２件結果が返ってきてしまい混乱した
+-----+---------------+---------+-------+ |lang | country | state | count | +-----+---------------+---------+-------+ | EN | Japan | null | 3 | | EN | Japan | null | 1 | +-----+---------------+---------+-------+
ROLLUPを使ったことがなかったのでクエリを疑ったが、結果的に都市名にnullがあるのが原因とわかった
| 宿名 | 言語 | 国名 | 都市名 |
|---|---|---|---|
| Donuts | EN | Japan | null |
つまり、このnullがあるレコードと集計結果のnullの２件が表示されていた
集計結果のレコードでは各カラムがnullになることを意識してなかったし、こうなるとは知らなかった
ただ、このnullレコードは異常値ではない
そこで、COALESCEを使ってnullがあるレコードを別の値に変更することにしてからROLLUPをかけることにした
COALESCEはnull値を別の値に変換する関数
SELECT *, COUNT( 言語 ) AS count FROM ( SELECT COALESCE (言語, '') as 言語, COALESCE (国名, '') as 国名, COALESCE (都市名, '') as 都市名 FROM MY_TABLE) GROUP BY ROLLUP ( 言語 , 国名, 都市名)
これにより集計結果と区別することができるようになった
+-----+---------------+---------+-------+ |lang | country | state | count | +-----+---------------+---------+-------+ | EN | Japan | null | 3 | | EN | Japan |"" | 1 | +-----+---------------+---------+-------+
ROLLUPするときは、各カラムにnullがないかを事前に考えておく必要があることがわかった
