У меня есть таблица SQL следующего формата:
ID Cat
1 A
1 B
1 D
1 F
2 B
2 C
2 D
3 A
3 F
Теперь я хочу создать таблицу с одним идентификатором в строке и несколькими кошками в строке. Мой желаемый результат выглядит следующим образом:
ID A B C D E F
1 1 1 0 1 0 1
2 0 1 1 1 0 0
3 1 0 0 0 0 1
Я нашел:
Преобразование таблицы в горячее кодирование значения одного столбца
Однако у меня более 1000 кошек, поэтому я ищу код для автоматического написания, а не вручную. Кто может помочь мне с этим?
тонны дубликатов для этого вопроса!! ты хоть пытался поискать?
У BigQuery нет динамического столбца со стандартным SQL, но в зависимости от того, что вы хотите сделать на следующем шаге, может быть способ упростить его.
В следующем примере кода Cat группируется по идентификатору и используется функция JavaScript для быстрого кодирования и возврата строки JSON.
CREATE TEMP FUNCTION trans(cats ARRAY<STRING>)
RETURNS STRING
LANGUAGE js
AS
"""
// TODO: Doing one hot encoding for one cat and return as JSON string
return "{a:1}";
"""
;
WITH id_cat AS (
SELECT 1 as ID, 'A' As Cat UNION ALL
SELECT 1 as ID, 'B' As Cat UNION ALL
SELECT 1 as ID, 'C' As Cat UNION ALL
SELECT 2 as ID, 'A' As Cat UNION ALL
SELECT 3 as ID, 'C' As Cat)
SELECT ID, trans(ARRAY_AGG(Cat))
FROM id_cat
GROUP BY ID;
Сначала позвольте мне преобразовать данные, которые вы вставили, в настоящую таблицу:
WITH data AS (
SELECT REGEXP_EXTRACT(data2, '[0-9]') id, REGEXP_EXTRACT(data2, '[A-Z]') cat
FROM (
SELECT SPLIT("""1 A
1 B
1 D
1 F
2 B
2 C
2 D
3 A
3 F""", '\n') AS data1
), UNNEST(data1) data2
)
SELECT * FROM data
(попробуйте поделиться столом в следующий раз)
Теперь мы можем сделать ручное 1-горячее кодирование:
SELECT id
, MAX(IF(cat='A',1,0)) cat_A
, MAX(IF(cat='B',1,0)) cat_B
, MAX(IF(cat='C',1,0)) cat_C
FROM data
GROUP BY id
Теперь мы хотим написать скрипт, который будет автоматически создавать нужные нам столбцы:
SELECT STRING_AGG(FORMAT("MAX(IF(cat='%s',1,0))cat_%s", cat, cat), ', ')
FROM (
SELECT DISTINCT cat
FROM data
ORDER BY 1
)
Это генерирует строку, которую вы можете скопировать и вставить в запрос, которая 1-горячая кодирует ваши массивы/строки:
SELECT id
,
MAX(IF(cat='A',1,0))cat_A, MAX(IF(cat='B',1,0))cat_B, MAX(IF(cat='C',1,0))cat_C, MAX(IF(cat='D',1,0))cat_D, MAX(IF(cat='F',1,0))cat_F
FROM data
GROUP BY id
И это именно то, к чему был задан вопрос. Вы можете сгенерировать SQL с помощью SQL, но вам нужно будет написать новый запрос, используя этот результат.
Хм, на самом деле у меня не работает... Я получаю "Запрос слишком велик. Максимальная стандартная длина SQL-запроса составляет 1024,00 тыс. символов, включая комментарии и пробельные символы".... Думаю, мне придется объединить запросы или что-то в этом роде. . Это становится глупо. Питон это :P
Это может помочь stackoverflow.com/questions/26272514/…