Tenho uma tabela como a seguinte:
| EU IRIA | col1 | col2 | col3 | col4 |
|---|---|---|---|---|
| UMA | 100 | 400 | 30 | 800 |
| B | 600 | 50 | 500 | 75 |
e eu quero uma consulta onde posso retornar algo como
| EU IRIA | col1 | col2 | col3 | col4 |
|---|---|---|---|---|
| UMA | 0 | 1 | 0 | 1 |
| B | 1 | 0 | 1 | 0 |
Exceto, eu quero que a lógica olhe para cada linha e para cada linha encontre quais duas colunas têm os 2 valores principais. Eu imagino que pode haver alguns CTEs ou subconsultas envolvidos. Mesmo chegar a um CTE que produziria o seguinte resultado seria bom o suficiente, mas não sei como chegar a este CTE:
| EU IRIA | top_2_col_name |
|---|---|
| UMA | col2 |
| UMA | col4 |
| B | col1 |
| B | col3 |
Existe uma maneira de fazer funções de agregação e janela em vez de colunas? Estou usando o BigQuery SQL do Google.
Se você quiser os dois valores principais, um método é desaninhar os valores, calcular a classificação e selecioná-los:
with t as (
select 'A' as id, 100 as col1, 400 as col2, 30 as col3, 800 as col4 union all
select 'B' as id, 600 as col1, 50 as col2, 500 as col3, 75 as col4
)
select * except (seqnum)
from (select t.id, col.*, row_number() over (partition by t.id order by col.val desc) as seqnum
from t cross join
unnest(array[struct('col1' as col, t.col1 as val),
struct('col2', t.col2),
struct('col3', t.col3),
struct('col4', t.col4)
]
) col
) tc
where seqnum <= 2;
Esta é a segunda forma do seu conjunto de resultados.
Você pode generalizar isso para qualquer número de colunas usando um truque JSON. Isso produz uma string e, em seguida, analisa a string para as colunas de seu interesse, desaninha-as e realiza operações semelhantes:
with t as (
select 'A' as id, 100 as col1, 400 as col2, 30 as col3, 800 as col4 union all
select 'B' as id, 600 as col1, 50 as col2, 500 as col3, 75 as col4
)
select t.id, concat('col', n), val
from (select t.id, val, n, row_number() over (partition by t.id order by val desc) as seqnum
from t cross join
unnest(regexp_extract_all(to_json_string(t), '"col[0-9]+":([0-9]+)')) val with offset n
) t
where seqnum <= 2;
Isso pode funcionar em qualquer número de colunas. Obviamente, se você tiver uma estrutura de dados como essa, os valores devem ser armazenados em um array.
Este artigo é coletado da Internet.
Se houver alguma infração, entre em [email protected] Delete.
deixe-me dizer algumas palavras