Преобразование таблицы в горячее кодирование для многих строк

У меня есть таблица 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 кошек, поэтому я ищу код для автоматического написания, а не вручную. Кто может помочь мне с этим?

Это может помочь stackoverflow.com/questions/26272514/…

Nick 09.05.2019 14:11

тонны дубликатов для этого вопроса!! ты хоть пытался поискать?

Mikhail Berlyant 10.05.2019 01:24
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
4
2
2 889
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

У 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

Charles 10.12.2020 14:49

Другие вопросы по теме