目次
こんにちは、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ライフを。
株式会社grasys(グラシス)は、技術が好きで一緒に夢中になれる仲間を募集しています。
grasysは、大規模・高負荷・高集積・高密度なシステムを多く扱っているITインフラの会社です。Google Cloud (GCP)、Amazon Web Services (AWS)、Microsoft Azureの最先端技術を活用してクラウドインフラやデータ分析基盤など、ITシステムの重要な基盤を設計・構築し、改善を続けながら運用しています。
お客様の課題解決をしながら技術を広げたい方、攻めのインフラ技術を習得したい方、とことん技術を追求したい方にとって素晴らしい環境が、grasysにはあります。
お気軽にご連絡ください。
