-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathlangage_sql.html
61 lines (60 loc) · 21.9 KB
/
langage_sql.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
<!DOCTYPE html>
<html lang="fr">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width,initial-scale=1">
<title>Langage SQL | SECMAR</title>
<meta name="description" content="Jeu de données des affaires maritimes sur l'assistance et le sauvetage en mer">
<link rel="icon" href="https://www.snosan.fr/themes/rainlab-vanilla/assets/images/favicon.png">
<meta name="theme-color" content="#0d3f94">
<link rel="preload" href="/secmar-documentation/assets/css/0.styles.90835b9a.css" as="style"><link rel="preload" href="/secmar-documentation/assets/js/app.b0f9bbc6.js" as="script"><link rel="preload" href="/secmar-documentation/assets/js/4.7b387fda.js" as="script"><link rel="preload" href="/secmar-documentation/assets/js/9.4f108125.js" as="script"><link rel="prefetch" href="/secmar-documentation/assets/js/7.0cbe5dfc.js"><link rel="prefetch" href="/secmar-documentation/assets/js/2.413b4e91.js"><link rel="prefetch" href="/secmar-documentation/assets/js/3.1a376dc8.js"><link rel="prefetch" href="/secmar-documentation/assets/js/5.46a485ad.js"><link rel="prefetch" href="/secmar-documentation/assets/js/6.96d518fd.js"><link rel="prefetch" href="/secmar-documentation/assets/js/8.9ee0e24e.js"><link rel="prefetch" href="/secmar-documentation/assets/js/10.3d1752a0.js"><link rel="prefetch" href="/secmar-documentation/assets/js/11.95da56de.js"><link rel="prefetch" href="/secmar-documentation/assets/js/12.08f5e00c.js"><link rel="prefetch" href="/secmar-documentation/assets/js/13.2e342c73.js"><link rel="prefetch" href="/secmar-documentation/assets/js/14.a3f89f9d.js"><link rel="prefetch" href="/secmar-documentation/assets/js/15.fdd30358.js"><link rel="prefetch" href="/secmar-documentation/assets/js/16.33e434b2.js">
<link rel="stylesheet" href="/secmar-documentation/assets/css/0.styles.90835b9a.css">
</head>
<body>
<div id="app" data-server-rendered="true"><div class="theme-container"><header class="navbar"><div class="sidebar-button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512" class="icon"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div> <a href="/secmar-documentation/" class="home-link router-link-active"><!----> <span class="site-name">SECMAR</span></a> <div class="links" style="max-width:nullpx;"><div class="search-box"><input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div> <nav class="nav-links can-hide"><div class="nav-item"><a href="https://www.data.gouv.fr/fr/datasets/operations-coordonnees-par-les-cross/" target="_blank" rel="noopener noreferrer" class="nav-link external">
Données brutes
<svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg></a></div><div class="nav-item"><a href="https://carte.snosan.fr" target="_blank" rel="noopener noreferrer" class="nav-link external">
Cartographie
<svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg></a></div> <!----></nav></div></header> <div class="sidebar-mask"></div> <div class="sidebar"><nav class="nav-links"><div class="nav-item"><a href="https://www.data.gouv.fr/fr/datasets/operations-coordonnees-par-les-cross/" target="_blank" rel="noopener noreferrer" class="nav-link external">
Données brutes
<svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg></a></div><div class="nav-item"><a href="https://carte.snosan.fr" target="_blank" rel="noopener noreferrer" class="nav-link external">
Cartographie
<svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg></a></div> <!----></nav> <ul class="sidebar-links"><li><a href="/secmar-documentation/" class="sidebar-link">Accueil</a></li><li><a href="/secmar-documentation/preambule.html" class="sidebar-link">Préambule et avertissement</a></li><li><a href="/secmar-documentation/CHANGELOG.html" class="sidebar-link">Changements sur le jeu de données</a><ul class="sidebar-sub-headers"></ul></li><li><div class="sidebar-group collapsable"><p class="sidebar-heading"><span>Concepts métier</span> <span class="arrow right"></span></p> <!----></div></li><li><div class="sidebar-group"><p class="sidebar-heading"><span>Modèle de données</span> <!----></p> <ul class="sidebar-group-items"><li><a href="/secmar-documentation/schema.html" class="sidebar-link">Schéma de données</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header"><a href="/secmar-documentation/schema.html#operations" class="sidebar-link">operations</a></li><li class="sidebar-sub-header"><a href="/secmar-documentation/schema.html#resultats-humain" class="sidebar-link">resultats_humain</a></li><li class="sidebar-sub-header"><a href="/secmar-documentation/schema.html#flotteurs" class="sidebar-link">flotteurs</a></li><li class="sidebar-sub-header"><a href="/secmar-documentation/schema.html#operations-stats" class="sidebar-link">operations_stats</a></li></ul></li><li><a href="/secmar-documentation/tables_codes.html" class="sidebar-link">Tables de codes</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header"><a href="/secmar-documentation/tables_codes.html#transition-de-seamis-vers-le-snosan" class="sidebar-link">Transition de SeaMIS vers le SNOSAN</a></li><li class="sidebar-sub-header"><a href="/secmar-documentation/tables_codes.html#cross" class="sidebar-link">CROSS</a></li><li class="sidebar-sub-header"><a href="/secmar-documentation/tables_codes.html#evenements-declenchant" class="sidebar-link">Événements déclenchant</a></li><li class="sidebar-sub-header"><a href="/secmar-documentation/tables_codes.html#moyen-d-alerte" class="sidebar-link">Moyen d'alerte</a></li><li class="sidebar-sub-header"><a href="/secmar-documentation/tables_codes.html#qui-alerte" class="sidebar-link">Qui alerte</a></li><li class="sidebar-sub-header"><a href="/secmar-documentation/tables_codes.html#bilan-humain" class="sidebar-link">Bilan humain</a></li><li class="sidebar-sub-header"><a href="/secmar-documentation/tables_codes.html#autorites-des-moyens" class="sidebar-link">Autorités des moyens</a></li><li class="sidebar-sub-header"><a href="/secmar-documentation/tables_codes.html#types-precis-de-flotteurs" class="sidebar-link">Types précis de flotteurs</a></li><li class="sidebar-sub-header"><a href="/secmar-documentation/tables_codes.html#ports-de-reference-pour-la-maree" class="sidebar-link">Ports de référence pour la marée</a></li></ul></li></ul></div></li></ul> <div class="sidebar__toggle"><span class="arrow left"></span> <span class="sidebar__toggle-label">
Cacher le menu
</span></div></div> <div class="page"> <div class="content"><h1 id="langage-sql"><a href="#langage-sql" aria-hidden="true" class="header-anchor">#</a> Langage SQL</h1> <p>Cette page comporte des rappels sur le langage SQL. Le diaporama complet est <a href="https://docs.google.com/presentation/d/1hn6SA78FhIaXljEb7Pof9WVl0woEjurLm4VCAlHT1mM/" target="_blank" rel="noopener noreferrer">disponible en ligne<svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg></a>. Il est possible que certaines notions n'aient pas été abordées lors de la formation.</p> <h2 id="requete-de-selection"><a href="#requete-de-selection" aria-hidden="true" class="header-anchor">#</a> Requête de sélection</h2> <p>Une requête de sélection peut faire apparaitre les éléments suivants :</p> <ul><li><code>SELECT</code> : les colonnes que l'on veut faire apparaitre dans le résultat ;</li> <li><code>FROM</code> : la table source des données ;</li> <li><code>JOIN</code> : si l'on souhaite travailler avec plusieurs tables, il faut effectuer des jointures et spécifier les colonnes qui doivent correspondre (<code>operation_id</code> pour SECMAR) ;</li> <li><code>WHERE</code> : les contraintes / filtres à appliquer à nos données (spécifier une année, une situation météo etc.) ;</li> <li><code>GROUP BY</code> : lorsque l'on veut résumer l'information, il faut procéder à un aggrégat pour ne pas avoir autant de lignes que le nombre d'opérations qu'il y a. Cette clause permet d'avoir des résultats par année et par CROSS par exemple ;</li> <li><code>ORDER BY</code> : permet de trier le résultat obtenu selon l'ordre ascendant, descendant des colonnes. On utilise la position d'une colonne et les mots clés <code>ASC</code> ou <code>DESC</code>.</li></ul> <p>Un exemple complet d'une requête (sans intérêt métier) faisant apparaitre les différentes syntaxes possibles est présenté ci-dessous.</p> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">SELECT</span>
<span class="token string">"stats"</span><span class="token punctuation">.</span><span class="token string">"annee"</span><span class="token punctuation">,</span>
<span class="token string">"op"</span><span class="token punctuation">.</span><span class="token string">"cross"</span><span class="token punctuation">,</span>
<span class="token string">"op"</span><span class="token punctuation">.</span><span class="token string">"type_operation"</span><span class="token punctuation">,</span>
<span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token string">"op"</span><span class="token punctuation">.</span><span class="token string">"operation_id"</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token string">"nb_operations"</span><span class="token punctuation">,</span>
<span class="token function">SUM</span><span class="token punctuation">(</span><span class="token string">"stats"</span><span class="token punctuation">.</span><span class="token string">"nombre_personnes_impliquees"</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token string">"total_impliques"</span>
<span class="token keyword">FROM</span> <span class="token string">"operations"</span> <span class="token keyword">AS</span> <span class="token string">"op"</span>
<span class="token keyword">JOIN</span> <span class="token string">"operations_stats"</span> <span class="token keyword">AS</span> <span class="token string">"stats"</span> <span class="token keyword">ON</span> <span class="token string">"stats"</span><span class="token punctuation">.</span><span class="token string">"operation_id"</span> <span class="token operator">=</span> <span class="token string">"op"</span><span class="token punctuation">.</span><span class="token string">"operation_id"</span>
<span class="token keyword">WHERE</span> <span class="token string">"op"</span><span class="token punctuation">.</span><span class="token string">"cross"</span> <span class="token operator">=</span> <span class="token string">'Jobourg'</span>
<span class="token operator">AND</span> <span class="token string">"op"</span><span class="token punctuation">.</span><span class="token string">"evenement"</span> <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token string">'Isolement par la marée / Envasé'</span><span class="token punctuation">,</span> <span class="token string">'Suicide'</span><span class="token punctuation">)</span>
<span class="token operator">AND</span> <span class="token string">"stats"</span><span class="token punctuation">.</span><span class="token string">"annee"</span> <span class="token operator">BETWEEN</span> <span class="token number">2010</span> <span class="token operator">AND</span> <span class="token number">2013</span>
<span class="token operator">AND</span> <span class="token punctuation">(</span><span class="token string">"op"</span><span class="token punctuation">.</span><span class="token string">"vent_force"</span> <span class="token operator"><=</span> <span class="token number">4</span> <span class="token operator">OR</span> <span class="token string">"op"</span><span class="token punctuation">.</span><span class="token string">"vent_force"</span> <span class="token operator">></span> <span class="token number">7</span><span class="token punctuation">)</span> <span class="token comment">-- Vent jusqu'à 4 ou strictement supérieur à 7</span>
<span class="token operator">AND</span> <span class="token string">"stats"</span><span class="token punctuation">.</span><span class="token string">"mois_texte"</span> <span class="token operator"><></span> <span class="token string">'Juin'</span> <span class="token comment">-- Tous les mois sauf Juin</span>
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">,</span> <span class="token number">3</span> <span class="token comment">-- Aggrège par les colonnes 1, 2 et 3 (année, CROSS et type d'opération)</span>
<span class="token keyword">HAVING</span> <span class="token function">SUM</span><span class="token punctuation">(</span><span class="token string">"stats"</span><span class="token punctuation">.</span><span class="token string">"nombre_personnes_impliquees"</span><span class="token punctuation">)</span> <span class="token operator">>=</span> <span class="token number">10</span> <span class="token comment">-- Ne conserver que les groupes avec au moins 10 personnes impliquées</span>
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token number">1</span> <span class="token keyword">ASC</span><span class="token punctuation">,</span> <span class="token number">2</span> <span class="token keyword">DESC</span> <span class="token comment">-- Tri par l'ordre ascendant la colonne 1 (année) puis descendant par la colonne 2 (CROSS)</span>
</code></pre></div><p>Il est recommandé de suivre la présentation ci-dessus en terme de respect de la casse, espacements et retours à la ligne pour faciliter la lisibilité des requêtes et conserver une cohérence.</p> <h2 id="fonctions-supplementaires-en-sql"><a href="#fonctions-supplementaires-en-sql" aria-hidden="true" class="header-anchor">#</a> Fonctions supplémentaires en SQL</h2> <p>Ici sont présentées quelques fonctionnalités supplémentaires du langage SQL qui peuvent être utiles lors de requêtes spécifiques. La liste complète des fonctionnalités peut être trouvée sur un moteur de recherche, pour une base de données PostgreSQL.</p> <h3 id="annees-glissantes"><a href="#annees-glissantes" aria-hidden="true" class="header-anchor">#</a> Années glissantes</h3> <p>Il est possible dans une requête de récupérer la date courante lors de l'exécution de la requête à l'aide de <code>current_date</code>.</p> <p>Ainsi, une requête pour calculer l'année d'il y a 10 ans peut s'exprimer de la façon suivante :</p> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">-- `current_date` donne la date courante</span>
<span class="token comment">-- `date_part('year', current_date)` donne seulement l'année de la date courante</span>
<span class="token keyword">select</span> <span class="token punctuation">(</span>date_part<span class="token punctuation">(</span><span class="token string">'year'</span><span class="token punctuation">,</span> <span class="token keyword">current_date</span><span class="token punctuation">)</span> <span class="token operator">-</span> <span class="token number">10</span><span class="token punctuation">)</span>
</code></pre></div><p>Cette fonctionnalité permet de calculer des choses sur des années glissantes. Par exemple, le nombre d'opérations SAR/MAS/DIV/SUR/POL pour tous les CROSS par année sur les 10 dernières années :</p> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">SELECT</span>
<span class="token string">"stats"</span><span class="token punctuation">.</span><span class="token string">"annee"</span><span class="token punctuation">,</span>
<span class="token string">"op"</span><span class="token punctuation">.</span><span class="token string">"cross"</span><span class="token punctuation">,</span>
<span class="token string">"op"</span><span class="token punctuation">.</span><span class="token string">"type_operation"</span><span class="token punctuation">,</span>
<span class="token function">count</span><span class="token punctuation">(</span><span class="token string">"op"</span><span class="token punctuation">.</span><span class="token string">"operation_id"</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token string">"nb_operations"</span>
<span class="token keyword">FROM</span> <span class="token string">"operations"</span> <span class="token keyword">AS</span> <span class="token string">"op"</span>
<span class="token keyword">JOIN</span> <span class="token string">"operations_stats"</span> <span class="token keyword">AS</span> <span class="token string">"stats"</span> <span class="token keyword">ON</span> <span class="token string">"stats"</span><span class="token punctuation">.</span><span class="token string">"operation_id"</span> <span class="token operator">=</span> <span class="token string">"op"</span><span class="token punctuation">.</span><span class="token string">"operation_id"</span>
<span class="token keyword">WHERE</span> <span class="token string">"stats"</span><span class="token punctuation">.</span><span class="token string">"annee"</span> <span class="token operator">>=</span> <span class="token punctuation">(</span>date_part<span class="token punctuation">(</span><span class="token string">'year'</span><span class="token punctuation">,</span> <span class="token keyword">current_date</span><span class="token punctuation">)</span> <span class="token operator">-</span> <span class="token number">10</span><span class="token punctuation">)</span>
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">,</span> <span class="token number">3</span>
</code></pre></div><h3 id="modifications-de-la-casse"><a href="#modifications-de-la-casse" aria-hidden="true" class="header-anchor">#</a> Modifications de la casse</h3> <p>Il est possible de passer du texte tout en majuscules ou tout en minuscules avec les commandes <code>UPPER</code> et <code>LOWER</code> respectivement.</p> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token keyword">DISTINCT</span>
<span class="token string">"cross"</span><span class="token punctuation">,</span>
LOWER<span class="token punctuation">(</span><span class="token string">"cross"</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token string">"cross_minuscules"</span><span class="token punctuation">,</span>
UPPER<span class="token punctuation">(</span><span class="token string">"cross"</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token string">"cross_majuscules"</span>
<span class="token keyword">FROM</span> <span class="token string">"operations"</span>
</code></pre></div></div> <div class="page-edit"><!----> <!----></div> <!----> </div> <!----></div></div>
<script src="/secmar-documentation/assets/js/4.7b387fda.js" defer></script><script src="/secmar-documentation/assets/js/9.4f108125.js" defer></script><script src="/secmar-documentation/assets/js/app.b0f9bbc6.js" defer></script>
</body>
</html>