🪟 Fonctions de fenêtrage PostgreSQL — Cheatsheet

Syntaxe de base

SELECT <fonction_fenetre>(arguments)
  OVER (
    [PARTITION BY colonne(s)]   -- découpe en partitions indépendantes
    [ORDER BY colonne(s)]       -- ordre logique DANS chaque partition
    [frame_clause]              -- sous-ensemble de lignes de la partition
  )
FROM ...

Forme factorisée avec une fenêtre nommée :

SELECT
  sum(montant) OVER w AS total,
  avg(montant) OVER w AS moyenne,
  rank()       OVER (w ORDER BY montant DESC) AS rang
FROM ventes
WINDOW w AS (PARTITION BY region);

💡 Contrairement à GROUP BY, une fonction de fenêtrage ne réduit pas le nombre de lignes : chaque ligne conserve ses valeurs d'origine, enrichies des calculs agrégés.

📊 Fonctions de classement

⚠️ Toutes nécessitent ORDER BY dans OVER, sauf row_number() (déconseillé sans).

🧮 Agrégats utilisables comme fonctions de fenêtrage

Tout agrégat classique accepte OVER :

• sum(), avg(), min(), max(), count()
• bool_and(), bool_or(), every()
• string_agg(), array_agg(), json_agg()
• Agrégats statistiques : stddev(), variance(), corr(), percentile_cont() (⚠️ non supportés avec OVER — passer par un sous-SELECT ou FILTER)

count(*) OVER () → nombre total de lignes du résultat (utile pour calculer des pourcentages).

🖼️ Frames (sous-ensembles de lignes)

Uniquement avec ORDER BY. Sans frame explicite :

• Avec ORDER BY → frame par défaut : RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
• Sans ORDER BY → frame par défaut : toute la partition

OVER (ORDER BY date
      ROWS BETWEEN <début> AND <fin>)

ROWS vs RANGE :

• ROWS : bornes en nombre de lignes physiques
• RANGE : bornes en valeurs logiques — inclut les lignes ex æquo de la ligne courante (important pour sommes cumulées sur dates avec doublons)

EXCLUDE CURRENT ROW, EXCLUDE GROUP, EXCLUDE TIES : retire des lignes de la frame (ex : total sans la ligne courante).

🔎 Filtrer dans ou autour de la fenêtre

Pas de WHERE dans le OVER — quatre mécanismes selon le besoin.

1. FILTER (WHERE ...) — sous-total calculé sur un sous-ensemble de lignes

Les agrégats fenêtrés acceptent FILTER, qui agit comme un WHERE interne à la fonction : seules les lignes vérifiant la condition alimentent le calcul, mais toutes les lignes reçoivent le résultat.

SELECT vendeur, mois, region, montant,
       sum(montant) FILTER (WHERE region = 'Nord') OVER () AS total_nord,
       sum(montant) FILTER (WHERE region = 'Sud')  OVER () AS total_sud
FROM ventes;
-- chaque ligne affiche le total Nord, même les lignes du Sud

⚠️ FILTER n'accepte que les agrégats (sum, count, avg, string_agg…). Les fonctions de classement (row_number, rank, lag, first_value…) le refusent. Ordre d'écriture : fonction(args) FILTER (WHERE ...) OVER (...).

2. CASE WHEN — l'alternative universelle

Fonctionne partout, y compris pour les filtres que FILTER refuse :

SELECT vendeur, mois, montant,
       sum(CASE WHEN region = 'Nord' THEN montant END) OVER () AS total_nord,
       count(CASE WHEN montant > 100 THEN 1 END) OVER () AS nb_grosses_ventes
FROM ventes;

3. WHERE de la requête — la fenêtre ne voit que certaines lignes

Le WHERE s'exécute avant le calcul des fenêtres : les lignes exclues n'entrent dans aucun calcul (sommes, rangs, count(*) OVER ()…).

-- la fenêtre ne voit que les ventes du Nord
SELECT vendeur, mois, montant,
       row_number() OVER (ORDER BY montant DESC) AS rn
FROM ventes
WHERE region = 'Nord';

Pour conserver toutes les lignes dans la partition mais exclure certaines lignes du calcul, utiliser FILTER ou CASE (options 1 et 2).

