SQL の小ネタ

こんにちは、ATです。

新型コロナがなにかと世間を騒がしていますね。
こういう時こそ冷静に行動したいです。(買い占めやめて、お願い！)
というわけでネタに困ったときの小ネタ回です。 今回はSQLの小ネタを書いていきます。

とりあえず文字数を稼いだところで早速ネタに。

前提：Bigquery　StandardSQL

1. 条件付きカウント

COUNT()は条件を指定して、当てはまるデータだけカウントする機能があります。
便利なのですが書き方にクセがあります。SQLに慣れてないと「え、なんで？」となります(私です)。

COUNT(カラム名 条件 OR NULL)

例)
COUNT(type = 0 OR NULL)

COUNT()はNULL以外をカウントするので普通に条件書くとBOOL値になります。NULLでないから真偽関係なくカウントされてしまう。BOOL値とNULLの論理演算では偽とNULLの論理和はNULLになるので上記の内容で条件付きカウントが行える。

2. カラム値のソート

よく見るテーブルで下記のようなものがあります。

id | option1 | option2 | option3 | option4

このようなテーブルでまれにoption1〜4の値をソートして出力してという要望があったりします。 そのような場合下記のやり方で一応対応できます。

WITH foo AS (
SELECT 1 AS id, 4 AS option1, 7 AS option2, 3 AS option3, 1 AS option4 UNION ALL
SELECT 2 AS id, 3 AS option1, 12 AS option2, 9 AS option3, 51 AS option4 UNION ALL
SELECT 3 AS id, 17 AS option1, 234 AS option2, 6 AS option3, 8 AS option4
),
bar AS (
  SELECT
    id,
    ARRAY((SELECT a FROM UNNEST([option1, option2, option3, option4]) AS a ORDER BY a)) AS options
  FROM foo
)
SELECT id, options[OFFSET(0)], options[OFFSET(1)], options[OFFSET(2)], options[OFFSET(3)]
FROM bar

配列に固めて、サブクエリでばらしつつソートし配列に戻してる。
あとは配列のまま処理続けるか、文字列にするか、はたまた元の形にバラすか。
要件次第でお好きなように。今回はまたバラす処理をとりあえず書いてみました。

3. NULL含めたORDER BY

最後並べ替えの話を。
NULLが入っているカラムをキーとした並べ替えでは何もしないとNULLが先頭にきてしまう。 だがしかし、NULLは最後にして欲しい、なんて注文は割とあったりします。 そんな時はこう書きましょう。

ORDER BY カラム名 IS NULL, カラム名

例)
ORDER BY num IS NULL, num

 NULLが最後になる

世の中にはNULLS LASTなんて指定もあるみたいですがBigqueryは対応してないような気配がします。

といったところでお時間になりました。
一応、今までのネタは業務で使ったことがあるので、それなりに必要な場面があるのではと心の片隅で思ってます。何かのお役に立てば幸いです。
では良きSQLライフを。