Skip to content

Instantly share code, notes, and snippets.

@m-nyuki

m-nyuki/mitene_meetup4_nakano.sql Secret

Last active Feb 15, 2019
Embed
What would you like to do?
みてねのMeetup #4 BigQuery で1秒動画の配信対象家族を爆速で抽出する
#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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.