BigQuery и стандартный SQL: агрегат на поле в массиве

Question

BigQuery и стандартный SQL: агрегат на поле в массиве

Я пытаюсь рассчитать сумму трафика (в Гбит / с) по IP-адресу района для суммы всех пятиминутных периодов.

Я новичок в BigQuery, поэтому, посмотрев на другие примеры, я попробовал следующее:

WITH `project.dataset.test` AS (
  SELECT '01/01/2019 12:30' time, '192.168.10.1' ip_address, 10 network, 1 gbps UNION ALL
  SELECT '01/01/2019 12:30', '192.168.10.2', 11, 2 UNION ALL
  SELECT '01/01/2019 12:30', '192.168.10.3', 12, 3 UNION ALL
  SELECT '01/01/2019 12:35', '192.168.10.1', 10, 1 UNION ALL
  SELECT '01/01/2019 12:35', '192.168.10.2', 11, 2 UNION ALL
  SELECT '01/01/2019 12:35', '192.168.10.3', 12, 3 UNION ALL
  SELECT '01/01/2019 12:40', '192.168.10.1', 10, 1 UNION ALL
  SELECT '01/01/2019 12:40', '192.168.10.2', 11, 2 UNION ALL
  SELECT '01/01/2019 12:40', '192.168.10.3', 12, 3
  ),
ip AS (
  SELECT DISTINCT (ip_address) ip_address
  FROM `project.dataset.test`
),
qualified AS (
SELECT ip_address, network, ARRAY_AGG (gbps ORDER BY ip_address DESC LIMIT 1)[SAFE_OFFSET(0)] gbps
FROM `project.dataset.test`
GROUP BY ip_address, network
)
SELECT ip_address, network, SUM(gbps)gbps
FROM (
SELECT d.ip_address ip_address, network, ARRAY_AGG (gbps ORDER BY q.ip_address DESC LIMIT 1)[SAFE_OFFSET(0)] gbps
FROM ip d
JOIN qualified q
ON q.ip_address = d.ip_address
GROUP BY ip_address, network
)
group BY ip_address, network
ORDER BY gbps DESC

Я ожидал, что результат будет:

Row     ip_address      network   gbps  
1       192.168.10.3    12        9
2       192.168.10.2    11        6
3       192.168.10.1    10        3

Вместо этого фактический результат:

Row     ip_address         network   gbps   
1       192.168.10.3       12        3
2       192.168.10.2       11        2
3       192.168.10.1       10        1

Что я делаю неправильно? Как выбрать сумму отдельных IP-адресов, независимо от количества 5-минутных периодов и / или сетей? Кстати, у меня есть тысячи строк для сортировки, это всего лишь пример, с которым я работаю.

1

google-bigquery standard-sql

Источник

user9977920 18 фев '19 в 22:21

1 ответ

Решение

Другие вопросы по тегам google-bigquery standard-sql

user5221944 18 фев '19 в 22:32 2019-02-18 22:32 · Accepted Answer · 2019-02-18 22:32

Как выбрать сумму отдельных IP-адресов, независимо от количества 5-минутных периодов и / или сетей?

Ниже приведен пример для BigQuery Standatd SQL

#standardSQL
WITH `project.dataset.test` AS (
  SELECT '01/01/2019 12:30' time, '192.168.10.1' ip_address, 10 network, 1 gbps UNION ALL
  SELECT '01/01/2019 12:30', '192.168.10.2', 11, 2 UNION ALL
  SELECT '01/01/2019 12:30', '192.168.10.3', 12, 3 UNION ALL
  SELECT '01/01/2019 12:35', '192.168.10.1', 10, 1 UNION ALL
  SELECT '01/01/2019 12:35', '192.168.10.2', 11, 2 UNION ALL
  SELECT '01/01/2019 12:35', '192.168.10.3', 12, 3 UNION ALL
  SELECT '01/01/2019 12:40', '192.168.10.1', 10, 1 UNION ALL
  SELECT '01/01/2019 12:40', '192.168.10.2', 11, 2 UNION ALL
  SELECT '01/01/2019 12:40', '192.168.10.3', 12, 3
)
SELECT ip_address, network, SUM(gbps) gbps
FROM `project.dataset.test`
GROUP BY ip_address, network

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

Row ip_address      network gbps     
1   192.168.10.3    12      9    
2   192.168.10.2    11      6    
3   192.168.10.1    10      3