4. Filtrer sur le résultat de la fenêtre

Impossible dans la même requête (pas de QUALIFY en PostgreSQL) → imbriquer :

SELECT * FROM (
  SELECT v.*,
         rank() OVER (PARTITION BY region ORDER BY montant DESC) AS rk
  FROM ventes v
) t
WHERE rk <= 3;   -- top 3 par région

🧪 Exemples sur données de démo

CREATE TABLE ventes (region text, vendeur text, mois date, montant int);
INSERT INTO ventes VALUES
  ('Nord', 'Alice', '2026-01-01', 100),
  ('Nord', 'Alice', '2026-02-01', 150),
  ('Nord', 'Bob',   '2026-02-01', 150),
  ('Sud',  'Chloe', '2026-01-01', 200),
  ('Sud',  'Chloe', '2026-02-01', 250);

1. Somme cumulée (running total)

SELECT vendeur, mois, montant,
       sum(montant) OVER (ORDER BY mois) AS cumul
FROM ventes;
-- 2026-01 → 100 ; 2026-02 → 400 (RANGE additionne les ex æquo du 02)

2. Moyenne mobile sur 3 mois

SELECT mois, montant,
       avg(montant) OVER (
         ORDER BY mois
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS mm3
FROM ventes;

3. Comparer au mois précédent (évolution %)

SELECT vendeur, mois, montant,
       lag(montant) OVER (PARTITION BY vendeur ORDER BY mois) AS precedent,
       round(100.0 * montant / lag(montant)
             OVER (PARTITION BY vendeur ORDER BY mois) - 100, 1) AS variation_pct
FROM ventes;

4. Top 1 par groupe (plus grosse vente par région)

SELECT * FROM (
  SELECT v.*,
         row_number() OVER (PARTITION BY region ORDER BY montant DESC) AS rn
  FROM ventes v
) t
WHERE rn = 1;

Variante « tous les ex æquo » : remplacer row_number() par dense_rank() ou rank().

5. Pourcentage du total par région

SELECT vendeur, region, montant,
       round(100.0 * montant
             / sum(montant) OVER (PARTITION BY region), 1) AS pct_region
FROM ventes;

6. Quartiles

SELECT vendeur, montant,
       ntile(4) OVER (ORDER BY montant) AS quartile
FROM ventes;

🍽️ Recettes classiques

⚠️ Pièges fréquents

1. last_value() sans frame explicite s'arrête à la ligne courante → utiliser ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
2. RANGE par défaut avec ORDER BY : les lignes ex æquo sont agrégées ensemble dans les sommes cumulées.
3. Pas d'alias de colonne dans OVER : on ne peut référencer que des colonnes sources (contourner avec un sous-SELECT ou CTE).
4. WHERE ne voit pas les fonctions de fenêtrage : elles s'exécutent après WHERE et GROUP BY. Filtrer sur leur résultat → sous-SELECT, CTE, ou QUALIFY-style avec un wrapper (PostgreSQL n'a pas de QUALIFY).
5. ORDER BY dans OVER ≠ ORDER BY final : l'ordre du résultat doit être fixé par un ORDER BY de requête, pas par la fenêtre.
6. FILTER fonctionne avec les agrégats fenêtrés : sum(x) FILTER (WHERE ...) OVER (...).
7. percentile_cont / percentile_disc refusent OVER (erreur « function is not a window function »).

🏗️ Placement de la clause WINDOW et ordre d'évaluation

Position syntaxique dans la requête

SELECT ...
FROM ...
[JOIN ...]
WHERE ...
GROUP BY ...
HAVING ...
WINDOW ...        -- ← ici, entre HAVING et ORDER BY
ORDER BY ...
LIMIT ...

La clause WINDOW se place après HAVING et avant ORDER BY. C'est une simple définition réutilisable : elle ne calcule rien par elle-même. Les définitions OVER (...) inline restent dans la liste du SELECT.

Ordre d'évaluation logique

FROM / JOIN  →  WHERE  →  GROUP BY  →  HAVING
→ fonctions de fenêtrage  →  SELECT  →  DISTINCT  →  ORDER BY  →  LIMIT

