Блог

Я работаю над проблемой, в которой у меня есть следующая таблица:

   ----------  |  ------  |  ------------ 
 company_id   | country  | total revenue

      1         Russia     1200

      2         Croatia    1200
      2         Italy      1200

      3         USA        1200
      3         UK         1200
      3         Italy      1200
  

В этой таблице 3 компании, но офисы компаний «2» и «3» расположены в 2 и 3 странах соответственно. Все компании платят 1200 в месяц, и поскольку у компании 2 есть 2 офиса, это показывает, как будто они платили 1200 в месяц 2 раза, а поскольку у компании 3 есть 3 офиса, это показывает, как будто она платила 1200 в месяц 3 раза. Вместо этого я хотел бы, чтобы доход распределялся равномерно в зависимости от того, сколько раз company_id появляется в таблице. company_id будет отображаться только несколько раз для каждой дополнительной страны, в которой базируется компания.

Предполагая, что каждая компания всегда платит 1200 в месяц, мой желаемый результат:

   ----------  |  ------  |  ------------ 
     company_id   | country  | total revenue

          1         Russia     1200

          2         Croatia    600
          2         Italy      600

          3         USA        400
          3         UK         400
          3         Italy      400
  

Будучи новичком в SQL, я подумал, что это может быть сделано с помощью оператора CASE WHEN, но я научился использовать CASE WHEN только тогда, когда я хочу вывести строку в зависимости от условия. Здесь я пытаюсь присвоить равный вес дохода стране каждой компании, в зависимости от того, во скольких странах базируется компания.

Заранее благодарю вас за помощь!

Ниже приведен стандартный SQL BigQuery

 #standardSQL 
SELECT company_id, country, 
  total_revenue / (COUNT(1) OVER(PARTITION BY company_id)) AS total_revenue
FROM `project.dataset.table`    
  

Если применить к образцу данных из вашего вопроса — вывод

 Row company_id  country total_revenue    
1   1           Russia  1200.0   
2   2           Croatia 600.0    
3   2           Italy   600.0    
4   3           USA     400.0    
5   3           UK      400.0    
6   3           Italy   400.0