Блог

Есть ли какой-нибудь простой метод для объединения значений в пакеты (с сохранением порядка)

из этого

 key | value
---- ------
a   |  1
a   |  2
a   |  3
a   |  4
a   |  5
a   |  6

b   |  9
b   |  8
b   |  7
b   |  6
b   |  5
b   |  4
  

к этому

 key | batches
---- -------------------------------------------------------------
a   | [{'values': [1, 2]}, {'values': [3, 4]}, {'values': [5, 6]}]
b   | [{'values': [9, 8]}, {'values': [7, 6]}, {'values': [5, 4]}]
  

{'values': [1, 2]} — СТРУКТУРА
[{'values': [1, 2]}, {'values': [3, 4]}, {'values': [5, 6]}] — ПОВТОРЯЮЩИЕСЯ СТРУКТУРЫ

Для BigQuery стандартный SQL

Есть ли какой-нибудь простой метод для объединения значений в пакеты (с сохранением порядка)

Примечание; чтобы «сохранить порядок» — вам нужно иметь какое-то указание на этот порядок в ваших данных — я предполагаю, что у вас есть что-то, что определяет этот порядок (я использую ts поле для этого) — обычно это временная метка или тип данных order / position и т.д.

Итак, ниже приведена отправная точка для достижения точно указанной цели

 #standardSQL
SELECT key, 
  ARRAY_AGG(value ORDER BY pos) AS batch,
  DIV(pos - 1, 2) batch_num
FROM (
  SELECT *, 
    ROW_NUMBER() OVER(PARTITION BY key ORDER BY ts) pos,
    DIV(ROW_NUMBER() OVER(PARTITION BY key ORDER BY ts) - 1, 2) batch
  FROM `project.dataset.table`
)
GROUP BY key, batch_num
   
  

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

Отсюда вы можете упаковать приведенный выше результат в любой конечный формат, который вам нужен, например

 #standardSQL
SELECT key, ARRAY_AGG(batch ORDER BY batch_num) batches
FROM (
  SELECT key, 
    STRUCT(ARRAY_AGG(value ORDER BY pos) AS values) batch,
    DIV(pos - 1, 2) batch_num
  FROM (
    SELECT *, 
      ROW_NUMBER() OVER(PARTITION BY key ORDER BY ts) pos,
      DIV(ROW_NUMBER() OVER(PARTITION BY key ORDER BY ts) - 1, 2) batch
    FROM `project.dataset.table`
  )
  GROUP BY key, batch_num
)
GROUP BY key
  

которые дают вывод ниже (довольно близкий к ожидаемому примеру в вашем вопросе)

 [
  {
    "key": "a",
    "batches": [
      {
        "values": [
          "1",
          "2"
        ]
      },
      {
        "values": [
          "3",
          "4"
        ]
      },
      {
        "values": [
          "5",
          "6"
        ]
      }
    ]
  },
  {
    "key": "b",
    "batches": [
      {
        "values": [
          "9",
          "8"
        ]
      },
      {
        "values": [
          "7",
          "6"
        ]
      },
      {
        "values": [
          "5",
          "4"
        ]
      }
    ]
  }
]