Средняя колонка BigQuery по условию

Предположим, у меня есть следующие упрощенные данные в BigQuery:

WITH sales_log AS
 (
  SELECT 'John' as employee, 'ABC' client, 1234.56 sales, "phone" sale_type UNION ALL
  SELECT 'John' as employee, 'ABC' client, 9857.56 sales, "online" sale_type UNION ALL
  SELECT 'John' as employee, 'XYZ' client, 5678.56 sales, "phone" sale_type UNION ALL
  SELECT 'John' as employee, 'XYZ' client, 64875.25 sales, "online" sale_type UNION ALL
  SELECT 'Mary' as employee, 'ABC' client, 456.58 sales, "phone" sale_type UNION ALL
  SELECT 'Mary' as employee, 'ABC' client, 11585.58 sales, "online" sale_type UNION ALL
  SELECT 'Mary' as employee, 'XYZ' client, 4578.52 sales, "phone" sale_type UNION ALL
  SELECT 'Mary' as employee, 'XYZ' client, 56853.45 sales, "online" sale_type
  )
SELECT employee, AVG(sales) AS avg_sales
FROM sales_log
GROUP BY employee

Я могу легко получить среднее значение продаж сотрудников.

Есть ли простой способ также получить среднее значение КАЖДОГО ТИПА продаж в одной строке? Чтобы вывод был таким:

работник	avg_sales	avg_phone_sales	avg_online_sales
Джон	20411.4825	3456,56	37366.405
Мэри	18368.5325	2517,55	34219.515

Заранее спасибо.

sql google-bigquery

18.03.2022 21:56

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Ответ принят как подходящий

Пара подходов:

ДЕМО внизу:
Если типы ограничены, вы можете использовать выражения case для каждого столбца.
Если они «динамические», вам нужен динамический поворот. (Существует несколько онлайн-примеров, но это означает динамический SQL, который может быть подвержен внедрению SQL)
ПРИМЕЧАНИЕ. Обычно такое форматирование данных выполняется в пользовательском интерфейсе, а не в SQL.
ПРИМЕЧАНИЕ. Использование значения Null гарантирует, что «пустые» значения не повлияют на ваше среднее значение.

WITH sales_log AS
 (
  SELECT 'John' as employee, 'ABC' client, 1234.56 sales, 'phone' sale_type UNION ALL
  SELECT 'John' as employee, 'ABC' client, 9857.56 sales, 'online' sale_type UNION ALL
  SELECT 'John' as employee, 'XYZ' client, 5678.56 sales, 'phone' sale_type UNION ALL
  SELECT 'John' as employee, 'XYZ' client, 64875.25 sales, 'online' sale_type UNION ALL
  SELECT 'Mary' as employee, 'ABC' client, 456.58 sales, 'phone' sale_type UNION ALL
  SELECT 'Mary' as employee, 'ABC' client, 11585.58 sales, 'online' sale_type UNION ALL
  SELECT 'Mary' as employee, 'XYZ' client, 4578.52 sales, 'phone' sale_type UNION ALL
  SELECT 'Mary' as employee, 'XYZ' client, 56853.45 sales, 'online' sale_type
  )
SELECT employee, AVG(sales) AS avg_sales, 
       AVG(case when sale_type = 'phone' then sales else NULL end) as AVG_PhoneSales, 
       AVG(case when sale_type = 'online' then sales else NULL end) as AVG_OnLineSales
FROM sales_log
GROUP BY employee

ПРЕДОСТАВЛЯЕМ НАМ:

+----------+--------------------+-----------------------+--------------------+
| employee |     avg_sales      |  AVG_phonesales       | AVG_onlinesales    |
+----------+--------------------+-----------------------+--------------------+
| Mary     | 18368.532500000000 | 2517.5500000000000000 | 34219.515000000000 |
| John     | 20411.482500000000 | 3456.5600000000000000 | 37366.405000000000 |
+----------+--------------------+-----------------------+--------------------+

ДЕМО : Хотя это и не большой запрос Google, в этом случае синтаксис будет таким же. Динамический SQL или динамический свод будут другими.

Пример динамического: Новая функция PIVOT в BigQuery

@Халат. взгляните на другой ответ. Кажется, что это больше похоже на большой запрос Google. Не то чтобы я не ценил положительные отзывы, я просто хочу, чтобы у вас был лучший ответ на вашу проблему и на другие. Это просто распространенный шаблон проектирования, который я использую, когда не все инструменты одинаково поддерживают поворот.

— 18.03.2022 22:44

18.03.2022 22:09

Рассмотрим ниже подход

select * from (
  select employee, sale_type, avg(sales) as avg_sales
  from sales_log
  group by rollup (employee, sale_type)
  having not employee is null
)
pivot (min(avg_sales) avg for ifnull(sale_type || '_sales', 'sales') in ('sales', 'phone_sales', 'online_sales'))

если применить к выборочным данным в нашем вопросе - вывод