{"id":1242,"date":"2018-02-12T14:33:41","date_gmt":"2018-02-12T13:33:41","guid":{"rendered":"http:\/\/130.61.50.57\/?p=1242"},"modified":"2018-02-12T23:10:02","modified_gmt":"2018-02-12T22:10:02","slug":"e-day","status":"publish","type":"post","link":"https:\/\/blog.tiran.stream\/?p=1242","title":{"rendered":"e Day!"},"content":{"rendered":"

Un peu plus t\u00f4t dans le mois, j’ai d\u00e9couvert le e-day<\/a>!<\/p>\n

e \u00e9tant le nombre d’Euler<\/a>: 2.718<\/p>\n

Cette ann\u00e9e, le 7 f\u00e9vrier 2018 – soit 2\/7\/18 au format am\u00e9ricain – la date reprenait les 4 premi\u00e8res d\u00e9cimales de e!<\/p>\n

En clin d\u2019\u0153il, je me suis demand\u00e9 comment r\u00e9aliser une approximation probabiliste de e en SQL. Cf article similaire du pi-day<\/a>!<\/p>\n

Le thread https:\/\/stats.stackexchange.com\/questions\/193990\/approximate-e-using-monte-carlo-simulation<\/a>\u00a0d\u00e9taille plusieurs m\u00e9thodes.<\/p>\n

Je suis parvenu \u00e0 la formulation suivante \u00e0 partir de l’algorithme principal indiqu\u00e9 dans le thread:<\/p>\n

SQL> set timing on\r\nSQL> WITH\r\n  2      src\r\n  3      AS\r\n  4          (    SELECT LEVEL id, TRUNC (LEVEL \/ 10) grp, DBMS_RANDOM.VALUE val\r\n  5                 FROM DUAL\r\n  6           CONNECT BY LEVEL < 1e5),\r\n  7      interim\r\n  8      AS\r\n  9          (  SELECT grp, COUNT (*) + 1 cnt\r\n 10               FROM (SELECT grp,\r\n 11                            SUM (val) OVER (PARTITION BY grp ORDER BY id) rollsum\r\n 12                       FROM src)\r\n 13              WHERE rollsum < 1\r\n 14           GROUP BY grp)\r\n 15  SELECT AVG (cnt)\r\n 16    FROM interim;\r\n\r\n  AVG(CNT)\r\n----------\r\n    2.7149\r\n\r\nElapsed: 00:00:01.11\r\nSQL>\r\n<\/pre>\n
Une formule alternative – mais dont je ne comprends pas les fondements (a\u00efe!) – est propos\u00e9 par Xi’An. J’ai traduit son code R en SQL et effectivement \u00e7a marche…<\/p>\n
SQL> SELECT 1 \/ AVG (sup1)\r\n  2    FROM (SELECT CASE\r\n  3                     WHEN   (  v\r\n  4                             - NVL (\r\n  5                                   LAG (v) OVER (PARTITION BY NULL ORDER BY r),\r\n  6                                   0))\r\n  7                          * COUNT (*) OVER (PARTITION BY NULL) >\r\n  8                          1\r\n  9                     THEN\r\n 10                         1\r\n 11                     ELSE\r\n 12                         0\r\n 13                 END\r\n 14                     sup1\r\n 15            FROM (SELECT ROWNUM r, v\r\n 16                    FROM (    SELECT DBMS_RANDOM.VALUE v\r\n 17                                FROM DUAL\r\n 18                          CONNECT BY LEVEL < 1e5\r\n 19                            ORDER BY 1)));\r\n\r\n1\/AVG(SUP1)\r\n-----------\r\n 2.71411899\r\n\r\nElapsed: 00:00:00.75\r\nSQL> \r\n<\/pre>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
Un peu plus t\u00f4t dans le mois, j’ai d\u00e9couvert le e-day! e \u00e9tant le nombre d’Euler: 2.718 Cette ann\u00e9e, le<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"colormag_page_container_layout":"default_layout","colormag_page_sidebar_layout":"default_layout","footnotes":""},"categories":[6,11],"tags":[],"class_list":["post-1242","post","type-post","status-publish","format-standard","hentry","category-oracle","category-probabilites"],"_links":{"self":[{"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/posts\/1242","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1242"}],"version-history":[{"count":3,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/posts\/1242\/revisions"}],"predecessor-version":[{"id":1245,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/posts\/1242\/revisions\/1245"}],"wp:attachment":[{"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1242"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1242"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1242"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}