Structure d’une requête (mais pas l’ordre d’exécution). Plus de détail ici.
SELECT *
FROM ...
JOIN ... ON ...
WHERE ... OR ... AND
GROUP BY ...
HAVING ...
ORDER BY ... DESC
LIMIT ...
HAVING c’est comme un WHERE mais pour les aggrégats.
Window Functions
SELECT
orders.id,
orders.ordered_at,
orders.customer_id,
SUM(orders.amount) OVER (
PARTITION BY orders.customer_id
ORDER BY orders.ordered_at
) AS cumulative_amount
FROM orders
PARTITION BY c’est le GROUP BY des Window Functions
WITH Clause. Cela crée une table virtuelle.
WITH matches_per_month AS (
SELECT
STRFTIME('%Y-%m', DATE(matches.date)) AS period,
COUNT(*) AS cnt
FROM "Match" AS matches
GROUP BY period
ORDER BY period
)
SELECT
matches_per_month.period,
SUM(matches_per_month.cnt) OVER (
ORDER BY matches_per_month.period
) AS cumulative_count
FROM matches_per_month
GROUP BY c’est que pour les aggrégateurs de type SUM, COUNT, AVG etc.
Order tables by size :
SELECT table_name, pg_size_pretty(pg_table_size(quote_ident(table_name))), pg_table_size(quote_ident(table_name))
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY 3 DESC;
Create Index concurrently for huge table
CREATE INDEX CONCURRENTLY ON products (sku)
Ce que les développeurs doivent savoir sur les index par Nelson Dionisi (vidéo)
Create an advanced search engine with PostgreSQL
Select unique latest grouped records from DB