Les fonctions de fenêtrage sont calculées après WHERE/GROUP BY/HAVING (elles ne voient que les lignes déjà filtrées et groupées) mais avant SELECT, DISTINCT, ORDER BY et LIMIT.

Quelles clauses peuvent utiliser une fonction de fenêtrage ?

C'est pour cela qu'on ne peut ni filtrer ni regrouper sur le résultat d'une fonction de fenêtrage dans la même requête (PostgreSQL n'a pas de QUALIFY contrairement à Snowflake ou DuckDB) : il faut imbriquer. À noter aussi : une ligne éliminée par WHERE n'existe pas pour la fenêtre — elle ne compte ni dans les sommes, ni dans les rangs.

📚 Références officielles

• Window Functions — PostgreSQL Documentation
• Window Function Calls — SQL Reference

PostgreSQL — Cheat sheet Regex

Le moteur regex de PostgreSQL est ARE (Advanced Regular Expressions, bibliothèque de
Henry Spencer, proche de la syntaxe Tcl). Ce n'est pas du PCRE : pas de \b, pas de
\p{...}, pas de \K.

⚠️ Pièges classiques PCRE → PostgreSQL

Opérateurs de correspondance

'chaine' ~  'motif'   -- correspondance (sensible à la casse)
'chaine' ~* 'motif'   -- correspondance (insensible à la casse)
'chaine' !~  'motif'  -- PAS de correspondance
'chaine' !~* 'motif'  -- PAS de correspondance (insensible à la casse)

SELECT 'PostgreSQL 16' ~ '\d+';      -- true
SELECT 'test' ~* '^TEST$';            -- true
SELECT 'mot test' ~ '\ytest\y';      -- true (limite de mot)

Fonctions principales

Remplacement avec références de groupes : \1, \2…

SELECT regexp_replace('Dupont Jean', '^(\w+) (\w+)$', '\2 \1');
-- Jean Dupont
SELECT regexp_replace('a1 b2 c3', '\d', 'X', 'g');   -- g = toutes les occurrences
-- aX bX cX

Flags des fonctions (dernier argument, chaîne text)

Flags embarqués dans le motif (syntaxe ARE)

