Duracao media de estadias

-- retorna a duração média de todas as estadias registradas
DELIMITER $$
CREATE FUNCTION CalculaDuracaoMediaEstadias()
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE duracaoMedia INT;

-- Calcula a média arredondada da diferença entre data_fim e data_inicio
SELECT ROUND(AVG(DATEDIFF(data_fim, data_inicio)))
INTO duracaoMedia
FROM alugueis;

RETURN duracaoMedia;

END$$

DELIMITER ;
-- Como Usar
SELECT CalculaDuracaoMediaEstadias() AS DuracaoMediaDias;

-- Duração media por tipo de hospedagem
DELIMITER $$

CREATE FUNCTION CalculaDuracaoMediaPorTipoHospedagem(p_tipo VARCHAR(255))
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE duracaoMedia INT;

-- Calcula a duração média arredondada das estadias para o tipo de hospedagem informado
SELECT ROUND(AVG(DATEDIFF(a.data_fim, a.data_inicio)))
INTO duracaoMedia
FROM alugueis a
JOIN hospedagens h ON a.hospedagem_id = h.hospedagem_id
WHERE h.tipo = p_tipo;

RETURN duracaoMedia;

END$$

DELIMITER ;

-- Como usar
SELECT CalculaDuracaoMediaPorTipoHospedagem('Casa') AS DuracaoMediaCasa;
SELECT CalculaDuracaoMediaPorTipoHospedagem('Apartamento') AS DuracaoMediaApartamento;
SELECT CalculaDuracaoMediaPorTipoHospedagem('hotel') AS DuracaoMediaApartamento;

-- Lista de todas as categorias de hospedagem com suas respectivas medias
DELIMITER $$

CREATE FUNCTION CalculaDuracaoMediaTodasCategorias()
RETURNS JSON
DETERMINISTIC
BEGIN
DECLARE resultado JSON;

-- Calcula a duração média por tipo e converte para JSON
SET resultado = (
    SELECT JSON_ARRAYAGG(
        JSON_OBJECT(
            'TipoHospedagem', h.tipo,
            'DuracaoMediaDias', ROUND(AVG(DATEDIFF(a.data_fim, a.data_inicio)))
        )
    )
    FROM alugueis a
    JOIN hospedagens h ON a.hospedagem_id = h.hospedagem_id
    GROUP BY h.tipo
);

RETURN resultado;

END$$

-- Como usar
SELECT CalculaDuracaoMediaTodasCategorias() AS DuracaoMediaPorTipo;

DELIMITER ;

A Função CalculaDuracaoMediaTodasCategorias() esta retornando erro, porque pelo que pesquisei, o MySQL não permite usar funções de agregação (AVG, SUM, etc.) diretamente dentro de uma função para retornar múltiplos resultados ou JSON com GROUP BY). so permite valores escalaveis simples, e não tabelas, e nem múltiplas linhas com agregação complexas, poderiamos se for nescesario usar uma procedure. como abaixo.

DELIMITER $$

CREATE PROCEDURE sp_DuracaoMediaPorTipo()
BEGIN
SELECT
h.tipo AS TipoHospedagem,
ROUND(AVG(DATEDIFF(a.data_fim, a.data_inicio))) AS DuracaoMediaDias
FROM alugueis a
JOIN hospedagens h ON a.hospedagem_id = h.hospedagem_id
GROUP BY h.tipo
ORDER BY DuracaoMediaDias DESC;
END$$

DELIMITER ;

-- Como Usar
CALL sp_DuracaoMediaPorTipo();

Funciona perfeitamente