29:null 2a:["$","title",null,{"children":"DuckDB redonne une belle jeunesse au langage SQL | Éric Mauvière | Observable"}] 2f:T679,{ // Cette syntaxe utilise l'injection de paramètres, variante classique en programmation SQL, + sécurisée let data = await db.query(`SELECT preusuel, annais, SUM(nombre) AS eft FROM prenoms2021 WHERE preusuel = ? AND annais <> 'XXXX' GROUP BY ALL ORDER BY annais`, [prenom.preusuel]) // Je recode l'année sous forme d'un vrai type Date, pour que l'axe des x du graphique à venir soit joliment construit data = data.map(d => d.toJSON()).map(d => {d.annais = new Date(d.annais, 0); return d}) const pic = d3.greatest(data, d => d.eft).annais.getFullYear(), // année du pic col = prenom.sexe == '1' ? "firebrick" : "olivedrab", // couleur différenciée garçons/filles prenom_s = getTitleCase(prenom.preusuel) // conversion + élégante majuscule-minuscules du prénom return Plot.plot({ width: Math.min(600, width), marginBottom: 50, x: {label: "Année de naissance", labelAnchor: 'center', labelOffset: 40, tickPadding: 5}, y: {grid: true, tickFormat : d => d.toLocaleString(), label: '', tickSize: 0}, marks: [ Plot.ruleY([0]), Plot.lineY(data, {x: "annais", y: "eft", stroke: col, strokeWidth: 3}), Plot.text(data, Plot.selectMaxY({x: "annais", y: "eft", fill: col, text: d => "Pic " + prenom_s + " : " + pic, textAnchor: pic < 2000 ? "start" : "end", dx: pic < 2000 ? 10 : -10, fontSize: 14, fontWeight: 700})), Plot.dot(data, Plot.selectMaxY({x: "annais", y: "eft", stroke: col, strokeWidth: 2, r: 5})) ] }) }30:T4b4,viewof prenom_search_list = { let s = Inputs.select([{preusuel: 'Tapez plus de 2 lettres ci-dessus...', eft: 0}], {format: d => d.preusuel, label: "Prénoms trouvés"}) if (prenom_search.length > 2) { const sql = `SELECT preusuel, sexe, SUM(nombre) AS eft FROM prenoms2021 WHERE ( contains(lower(strip_accents(preusuel)), lower(strip_accents('${prenom_search}'))) OR (${extend_search.length} = 1 AND levenshtein(lower(strip_accents(preusuel)), lower(strip_accents('${prenom_search}'))) < 2 )) AND annais <> 'XXXX' GROUP BY ALL HAVING(eft > 50) ORDER BY levenshtein(lower(strip_accents(preusuel)), lower(strip_accents('${prenom_search}'))), eft DESC` const sel = await db.query(sql) if (sel.length > 0 ) s = Inputs.select(sel, {format: d => `${getTitleCase(d.preusuel)} (${d.eft.toLocaleString()})`, label: "Prénoms trouvés (" + sel.length + ")"}) } s.style.setProperty('--label-width', "auto") s.style.setProperty('max-width', "350px") return s }31:T55f,{ if (prenom_search_list.eft == 0) { return svg`` } let data = await db.query(`SELECT preusuel, annais, SUM(nombre) AS eft FROM prenoms2021 WHERE preusuel = ? and annais <> 'XXXX' GROUP BY ALL ORDER BY annais`, [prenom_search_list.preusuel]) data = data.map(d => d.toJSON()).map(d => {d.annais = new Date(d.annais, 0); return d}) const pic = d3.greatest(data, d => d.eft).annais.getFullYear(), col = prenom_search_list.sexe == '1' ? "firebrick" : "olivedrab", prenom_s = getTitleCase(prenom_search_list.preusuel) return Plot.plot({ width: Math.min(600, width), marginBottom: 50, x: {label: "Année de naissance", labelAnchor: 'center', labelOffset: 40, tickPadding: 5}, y: {grid: true, tickFormat : d => d.toLocaleString(), label: ''}, marks: [ Plot.ruleY([0]), Plot.lineY(data, {x: "annais", y: "eft", stroke: col, strokeWidth: 3}), Plot.text(data, Plot.selectMaxY({x: "annais", y: "eft", fill: col, text: d => "Pic " + prenom_s + " : " + pic, textAnchor: pic < 2000 ? "start" : "end", dx: pic < 2000 ? 10 : -10, fontSize: 14, fontWeight: 700})), Plot.dot(data, Plot.selectMaxY({x: "annais", y: "eft", stroke: col, strokeWidth: 2, r: 5})) ] }) }32:T637,{ let data = await db.query(`SELECT preusuel, annais, SUM(nombre) AS eft FROM prenoms2021 LEFT JOIN (SELECT codgeo, libgeo, lpad(reg, 2, '0') AS reg FROM dept) d ON prenoms2021.dpt = d.codgeo WHERE preusuel = '${prenom_f.preusuel}' AND annais <> 'XXXX' AND ('${sexe}' = '0' OR sexe = '${sexe}') AND ('${dpt}' = '' OR dpt = '${dpt}') AND ('${reg}' = '' OR reg = '${reg}') GROUP BY 1,2 ORDER BY annais`) data = data.map(d => d.toJSON()).map(d => {d.annais = new Date(d.annais, 0); return d}) const pic = d3.greatest(data, d => d.eft).annais.getFullYear(), col = prenom_f.sexe == '1' ? "firebrick" : "olivedrab", prenom_s = getTitleCase(prenom_f.preusuel) return Plot.plot({ width: Math.min(600, width), marginBottom: 50, x: {label: "Année de naissance", labelAnchor: 'center', labelOffset: 40, tickPadding: 5}, y: {grid: true, tickFormat : d => d.toLocaleString(), label: ''}, marks: [ Plot.ruleY([0]), Plot.lineY(data, {x: "annais", y: "eft", stroke: col, strokeWidth: 3}), Plot.text(data, Plot.selectMaxY({x: "annais", y: "eft", fill: col, text: d => "Pic " + prenom_s + " : " + pic, textAnchor: pic < 2000 ? "start" : "end", dx: pic < 2000 ? 10 : -10, fontSize: 14, fontWeight: 700})), Plot.dot(data, Plot.selectMaxY({x: "annais", y: "eft", stroke: col, strokeWidth: 2, r: 5})) ] }) }33:T509,Ne soyez pas surpris de ne pas retrouver ici absolument tous les prénoms que vous pourriez connaître. L'Insee précise dans [la documentation du fichier](https://www.insee.fr/fr/statistiques/2540004?sommaire=4767262#documentation) qu'il a traité de façon particulière les prénoms les plus rares. Voici un extrait de cette doc : 1. Sur la période allant de 1900 à 1945, le prénom a été attribué au moins 20 fois à des personnes de sexe féminin et/ou au moins 20 fois à des personnes de sexe masculin. 2. Sur la période allant de 1946 à 2021, le prénom a été attribué au moins 20 fois à des personnes de sexe féminin et/ou au moins 20 fois à des personnes de sexe masculin. 3. Pour une année de naissance donnée, le prénom a été attribué au moins 3 fois à des personnes de sexe féminin ou de sexe masculin. Les effectifs des prénoms ne remplissant pas les conditions 1 et 2 sont regroupés (pour chaque sexe et chaque année de naissance) dans un enregistrement dont le champ prénom (preusuel) prend la valeur «_PRENOMS_RARES». Les effectifs des prénoms remplissant la condition 2 mais pas la condition 3 sont regroupés (pour chaque sexe et chaque prénom) dans un enregistrement dont le champ année de naissance (annais) prend la valeur «XXXX».34:T419,credits = md`

Code R

~~~r library(curl); library(tidyverse); library(arrow); zipfile = curl_download("https://www.insee.fr/fr/statistiques/fichier/2540004/dpt2021_csv.zip", tempfile()) csvfile = unzip(zipfile) prenoms2021 = read_delim(csvfile, col_types = list(sexe = 'c')) # A tibble: 3,784,673 x 5 sexe preusuel annais dpt nombre 1 1 _PRENOMS_RARES 1900 02 7 2 1 _PRENOMS_RARES 1900 04 9 3 1 _PRENOMS_RARES 1900 05 8 4 1 _PRENOMS_RARES 1900 06 23 5 1 _PRENOMS_RARES 1900 07 9 6 1 _PRENOMS_RARES 1900 08 4 7 1 _PRENOMS_RARES 1900 09 6 8 1 _PRENOMS_RARES 1900 10 3 9 1 _PRENOMS_RARES 1900 11 11 10 1 _PRENOMS_RARES 1900 12 7 # ... with 3,784,663 more rows write_parquet(prenoms2021 |> mutate(across(where(is.character), as.factor)), 'data/prenoms2021.parquet') ~~~ `35:T470,viewof prenom_search_list2 = { let s = Inputs.select([{preusuel: 'Tapez plus de 2 lettres ci-dessus...', eft: 0}], {format: d => d.preusuel, label: "Prénoms trouvés"}) if (prenom_search2.length > 2) { const sql = `SELECT preusuel, SUM(nombre) AS eft FROM prenoms2021 WHERE ( contains(lower(strip_accents(preusuel)), lower(strip_accents('${prenom_search2}'))) OR ${extend_search.length} = 1 AND levenshtein(lower(strip_accents(preusuel)), lower(strip_accents('${prenom_search2}'))) < 2 ) AND annais <> 'XXXX' GROUP BY 1 HAVING(eft > 50) ORDER BY levenshtein(lower(strip_accents(preusuel)), lower(strip_accents('${prenom_search2}'))), eft DESC` const sel = await db.query(sql) if (sel.length > 0 ) s = Inputs.select(sel, {format: d => `${getTitleCase(d.preusuel)} (${d.eft.toLocaleString()})`, label: "Prénoms trouvés (" + sel.length + ")"}) } s.style.setProperty('--label-width', "auto") s.style.setProperty('max-width', "350px") return s }36:T9d6,map_svg = { const map_svg = svg`` const path2154 = d3.geoPath(proj_e4326_to_map_e2154), col = 'steelblue' d3.select(map_svg).select('.fra').selectAll('path') .data([a_fra]) .join('path') .attr('d', path2154) .attr("fill", '#F5F5F5') .attr("stroke", '#444') d3.select(map_svg).select('.depts').selectAll('path') .data([l_dep]) .join('path') .attr('d', path2154) .attr("stroke", '#CCC') .attr("fill", 'none') d3.select(map_svg).select('.regs').selectAll('path') .data([l_reg]) .join('path') .attr('d', path2154) .attr("stroke", '#999') .attr("fill", 'none') if (data_prenoms.rowmax) { d3.select(map_svg).select('.key') .append('circle') .attr('r', rdmax) .attr('cx', width/10) .attr('cy', width/2) .attr("stroke", '#FFF') .attr("fill", 'steelblue') d3.select(map_svg).select('.key') .append('text') .attr('x', width/10) .attr('y', width/2 - rdmax - 5) .attr('text-anchor', 'middle') .text(data_prenoms.rowmax.eft.toLocaleString('fr', {maximumSignificantDigits: 2})) } return Object.assign(map_svg, { update: function(an) { d3.select(map_svg).select('.ronds').selectAll('circle') .data(data_ronds(an), d => d.dpt) .join('circle') .attr('r', d => d.r) .attr('cx', d => maps_centroids.map_x.get(d.dpt)) .attr('cy', d => maps_centroids.map_y.get(d.dpt)) .attr("stroke", '#FFF') .attr("fill", col) .append('title') .text(d => d.eft.toLocaleString() + " " + getTitleCase(prenom_search_list2.preusuel) + " en " + an + " (dept " + d.dpt + ")" ) return an } }) }37:[{"id":"0194d9d973de3ef8c5a776bb5fe91ca68e3489c615c8dee3fcbe2e178524a70a332dd2f348f7bdd40c6fd2adaf66f7da4dd063d6e8e009bb32d6912fc65a22b4","href":"https://static.observableusercontent.com/files/0194d9d973de3ef8c5a776bb5fe91ca68e3489c615c8dee3fcbe2e178524a70a332dd2f348f7bdd40c6fd2adaf66f7da4dd063d6e8e009bb32d6912fc65a22b4","name":"dept2020.csv","type":"text/csv","lastModified":1668459346950.927,"size":1768},{"id":"f9ccfe14744ced7fac904bbee1324ea14f46e0eeaaf03a73601f4e7d1432019d0e53f78d2ba5d9ff1b06f22be068c5ec88d67060dc3e473fdf5a237db5b62ccc","href":"https://static.observableusercontent.com/files/f9ccfe14744ced7fac904bbee1324ea14f46e0eeaaf03a73601f4e7d1432019d0e53f78d2ba5d9ff1b06f22be068c5ec88d67060dc3e473fdf5a237db5b62ccc","name":"reg2020.csv","type":"text/csv","lastModified":1668459695972.916,"size":343},{"id":"1ee81194ae045bd613c6218563460a2a59a78f7681da873fe2f052b63a1e0bfd628d569364c6c88ea2a1a2944ecdb983c6fa83da217fd89e4d644f91ebd17f61","href":"https://static.observableusercontent.com/files/1ee81194ae045bd613c6218563460a2a59a78f7681da873fe2f052b63a1e0bfd628d569364c6c88ea2a1a2944ecdb983c6fa83da217fd89e4d644f91ebd17f61","name":"prenoms2021_f.parquet","type":"application/octet-stream","lastModified":1668500622395.137,"size":7653508},{"id":"ec76bcab43aef11fbdb68f633e6570eba2dd6189ab507c6ad32f0a8185aa632bc85df8bb5daf9caa3b2ef4d6c7c05b3ac4eb533d9a97408f4395dfe30896d0e7","href":"https://static.observableusercontent.com/files/ec76bcab43aef11fbdb68f633e6570eba2dd6189ab507c6ad32f0a8185aa632bc85df8bb5daf9caa3b2ef4d6c7c05b3ac4eb533d9a97408f4395dfe30896d0e7","name":"a_dpt_topo.json","type":"application/json","lastModified":1668615621002.672,"size":524735},{"id":"1a06e850beddd09d1a06c6875e4efdaa330afb6ef59c87765e1603ca76c7fa6fac4cd022d883d39eb785a0229dc950f0d942c0ef5d18a97fb7e0f996714ba4bc","href":"https://static.observableusercontent.com/files/1a06e850beddd09d1a06c6875e4efdaa330afb6ef59c87765e1603ca76c7fa6fac4cd022d883d39eb785a0229dc950f0d942c0ef5d18a97fb7e0f996714ba4bc","name":"dep2020.csv","type":"text/csv","lastModified":1668695152921.115,"size":1781},{"id":"9259a0519bb64602e49aed0a159070931c90e3c003694db7e029e19136da8a785863b88fef9f7c860562aabaa9ee2863d9f239b7c876a30d04fde4778d4ddaaf","href":"https://static.observableusercontent.com/files/9259a0519bb64602e49aed0a159070931c90e3c003694db7e029e19136da8a785863b88fef9f7c860562aabaa9ee2863d9f239b7c876a30d04fde4778d4ddaaf","name":"reg2021.csv","type":"text/csv","lastModified":1668867284445.176,"size":343},{"id":"133686eda66df8b3804668fb3ff22ce57e12048e3a8a8b5ade90001b6e0f97a5370c5a5c67dd157a73578ff524b60c37c56d549378980c93cb7dc7e36c7f39b4","href":"https://static.observableusercontent.com/files/133686eda66df8b3804668fb3ff22ce57e12048e3a8a8b5ade90001b6e0f97a5370c5a5c67dd157a73578ff524b60c37c56d549378980c93cb7dc7e36c7f39b4","name":"dep2021.csv","type":"text/csv","lastModified":1668867284445.429,"size":1781}] 2d:{"id":"29f9f5cd9d2ff909","slug":"duckdb-redonne-nouvelle-vie-sql","title":"DuckDB redonne une belle jeunesse au langage SQL","thumbnail":"dc1aa86aca51ade3ff4330e95c9af8cb628273806543987bb7652c6e0e665444","access_level":"public","create_time":"$D2022-11-14T15:53:44.592Z","update_time":"$D2023-07-19T11:14:55.943Z","view_time":null,"first_public_time":"$D2022-11-14T20:24:40.153Z","has_unpublished_edits":null,"fork":false,"stars":20,"starred":false,"owner":{"type":"team","id":"cd09b02ddc62abda","login":"ericmauviere","name":"Éric Mauvière","avatar_url":"https://avatars-next.observableusercontent.com/a35a91b5ca23c5dd084a628d6c4898fb6eefb2ede192b1801b0efae516f49017","tier":"starter_2024"},"version":1337,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"c86281fcf8f7c7cc","login":"ericmauviere","name":"Éric Mauvière","avatar_url":"https://avatars-next.observableusercontent.com/a35a91b5ca23c5dd084a628d6c4898fb6eefb2ede192b1801b0efae516f49017","tier":null}],"members":[{"type":"individual","id":"c86281fcf8f7c7cc","login":"ericmauviere","name":"Éric Mauvière","avatar_url":"https://avatars-next.observableusercontent.com/a35a91b5ca23c5dd084a628d6c4898fb6eefb2ede192b1801b0efae516f49017","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":true,"trashed":false,"lapsed":null,"subscribed":null,"forks":10,"collection":null,"type":"notebook","body":{"title":"DuckDB redonne une belle jeunesse au langage SQL","cells":[{"id":0,"value":"# DuckDB redonne une belle jeunesse au langage SQL","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":602,"value":"[English version here](https://observablehq.com/@ericmauviere/duckdb-gives-new-life-to-sql)\n\nDe vraies bases de données SQL dans votre navigateur, compatibles avec les nouveaux formats Arrow et Parquet ? 📣 C'est désormais possible avec DuckDB, et de superbe manière !\n\nDémontrons-le avec un exemple concret : l'Insee met à disposition le [fichier domicilié des prénoms attribués aux enfants nés en France](https://www.insee.fr/fr/statistiques/2540004?sommaire=4767262) dans un format CSV (zippé). \n\nCette table est riche, elle comprend 3 800 000 enregistrements dans sa version publiée en juin 2022, couvrant les années de naissance de 1900 à 2021. En voici un extrait :","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1006,"value":"Inputs.table(extrait, {height: 200, layout: \"auto\", maxWidth: 500})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1014,"value":"Le fichier CSV pèse 80 Mo, sa version zippée 13 Mo. Cette table se manie bien avec R. Mais comment faire avec un navigateur ? \nLa réponse, c'est l'extraordinaire [DuckDB](https://duckdb.org/docs/) !\n\n**Je vais charger avec DuckDB une version optimisée de ce jeu de données, au format [Apache Parquet](https://parquet.apache.org/)** : elle est plus compacte (8 Mo) et bien plus rapide à lire car Parquet est un format intelligent, binaire, compressé et \"orienté colonnes\". Il répond très rapidement aux requêtes en lecture seule. \n\n*La conversion CSV => Parquet est facile à réaliser dans R (cf. fin de ce document).* \n\nDifférentes librairies JavaScript peuvent lire le format Parquet, on privilégie ici DuckDB, dont la version pour JS est maintenant [intégrée nativement à Observable](https://observablehq.com/@observablehq/duckdb).","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":265,"value":"## DuckDB révolutionne le traitement des données dans le navigateur","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":741,"value":"DuckDB présente plein d'avantages : il permet de constituer, en mémoire dans le navigateur, une véritable base de données dont les tables proviennent de différentes sources de données (CSV, JSON, Parquet, Arrow...) Cette base se travaille avec le langage SQL, et en l'occurrence **une version moderne et enrichie de ce bon vieux SQL, bourrée de compléments très astucieux**.\n\nJe constitue ici une base **`db`** comprenant trois tables, une issue d'un format Parquet (notre base des prénoms), deux autres de fichiers CSV (ces trois fichiers sources sont *attachés* au présent document).","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":7,"value":"db = DuckDBClient.of({\n prenoms2021: FileAttachment(\"prenoms2021_f.parquet\"),\n dept: {\n file: FileAttachment(\"dep2021.csv\"),\n header: true\n },\n reg: {\n file: FileAttachment(\"reg2021.csv\"),\n header: true\n }\n})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":748,"value":"Ca va plutôt vite pour une base d'une telle richesse ! \n\n**La version cliente de DuckDb pour JS est *pré-compilée* en Web Assembly (WASM)**, donc sous forme d'un code de bas niveau directement exécutable par le navigateur, bien plus performant que le JavaScript classique. Chargements initiaux et requêtes ultérieures sont spectaculairement accélérés. \n\nLe format Parquet y contribue : précurseur de son cousin [Arrow](https://arrow.apache.org/), sa structure facilite le chargement des données en mémoire, pratiquement sans transformation. En réalité, [DuckDB lit le fichier Parquet directement](https://duckdb.org/2021/06/25/querying-parquet.html), sans import ni conversion.\n\nVoici à nouveau un aperçu de la table des prénoms, c'est la traduction d'une première requête SQL :","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":363,"value":"viewof extrait = {\n const data = await db.query(`SELECT * FROM prenoms2021 \n WHERE preusuel <> '_PRENOMS_RARES' AND annais <> 'XXXX'\n USING SAMPLE 100`)\n \n // L'usage des backticks `` permet d'écrire une requête SQL sur plusieurs lignes\n // Écrire les verbes SQL en majuscules n'est pas obligatoire, mais c'est plus joli !\n \n return Inputs.table(data, {height: 200, layout: \"auto\", maxWidth: 500})\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":779,"value":"**db.query()** exécute le SQL et renvoie un jeu de données JavaScript (un _itérable_), que j'affiche avec un composant standard d'Observable. (*On peut aussi utiliser un composant plus riche dénommé [SQL Cell](https://observablehq.com/@observablehq/sql-cell), mais il est consommateur en ressources, je le laisse de côté ici*).\n\n## Courbes d'évolution pour les 50 prénoms les plus donnés\n\nLa liste déroulante suivante résulte d'une deuxième requête SQL. **C'est un vrai bonheur de simplicité d'écriture**, que je vous laisse découvrir et adapter en déroulant le code via la flèche à gauche de la liste :","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":29,"value":"viewof prenom = {\n const sel = await db.query(\n `SELECT preusuel, sexe, SUM(nombre) AS eft FROM prenoms2021 \n WHERE preusuel <> '_PRENOMS_RARES' \n GROUP BY ALL \n ORDER BY eft desc \n LIMIT 50`\n )\n\n // Constitution de la liste déroulante avec prénom et effectif\n const s = Inputs.select(sel, {\n format: d => `${getTitleCase(d.preusuel)} (${d.eft.toLocaleString()})`,\n label: \"Prénom\"\n })\n\n s.style.setProperty('--label-width', \"auto\")\n s.style.setProperty('max-width', \"300px\")\n\n return s\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":813,"value":"Le prénom choisi dans la liste évolue au fil des années de la façon suivante, avec une subtile mise en évidence du pic permise par l'épatante librairie Observable Plot :","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":97,"value":"$2f","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":726,"value":"## Recherche libre de prénoms","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":840,"value":"DuckDB fournit d'[astucieuses fonctions de traitement de textes](https://duckdb.org/docs/sql/functions/char). Elles permettent par exemple de s'affranchir des accents, et donc d'ouvrir plus largement le champ d'une recherche sur tout ou partie d'un prénom :","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":645,"value":"viewof input = html``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":657,"value":"$30","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1098,"value":"viewof extend_search = Inputs.checkbox([\"Élargir la recherche à de petites variantes orthographiques\"])","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":849,"value":"Les échos de cette liste sont élégamment triés grâce à la [distance de Levenshtein](https://fr.wikipedia.org/wiki/Distance_de_Levenshtein), qui mesure l'écart entre deux mots (nombre d'opérations sur les lettres pour passer de l'un à l'autre). \n\nUn autre exemple de l'intérêt de Levenshtein : si vous cochez la case ci-dessus \"Élargir la recherche...\", et tapez \"antony\", vous ciblerez aussi les Anthony (il se reconnaitra) !","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":712,"value":"$31","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":689,"value":"prenom_search = debounce(viewof input)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":410,"value":"## Requête multi-paramètres","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":853,"value":"Pour profiter pleinement de la richesse de cette table des prénoms, multiplions les critères de filtrage. \n\nJ'utilise les deux autres tables, nomenclature des départements et régions de France, pour agrémenter l'apparence des listes et permettre d'agréger au niveau régional.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":951,"value":"viewof sexe = {\n \n const s = Inputs.radio(\n new Map([[\"indifférent\", \"0\"], [\"masculin\", \"1\"], [\"féminin\", \"2\"]]),\n {value: \"0\", label: \"Sexe\", format: ([name, value]) => `${name}`}\n )\n\n s.style.setProperty('--label-width', \"145px\")\n s.style.setProperty('max-width', \"500px\")\n\n return s\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":374,"value":"viewof annais = {\n\n const sql = `SELECT DISTINCT annais FROM prenoms2021 \n WHERE annais <> 'XXXX' \n ORDER BY 1`\n \n const sel = await db.query(sql)\n \n const s = Inputs.select(sel.map(d => d.annais), {\n label: \"Année de naissance >= \"\n })\n\n s.style.setProperty('--label-width', \"150px\")\n s.style.setProperty('max-width', \"400px\")\n\n return s\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":977,"value":"viewof reg = {\n\n const sql = `SELECT lpad(codgeo, 2, '0') AS codgeo, libgeo FROM reg \n ORDER BY codgeo`\n \n const sel = await db.query(sql)\n\n const s = Inputs.select([{codgeo: ''}].concat(sel), {\n label: \"Région de naissance \", format: d => d.codgeo == '' ? 'Toutes' : (d.codgeo + \" - \" + d.libgeo), valueof: d => d.codgeo\n })\n \n s.style.setProperty('--label-width', \"150px\")\n s.style.setProperty('max-width', \"400px\")\n\n return s\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":386,"value":"viewof dpt = {\n\n let sql = `SELECT * FROM (SELECT DISTINCT dpt FROM prenoms2021 WHERE dpt <>'XX') p \n LEFT JOIN \n (SELECT codgeo, libgeo, lpad(reg, 2, '0') AS reg FROM dept) d \n ON p.dpt = d.codgeo \n WHERE ('' = '${reg}' OR reg = '${reg}')\n ORDER BY dpt`\n // Les deux départements de Corse sont regroupés dans la base avec un code 20\n \n const sel = await db.query(sql)\n \n const s = Inputs.select([{codgeo: ''}].concat(sel), {\n label: \"Département de naissance \", format: d => d.codgeo == '' ? 'Tous' : (d.codgeo + \" - \" + d.libgeo), valueof: d => d.codgeo\n })\n \n s.style.setProperty('--label-width', \"150px\")\n s.style.setProperty('max-width', \"400px\")\n\n return s\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":313,"value":"viewof prenom_f = {\n \n let sql = `SELECT preusuel, sexe, SUM(nombre) as eft FROM prenoms2021 \n LEFT JOIN (SELECT codgeo, libgeo, lpad(reg, 2, '0') AS reg FROM dept) d \n ON prenoms2021.dpt = d.codgeo\n WHERE preusuel <> '_PRENOMS_RARES' AND annais <> 'XXXX' `\n \n if (sexe != '0') {\n sql += ` AND sexe = '${sexe}'`\n }\n \n if (annais != '1900') {\n sql += ` AND annais > '${annais}'`\n }\n\n if (reg != '') {\n sql += ` AND reg = '${reg}' `\n }\n \n if (dpt != '') {\n sql += ` AND dpt = '${dpt}' `\n }\n\n sql += `GROUP BY 1, 2 \n ORDER BY 3 DESC \n LIMIT 50`\n\n const sel = await db.query(sql)\n \n const s = Inputs.select(sel, {format: (d) => `${getTitleCase(d.preusuel)} (${d.eft.toLocaleString()})`,\n label: \"Prénom\"\n })\n \n s.style.setProperty('--label-width', \"150px\")\n s.style.setProperty('max-width', \"400px\")\n\n return s\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":346,"value":"$32","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1274,"value":"## Prénoms les plus donnés par région et année","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1277,"value":"viewof annais_reg = {\n const s = Inputs.select(d3.range(1900, 2022).map(d => d.toString()), {\n label: \"Année de naissance \", value: \"2021\"\n })\n\n s.style.setProperty('--label-width', \"auto\")\n s.style.setProperty('max-width', \"200px\")\n\n return s\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1278,"value":"viewof sexe_reg = {\n \n const s = Inputs.radio(\n new Map([[\"masculin\", \"1\"], [\"féminin\", \"2\"]]),\n {value: \"1\", label: \"Sexe\", format: ([name, value]) => `${name}`}\n )\n\n s.style.setProperty('--label-width', \"auto\")\n s.style.setProperty('max-width', \"500px\")\n\n return s\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1279,"value":"{\n const data = await db.query(\n `SELECT reg || ' - '|| libgeo as Région, arg_max(preusuel, nombre) as '1er prénom' FROM \n ( SELECT reg, preusuel, SUM(nombre) AS nombre \n FROM prenoms2021 left join (SELECT codgeo, libgeo, lpad(reg, 2, '0') AS reg FROM dept) d\n ON prenoms2021.dpt = d.codgeo\n WHERE annais = ? AND sexe = ? AND preusuel <> '_PRENOMS_RARES' \n GROUP BY ALL \n ) p LEFT JOIN reg ON p.reg = reg.codgeo\n GROUP BY ALL ORDER BY ALL`, \n [annais_reg, sexe_reg])\n\n return Inputs.table(data.map(d => d.toJSON())\n .map(d => {d['1er prénom'] = getTitleCase(d['1er prénom']); return d}), {height: 440, layout: \"auto\", maxWidth: 400})\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1285,"value":"Cette liste s'obtient, en SQL DuckDB, par une requête _relativement simple_ grâce à la fonction magique ✨ `arg_max()` !","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1283,"value":"## Doc sur la base des prénoms","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":857,"value":"$33","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":864,"value":"## Comment convertir un fichier CSV au format Parquet","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":866,"value":"5 lignes de code R ont suffi à télécharger le zip de l'Insee et à en convertir le contenu au format Parquet. Alors ne vous privez pas d'utiliser ce format !","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":895,"value":"$34","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":947,"value":"Parquet stocke chaque colonne de données séparément, et décrit finement les caractéristiques de chaque colonne. Pour les types caractères par exemple, un _dictionnaire_ liste toutes les modalités distinctes, par exemple chaque code département de la colonne `dpt`.\n\n**Dans R, cela rejoint la notion de _factors_**. Convertir les colonnes de type caractère en factors accélère les futures requêtes sur le fichier Parquet exporté.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":903,"value":"## Ressources diverses","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":905,"value":"Peu de choses à charger manuellement jusqu'ici, DuckDB, Plot, ou la librairie de composants Input font partie d'Observable !","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":590,"value":"function getTitleCase(str) {\n return str.split('-').map(d => d.charAt(0).toUpperCase() + d.slice(1).toLowerCase()).join('-')\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":649,"value":"import {debounce} from \"@mbostock/debouncing-input\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1192,"value":"## Annexe cartographique","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1193,"value":"Cette dernière section anime la carte d'un prénom en prenant comme référence l'année du pic. On constate que là encore DuckDB est au rendez-vous de la fluidité.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1194,"value":"viewof input2 = html``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1195,"value":"$35","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1196,"value":"viewof an = Scrubber(d3.range(1900,2022), {initial: data_prenoms.an_pic_i, autoplay: false, loop: false, delay: 50})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1197,"value":"viewof rdmax = Inputs.range([20, 100], {label: \"Rayon maximum\", step: 5, value: width/30})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1198,"value":"$36","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1218,"value":"_Note : la Corse est fusionnée dans la base des prénoms sous le code \"20\"_","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1225,"value":"**Logique fonctionnelle**","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1199,"value":"data_ronds = function(an) {\n const alpha = rdmax / Math.sqrt(data_prenoms.rowmax.eft)\n \nreturn data_prenoms.data.filter(d => d.annais == an)\n .sort((a,b) => d3.descending(a.eft, b.eft))\n .map(d => d.toJSON())\n .map(d => {return {dpt: d.dpt, r: alpha * Math.sqrt(d.eft), eft: d.eft}})\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1200,"value":"data_prenoms = {\n if (prenom_search_list2.eft == 0) {\n return {data: [], rowmax: null, an_pic_i: 0}\n }\n \n let data = await db.query(`SELECT dpt, annais, SUM(nombre) AS eft FROM prenoms2021\n WHERE preusuel = ? AND annais <> 'XXXX'\n GROUP BY GROUPING SETS ((dpt, annais), (annais))\n ORDER BY dpt, annais`, \n [prenom_search_list2.preusuel])\n\n const row_an_max = d3.greatest(data.filter(d => d.dpt === null), d => d.eft)\n \n data = data.filter(d => d.dpt !== null)\n \n const row_dpt_an_max = d3.greatest(data, d => d.eft)\n\n return {data: data, rowmax: row_dpt_an_max, an_pic_i: +row_an_max.annais - 1900}\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1201,"value":"{\n if (prenom_search_list2.eft > 0) map_svg.update(an)\n\n return \"maj de la carte\"\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1202,"value":"maps_centroids = {\n const coords = a_dpt.features.map(d => [d.properties.dep].concat(proj_e4326_to_map_e2154([d.properties.xcl4326, d.properties.ycl4326])))\n\n coords.push(['20'].concat(proj_e4326_to_map_e2154([9.12, 42.15]))) // 20 = Corse, ajout manuel du centroïde\n\n const map_x = new Map( coords.map(d => [d[0], Math.round(d[1])]))\n const map_y = new Map( coords.map(d => [d[0], Math.round(d[2])]))\n\n return {map_x, map_y}\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1203,"value":"prenom_search2 = debounce(viewof input2)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1229,"value":"**Paramètres et ressources externes**","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1204,"value":"map_extent = [\n [margin, margin],\n [size.width - margin, size.height - margin]\n ]","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1205,"value":"size = {\n const d = Math.min(window.screen.width, window.screen.height)\n const w = Math.min(width, 900, d) \n const h = w\n\n return {width: w, height: h}\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1206,"value":"margin = 15","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1207,"value":"proj_e4326_to_map_e2154 = proj4d3(2154).fitExtent(map_extent, a_dpt)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1209,"value":"a_dpt_topo = FileAttachment('a_dpt_topo.json').json()","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1208,"value":"a_dpt = topojson.feature(a_dpt_topo, a_dpt_topo.objects.a_dpt_topo)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1210,"value":"a_fra = topojson.merge(a_dpt_topo, a_dpt_topo.objects.a_dpt_topo.geometries)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1211,"value":"l_dep = topojson.mesh(a_dpt_topo, a_dpt_topo.objects.a_dpt_topo, \n (a, b) => a !== b )","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1212,"value":"l_reg = topojson.mesh(a_dpt_topo, a_dpt_topo.objects.a_dpt_topo, \n (a, b) => a.properties.reg !== b.properties.reg )","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1213,"value":"proj4 = {\n let proj4 = await require(\"proj4\")\n let epsg2154 = \"+proj=lcc +lat_1=49 +lat_2=44 +lat_0=46.5 +lon_0=3 +x_0=700000 +y_0=6600000 +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +units=m +no_defs\"\n \n proj4.defs([ ['EPSG:2154', `+title=France Lambert93 ${epsg2154}`] ])\n \n return proj4\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1214,"value":"function proj4d3(proj4string) {\n let raw\n \n if (+proj4string == proj4string) \n raw = proj4('EPSG:' + proj4string)\n \n if (!raw)\n raw = proj4(proj4string)\n \n const degrees = 180 / Math.PI,\n radians = 1 / degrees,\n p = function(lambda, phi) {\n return raw.forward([lambda * degrees, phi * degrees])\n }\n p.invert = function(x, y) {\n return raw.inverse([x, y]).map(function(d) {\n return d * radians\n })\n }\n const projection = d3.geoProjection(p).scale(1)\n projection.raw = raw\n return projection\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1215,"value":"import {Scrubber} from \"@mbostock/scrubber\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W37","stdlib":"1","titling":"auto"}} 2e:"$2d:body"