'ABC' ~ '(?i)abc'          -- true : (?i) = insensible à la casse à partir de ce point
'ABC' ~ '(?i\:abc)'         -- true : limités à un groupe
'a b \d' ~ '(?x) a  b \d'  -- x : mode étendu (espaces et #commentaires ignorés)
'aaa' ~ '(?s)a+?'          -- s : quantificateurs non-gloutons ; g : gloutons

Classes et ensembles

⚠️ Préférer [[:digit:]] ou [0-9] à \d dans un crochet.

SELECT 'a1' ~ '^\w\d$';        -- true
SELECT 'x' ~ '[[\:alpha:]]';    -- true

Ancres et contraintes

Quantificateurs

Groupes et assertions

SELECT regexp_matches('abab', '(ab)\1');          -- {ab} : rétro-référence
SELECT regexp_matches('foo bar', '\w+(?= bar)');  -- {foo} : lookahead

Échappements de caractères

\n \t \r \f \v \a \b⚠️ (backspace) — et \. \* \( … pour les
métacaractères littéraux.

Écriture des littéraux SQL

• Avec standard_conforming_strings = on (défaut depuis PG 9.1) : '\ytest\y'
  fonctionne tel quel, un seul antislash.
• Avec les chaînes E'...' : doubler les antislashs → E'\\ytest\\y'.
• Échapper une apostrophe SQL : '\d{2}''\d{3}' ou dollar-quoting $$\d{2}$$.

SIMILAR TO (SQL standard, à connaître)

'abc' SIMILAR TO 'a%'     -- % = n'importe quelle suite, _ = un caractère
'abc' SIMILAR TO '(a|b)%' -- alternation avec |, équivalent regex + ancrage total
'abc' NOT SIMILAR TO 'b%'

Traduit en interne en regex ARE et ancré aux deux extrémités — en général, on préfère
directement ~ et ses variantes.

Recettes courantes

-- Valider un email (simple)
SELECT 'user@example.com' ~ '^[[\:alnum:]_.-]+@[[\:alnum:]-]+\.[[\:alpha:]]{2,}$';

-- Extraire le code postal
SELECT substring('75001 Paris' FROM '\d{5}');

-- Normaliser un numéro de téléphone
SELECT regexp_replace('06 12 34 56 78', '\D', '', 'g');   -- 0612345678

-- Découper sur plusieurs séparateurs
SELECT regexp_split_to_table('a,b;c d', '[,; ]');

-- Lignes où un mot apparaît exactement (pas comme sous-mot)
SELECT * FROM t WHERE libelle ~ '\ychaud\y';

-- Dédupliquer un mot immédiatement répété
SELECT regexp_replace('bon bonjour', '(\w+) \1', '\1');   -- bonjour

Vive le progrès !

• Les résultats de l’essai ANRS VRI06 de phase I évaluant le candidat-vaccin CD40.HIVRI.Env publiés en octobre 2024 dans la revue eClinicalMedicine avait montré une bonne tolérance et l’apparition de réponses immunitaires cellulaires et humorales persistantes jusqu’à 48 semaines après la vaccination.1
• En mai 2026, les résultats à long terme de l’essai ANRS VRI06 ont été publiés dans la revue Scientific Reports: ils confirment la durabilité de la réponse immunitaire induite par ce candidat vaccin ciblant directement les cellules dendritiques.2
• De plus ils montrent qu’un rappel administré plusieurs mois après la dernière vaccination (late boost) permet de réactiver fortement et durablement les réponses immunitaires dirigées contre le VIH.

Voir les appli Shizuku & Canta pour dégoogliser.

La plupart des outils anti-pistage mentent sur tout : faux user-agent, faux écran, faux GPU sauf que mentir, ça vous rend encore plus repérable. Bah oui, un browser qui prétend être 3 machines à la fois, ça se repère vite.

Du coup, Fingerprint Defender fait l'inverse : il randomise seulement les surfaces qui servent à vous tracer, et laisse passer vos vraies valeurs communes pour que vous ressembliez à tout le monde.

Ainsi, chaque session il ajoute un léger bruit aléatoire sur le canvas, sur la sortie audio de l'AudioContext et sur les mesures de position des éléments de la page. Il bloque aussi les fuites d'IP par WebRTC et coupe l'API Battery Status (que Firefox planque déjà aux sites depuis des années, mais bon). Et pour l'écran, il annonce du 1920x1080, la résolution la plus banale qui soit !

Et surtout, il laisse volontairement passer votre WebGL, votre fuseau horaire, vos polices et votre user-agent réels. Pourquoi me direz vous ?? Hé bien parce que ce sont des valeurs que des millions de gens partagent donc c'est complétement inutile de les falsifier. Ça vous complique juste la vie.

Le pari de "se fondre dans la masse" est bien meilleur qu'un spoofer naïf, et la recherche sur le sujet (le fameux Panopticlick de l'EFF ) montre bien que la protection vraiment béton, c'est l'uniformité totale. Il faut faire en sorte que tous les utilisateurs soient strictement identiques, comme le fait Tor Browser.

La noyade : À quoi ressemble un enfant qui se noie ?

Une famille est au bord d'une piscine, ou sur une plage.
Les enfants jouent dans l'eau, les parents discutent quelques mètres plus loin, l'œil distrait. Soudain, le maître-nageur traverse le bassin à toute vitesse, plonge, et ressort de l'eau un enfant qui se noyait. Juste sous le nez des parents, qui n'avaient absolument rien vu venir.

Comment est-ce possible ? La réponse est simple, et un peu terrifiante : une noyade ne ressemble pas du tout à une noyade.
Conditionnés par les films et les séries, on s'attend à de grands cris, des bras qui s'agitent, des éclaboussures spectaculaires, des « AU SECOURS ! » théâtraux. Or, dans la réalité, une personne en train de se noyer ne fait pratiquement aucun bruit, n'agite pas les bras et n'appelle pas à l'aide. C'est ce que le Dr Francesco A. Pia, ancien sauveteur et chercheur, a baptisé la réponse instinctive à la noyade, ou Instinctive Drowning Response.

Cette discrétion trompeuse s'explique par plusieurs mécanismes physiologiques. Tout d'abord, une personne qui se noie ne peut tout simplement pas crier. Notre appareil respiratoire est conçu pour respirer ; la parole n'est qu'une fonction secondaire, qui passe après. Quand le corps lutte pour survivre, il consacre toutes ses ressources à inspirer de l'air, pas à former des phrases. Et comme la bouche de la victime fait des allers-retours rapides au-dessus et en dessous de la surface, elle a tout juste le temps d'inspirer-expirer avant de replonger. Pas le temps de hurler.
Ensuite, une personne qui se noie ne peut pas agiter les bras pour faire signe. Instinctivement, ses bras s'écartent latéralement et s'appuient sur l'eau pour pousser son corps vers le haut, afin de sortir la bouche à la surface. Ce mouvement est involontaire et incontrôlable : la victime ne peut pas s'en empêcher pour, par exemple, attraper une perche ou faire un grand signe au sauveteur. Tout son corps reste vertical, sans battement de jambes pour se propulser. Comme si elle essayait de grimper à une échelle invisible.

Sans intervention, une victime ne reste à la surface qu'entre 20 et 60 secondes avant de couler définitivement. Une minute, montre en main, pour réagir.
D'où l'importance de savoir reconnaître les signes : tête basse dans l'eau avec la bouche au niveau de la surface, tête renversée en arrière, regard vitreux ou yeux fermés, cheveux devant le visage, corps strictement vertical, hyperventilation, mouvements de nage qui n'avancent pas. Et si vous avez un doute sur quelqu'un dans l'eau, posez-lui simplement la question : « ça va ? » : s'il vous répond, tout va bien. S'il vous fixe sans rien dire, il est temps d'agir…

Une dernière chose, particulièrement valable pour les parents : les enfants qui jouent dans l'eau font du bruit. Beaucoup de bruit. Quand ils deviennent soudainement silencieux, il faut aller voir pourquoi. Aux États-Unis, la noyade est la deuxième cause de mort accidentelle chez les moins de quinze ans, et dans environ 10 % des cas, un adulte assiste à la scène sans se rendre compte qu'un drame est en train de se jouer sous ses yeux.

À noter qu'il existe aussi une phase préalable, appelée détresse aquatique, durant laquelle la victime peut encore appeler à l'aide et coopérer à son sauvetage. Mais cette phase est courte, et n'est pas systématiquement présente avant la noyade proprement dite. Autant dire : ne comptez pas dessus.
Bref, la prochaine fois que vous serez au bord de l'eau, méfiez-vous du calme.

Paramètres utiles :

• --cookies-from-browser firefox → Utilise les cookies disponibles du navigateur. Pratique quand Youtube bloque certains retours.
• --flat-playlist → Retourne la liste des vidéos disponibles.
• --playlist-end 10 → Limite la liste aux 10 premiers résultats.
• --print "%(id)s"$'\t'"%(duration_string)s"$'\t'"%(title)s" → Formate la liste de sortie en mettant l'ID, la durée et le titre de la vidéo sur une même ligne, le tout séparé par des tabulations.
• -F → Retourne la liste des formats audios et/ou vidéos disponibles pour une vidéo donnée.
• -f "399+139" → Sélectionne les formats 399 et 139 pour le téléchargement.
• --merge-output-format mp4 → Fusionne l'ensemble des formats téléchargés dans un même fichier mp4.
• --postprocessor-args "ffmpeg:-ss 60 -c copy -avoid_negative_ts make_zero -movflags +faststart" → Après téléchargement, utilise ffmpeg pour :
  • -ss 60 → Supprimer les 60 premières secondes (utile pour virer la pub).
  • -c copy → Copie les flux (vidéo/audio) sans ré-encodage (rapide, conserve la qualité).
  • -avoid_negative_ts make_zero → Corrige les timestamps négatifs (évite les bugs de lecture).
  • -movflags +faststart → Déplace l'index au début du fichier MP4 (permet une lecture instantanée et un seek fluide).
• --force-overwrites → Écrase le fichier s'il existe déjà (pas de confirmation).
• -o "L'Univers avant le Big Bang...".mp4 → Nomme le fichier de sortie.

Exemples :
1) yt-dlp --cookies-from-browser firefox --flat-playlist --playlist-end 10 --print "%(id)s"$'\t'"%(duration_string)s"$'\t'"%(title)s" "https://www.youtube.com/@hugolisoiryoutube/videos" | column -t -s $'\t'
Récupère la liste des 10 dernières vidéos publiées sur une chaine (ID, durée en [hh:]mm:ss, titre) sous forme de tableau.

