Autor: Juan Pablo Fuentes

-- Funciones matemáticas
-- Mysql como no podía ser menos incorpora funciones para realizar calculos matemáticos
-- Tenemos los sospechosos habituales: logaritmos, trigonométricas...

-- Valor absoluto: Se usa porque si tenga restas (de fechas, de valores...)

SELECT ABS(-5) AS valor_absoluto;

-- Redondear

SELECT ROUND(3.14159) AS redondeo_entero, ROUND(3.14159, 2) AS redondeo_dos_decimales,
sqrt(2) raiz, round(sqrt(2),3) raiz_redondeada;

-- Truncate nos quita los decimales SIN redondear

SELECT TRUNCATE(1.223,1) a, TRUNCATE(1.263,1) b;

-- round vs truncate

select round(1.2), truncate(1.2,0),round(1.5), truncate(1.5,0),round(1.8), truncate(1.8,0);

-- Trigonométricas y logaritmos...

SELECT pi(),SIN(PI()/2) AS seno_90_grados, COS(0) AS coseno_0_radianes, TAN(PI()/4) AS tangente_45_grados;
SELECT LOG(10) AS logaritmo_10;

-- Raices y exponente

SELECT POW(2,4), sqrt(2), POW(65536,1/4);

-- Números aleatorios
-- el primero es un número aleatorio entre 0 y 1 (no incluído)
-- El segundo es la fórmula para obtener números entre 1 y 6
-- El tercero es el mismo pero si le pongo un valor se utiliza como semilla
-- Y siempre nos devolverá lo mismo
select rand(), FLOOR(1 + (RAND() * 6)), FLOOR(1 + (RAND(15) * 6));

-- A veces se usa random para esto:

select * from actor
order by rand()

-- Funciones de fecha
-- Podemos seleccionar el momento actual, la fecha actual y la hora actual

select now() ahora, curdate() fecha,curtime() hora;

-- Una vez tenemos una fecha podemos obtener todo tipo de información de la misma

select year(now()),month(now()),day(now()),hour(now()),minute(now()),second(now());

select dayofyear(now()) dia_anyo,dayofweek(now()) dia_semana,weekday(now()) dia_semana0,dayname(now()) nombre,last_day(now()) dia_ultimo,microsecond(now()) microsegundos,week(now()) semana;

-- Que alquileres se han hecho en martes

select title, rental_date from film join inventory using(film_id) join rental using(inventory_id)
where weekday(rental_date)=1;

select dayname(rental_date) dia, count(rental_date) from film join inventory using(film_id) join rental using(inventory_id)
group by dia;

-- Con extract e intervalos

select extract(microsecond from now()) m,extract(second from now()) s,
extract(minute from now()) mi,extract(hour from now()) h,
extract(day from now()) d,extract(week from now()) w,
extract(month from now()) mo,extract(quarter from now()) q,
extract(year from now()) y,extract(HOUR_MICROSECOND from now()) sm;

-- Formatear fechas

SELECT DATE_FORMAT(NOW(), '%d/%m/%Y %H:%i:%s') AS fecha_formateada;

-- Sumar o restar días

SELECT ADDDATE(NOW(), 7) AS fecha_en_7_dias, SUBDATE(NOW(), 3) AS fecha_hace_3_dias;

-- Sumar o restar cualquier intervalo

SELECT DATE_ADD(now(),INTERVAL 1 DAY);
SELECT DATE_ADD('2018-05-01',INTERVAL -1 YEAR);

SELECT DATE_SUB('2018-05-01',INTERVAL -1 DAY);
SELECT DATE_SUB('2018-05-01',INTERVAL 1 YEAR);

-- pedidos para la próxima semana
-- where dia_pedido between now() and adddate(now(),7)

-- Restar fechas

-- En días
SELECT DATEDIFF(NOW(), '2024-01-01') ;

-- Con un intervalo determinado

SELECT TIMESTAMPDIFF(MINUTE,now(),'2024-01-01 12:05:55');

-- Si el case os permite evaluar varias opciones
-- El if solamente una condición y valor si cierto, valor si falso
-- Se parece mucho al operador ternario
-- if(condicion,valor_si_cierto,valor_si_falso)

select title, if(length>120,'Tostón','Entretenida') tipo
from film;

select first_name,last_name, 
if(right(first_name,1)=right(last_name,1),'SI','NO') final
from actor;

-- Tenemos dos funciones que nos permiten detectar nulos
-- Porque al concatenar con nulos se 'contagian'

select address,address2,postal_code,concat(address,address2,postal_code) dir
from address;

select 1+null+99;

-- Para solucionar esto tenemos
-- ifnull(valor, valor si nulo)

