calibrate_prediction.sql

WITH tmp1 as (
  select
      userid,
      array[ 
        probabilities[1],
        probabilities[2],
        probabilities[3],
        probabilities[4],
        probabilities[5],
        probabilities[6],
        probabilities[7],
        probabilities[8] * 0.8 -- M3
      ] as probabilities
  from
    rf_predicted_cv
),
tmp2 as (
  select
    userid, 
    probabilities,
    reduce(
      probabilities,
      CAST(ROW(-infinity(), 0, 0) AS ROW(max DOUBLE, index INTEGER, argmax INTEGER)),
      (s, x) -> CAST(ROW(if(x > s.max, x, s.max), s.index + 1, if(x > s.max, s.index, s.argmax)) AS ROW(max DOUBLE, index INTEGER, argmax INTEGER)),
      s -> s.argmax
    ) as label
  from
    tmp1
)
select
  l.userid,
  l.probabilities,
  r.label
from
  tmp2 l
  JOIN label_mapping r ON (l.label = r.label_id)
limit 10