2) yt-dlp -F https://www.youtube.com/watch\?v\=kHirUbwaO9I
Liste l'ensemble des formats audio et vidéo disponibles.

3) yt-dlp -f "399+139" --merge-output-format mp4 -o "L'Univers avant le Big Bang : 3 théories qui changent tout ! - Hugo Lisoir (0h13).mp4" --postprocessor-args "ffmpeg:-ss 60 -c copy -avoid_negative_ts make_zero -movflags +faststart" --force-overwrites https://www.youtube.com/watch\?v\=M2l1ANUedys
Télécharge une vidéo :

• sélectionne les formats 399 (vidéo mp4, 1920x1080, 30 fps) + 139 (audio m4a, mp4a.40.5),
• fusionne la vidéo et l'audio en un seul fichier mp4,
• nomme le fichier de sortie,
• supprime les 60 premières secondes qui ne contiennent que de la pub, en préservant l'index
• écrase le fichier résultat si déjà présent.

Indexe les ROM alternatives compatibles avec chaque modèle de smartphone.

Retrouver la météo d'un jour donné n'importe où depuis 1940.

La Cour de cassation a mis en avant le droit du salarié de refuser les modifications unilatérales de ses conditions de travail.

Ce droit de refus est important car il protège les employés contre les changements arbitraires et potentiellement préjudiciables imposés par l'employeur.

