Bigqueryで2番目に小さい(大きい)数字を出す方法

概要

Bigqueryで2番目に小さい数字を出すSQL。
2番目と書いたが何番目でも出せる。

背景

ユーザーID単位で初回CV日から2回目CVまでの日数を出すという作業が発生したのですが、
サブクエリ使ったり、window関数使ったりするのが面倒で集計関数で1発で出せないかなぁ
と考えたのがきっかけ。

結論

配列を使えば万事解決。

実例

下記のようなIDとコンバージョン日が入ったサンプルテーブルをイメージしてください

SQL

with table as (
select * from UNNEST([
  STRUCT(1 as id, "2019-1-1" as cv_date),
  (1,DATE("2019-1-1")),
  (1,DATE("2019-1-2")),
  (1,DATE("2019-1-3")),
  (1,DATE("2019-1-4")),
  (1,DATE("2019-1-5")),
  (1,DATE("2019-1-6")),
  (1,DATE("2019-1-7")),
  (2,DATE("2019-1-1")),
  (2,DATE("2019-1-4")),
  (2,DATE("2019-1-5")),
  (2,DATE("2019-1-8")),
  (2,DATE("2019-1-10")),
  (2,DATE("2019-1-11")),
  (3,DATE("2019-1-1"))
])
)

select 
  id,
  array_agg(distinct cv_date order by cv_date)[SAFE_OFFSET(0)] first_date,
  array_agg(distinct cv_date order by cv_date)[SAFE_OFFSET(1)] second_date
from 
  table
group by 
  id

結果

解説

• array_aggを使ってid単位で配列を作成。その際、日付単位でユニークにしてかつcv_dateでソートします。
  • ※今回はdistinctしていますがここは必要に応じて消してください。
• 作成した配列に対して SAFE_OFFSET もしくはSAFE_ORDINAL でアクセスします。SAFEでないと指定した数字が範囲外の場合エラーになります。
  • ※ここでN番目を指定してください。

所感

• 配列めっちゃ便利だなぁ
• 他の集計値も一緒に出せるのは嬉しい
• 配列作成する時にlimitを書くと少し速くなったような気がするが気のせいかもしれない
  • array_agg(distinct cv_date order by cv_date limit 2)[SAFE_OFFSET(1)]