ユースケースの背景

動画共有プラットフォームにおけるデータ収集プロセスでは、クローラーの不具合やネットワーク障害により、一部の日次データが欠損することがあります。この記事では、SQLを用いて動画のいいね数データの欠損値を補完する4つの方法について解説します。

実装する補完方法

1. 前方補完（直近の有効値を継承）
2. 後方補完（次の有効値を逆方向から継承）
3. 隣接平均値補完（前後の有効値の平均値）
4. 線形分位補完（前後の有効値に基づく線形補間）

テーブル構造

CREATE TABLE video_likes_data (
  video_id STRING,
  dt STRING,
  likes_num INT,
  show_likes_num INT
);

前方補完の実装

WITH ForwardFill AS (
  SELECT
    video_id,
    dt,
    likes_num,
    show_likes_num,
    LAST_VALUE(show_likes_num, TRUE) OVER (
      PARTITION BY video_id 
      ORDER BY dt 
      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) AS prev_value
  FROM video_likes_data
)
SELECT
  video_id,
  dt,
  likes_num,
  show_likes_num,
  COALESCE(show_likes_num, prev_value, 0) AS filled_value
FROM ForwardFill;

後方補完の実装

WITH BackwardFill AS (
  SELECT
    video_id,
    dt,
    likes_num,
    show_likes_num,
    FIRST_VALUE(show_likes_num, TRUE) OVER (
      PARTITION BY video_id 
      ORDER BY dt 
      ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
    ) AS next_value,
    LAST_VALUE(show_likes_num, TRUE) OVER (
      PARTITION BY video_id 
      ORDER BY dt 
      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) AS prev_value
  FROM video_likes_data
)
SELECT
  video_id,
  dt,
  likes_num,
  show_likes_num,
  COALESCE(show_likes_num, next_value, prev_value, 0) AS filled_value
FROM BackwardFill;

隣接平均値補完の実装

WITH NeighborValues AS (
  SELECT
    video_id,
    dt,
    likes_num,
    show_likes_num,
    LAST_VALUE(show_likes_num, TRUE) OVER (
      PARTITION BY video_id 
      ORDER BY dt 
      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) AS prev_value,
    FIRST_VALUE(show_likes_num, TRUE) OVER (
      PARTITION BY video_id 
      ORDER BY dt 
      ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
    ) AS next_value
  FROM video_likes_data
)
SELECT
  video_id,
  dt,
  likes_num,
  show_likes_num,
  CASE 
    WHEN show_likes_num IS NOT NULL THEN show_likes_num
    WHEN prev_value IS NOT NULL AND next_value IS NOT NULL 
      THEN (prev_value + next_value) / 2
    WHEN prev_value IS NOT NULL THEN prev_value
    ELSE 0
  END AS filled_value
FROM NeighborValues;

線形分位補完の実装

WITH DateContext AS (
  SELECT
    video_id,
    dt,
    likes_num,
    show_likes_num,
    LAST_VALUE(CASE WHEN show_likes_num IS NOT NULL THEN dt END, TRUE) OVER (
      PARTITION BY video_id 
      ORDER BY dt 
      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) AS prev_date,
    FIRST_VALUE(CASE WHEN show_likes_num IS NOT NULL THEN dt END, TRUE) OVER (
      PARTITION BY video_id 
      ORDER BY dt 
      ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
    ) AS next_date,
    LAST_VALUE(show_likes_num, TRUE) OVER (
      PARTITION BY video_id 
      ORDER BY dt 
      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) AS prev_value,
    FIRST_VALUE(show_likes_num, TRUE) OVER (
      PARTITION BY video_id 
      ORDER BY dt 
      ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
    ) AS next_value
  FROM video_likes_data
)
SELECT
  video_id,
  dt,
  likes_num,
  show_likes_num,
  CASE 
    WHEN show_likes_num IS NOT NULL THEN show_likes_num
    WHEN prev_value IS NOT NULL AND next_value IS NOT NULL THEN
      prev_value * (DATEDIFF(next_date, dt) / (DATEDIFF(next_date, prev_date) + 1)) +
      next_value * (DATEDIFF(dt, prev_date) / (DATEDIFF(next_date, prev_date) + 1))
    WHEN prev_value IS NOT NULL THEN prev_value
    ELSE 0
  END AS filled_value
FROM DateContext;