La jurisprudence actuelle affirme que le télétravail, une fois devenu une pratique régulière et prolongée, établit un droit acquis pour le salarié.

Ce droit ne peut être altéré sans un accord clair et consensuel, garantissant ainsi une certaine sécurité juridique dans l'organisation du travail à distance.

L’un des plus grands succès de la chanson suisse est un classique des fêtes : la “Danse des canards” (Der Ententanz dans sa version originale), composée par l’accordéoniste Werner Thomas en 1957. C’est aujourd’hui la chanson suisse la plus exportée au monde, avec plus de 400 versions enregistrées.

       IDENTIFICATION DIVISION.
       PROGRAM-ID. EnvExample.

       DATA DIVISION.
       WORKING-STORAGE SECTION.
       01  APP-MODE       PIC X(10).

       PROCEDURE DIVISION.
           ACCEPT APP-MODE FROM ENVIRONMENT "APP_MODE"
           DISPLAY "Mode d'exécution : " APP-MODE
           STOP RUN.

π RuView: WiFi DensePose est un projet qui fait un peu flipper puisque ça permet de transformer les ondes de votre box internet en détecteur de corps humains... à travers les murs ! Rythme cardiaque, respiration, posture, tout y passe et le pire (ou le mieux, c'est selon ^^), c'est que c'est open source et que vous pouvez tester ça chez vous.

Quand une baleine meurt, ce n’est pas la fin, mais le début d’un monde. Sa chute dans les abysses crée un écosystème complexe capable de nourrir jusqu’à 400 espèces — du requin aux bactéries — pendant des décennies. Certaines créatures ne vivent même que là.

Des vers comme Osedax se nourrissent directement des os, grâce à des bactéries capables d’en extraire l’énergie. Dans l’obscurité totale, une seule carcasse devient une oasis durable, un véritable îlot de vie au milieu du désert profond.

L’expression « faire la navette » a son origine au XVIIIᵉ siècle, et vient du tissage. La navette est la petite pièce (outil allongé) que le tisserand fait aller et venir sur le métier à tisser pour faire passer le fil dans la trame d’où, par image, l’idée de déplacements répétés entre deux points.

Au passage, le mot navette est lié à l’idée de petit “vaisseau” : il est souvent expliqué comme un diminutif associé à nef / nave (bateau), en référence à sa forme allongée. L’image s’est élargie : on parle de navette pour un service de transport qui fait des trajets courts et répétés (aéroport…)