Блог

Главная - Вопросы по программированию - Таблица транспонирования GBQ в SQL

Таблица транспонирования GBQ в SQL

# #sql #google-bigquery #pivot #transpose #unpivot

Вопрос:

Пытаюсь перенести таблицу в GBQ. Решение должно быть способно динамически работать с различными столбцами и строками. Я знаю, что есть способ сделать это, специально перечисляя столбцы, но у меня разные таблицы с большим количеством строк. Сейчас в GBQ есть функция unpivot, и моя идея состояла в том, чтобы отключить ее, а затем развернуть. Не уверен, что кто-нибудь уже сталкивался с этим?

Исходные Данные

Результат

введите описание изображения здесь

Ответ №1:

Рассмотрим следующий подход

 select * from (
  select * from data
  unpivot (value for Fruit in (Apple, Bananas, Orange))
)
pivot (max(value) for `Group` in ('North', 'South', 'West'))

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

введите описание изображения здесь

В нижеприведенной версии собраны все ссылки, необходимые для распаковки и динамического поворота на лету

 execute immediate (select '''select * from (
  select * from `project.dataset.table`
  unpivot (value for Fruit in (''' || (select array_to_string(regexp_extract_all(to_json_string((select as struct * except(`Group`) from unnest([t]))), r'"([^"] )":'), ', ')
from `project.dataset.table` t limit 1) || ''')) 
)
pivot (max(value) for `Group` in (''' || (select '"' || string_agg(distinct `Group`, '", "' order by `Group`) || '"' from `project.dataset.table`) || '''))
'''
);

Комментарии:

1. Спасибо за помощь Михаилу. Я понимаю, как это сделать таким образом, я ищу способ сделать это динамически, когда значения столбцов и строк могут меняться со временем. Я попытался создать переменные и сделать unpivot (значение для фруктов в (Выберите имя столбца из INFORMATION_SCHEMA.COLUMNS), но это приводит к ошибке

2. Я вижу, никаких проблем. скоро обновлю ответ :o)

3. Тамк Михаил, я сталкиваюсь с проблемой в своем коде, когда для последующих столбцов Север, Юг и Запад не все числовые типы данных. Некоторые из них являются int. Есть ли способ массового преобразования их в числовые? Кроме кастинга каждого по отдельности?

4. Я полностью ответил на ваш первоначальный вопрос. Пожалуйста, подумайте, по крайней мере, о том, чтобы проголосовать, а затем принять, если вы считаете, что мое утверждение верно и на ваш первоначальный вопрос получен ответ. Тем временем, если у вас есть дополнительные / дополнительные вопросы-пожалуйста, опубликуйте новый вопрос со всеми связанными деталями, и я буду рад помочь вам в дальнейшем :o)

Метки: