WedX - журнал о программировании и компьютерных науках

Получить группы времени простоя, BigQuery

Мне нужно получить группы времени простоя, где speed равно 0 и есть как минимум две такие последовательные записи, то есть:

+---------------------+---------------------+------+
|      min time       |      max time       | idle |
+---------------------+---------------------+------+
| 2017-01-31 23:59:43 | 2017-01-31 23:59:53 |   10 |
| 2017-01-31 23:58:13 | 2017-01-31 23:58:53 |   40 |
| 2017-01-31 23:56:18 | 2017-01-31 23:57:03 |   45 |
+---------------------+---------------------+------+

в идеале с суммарным временем простоя в секундах.

Пример данных:

+-------+-------------------------+
| speed |        datetime         |
+-------+-------------------------+
| 0     | 2017-01-31 23:59:53 UTC |
| 0     | 2017-01-31 23:59:43 UTC |
| 0.05  | 2017-01-31 23:59:33 UTC |
| 0     | 2017-01-31 23:59:23 UTC |
| 0.02  | 2017-01-31 23:59:13 UTC |
| 0.02  | 2017-01-31 23:59:03 UTC |
| 0     | 2017-01-31 23:58:53 UTC |
| 0     | 2017-01-31 23:58:43 UTC |
| 0     | 2017-01-31 23:58:33 UTC |
| 0     | 2017-01-31 23:58:23 UTC |
| 0     | 2017-01-31 23:58:13 UTC |
| 0.02  | 2017-01-31 23:58:03 UTC |
| 0.02  | 2017-01-31 23:57:53 UTC |
| 0     | 2017-01-31 23:57:43 UTC |
| 0.02  | 2017-01-31 23:57:33 UTC |
| 0     | 2017-01-31 23:57:23 UTC |
| 0.02  | 2017-01-31 23:57:22 UTC |
| 0.02  | 2017-01-31 23:57:13 UTC |
| 0     | 2017-01-31 23:57:09 UTC |
| 0.02  | 2017-01-31 23:57:08 UTC |
| 0     | 2017-01-31 23:57:03 UTC |
| 0     | 2017-01-31 23:56:53 UTC |
| 0     | 2017-01-31 23:56:51 UTC |
| 0     | 2017-01-31 23:56:43 UTC |
| 0     | 2017-01-31 23:56:34 UTC |
| 0     | 2017-01-31 23:56:33 UTC |
| 0     | 2017-01-31 23:56:23 UTC |
| 0     | 2017-01-31 23:56:18 UTC |
+-------+-------------------------+

Запрос:

#standardSQL
SELECT
 MIN(datetime), MAX(datetime)
FROM
 (SELECT t.*, 
   (SELECT 
     COUNT(*)
    FROM
     `dbtest.log_*` t2
    WHERE
     t2.datetime <= t.datetime and t2.speed > 0) grp
 FROM
 `dbtest.log_*` t
 WHERE t.speed = 0) x
 GROUP BY grp

Результат:

«LEFT OUTER JOIN не может использоваться без условия, которое является равенством полей с обеих сторон соединения».

  1. Что не так с моим запросом?
  2. Есть ли лучший способ добиться этого в bigquery?
google-bigquery
05.06.2017

  • Не могли бы вы пояснить, что вы подразумеваете под группами времени простоя? Каков ожидаемый результат для этого ввода? 05.06.2017
  • Вопрос @ElliottBrossard обновлен. 05.06.2017

Ответы:


1

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

#standardSQL
SELECT
  MIN(datetime) AS min_time,
  MAX(datetime) AS max_time,
  TIMESTAMP_DIFF(MAX(datetime), MIN(datetime), SECOND) AS idle
FROM (
  SELECT *, SUM(speed) OVER(ORDER BY datetime) AS grp
  FROM `dbtest.YourTable`
)
WHERE speed = 0
GROUP BY grp
HAVING COUNT(1) > 1
-- ORDER BY grp DESC

Вы можете играть / тестировать с фиктивными данными, например, из вашего вопроса