select address,address2,ifnull(postal_code,'NO POSTAL CODE') postal_code,concat(address,address2,ifnull(postal_code,'')) dir
from address;

select 1+ifnull(null,0)+99;

-- Podemos utilizar COALESCE
-- Es más genérico, le paso una lista de valores y me devuelve el primero que no es nulo

select address,address2,coalesce(postal_code,'NO POSTAL CODE') postal_code,concat(address,address2,coalesce(postal_code,'')) dir
from address;

select 1+coalesce(null,null,null,0)+99;

https://dev.mysql.com/doc/refman/8.0/en/flow-control-functions.html

https://www.w3schools.com/sql/func_mysql_coalesce.asp

-- Crear una consulta que me 
-- muestre las iniciales de los clientes en formato 'A.D.'

select concat_ws('. ',substring(first_name,1,1),substring(last_name,1,1),'') nombre
from customer;

select concat(substring(first_name,1,1),'. ',substring(last_name,1,1),'.') nombre
from customer;

select concat(left(first_name,1),'. ',left(last_name,1),'.') nombre
from customer;

-- Crear una consulta que me muestre los paises que tengan una ciudad que empiece por la misma letra que el país.

select distinct country from country
join city using(country_id)
where substring(country,1,1)=substring(city,1,1);

-- Crear una consulta que me muestre todos los nombres de actores que
-- contengan la letra z pero sin la letra z es decir si tengo un actor gomez me mostrará gome

select replace(first_name,'Z','') nombre, replace(last_name,'Z','') apellido from actor
where first_name like '%z%' or last_name like '%z%';

select replace(upper(first_name),'Z','') nombre, replace(upper(last_name),'Z','') apellido from actor
where first_name like '%z%' or last_name like '%z%';

-- Crear una consulta que me muestre el título de la película y 'barata' si su replacement_const es menor de 15,
-- 'cara' si es mayor de 25 y 'media' en cualquier otro caso.

select title,
 case
	when replacement_cost<15 then 'barata'
	when replacement_cost>25 then 'cara'
	else 'media'
	end tipo
 from film;
 
 -- Crear una consulta que me muestre el total de alquileres de las películas
 -- y me lo clasifique en 3 tramos 'alto', 'medio' y 'bajo' dependiendo de la
 -- cantidad de veces que se ha alquilado. Pensad bien que valores vamos a usar como límites
 
 select title, case
 when count(rental_id) <15 then 'bajo'
 when count(rental_id) <25 then 'medio'
 else 'alto'
 end tipo
 from film join inventory using(film_id)
 join rental using(inventory_id)
 group by film_id;
 
 -- Que pasa si queremos que sea dinámico, es decir, que si tengo más alquileres
 -- el rango cambie
 
 SELECT 
    COUNT(ren.`rental_id`) AS total_rental, 
    fil.`title`,
    CASE 
		WHEN COUNT(ren.`rental_id`) > (max_rental / 3) * 2 THEN 'Alto'
		WHEN COUNT(ren.`rental_id`) > (max_rental / 3) THEN 'Medio'
        ELSE 'Bajo'
    END AS type_rental,
    max_rental    
FROM `rental` ren
JOIN `inventory` inv ON ren.`inventory_id` = inv.`inventory_id`
JOIN `film` fil ON inv.`film_id` = fil.`film_id`
JOIN (
    SELECT 
        MAX(count_total) AS max_rental
    FROM (
        SELECT COUNT(ren.`rental_id`) AS count_total
        FROM `rental` ren
        JOIN `inventory` inv ON ren.`inventory_id` = inv.`inventory_id`
        JOIN `film` fil ON inv.`film_id` = fil.`film_id`
        GROUP BY inv.`film_id`
    ) AS total_rental_tmp
) AS max_rental_tmp
GROUP BY inv.`film_id`
ORDER BY total_rental DESC;

-- Otra manera de hacerlo

create view alquileres_por_pelicula as
 SELECT fil.film_id,title, COUNT(ren.`rental_id`) AS total
        FROM `rental` ren
        JOIN `inventory` inv ON ren.`inventory_id` = inv.`inventory_id`
        JOIN `film` fil ON inv.`film_id` = fil.`film_id`
        GROUP BY fil.`film_id`;
        
-- con esta vista creada la cosa se quedda así:
select title, CASE 
		WHEN total > ((select max(total) from alquileres_por_pelicula) / 3) * 2 THEN 'Alto'
		WHEN total > ((select max(total) from alquileres_por_pelicula) / 3) THEN 'Medio'
        ELSE 'Bajo'
    END AS type_rental
    from alquileres_por_pelicula;

