BETWEENに気をつけろ！BigQueryの日次集計で罠にハマった話

こんにちは。AI・機械学習チームの高田です。このブログはAI・機械学習チームブログリレー7日目の記事です。

はじめに

私たちAI・機械学習チームでは、機械学習モデルの学習データ準備やデータパイプライン開発のために日次でのデータ集計をしています。これらの集計データは、モデルの精度評価や特徴量エンジニアリングにおいて重要な役割を果たします。

先日、月次レポートを作成する際に、とあるログデータの日次集計の合計値と月次集計の値に差異があることに気づきました。単なるデータ欠損にとどまらない重大なバグの可能性もあるため、原因を徹底的に追求することが必要でした。

はじめに
問題の発見
原因調査
- 差分データの抽出
  - データの準備
- 問題の特定
根本原因：BETWEEN句の落とし穴
- 修正方法の検討
- 修正後のSQL
まとめ
We're hiring!

問題の発見

月次レポートの作成中に、次のようなSQLクエリで集計した日次データの合計が、月次で一括集計した結果と一致しないことに気づきました。

WITH target_date_data AS (
    SELECT
        data_id,
        col1,
        col2,
        col3,
        created_time,
    FROM
        `m3_project_id.m3_dataset.m3_table`
    WHERE
        created_time BETWEEN PARSE_TIMESTAMP(
            '%Y-%m-%d %H:%M:%S',
            ${from_datetime},  -- (例) '2025-05-19 00:00:00'
            'Asia/Tokyo'
        )
        AND PARSE_TIMESTAMP(
            '%Y-%m-%d %H:%M:%S', 
            ${to_datetime},    -- (例) '2025-05-19 23:59:59'
            'Asia/Tokyo'
        )
),
... 以下は省略 ...

このクエリを日次で実行しているのですが、1ヶ月分の出力件数の合計が、月次集計の件数と合わないという問題が発生しました。

具体的には、約0.01%の差分が発生しており、日次集計の合計件数が月次集計よりも少ないという現象でした。

原因調査

差分データの抽出

日次処理と月次処理の結果に差分が生じる場合、その原因を特定することが重要です。以下では、pandas を使用して両者の差分レコードを効率的に抽出する方法を示します。

データの準備

まず、日次処理と月次処理でそれぞれデータを取得します。

from datetime import datetime, time, timedelta
from string import Template
import pandas as pd

# 設定値を定数として管理
class DateRangeConfig:
    START_DATE = datetime(2025, 5, 1)
    END_DATE = datetime(2025, 5, 31)

def build_query(start_date: date, end_date: date) -> str:
    """クエリを生成"""
    sql_params = {
        'from_datetime': datetime.combine(start_date, time(0, 0, 0)).strftime('%Y-%m-%d %H:%M:%S'),
        'to_datetime': datetime.combine(end_date, time(23, 59, 59)).strftime('%Y-%m-%d %H:%M:%S'),
    }
    with open('query.sql') as f:
        sql_template = Template(f.read())

    return sql_template.substitute(**sql_params)


daily_df = pd.concat([
  pd.read_gbq(
      build_query(start_date=d.date(), end_date=d.date()),
      project_id='m3_project_id'
  )
  for d in pd.date_range(DateRangeConfig.START_DATE, DateRangeConfig.END_DATE)
], ignore_index=True)

monthly_df = pd.read_gbq(build_query(
    start_date=DateRangeConfig.START_DATE,
    end_date=DateRangeConfig.END_DATE
), project_id='m3_project_id')

lacking_df = monthly_df[~monthly_df['data_id'].isin(daily_df['data_id'])]

問題の特定

このようにして、差分となるレコードをpandasで抽出・分析したところ、日次集計で不足していた全てのcreated_timeの値が23:59:59台であることに気づきました。

さらに詳しく調査すると、created_timeがマイクロ秒を含んでいることが判明しました。

根本原因：BETWEEN句の落とし穴

調査の結果、次のことが明らかになりました

PARSE_TIMESTAMPの精度: 秒単位までしかパースしない（23:59:59.000000）
実際のデータ: マイクロ秒を含む（23:59:59.834521）
BETWEEN句の動作: 23:59:59.000000までしか含まない

つまり、ミリセカンドを考慮できておらず23:59:59.000001から23:59:59.999999のデータが集計から漏れていました。

