Funções da janela: PARTITION BY uma coluna após ORDER BY another
Aviso Legal: O problema mostrado é muito mais geral do que eu esperava primeiro. O exemplo abaixo é retirado de uma solução para outra pergunta. Mas agora eu estava usando esta amostra para resolver muitos problemas mais - principalmente relacionados a séries temporais (veja a seção "Vinculado" na barra direita).
Então, eu estou tentando explicar o problema mais geralmente primeiro:
Estou usando o PostgreSQL, mas tenho certeza de que esse problema existe em outra função da janela que suporta DBMS '(MS SQL Server, Oracle, ...) também.
Funções da janela pode ser usado para agrupar determinados valores por um atributo ou valor comum. Por exemplo, você pode agrupar linhas por uma data. Então, você poderá calcular o valor máximo em cada data ou um valor médio ou linhas de contagem ou qualquer outra coisa.
Isso pode ser alcançado através da definição de umPARTITION. O agrupamento por datas funcionaria comPARTITION BY date_column. Agora você deseja executar uma operação que precisa de uma ordem especial dentro de seus grupos (calculando números de linhas ou resumindo uma coluna). Isso pode ser feito comPARTITON BY date_column ORDER BY an_attribute_column.
Agora pense em uma resolução mais precisa das séries temporais. E se você não tiver datas, mas timestamps. Então você não pode mais agrupar pela coluna do tempo. No entanto, pode ser importante analisar os dados na ordem em que foram adicionados (talvez o carimbo de data e hora seja o tempo de criação do seu conjunto de dados). Então você percebe que algumas linhas consecutivas têm o mesmo valor e deseja agrupar seus dados por esse valor comum. Mas a pista é que as linhas têm timestamps diferentes.
O problema aqui é que você não pode fazer umaPARTITION BY value_column. PorquePARTITION BY força uma ordem primeiro. Portanto, sua mesa seria solicitada pelovalue_column antes do agrupamento e não é mais solicitado pelo carimbo de data e hora. Isso produz resultados que você não está esperando.
De um modo mais geral:O problema é garantir uma ordem especial, mesmo que a coluna ordenada não faça parte da partição criada.
Exemplo:
Eu tenho a seguinte tabela:
ts val
100000 50
130100 30050
160100 60050
190200 100
220200 30100
250200 30100
300000 300
500000 100
550000 1000
600000 1000
650000 2000
700000 2000
720000 2000
750000 300
Eu tive o problema de agrupar todos os valores vinculados da colunaval. Mas eu queria manter a ordemts. Para conseguir isso, eu queria adicionar uma coluna com um ID exclusivo porval grupo
Resultado esperado:
ts val group
100000 50 1
130100 30050 2
160100 60050 3
190200 100 4
220200 30100 5 \ same group
250200 30100 5 /
300000 300 6
500000 100 7
550000 1000 8 \ same group
600000 1000 8 /
650000 2000 9 \
700000 2000 9 | same group
720000 2000 9 /
750000 300 10
Primeira tentativa foi o uso dorank função de janela que faria esse trabalho normalmente:
SELECT
*,
rank() OVER (PARTITION BY val ORDER BY ts)
FROM
test
Mas, neste caso, isso não funciona porque oPARTITION BY A cláusula ordena a tabela primeiro por suas colunas de partição (val neste caso) e, em seguida, pela suaORDER BY colunas. Então a ordem é porval, ts em vez da ordem esperada ports. Portanto, o resultado não foi o esperado, é claro.
ts val rank
100000 50 1
190200 100 1
500000 100 2
300000 300 1
750000 300 2
550000 1000 1
600000 1000 2
650000 2000 1
700000 2000 2
720000 2000 3
130100 30050 1
220200 30100 1
250200 30100 2
160100 60050 1
A questão é:Como obter os IDs de grupo com relação ao pedido,ts?
Editar: Adicionei uma solução própria abaixo, mas me sinto muito desconfortável com ela. Parece muito complicado.Eu queria saber se existe uma maneira melhor para alcançar esse resultado.