-- Funciones de control de flujo
-- Tienen un comportamiento similar al if de programación
-- Sirven para devolver valores dependiendo de condiciones

-- CASE sirve para evaluar múltiples valores de un campo
-- O múltiples condiciones

-- CASE value WHEN compare_value THEN result 
-- [WHEN compare_value THEN result ...] [ELSE result] END

select case rating 
	when 'g' then 'Para todos los públicos'
	when 'r' then 'Para mayores de edad'
	when 'pg' then 'menores acompañados'
	else 'No lo se'
end clasificacion
from film;

-- CASE WHEN condition THEN result 
-- [WHEN condition THEN result ...] [ELSE result] END

select case 
	when length<90 then 'Corta'
	when length<120 then 'Media'
	else 'Larga'
end duracion
from film;

select first_name, case
	when first_name like '%a%' then 'A'
	when first_name like '%e%' then 'E'
	when first_name like '%o%' then 'O'
	else 'IU'
end letra
from actor;

select first_name
from actor
order by  case
	when first_name like '%a%' then 'A'
	when first_name like '%e%' then 'E'
	when first_name like '%o%' then 'O'
	else 'IU'
end;

select first_name
from actor
where  case
	when first_name like '%a%' then 'A'
	when first_name like '%e%' then 'E'
	when first_name like '%o%' then 'O'
	else 'IU'
end='A';

select title, case
	when rating='R' and length>120 then 'Adultos intelectuales'
    when rating<>'R' and length>120 then 'Todos los públicos con aguante'
    else 'Todos los públicos'
    end tipo
from film;

-- Funciones de conversión de tipos
-- CAST es estándar y nos permite convertir un tipo en otro
-- Los nombres de los tipos cuando convertimos no son los mismos
-- que cuando los creamos
-- BINARY,CHAR,DATE,DATETIME,TIME,DECIMAL,SIGNED,UNSIGNED
-- Todos los tipos los tenéis en https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html
select cast('1.23' as DECIMAL(8,2)) n;

-- Para qué convertir: si tengo valores de cadena que yo sé que
-- son numéricos o de fecha para operar y extraer información
-- Y al revés, si tengo datos numéricos para concatenar o extraer información
-- por posición

select convert('123',SIGNED) n;

-- Usando CAST
SELECT CAST('2023-01-01' AS DATE) fecha;

-- Usando CONVERT
SELECT CONVERT('2023-01-01', DATE) fecha;

-- Funciones específicas de Mysql
-- Funciones de cadena, de fecha, aritméticas,....

-- Funciones de cadena

-- CONCAT para concatenar cadenas

select CONCAT(first_name,' ',last_name) nombre from actor;

-- Cuidado porque al concatenar un valor nulo con valor válidos el nulo 'se contagia'
-- es decir, concat(cualquier-valor,null)-->null
select  CONCAT(address,' - ',address2,' (',postal_code,')') direccion from address;

-- Subcadenas SUBSTRING(posicion, longitud)
-- Ojo, las cadenas empiezan en 1
select substring(first_name,2,3) from actor;

-- Podemos usar valores negativos
select substring(first_name,-3,1) from actor;

-- Por ejemplo la primera letra:
select substring(first_name,1,1) from actor;

-- La última
select substring(first_name,-1,1) from actor;

-- Las funciones las pudo utilizar en el select o en otras partes de la consulta
-- Por ejemplo: actores cuyo nombre acaba en 'E'
-- El substring lo utilizo en el where
select * from actor
where substring(first_name,-1,1)='e';

-- Actores ordenador por la última letra del nombre
select * from actor
order by substring(first_name,-1,1);

-- longitud de ua cadena

select length(first_name) longitud from actor;

-- Paises ordenados por la longitud de su nombre
select * from country
order by length(country);

-- Pasar a mayúsculas o minúsculas
select upper(country) mayusculas,lower(country) minusculas from country;

-- Puedo reemplazar una cadena por otra

select replace(country,'a','@') pais from country;

-- trim nos elimina espacios del principio y del fin
-- Esto es super útil en la vida real porque ni os imagináis la cantidad de
-- espacios basura que hay en las bases de datos

select trim('    asdasd    ') foo;

-- Yo muchas veces hago cosas como esta
-- Esto me limpia de espacios ese campo
update actor set first_name=trim(first_name);

-- Concatena con el reparador que yo le ponga
select concat_ws(' ',first_name,last_name) nombre from actor;

-- Es muy útil cuando quiero juntar varios campos usando el mismo separador
select concat_ws(' ',address,address2,district,postal_code) direccion from address;

-- Lo índices nos sirven para acelerar búsquedas sobre un campo
-- Lo que hace la base de datos es crear una copia ordenada
-- de los valores de ese campo
-- por lo que al buscar hace la busqueda binaria que es mucho más rápida

