BigQuery: средневзвешенное значение

Таблица:

| User_ID |  Red | Blue | Green |  Rating |
|   a     |   23 |  33  |   42  |    99   |
|   a     |   56 |  45  |   62  |    45   |
|   a     |   23 |  49  |   28  |    67   |
|   b     |   39 |  59  |   10  |    87   |
|   b     |   18 |  28  |   59  |    38   |
|   b     |   40 |  50  |   38  |    94   |

В результате я хочу получить отдельную строку user_id со средневзвешенными значениями красного, синего и зеленого цветов на основе столбца рейтинга.

цвет * рейтинг / (сумма оценок для а или б)

//Редактировать

Не могу понять, как это сделать. Пробовал следующее, но это была тщетная попытка

   WITH
      averages AS (
      SELECT
        User_ID,
        SUM(rating) AS average
      FROM
`       project.dataset.table` 
      GROUP BY
        1)
    SELECT
      averages.User_ID,
      Red*(Rating/average),
      Blue*(rating/average),
      Green*(rating/average)
    FROM
      `project.dataset.table` a
    LEFT JOIN
      averages
    ON
      a.user_id = averages.user_id

google-bigquery bigquery-standard-sql
person sharkorama    schedule 25.02.2018    source источник
comment
Что вы пробовали до сих пор?   -  person Martin Weitzmann    schedule 25.02.2018
comment
отредактировали свой ответ кодом, который я пробовал :)   -  person sharkorama    schedule 25.02.2018

Ответы (2)


arrow_upward
4
arrow_downward

Понятно - это скорее математическая задача. Вы умножаете значения на их вес, а затем делите не на количество, а на сумму весов. Все на группу (идентификатор пользователя). Вы можете попробовать что-нибудь вроде SELECT SUM(x * weight) / SUM(weight) FROM table GROUP BY ...

WITH t AS (SELECT * FROM 
  UNNEST([
    STRUCT('a' AS userID, 23 AS red, 99 AS weight),
    STRUCT('a' AS userID, 56 AS red, 45 AS weight),
    STRUCT('a' AS userID, 23 AS red, 67 AS weight),
    STRUCT('b' AS userID, 39 AS red, 87 AS weight),
    STRUCT('b' AS userID, 18 AS red, 38 AS weight),
    STRUCT('b' AS userID, 40 AS red, 94 AS weight)
  ])
  )

SELECT
  userID,
  SUM(red*weight) / SUM(weight) weightedAvg,
  AVG(red) normalAvg
FROM
  t
GROUP BY
  userID

hth!

person Martin Weitzmann    schedule 25.02.2018

arrow_upward
3
arrow_downward 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'a' User_ID, 23 Red, 33 Blue, 42 Green, 99 Rating UNION ALL
  SELECT 'a', 56, 45, 62, 45 UNION ALL
  SELECT 'a', 23, 49, 28, 67 UNION ALL
  SELECT 'b', 39, 59, 10, 87 UNION ALL
  SELECT 'b', 18, 28, 59, 38 UNION ALL
  SELECT 'b', 40, 50, 38, 94 
)
SELECT User_ID,  
  CAST(SUM(Red * Rating) / SUM(Rating) AS INT64) Red,
  CAST(SUM(Blue * Rating) / SUM(Rating) AS INT64) Blue,
  CAST(SUM(Green * Rating) / SUM(Rating) AS INT64) Green
FROM `project.dataset.table` 
GROUP BY User_ID

с результатом

Row User_ID Red     Blue    Green    
1   a       30      41      42   
2   b       36      50      31
person Mikhail Berlyant    schedule 25.02.2018