Eu tenho uma tabela Timescale DB / PostgreSQL (DataTable) que se parece com isso
Estou tentando encontrar uma consulta que retorne colunas diferentes para diferentes combinações de subsistema/métricas como esta
Além disso, gostaria de reduzir a quantidade de pontos de dados solicitados usando a função Timescale DB time_bucket_gapfill() para manter o tempo de resposta baixo para grandes intervalos de tempo. Eu também gostaria de usar diferentes funções de agregação para diferentes combinações de subsistema/métrica. Eu uso os dados consultados para plotar as curvas no Grafana. Qual é a maneira mais eficiente de conseguir isso?
No momento eu uso consultas separadas para cada métrica que rapidamente fica muito lenta. Para o exemplo, as consultas podem ser assim
SELECT
time_bucket_gapfill('30s',time,start=>'2021-07-19T09:06:26.605Z',finish=>'2021-07-19T20:11:12.340Z') AS "time",
max(value) AS "Latitude"
FROM DataTable
WHERE
time BETWEEN '2021-07-19T09:06:26.605Z' AND '2021-07-19T20:11:12.340Z' AND
subsystem = 'position' AND
metric = 'lat'
GROUP BY 1,metric,subsystem
ORDER BY time
SELECT
time_bucket_gapfill('30s',time,start=>'2021-07-19T09:06:26.605Z',finish=>'2021-07-19T20:11:12.340Z') AS "time",
min(value) AS "Longitude"
FROM DataTable
WHERE
time BETWEEN '2021-07-19T09:06:26.605Z' AND '2021-07-19T20:11:12.340Z' AND
subsystem = 'position' AND
metric = 'lon'
GROUP BY 1,metric,subsystem
ORDER BY time
SELECT
time_bucket_gapfill('30s',time,start=>'2021-07-19T09:06:26.605Z',finish=>'2021-07-19T20:11:12.340Z') AS "time",
avg(value) AS "Temperature"
FROM DataTable
WHERE
time BETWEEN '2021-07-19T09:06:26.605Z' AND '2021-07-19T20:11:12.340Z' AND
subsystem = 'health' AND
metric = 'temperature'
GROUP BY 1,metric,subsystem
ORDER BY time
Eu agradeceria muito se alguém pudesse me apontar na direção certa.
Atualizar:
Usar a cláusula FILTER como sugerido por @Charlieface não funciona para mim, porque não remove as outras linhas, mas coloca NULLs lá. Aqui um exemplo com alguns dados reais (o exemplo anterior estava usando dados e nomes simplificados).
Também notei que os timestamps das métricas que pertencem juntas têm pequenas diferenças que impediriam que métricas diferentes estivessem na mesma linha, mas isso é fácil de alterar na unidade que alimenta os dados no banco de dados.
Atualização 2:
O problema de timestamps foi corrigido e a instrução WHERE foi adicionada novamente como sugerido por @Charlieface, mas ainda há NULL nos dados, o que faz o Grafana plotar os pontos de dados sem linhas entre eles.
Gostaria que o resultado ficasse assim.
Atualização 3:
Eu perdi essa resposta do @Charlieface apenas grupos por tempo. Se eu fizer isso, obterei o resultado que eu queria.
