Notes SQL (PostgreSQL)

Published: 08-07-2015

Updated: 20-06-2016

By: Maxime de Roucy

tags: database postgresql

Quelques notes, pense bêtes, concernant SQL ; principalement sous PostgreSQL.

Élement suivant

Pour obtenir l’élement suivant/précédent… pour chaque ligne d’une table, il faut utiliser les « Window Functions ». Ce type de fonction s’apparente à des à des fonctions d’agregation (eg. MAX, COUNT… suivit de GROUP BY) mais les lignes reste séparé (pas besoins de GROUP BY).

Par exemple pour la table suivante, représentant des tâches ticket_number assigné à des personnes member et l’heure de début de chaque tâche start_time.

 id  | member |  ticket_number   |         start_time         
-----+--------+------------------+----------------------------
  64 | NCH    | 13001            | 2015-06-22 11:47:17.637711
  66 | NCH    | 13002            | 2015-06-22 12:01:38.040852
  67 | NCH    | 13003            | 2015-06-22 12:01:38.908644
  68 | NCH    | 13004            | 2015-06-22 12:01:40.34213
  62 | MDR    | 13005            | 2015-06-22 11:35:16.168858
  96 | NCH    | 13006            | 2015-06-23 18:04:02.118589
  94 | MDR    | 13007            | 2015-06-23 17:54:17.965842
  …

Pour calculer la durée de chacune des taches j’utilise la requête suivante :

SELECT id, member, ticket_number, start_time, LEAD(start_time) OVER (PARTITION BY member ORDER BY start_time) - start_time AS duration FROM table;

Source et compléments :

etherpad

Commande pour récupérer la liste des pad d’une installation etherpad (base MySQL)

SELECT DISTINCT SUBSTRING(store.key,5,LOCATE(":",store.key,5)-5) FROM store WHERE store.key LIKE "pad:%";