みてねのMeetup #4 BigQuery で1秒動画の配信対象家族を爆速で抽出する

mitene_meetup4_nakano.sql

#standardSQL
-- 全6家族とする
WITH dummy_families AS(
  SELECT 1 AS id
  UNION ALL
  SELECT 2 AS id
  UNION ALL
  SELECT 3 AS id
  UNION ALL
  SELECT 4 AS id
  UNION ALL
  SELECT 5 AS id
  UNION ALL
  SELECT 6 AS id
),
-- 0～13のシーケンシャルな数値を生成
series AS(
  SELECT
    idx
  FROM
    unnest(generate_array(0,13)) AS idx
),
-- ダミーのメディアテーブル
dummy_media AS(
  -- 家族①は13個もっている
  SELECT m.* FROM (SELECT 1 AS family_id) AS m CROSS JOIN series
  UNION ALL
  -- 家族②は10個もっている
  (SELECT m.* FROM (SELECT 2 AS family_id) AS m CROSS JOIN series LIMIT 10)
  UNION ALL
  -- 家族③は11個もっている
  (SELECT m.* FROM (SELECT 3 AS family_id) AS m CROSS JOIN series LIMIT 11)
  UNION ALL
  -- 家族④は9個もっている
  (SELECT m.* FROM (SELECT 4 AS family_id) AS m CROSS JOIN series LIMIT 9)
  UNION ALL
  -- 家族⑤は13個もっている  
  SELECT m.* FROM (SELECT 5 AS family_id) AS m CROSS JOIN series
  UNION ALL
  -- 家族⑥は13個もっている    
  SELECT m.* FROM (SELECT 6 AS family_id) AS m CROSS JOIN series
),
-- ダミーの1秒動画テーブル
dummy_one_second_movie AS(
  -- 家族⑤はすでに1秒動画をもっている  
  SELECT 5 AS family_id
),
-- ダミーのアプリバージョンテーブル
dummy_app_version AS(
  SELECT 1 AS family_id , 2 AS version
  UNION ALL
  SELECT 2 AS family_id, 2 AS version
  UNION ALL
  SELECT 3 AS family_id, 2 AS version
  UNION ALL
  SELECT 4 AS family_id, 2 AS version
  UNION ALL
  SELECT 5 AS family_id, 2 AS version
  UNION ALL
  -- 家族⑥だけ古いバージョン
  SELECT 6 AS family_id, 1 AS version
),
-- 1秒動画を持たない家族
without_one_second_movie AS( 
  SELECT
    f.id AS family_id
  FROM
    dummy_families AS f
  LEFT JOIN
    dummy_one_second_movie AS o
    ON
      f.id = o.family_id
  WHERE
    o.family_id IS NULL
),
-- アプリバージョンを満たす家族
over_app_version_two AS(
  SELECT
    f.id AS family_id
  FROM
    dummy_families AS f
  LEFT JOIN
    dummy_app_version AS a
    ON
      f.id = a.family_id
  WHERE
    a.version >= 2
),
-- 10本以上メディアを持つ家族
over_ten_media AS(
  SELECT
      family_id
    , COUNT(1) AS media_cnt
  FROM
    dummy_media
  GROUP BY
    family_id
  HAVING
    COUNT(1) >= 10
)
SELECT 
  no_osms.family_id
FROM
  without_one_second_movie AS no_osms
JOIN
  over_app_version_two AS app_version
  ON
    no_osms.family_id = app_version.family_id
JOIN
  over_ten_media AS ten_media
  ON
    no_osms.family_id = ten_media.family_id
ORDER BY
  -- メディア数が多い順にソート
  ten_media.media_cnt DESC
LIMIT 2