Media de uma consulta em postgres? - resolvido

Programação
#1

Tenho esta consulta

SELECT (MAX("data_hora") - MIN("data_hora")) AS "media" , "id_player"
FROM "zanzar_midia" AS "ZanzarMidia" 
WHERE ("nome_campanha" LIKE 'MASP15Ba_00000.png' 
AND "data_hora" >= '2022-04-01 00:00:00' AND "data_hora" <= '2022-04-03 23:59:59' AND "longitude" != 0 AND "latitude" != 0)
group by "id_player";

Que retorna estas informações
image

Como pegar a media total de horas por id_player?

Tenho que colocar em node

#2

Aplicar um AVG das horas com um GROUP BY pelo id do player não funciona?

1 curtida
#3

Tentei assim

SELECT 
	avg((MAX("data_hora") - MIN("data_hora"))),
	"id_player"
	FROM "zanzar_midia" AS "ZanzarMidia" 
	WHERE 
		("nome_campanha" LIKE 'MASP15Ba_00000.png' 
		AND "data_hora" >= '2022-04-01 00:00:00' 
		AND "data_hora" <= '2022-04-03 23:59:59' 
		AND "longitude" != 0 
		AND "latitude" != 0)
	group by "id_player"
	order by "id_player" asc;

Mas deu erro

image
image924×512 18.5 KB

#4

Consegui assim

select avg(retorno."maxMin") media
from (
	SELECT 
		MAX("data_hora") - MIN("data_hora") "maxMin",
		"id_player"
		FROM "zanzar_midia" AS "ZanzarMidia" 
		WHERE 
			("nome_campanha" LIKE 'MASP15Ba_00000.png' 
			AND "data_hora" >= '2022-04-01 00:00:00' 
			AND "data_hora" <= '2022-04-03 23:59:59' 
			AND "longitude" != 0 
			AND "latitude" != 0)
		group by "id_player"
		order by "id_player" asc
) retorno;
1 curtida