BigQuery の BOOL, STRING, INT64 のソート

背景

BOOL のフィールドを order by でソートすると、どういう順番になるのか分からなかったので試してみた。

実行環境

• BigQuery

クエリ

INT64, STRING 型のフィールドについても気になったので試した。

BOOL

with unioned as (
  select true as sort_column
  union all
  select false
  union all
  select null
)

select
  *
from unioned
order by
  -- -- 結果: null -> false -> true
  sort_column asc
  -- sort_column asc nulls first
  -- sort_column

  -- -- 結果: false -> true -> null
  -- sort_column asc nulls last

  -- -- 結果: true -> false -> null
  -- sort_column desc
  -- sort_column desc nulls last

  -- -- 結果: null -> true -> false
  -- sort_column desc nulls first

INT64

with unioned as (
  select -1 as sort_column
  union all
  select 0
  union all
  select 1
  union all
  select null
)

select
  *
from unioned
order by
  -- -- 結果: null -> -1 -> 0 -> 1
  sort_column asc
  -- sort_column asc nulls first
  -- sort_column

  -- -- 結果: -1 -> 0 -> 1 -> null
  -- sort_column asc nulls last

  -- -- 結果: 1 -> 0 -> -1 -> null
  -- sort_column desc
  -- sort_column desc nulls last

  -- -- 結果: null -> 1 -> 0 -> -1
  -- sort_column desc nulls first

STRING

with unioned as (
  select 'a' as sort_column
  union all
  select 'aa'
  union all
  select 'b'
  union all
  select 'ab'
  union all
  select null
)

select
  *
from unioned
order by
  -- -- 結果: null -> a -> aa -> ab -> b
  sort_column asc
  -- sort_column asc nulls first
  -- sort_column

  -- -- 結果: a -> aa -> ab -> b -> null
  -- sort_column asc nulls last

  -- -- 結果: b -> ab -> aa -> a -> null
  -- sort_column desc
  -- sort_column desc nulls last

  -- -- 結果: null -> b -> ab -> aa -> a
  -- sort_column desc nulls first

まとめ

order by sort_column asc

• BOOL: null -> false -> true
• INT64: null -> 数字の昇順
• STRING: null -> 文字列の昇順
  • 1 文字目 を見て並び替える
  • 1 文字目 が同じ文字列だったら、2 文字目 を見て並び替える
  • n 文字目 までが同じ文字列だったら、n + 1 文字目 までを見て並び替える
  • n 文字目 までが同じ文字列で、n 文字 の文字列 と n + 1 文字 の文字列の値が存在する場合は、n 文字 の文字列が優先される

order by sort_column desc

order by sort_column asc の逆の順番になる。

• BOOL: true -> false -> null
• INT64: 数字の降順 -> null
• STRING: 文字列の降順 -> null
  • 1 文字目 を見て並び替える
  • 1 文字目 が同じ文字列だったら、2 文字目 を見て並び替える
  • n 文字目 までが同じ文字列だったら、n + 1 文字目 までを見て並び替える
  • n 文字目 までが同じ文字列で、n 文字 の文字列 と n + 1 文字 の文字列の値が存在する場合は、n + 1 文字 の文字列が優先される

order by sort_column nulls first

BigQuery では、null が優先される。 nulls first がデフォルトなので、指定しないときの順番と変わらない。

• null が、最初にくる
• ほかの値は、asc / desc で指定した順番になる

order by sort_column nulls last

• null が、最後にくる
• ほかの値は、asc / desc で指定した順番になる

参考

• Data types | BigQuery | Google Cloud