[bigquery]データ不備による抽出失敗例→between編

betweenを利用する項目は日付型にする必要がありますが、文字型の日付データをDATE関数などでCASTしてから利用する場合にハマるときがありましたのでメモ残します。

▼ between対象項目にnullはご法度

例えば以下のように、スプレッドシートなどにあるレコードをbigqueryに取り込んでからSQLで抽出をかける処理でbetweenを使ってた場合どうなるか？というと

▼ サンプルコード

-- 変数を定義
DECLARE select_date DATE;

-- 変数に値を設定
SET select_date = DATE('2021-01-10');

-- 結果をSQLのみで出せるように、スプレッドシートの中身を直接コードして、tempテーブルに挿入している
CREATE OR REPLACE TEMP TABLE temp_table1 AS
    SELECT '1' as id, 'みかん' as name, '2021-01-01' as start_date, '2021-01-30' as end_date, 100 as price union all 
    SELECT '2' as id, 'りんご' as name, null as start_date, '2021-01-30' as end_date, 200 as price union all 
    SELECT '3' as id, 'バナナ' as name, '2021-01-01' as start_date, '2021-01-30' as end_date, 150 as price union all 
    SELECT '4' as id, 'スイカ' as name, '2021-01-01' as start_date, null as end_date, 800 as price union all 
    SELECT '5' as id, 'いちご' as name, '2021-01-01' as start_date, '2021-01-30' as end_date, 500 as price union all 
    SELECT '6' as id, 'メロン' as name, '2021-01-01' as start_date, '2021-01-30' as end_date, 900 as price
;
-- 作成したtempテーブルから betweenで日付を指定して抽出する
SELECT 
    * 
FROM
    temp_table1
WHERE
    select_date BETWEEN DATE(start_date) AND DATE(end_date)  -- betweenで日付範囲が'2021-01-10'の範囲にあるかどうか？を判定する
ORDER BY
    id

▼ サンプルコードの実行結果

見ての通り、start_dateかend_dateどちらかにnullが入っている場合（スプレッドシートから取り込んだ時は値が入ってないものがnull扱いになる）betweenで範囲指定した結果、抽出対象外になってしまっています。

▼ 学べたこと

bigqueryにはスプレッドシートを簡単に取り込める為、ついつい営業などが管理しているシートを取り込んでその後集計処理のインプットにしたりするシーンが以前より増えてきている気がします。

その場合、上記のような日付の入力漏れがあっても気づかずにそのまま取り込んでしまい、さらにこのデータはエラーとならずに集計処理で抽出漏れが延々と発生することになります。（恐ろしい。。）

本来データ取込の際には、日付項目の相互チェック（片方の値が入ってない場合はエラーにしてテーブルに取り込めないようにする）などの処理も合わせて実装しておけばこういった障害を事前に防ぐことができますが、気軽にデータ集計する現場の場合、この手の処理はスルーされがちなところもあると思います。