BigQueryで、1000円分の切手問題を解く

背景

igcn.hateblo.jp

方法

実行は慎むこと。データ参照は0バイトだが、7000秒たっても（現時点）結果は出ない。こんなおバカなクエリをBQ様に投げて心が痛い。

WITH
  num_list AS (
  SELECT
    n
  FROM (
    SELECT
      GENERATE_ARRAY(1,1000) a )
  CROSS JOIN
    UNNEST(a) AS n),
n1 as (select n from num_list where n * 1 <= 1000),
n2 as (select n from num_list where n * 2 <= 1000),
n3 as (select n from num_list where n * 3 <= 1000),
n5 as (select n from num_list where n * 5 <= 1000),
n10 as (select n from num_list where n * 10 <= 1000),
n20 as (select n from num_list where n * 20 <= 1000),
n30 as (select n from num_list where n * 30 <= 1000),
n50 as (select n from num_list where n * 50 <= 1000),
n62 as (select n from num_list where n * 62 <= 1000),
n82 as (select n from num_list where n * 82 <= 1000),
n92 as (select n from num_list where n * 92 <= 1000),
n100 as (select n from num_list where n * 100 <= 1000),
n120 as (select n from num_list where n * 120 <= 1000),
n140 as (select n from num_list where n * 140 <= 1000),
n205 as (select n from num_list where n * 205 <= 1000),
n280 as (select n from num_list where n * 280 <= 1000),
n310 as (select n from num_list where n * 310 <= 1000),
n500 as (select n from num_list where n * 500 <= 1000),
n1000 as (select n from num_list where n * 1000 <= 1000)

select 
n1.n as nn1,
n2.n as nn2,
n3.n as nn3,
n5.n as nn5,
n10.n as nn10,
n20.n as nn20,
n30.n as nn30,
n50.n as nn50,
n62.n as nn62,
n82.n as nn82,
n92.n as nn92,
n100.n as nn100,
n120.n as nn120,
n140.n as nn140,
n205.n as nn205,
n280.n as nn280,
n310.n as nn310,
n500.n as nn500,
n1000.n as nn1000
from n1
cross join n2
cross join n3
cross join n5
cross join n10
cross join n20
cross join n30
cross join n50
cross join n62
cross join n82
cross join n92
cross join n100
cross join n120
cross join n140
cross join n205
cross join n280
cross join n310
cross join n500
cross join n1000
where 
n1.n * 1 +
n2.n * 2 +
n3.n * 3 +
n5.n * 5 +
n10.n * 10 +
n20.n * 20 +
n30.n * 30 +
n50.n * 50 +
n62.n * 62 +
n82.n * 82 +
n92.n * 92 +
n100.n * 100 +
n120.n * 120 +
n140.n * 140 +
n205.n * 205 +
n280.n * 280 +
n310.n * 310 +
n500.n * 500 +
n1000.n * 1000  = 1000

結論

バカは俺一人で十分