Finding X in sql bigquery

Question

We have 6 variables, certain combination of each of them should get the closest to a number. For example :

60 x1 +226 x2 + 400 x3 +554 x4+ 469 x5+ 278 x6 should be as close as feasible to a given a number say, 2620

Constraints : x1>=x2>=x3>=x4>=x5>=x6 Xs can only be integers and should also be >=1

Looking for an easily scalable solution for this in Google bigquery

Answer 1

Below is for BigQuery Standard SQL

#standardSQL
WITH puzzle AS (
  SELECT 'x1' x, 60 weight, 2620 target UNION ALL
  SELECT 'x2', 226, 2620 UNION ALL
  SELECT 'x3', 400, 2620 UNION ALL
  SELECT 'x4', 554, 2620 UNION ALL
  SELECT 'x5', 469, 2620 UNION ALL
  SELECT 'x6', 278, 2620 
), numbers AS (
  SELECT num FROM (
    SELECT DIV(ANY_VALUE(target), MIN(weight)) max_num
    FROM puzzle
  ), UNNEST(GENERATE_ARRAY(1, max_num)) num
)
SELECT x1.num x1, x2.num x2, x3.num x3, x4.num x4, x5.num x5, x6.num x6,
  (SELECT weight FROM puzzle WHERE x = 'x1') * x1.num + 
  (SELECT weight FROM puzzle WHERE x = 'x2') * x2.num + 
  (SELECT weight FROM puzzle WHERE x = 'x3') * x3.num + 
  (SELECT weight FROM puzzle WHERE x = 'x4') * x4.num + 
  (SELECT weight FROM puzzle WHERE x = 'x5') * x5.num + 
  (SELECT weight FROM puzzle WHERE x = 'x6') * x6.num AS result
FROM puzzle z,
  numbers x1,
  numbers x2,
  numbers x3,
  numbers x4,
  numbers x5,
  numbers x6
WHERE x1.num >= x2.num 
AND x2.num >= x3.num 
AND x3.num >= x4.num 
AND x4.num >= x5.num 
AND x5.num >= x6.num 
ORDER BY ABS(target - result)   
LIMIT 1

The output is

Row x1  x2  x3  x4  x5  x6  result   
1   4   3   1   1   1   1   2619     

Note: above approach can relatively easy be adopted for dynamic number of parameters variables