Criei umas queries.
Uma que criei
const latitudesLongitudes = await Model.findAll({
attributes: [
[Sequelize.fn("COUNT", Sequelize.col("*")), "contador"],
[
Sequelize.fn(
"round",
Sequelize.cast(Sequelize.col("latitude"), "numeric"),
2
),
"latitude",
],
[
Sequelize.fn(
"round",
Sequelize.cast(Sequelize.col("longitude"), "numeric"),
2
),
"longitude",
],
],
where,
group: [
Sequelize.fn(
"round",
Sequelize.cast(Sequelize.col("latitude"), "numeric"),
2
),
Sequelize.fn(
"round",
Sequelize.cast(Sequelize.col("longitude"), "numeric"),
2
),
],
order: [Sequelize.literal(" COUNT('*') DESC ")],
limit: 5,
});
Outra
const grafico = await Model.findAndCountAll({
attributes: [
Sequelize.fn("COUNT", Sequelize.col("*")),
Sequelize.fn("to_char", Sequelize.col("data_hora"), "DD/MM/YYYY"),
],
where,
group: [
Sequelize.fn("to_char", Sequelize.col("data_hora"), "DD/MM/YYYY"),
],
order: [
[
Sequelize.fn(
"to_char",
Sequelize.col("data_hora"),
"DD/MM/YYYY"
),
"ASC",
],
],
});
Mas esta estou confuso
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;
Alguém pode me dar uma dica ?