{"id":1735,"date":"2013-08-09T12:57:09","date_gmt":"2013-08-09T10:57:09","guid":{"rendered":"http:\/\/www.blue-bears.com\/blog\/?p=1735"},"modified":"2013-08-10T18:57:25","modified_gmt":"2013-08-10T16:57:25","slug":"analyse-des-performances-des-opcvm-req-sql","status":"publish","type":"post","link":"http:\/\/www.blue-bears.com\/blog\/?p=1735","title":{"rendered":"Analyse des performances des OPCVM (REQ SQL)"},"content":{"rendered":"<p>Les requ\u00eates SQL utilis\u00e9es &#8230;<!--more--><\/p>\n<ul>\n<li>\n<h1>Calcul du nombre d&rsquo;enregistrements par OPCVM<\/h1>\n<\/li>\n<\/ul>\n<p>SELECT<\/p>\n<p>mantiquore_valeurs.libelle_valeur,<br \/>\nmantiquore_valeurs.code_isin,<br \/>\nmantiquore_valeurs.ident_type,<br \/>\nmantiquore_valeurs.ident_code_pays,<br \/>\nmantiquore_valeurs.ref_valeur,<\/p>\n<p>COUNT(BB_histo.datas_value)<\/p>\n<p>FROM<\/p>\n<p>BB_histo, mantiquore_valeurs<\/p>\n<p>WHERE<br \/>\nBB_histo.valeurs_ref = mantiquore_valeurs.ref_valeur<br \/>\nAND mantiquore_valeurs.ident_type = &lsquo;OPCVM&rsquo;<br \/>\nAND BB_histo.champs_ref &gt; 37628<\/p>\n<p>GROUP BY mantiquore_valeurs.ref_valeur<\/p>\n<p>ORDER BY COUNT( BB_histo.datas_value ) DESC<\/p>\n<ul>\n<li>\n<h1>Classement des Rendements<\/h1>\n<\/li>\n<\/ul>\n<p>SELECT<br \/>\nmantiquore_valeurs.libelle_valeur,<br \/>\nmantiquore_valeurs.code_isin,<br \/>\nmantiquore_valeurs.ref_valeur,<br \/>\nBB_histo.champs_ref,<br \/>\nBB_histo.datas_value<br \/>\nFROM<br \/>\nBB_histo, mantiquore_valeurs<br \/>\nWHERE<br \/>\nBB_histo.valeurs_ref = mantiquore_valeurs.ref_valeur<br \/>\nAND mantiquore_valeurs.ident_type = &lsquo;OPCVM&rsquo;<br \/>\nAND BB_histo.champs_ref &gt; 37628<br \/>\nORDER BY BB_histo.datas_value\u00a0 DESC<\/p>\n<ul>\n<li>\n<h1>Selection des rendements &gt; 28% et &lt;-28%<\/h1>\n<\/li>\n<\/ul>\n<p>SELECT<br \/>\nmantiquore_valeurs.libelle_valeur,<br \/>\nmantiquore_valeurs.code_isin,<br \/>\nmantiquore_valeurs.ref_valeur,<br \/>\nBB_histo.champs_ref,<br \/>\nBB_histo.datas_value<br \/>\nFROM<br \/>\nBB_histo, mantiquore_valeurs<br \/>\nWHERE<br \/>\nBB_histo.valeurs_ref = mantiquore_valeurs.ref_valeur<br \/>\nAND mantiquore_valeurs.ident_type = &lsquo;OPCVM&rsquo;<br \/>\nAND BB_histo.champs_ref &gt; 37628<br \/>\nAND (BB_histo.datas_value &gt; 0.28 OR BB_histo.datas_value &lt; -0.28)<br \/>\nORDER BY BB_histo.datas_value\u00a0 DESC<\/p>\n<ul>\n<li><strong>Mise \u00e0 jour des Rendements \u00e0 0<\/strong><\/li>\n<\/ul>\n<p>UPDATE<br \/>\nBB_histo, mantiquore_valeurs<br \/>\nSET BB_histo.datas_value = 0<br \/>\nWHERE<br \/>\nBB_histo.valeurs_ref = mantiquore_valeurs.ref_valeur<br \/>\nAND mantiquore_valeurs.ident_type = &lsquo;OPCVM&rsquo;<br \/>\nAND BB_histo.champs_ref &gt; 37628<br \/>\nAND (BB_histo.datas_value &gt; 0.28 OR BB_histo.datas_value &lt; -0.28)<\/p>\n<ul>\n<li><strong>Extraction des valeurs de la liste 202<\/strong><\/li>\n<\/ul>\n<p>SELECT mantiquore_valeurs.code_isin, mantiquore_valeurs.code_factset, mantiquore_valeurs.libelle_valeur, BB_liste_valeurs.ref_liste, BB_liste_valeurs.ref_val<\/p>\n<p>FROM BB_liste_valeurs,mantiquore_valeurs<\/p>\n<p>WHERE BB_liste_valeurs.ref_val=mantiquore_valeurs.ref_valeur<br \/>\nAND BB_liste_valeurs.ref_liste = 202<\/p>\n<p>ORDER BY mantiquore_valeurs.code_isin ASC<\/p>\n<ul>\n<li><strong>Compte le nombre de valeur dans chaque listes<\/strong><\/li>\n<\/ul>\n<p>SELECT<br \/>\n`BB_liste_valeurs`.`ref_liste`,<br \/>\nCOUNT(`BB_liste_valeurs`.`ref_val`)<\/p>\n<p>FROM `BB_liste_valeurs`<\/p>\n<p>GROUP BY (`ref_liste`)<br \/>\nORDER BY COUNT(`BB_liste_valeurs`.`ref_val`)DESC<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Les requ\u00eates SQL utilis\u00e9es &#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17],"tags":[],"class_list":["post-1735","post","type-post","status-publish","format-standard","hentry","category-informatique"],"_links":{"self":[{"href":"http:\/\/www.blue-bears.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1735","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.blue-bears.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.blue-bears.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.blue-bears.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.blue-bears.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1735"}],"version-history":[{"count":14,"href":"http:\/\/www.blue-bears.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1735\/revisions"}],"predecessor-version":[{"id":1765,"href":"http:\/\/www.blue-bears.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1735\/revisions\/1765"}],"wp:attachment":[{"href":"http:\/\/www.blue-bears.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1735"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.blue-bears.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1735"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.blue-bears.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1735"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}