#standardSQL
WITH yourTable AS (
  SELECT 0 AS speed, TIMESTAMP '2017-01-31 23:59:53 UTC' AS datetime UNION ALL
  SELECT 0, TIMESTAMP '2017-01-31 23:59:43 UTC' UNION ALL
  SELECT 0.05, TIMESTAMP '2017-01-31 23:59:33 UTC' UNION ALL
  SELECT 0, TIMESTAMP '2017-01-31 23:59:23 UTC' UNION ALL
  SELECT 0.02, TIMESTAMP '2017-01-31 23:59:13 UTC' UNION ALL
  SELECT 0.02, TIMESTAMP '2017-01-31 23:59:03 UTC' UNION ALL
  SELECT 0, TIMESTAMP '2017-01-31 23:58:53 UTC' UNION ALL
  SELECT 0, TIMESTAMP '2017-01-31 23:58:43 UTC' UNION ALL
  SELECT 0, TIMESTAMP '2017-01-31 23:58:33 UTC' UNION ALL
  SELECT 0, TIMESTAMP '2017-01-31 23:58:23 UTC' UNION ALL
  SELECT 0, TIMESTAMP '2017-01-31 23:58:13 UTC' UNION ALL
  SELECT 0.02, TIMESTAMP '2017-01-31 23:58:03 UTC' UNION ALL
  SELECT 0.02, TIMESTAMP '2017-01-31 23:57:53 UTC' UNION ALL
  SELECT 0, TIMESTAMP '2017-01-31 23:57:43 UTC' UNION ALL
  SELECT 0.02, TIMESTAMP '2017-01-31 23:57:33 UTC' UNION ALL
  SELECT 0, TIMESTAMP '2017-01-31 23:57:23 UTC' UNION ALL
  SELECT 0.02, TIMESTAMP '2017-01-31 23:57:22 UTC' UNION ALL
  SELECT 0.02, TIMESTAMP '2017-01-31 23:57:13 UTC' UNION ALL
  SELECT 0, TIMESTAMP '2017-01-31 23:57:09 UTC' UNION ALL
  SELECT 0.02, TIMESTAMP '2017-01-31 23:57:08 UTC' UNION ALL
  SELECT 0, TIMESTAMP '2017-01-31 23:57:03 UTC' UNION ALL
  SELECT 0, TIMESTAMP '2017-01-31 23:56:53 UTC' UNION ALL
  SELECT 0, TIMESTAMP '2017-01-31 23:56:51 UTC' UNION ALL
  SELECT 0, TIMESTAMP '2017-01-31 23:56:43 UTC' UNION ALL
  SELECT 0, TIMESTAMP '2017-01-31 23:56:34 UTC' UNION ALL
  SELECT 0, TIMESTAMP '2017-01-31 23:56:33 UTC' UNION ALL
  SELECT 0, TIMESTAMP '2017-01-31 23:56:23 UTC' UNION ALL
  SELECT 0, TIMESTAMP '2017-01-31 23:56:18 UTC'
)
SELECT
  MIN(datetime) AS min_time,
  MAX(datetime) AS max_time,
  TIMESTAMP_DIFF(MAX(datetime), MIN(datetime), SECOND) AS idle
FROM (
  SELECT *, SUM(speed) OVER(ORDER BY datetime) AS grp
  FROM yourTable
)
WHERE speed = 0
GROUP BY grp
HAVING COUNT(1) > 1
-- ORDER BY grp DESC
05.06.2017
Новые материалы
Я хотел выучить язык программирования MVC4, но не мог выучить его раньше, потому что это выглядит сложно…
Просто начните и учитесь самостоятельно Я хотел выучить язык программирования MVC4, но не мог выучить его раньше, потому что он кажется мне сложным, и я бросил его. Это в основном инструмент..
Лицензии с открытым исходным кодом: руководство для разработчиков и создателей
В динамичном мире разработки программного обеспечения открытый исходный код стал мощной парадигмой, способствующей сотрудничеству, инновациям и прогрессу, движимому сообществом. В основе..
Объяснение документов 02: BERT
BERT представил двухступенчатую структуру обучения: предварительное обучение и тонкая настройка. Во время предварительного обучения модель обучается на неразмеченных данных с помощью..
Как проанализировать работу вашего классификатора?
Не всегда просто знать, какие показатели использовать С развитием глубокого обучения все больше и больше людей учатся обучать свой первый классификатор. Но как только вы закончите..
Работа с цепями Маркова, часть 4 (Машинное обучение)
Нелинейные цепи Маркова с агрегатором и их приложения (arXiv) Автор : Бар Лайт Аннотация: Изучаются свойства подкласса случайных процессов, называемых дискретными нелинейными цепями Маркова..
Crazy Laravel Livewire упростил мне создание электронной коммерции (панель администратора и API) [Часть 3]
Как вы сегодня, ребята? В этой части мы создадим CRUD для данных о продукте. Думаю, в этой части я не буду слишком много делиться теорией, но чаще буду делиться своим кодом. Потому что..
Использование машинного обучения и Python для классификации 1000 сезонов новичков MLB Hitter
Чему может научиться машина, глядя на сезоны новичков 1000 игроков MLB? Это то, что исследует это приложение. В этом процессе мы будем использовать неконтролируемое обучение, чтобы..
Для любых предложений по сайту: [email protected]