~~stoggle_buttons~~
====== Links ======
  * [[https://jvns.ca/blog/2019/10/03/sql-queries-don-t-start-with-select/|SQL queries run in this order]]
  * [[https://developer.okta.com/blog/2019/07/19/mysql-vs-postgres|MySQL vs PostgreSQL -- Choose the Right Database for Your Project]]
======  MySQL ====== 
===== Info de espacio libre =====
<code sql>
-- Get the database free space
SELECT table_schema "Data Base Name",
sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",
sum( data_free )/ 1024 / 1024 "Free Space in MB"
FROM information_schema.TABLES
GROUP BY table_schema; 

-- Get the database last update ordered by update time then by create time.
SELECT MAX(UPDATE_TIME), MAX(CREATE_TIME), TABLE_SCHEMA
FROM `TABLES`
GROUP BY TABLE_SCHEMA
ORDER BY 1, 2; 
</code>

  * [[http://mysqladministrators.blogspot.com/2012/02/get-database-size.html]]

===== Simular lag en MySQL =====

[[https://stackoverflow.com/questions/11303532/simulate-lag-function-in-mysql]]

===== Matar procesos que se quedan abiertos desde MySQL =====
<code sql>
SHOW PROCESSLIST;

KILL 38239;
KILL 38240;
-- Para hacerlo más automático, se puede hacer lo sigiente:
-- También matchea el propio proceso que ejecuta la query,
-- por lo que siempre devuelve un resultado al menos
select concat('KILL ',id,';') from information_schema.processlist where INFO like "%FaultyTable%"
 
</code>
[[https://stackoverflow.com/questions/24496918/mysql-slow-drop-table-command]]

===== Backup de una tabla =====
<code sql>
CREATE TABLE db.backup LIKE db.mytable; 
INSERT db.backup SELECT * FROM db.mytable;
</code>

===== MySQL Time Type =====
* https://dev.mysql.com/doc/refman/8.0/en/time.html
* https://stackoverflow.com/questions/1426100/adding-certain-minutes-to-time-in-mysql/47887005



====== PostgreSQL ======
===== conexiones sencillas con url =====
''%%psql "postgres://postgres:postgres@localhost:5432/postgres"%%''

===== Importar a una tabla desde un csv =====
**Mucho cuidado porque el orden de los campos importa**
<code bash>
psql "$POSTGRES_DATABASE_URI" -c "\copy table_name from 'file.csv' delimiter ',' csv header;"
</code>
<code bash>
file="table.csv"; tail -n +2 $file | psql -d mydb -U postgres -W -h localhost -p 5432 -c
"COPY landing_tbl ( $(head -n 1 $file) ) FROM STDIN CSV;"
</code>
El ''tail -n +2'' se salta el encabezado del csv, el ''head -n 1 final.csv'' nos saca el encabezado para la lista de campos

===== Update con join =====
<code sql>
update new_table
set field = b.field
from backup b
where  b.created_at = new_table.created_at and b.id = new_table.id and 
b.field is not null
</code>


===== Contraseña postgres =====
  * PostgreSQL por defecto no tiene contraseña y no se puede acceder a ella. Hay que hacer:
  * ''%%sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'postgres';"%%''
  * ''psql -d postgres -U postgres -W -h localhost -p 5432'' para comprobar que funciona

===== updates csv =====
  * Tener una tabla temporal intermedia para la copia. Al añadir columnas a la tabla final, no se rompe el proceso que insertaba en esa tabla, y si cambias el CSV, tienes que cambiar la temporal
  * Aprovechar ese paso intermedio para intentar no perder registros, p.e. haciendo un [[https://www.postgresqltutorial.com/postgresql-upsert/|upsert]] en la tabla final desde la temporal

Script de upsert SQL
<code sql>
begin;

create temporary table temp_mytable as
select :fields_list
from :dest_table
where 1 <> 1;

\copy temp_mytable from ':filename' delimiter ',' csv;

insert into :dest_table(:fields_list)
select 
:fields_list
from temp_mytable
on conflict (:pkey_fields_list)
do update set 
:update_set;

commit;
</code>

===== backups =====

<code bash>
# Primero hay que levantar un proxy a PROD para conectarnos a ella en localhost:54321 (según el ejemplo)
psql "postgres://postgres:postgres@localhost:5432/postgres" -c "\copy tablename to tablename.csv delimiter ',' csv header"
pg_dump "postgres://postgres:postgres@localhost:5432/postgres" >  out.sql 
psql "postgres://postgres:postgres@localhost:5432/postgres" < out.sql
</code>

====== dbeaver ======
===== exportar queries sin ejecutarlas =====

[[https://stackoverflow.com/questions/43329790/export-result-set-on-dbeaver-to-csv]]

Seleccionar Query > Execute > Export from query

====== Patrones SQL ======
===== Seleccionar último registro =====
<code sql>
select *
from mytable
join (
  select max(created_at)
  from mytable
  group by id
) latest
on mytable.id = latest.id
</code>

===== Upsert =====
Al subir un csv, el orden de campos importa, por lo que es mejor:
  - Subir la tabla a una tabla temporal asegurándonos de que tiene el mismo esquema (o creándolo automáticamente en python, por ejemplo)
  - Hacer un **upsert** (update+insert) de los campos que hemos incluido

===== lag, nulos =====
Cuando haces un lag, te salen nulos
<code sql>
timestamp > lag(timestamp) -- OJO! Esto excluye registros con timestamp nulo!
timestamp > coalesce(lag(timestamp), min_timestamp)
</code>
====== Trucos ======
  * Al atascarse en una query, puede ser útil cambiar el orden en el que se ejecutan las queries: sacar una subquery al nivel superior, hacer primero un cruce que se hace más adelante...

===== Cuentas distintas sobre una misma tabla =====

<code>
select
c.name->>"$.translations.en" as "Ciudad",
coalesce(created_at, 0) as "Día",
coalesce(sum(created_at >= '2020-04-01 02:00:00' and created_at < '2020-05-01 02:00:00'), 0) as "Registrados",
coalesce(sum(accept_date >= '2020-04-01 02:00:00' and accept_date < '2020-05-01 02:00:00'), 0) as "Validados"
from motit.iterable i
left join motit.`User` u
on i.num = date(u.created_at)
left join motit.City c
on u.city_id = c.id
where created_at >= '2020-04-01 02:00:00'
and created_at < '2020-05-01 02:00:00'
group by 1, 2
order by c.name->>"$.translations.en", date(created_at)


Básicamente haces un

select
sum(condición 1 que iria en el where) as cuenta1, sum(condición 2 que iria en el where) as cuenta2
</code>