Представление категориального столбца как One-Hot Encoding с использованием SQL

Вы не найдете способа сделать SQL таким динамическим.

Лично у меня есть собственная библиотека шаблонов, которая отображает SQL-код, и я использую Jinja для написания своих шаблонов.

rasgoql делает то же самое, и вот джинджа для горячего кодирования:

SELECT *,
{% for val in distinct_col_vals %}
    {%- if val is not none %}
    CASE WHEN {{ column }} = {{ "'" ~ val ~ "'"}} THEN 1 ELSE 0 END as {{ column ~ '_' ~ val }}{{ ', ' if not loop.last else '' }}
    {%- else %}
    CASE WHEN {{ column }} IS NULL THEN 1 ELSE 0 END as {{ column }}_IS_NULL{{ ', ' if not loop.last else '' }}
    {%- endif -%}
{% endfor %}
FROM {{ source_table }}

Очевидно, вам нужно передать отдельные значения в качестве аргумента, что требует, чтобы вы либо сделали это в Python, либо создали себе функцию run_query(), которая делает это автоматически, что я и делаю.

Без особых усилий вы можете получить шаблон, который автоматически извлекает значения:

{%- set run_query_error_message -%}
This transform depends on dynamic values to work, but no Data Warehouse connection is available. 
Instead, please use the `list_of_vals` argument to provide these values explicitly
{%- endset -%}

{%- if list_of_vals is not defined -%}
    {%- set results = run_query("SELECT DISTINCT " +  column + " FROM " + source_table) -%}
    {%- if results is none -%}
        {{ raise_exception(run_query_error_message) }}
    {%- endif -%}
    {%- set distinct_col_vals = results[column].to_list() -%}
{%- else -%}
    {%- set distinct_col_vals = list_of_vals -%}
{%- endif -%}

SELECT *,
{% for val in distinct_col_vals %}
    {%- if val is not none %}
    CASE WHEN {{ column }} = {{ "'" ~ val ~ "'"}} THEN 1 ELSE 0 END as {{ column ~ '_' ~ val }}{{ ', ' if not loop.last else '' }}
    {%- else %}
    CASE WHEN {{ column }} IS NULL THEN 1 ELSE 0 END as {{ column }}_IS_NULL{{ ', ' if not loop.last else '' }}
    {%- endif -%}
{% endfor %}
FROM {{ source_table }}

Вы можете подумать, что это перебор. И да, это так. Но я много пишу динамический SQL, и, по моему мнению, оно того стоит.

Но краткий ответ на ваш вопрос: нет, SQL требует, чтобы вы вводили столбцы, и вы не сделаете сам движок динамическим. Так что либо смиритесь с этим, изучите приемы динамического написания SQL для вас с использованием Excel или Python, либо переборщите и создайте библиотеку шаблонов, такую ​​​​как rasgoql.

Этого можно добиться с помощью SQL и динамического PIVOT:

SELECT *
FROM (SELECT id, VALUE, IFF(value='',0, 1) AS VALUE2 FROM t)
PIVOT (MAX(VALUE2) FOR VALUE IN (SELECT VALUE FROM t WHERE VALUE != '') 
       DEFAULT ON NULL (0));

Для тестовых данных:

CREATE OR REPLACE TABLE t(id TEXT, VALUE TEXT) AS
SELECT 'A', 'G802' UNION ALL 
SELECT 'A', 'R620' UNION ALL 
SELECT 'A', '' UNION ALL 
SELECT 'B', 'J209'UNION ALL  
SELECT 'B', 'B009'UNION ALL  
SELECT 'C', '' UNION ALL 
SELECT 'C', 'R509';

Выход:

Как это работает:

• IFF(value='',0, 1) AS VALUE2 вспомогательный столбец для категоризации данных 0 или 1
• PIVOT MAX(VALUE2) — результат сводной таблицы на основе вспомогательного столбца
• VALUE IN (SELECT VALUE FROM t WHERE VALUE != '') - динамический поворот, пропуск ''
• если не заморачиваться с пустой колонкой '', то просто VALUE IN (ANY)

Используя VALUE IN (ANY) и EXCLUDE:

SELECT * EXCLUDE "''"
FROM (SELECT id, VALUE, IFF(value='',0, 1) AS VALUE2 FROM t)
PIVOT (MAX(VALUE2) FOR VALUE IN (ANY) DEFAULT ON NULL (0));