修正方法の検討

判定期間の範囲について、開始時刻は含まれ、終了時刻は含まれない半開区間での判定を行う必要があります。

修正案はいくつかありました。

案1: 比較演算子を使った範囲指定

sql_params = dict(
    from_datetime='2025-05-19 00:00:00',
    to_datetime='2025-05-20 00:00:00'
)

WHERE created_time >= PARSE_TIMESTAMP(
        '%Y-%m-%d %H:%M:%S',
        '${from_datetime}',
        'Asia/Tokyo'
    ) -- (例) 2025-05-19 00:00:00
    AND created_time < PARSE_TIMESTAMP(
        '%Y-%m-%d %H:%M:%S',
        '${to_datetime}',
        'Asia/Tokyo'
    ) -- (例) 2025-05-20 00:00:00

ロジックとしては明快ですが、半開区間での判定を行うためには比較演算子に注意する必要があり、人為的なミスが発生する可能性が相対的に上がります。

案2: TIMESTAMP型をDATE型に変換

WHERE DATE(created_time, 'Asia/Tokyo') BETWEEN '${from_date}' AND '${to_date}'

この方法は、created_timeをDATE型に変換してから比較するため、マイクロ秒の影響を受けません。

今回は日付単位で時刻を丸めることで対応できますが、、丸める精度については「日付単位ではなく分単位である」、「境界値がキリの良い時刻ではない」など要件によって意識して実装する必要があります。

案3: DATE_TRUNC関数

WHERE DATE_TRUNC(created_time, DAY, 'Asia/Tokyo') = DATE('${target_date}')

これも案2と同様に、created_timeを日単位で丸めてから比較する方法です。

案4: RANGE_CONTAINS関数を使った半開区間

sql_params = dict(
    from_datetime='2025-05-19 00:00:00',
    to_datetime='2025-05-20 00:00:00',
)

WHERE RANGE_CONTAINS(
    RANGE<TIMESTAMP> '[${from_datetime}, ${to_datetime})',
    created_time
)

BigQueryのRANGE型は半開区間 [start, end) のみをサポートしています

[start, end): 開始値を含み、終了値を含まない。
例：RANGE<TIMESTAMP> '[2023-01-01 00:00:00, 2023-01-31 00:00:00)' は、2023年1月1日0時0分0秒を含むが、2023年1月31日0時0分0秒は含まない

このアプローチでは、パラメータ設計を見直して、to_datetimeには翌日の00:00:00を渡すようにする必要があります。しかし、RANGE型を使用することで次のようなメリットがあります。

強制的に半開区間での判定が行える
キャストや時刻の丸めを意識する必要がなく、コードがシンプルになる

ビルトインで用意されている RANGE_CONTAINS関数を使う方法が、半開区間で判定でき、最もストレートフォワードな解決策であると判断し、案4を採用しました。

修正後のSQL

WITH target_date_data AS (
    SELECT
        data_id,
        col1,
        col2,
        col3,
        created_time,
    FROM
        `m3_project_id.m3_dataset.m3_table`
    WHERE
        RANGE_CONTAINS(
            RANGE<TIMESTAMP> '[${from_datetime}, ${to_datetime})',
            created_time
        )
)
-- 以下は変更なし

まとめ

修正後、日次集計と月次集計が完全に一致し、欠損データが解消されました！

データ欠損の原因は、BETWEENは閉区間ということは知りながらも、マイクロ秒の精度を考慮していなかったことにありました。そもそも、時刻の範囲指定で23:59:59を指定すること自体が危うい設計だったことは反省点です。

今後は特定の範囲の日付のデータを取得する際には、半開区間で判定できるRANGE_CONTAINS関数を積極的に活用していきたいと思います。

今回の不具合調査を通して、WHERE句やJOIN句では特に使用するフィールドの生データを確認しながら条件を設定することの重要性を再認識しました。

We're hiring!

データ利活用のためのデータパイプラインの開発に興味がある方、一緒に働きませんか？

医療×テクノロジーの分野で、より良いサービスを作っていきましょう。

少しでも興味を持っていただけた方は、次のリンクからカジュアル面談にご応募ください！

jobs.m3.com

エムスリーテックブログ

エムスリー(m3)のエンジニア・開発メンバーによる技術ブログです