-- Crear un índice tiene pROS y CONTRAS
-- PRO: mayor velocidad en la búsqueda
-- CONTRA: Ocupa más espacio y las modificaciones e inserciones tardan más
-- COn la consideración de que hoy en día no vamos cortos de espacio
-- Que el retraso en la inserción es inapreciable
-- Utilizar índices es bastante recomendable

-- ¿Usamos índices para todo? NO
-- Un poco de cabeza:
-- Si tengo consultas que tardan mucho tengo que evaluar usar índices
-- Si yo sé que se realizan muchas consultas sobre ciertos campos
-- Puedo curarme en salud y usar índices
-- Los campos clave son siempre índices
-- Las claves extrangeras son siempre índices
-- Cuando nunca usar índices
-- Cuando tenemos campos con valores que se repiten mucho

-- Podemos tener índices combinados de varios campos
-- No son frecuentes, pero se usan cuando la búsqueda se hace por varios
-- campos a la vez y a lo mejor individualmente, como he dicho antes
-- se repiten mucho

-- Cuando yo creo un índice por defecto se entiende que los valores se pueden repetir
-- Pero puedo crear índices únicos con valores que no se repiten

-- Resumiendo: los índices se crean para acelerar búsquedas, usamos el prefijo
-- ix_ o idx_ tenemos la posibilidad de que sean únicos

https://www.w3schools.com/sql/sql_create_index.asp

-- actores_por_peliculas: 
-- Todos los datos de la película y el total de actores que han trabajado
-- drop view if exists actores_por_peliculas;
-- en el caso de eliminar puedo usar el 'IF EXISTS' para asegurarme de que existe
-- En caso contrario no hace nada

-- En el caso de las vistas puedo usar OR REPLACE para que si existe
-- La reemplace por la nueva definición
create or replace view actores_por_peliculas as
select film.*, count(actor_id) actores
from `film` join film_actor on film.film_id=film_actor.film_id
group by film_id;

-- estadisticas_peliculas: el título de la película, la cantidad de veces que se ha alquilado, 
-- el importe total de pagos, la media de los pagos

create or replace view estadisticas_peliculas as
select title, count(rental_id) alquileres, sum(amount) total_pagos,avg(amount) media_pagos
from film left join inventory using(film_id)
left join rental using(inventory_id)
left join payment using(rental_id)
group by film_id;

-- estadisticas_pais: El nombre del país, la cantidad de clientes que tiene, 
-- la cantidad de alquileres y la suma total de los pagos

-- Usamos DISTINCT para que no nos cuenta varias veces el mismo cliente
-- Usamos LEFT JOIN para que nos salgan los paises que no tengan clientes
-- O que tengan clientes que no han alquilado nada

create or replace view estadisticas_pais as
select country, count(distinct customer.customer_id) clientes, count(rental_id) alquileres, sum(amount) pagos
from country left join city using(country_id)
left join address using(city_id)
left join customer using(address_id)
left join rental using(customer_id)
left join payment using(rental_id)
group by country_id;

-- Cread una tabla 'peliculas_sin_alquilar' con 
-- toda la información de las películas que no se han alquilado ninguna vez
-- En este caso mejor elimino la que existe para sustituir
-- O la creo solo si no existe
drop table if exists peliculas_sin_alquilar;
create table if not exists peliculas_sin_alquilar
select film.* from film left join inventory on film.film_id=inventory.film_id
left join rental on inventory.inventory_id=rental.inventory_id
where rental_id is null;

-- Cread una tabla 'actores_comedia' con todos los actores que han trabajado en comedia (sin duplicados)

drop table if exists actores_comedia;
create table if not exists actores_comedia
select actor.* from actor join film_actor using (actor_id)
join film using (film_id)
join film_category using (film_id)
join category using (category_id)
where name='comedy';

-- Duplicar tablas o copiar datos en una tabla

-- Con esta sentencia creamos una tabla nueva con la misma estructura que otra
-- Copiando los datos
CREATE TABLE nueva_tabla SELECT * FROM actor;

-- Con esta sentencia copiamos los datos de una tabla en otra ya existente
insert into nueva_tabla (first_name, last_name)
select first_name,last_name from actor;

-- A veces no creamos una vista sino una tabla porque queremos 'congelar'
-- Un instante de tiempo o si el cálculo es muy costoso y quiero
-- realizar consultas sobre ese resultado me sale a cuenta hacer
-- el cálculo una vez y consultar muchas

create table total_pagos
select customer.*, sum(amount) pagos from customer
join payment on customer.customer_id=payment.customer_id
group by customer_id;