{"id":605,"date":"2025-07-24T20:04:26","date_gmt":"2025-07-24T11:04:26","guid":{"rendered":"https:\/\/www.flumen-jp.com\/wordpress\/?p=605"},"modified":"2025-07-24T20:14:42","modified_gmt":"2025-07-24T11:14:42","slug":"lean_sql","status":"publish","type":"post","link":"https:\/\/www.flumen-jp.com\/wordpress\/index.php\/2025\/07\/24\/lean_sql\/","title":{"rendered":"SQL \u306e\u304a\u52c9\u5f37"},"content":{"rendered":"\n<p>\u3000SQL \u306e\u304a\u52c9\u5f37\u3067\u3059\u3002\u305f\u307e\u306b\u4f7f\u308f\u306a\u3044\u3068\u8272\u3005\u5fd8\u308c\u3066\u3057\u307e\u3046\u306e\u3067\u30fb\u30fb\u30fb\u3002<\/p>\n\n\n\n<p>\u3000\u60f3\u5b9a\u554f\u984c\u3068\u3057\u3066\uff12\u5e74\u5206\u306e\u65e5\u96e8\u91cf\u30c7\u30fc\u30bf rainfall.csv<\/p>\n\n\n\n<div class=\"hcb_wrap\"><pre class=\"prism line-numbers lang-plain\"><code>2023-01-01,0.0\n2023-01-02,0.0\n2023-01-03,0.0\n\u00a0 \u00a0 :\n2024-12-29,0.0\n2024-12-30,0.0\n2024-12-31,4.5<\/code><\/pre><\/div>\n\n\n\n<p>\u304b\u3089\u5404\u5e74\u306e\u6700\u5927\u65e5\u96e8\u91cf\u3001\u6700\u5927\uff12\u65e5\u96e8\u91cf\u3001\u6700\u5927\uff13\u65e5\u96e8\u91cf\u3092\u62bd\u51fa\u3059\u308b\u30af\u30a8\u30ea\u3092\u8003\u3048\u3066\u307f\u305f\u3002<br>\u3000\u6b21\u306e\u30b9\u30af\u30ea\u30d7\u30c8 rainfall.sql \u306f\u8a66\u884c\u932f\u8aa4\u3092\u7d4c\u3066\u5b8c\u6210\u3057\u305f\u3082\u306e\u3002\u30b5\u30d6\u30af\u30a8\u30ea\u5185\u3067\u7a93\u95a2\u6570 (over) \u3092\u4f7f\u3063\u3066\uff12\u65e5\u96e8\u91cf\u3001\uff13\u65e5\u96e8\u91cf\u3092\u8a08\u7b97\u3059\u308b\u306e\u304c\u30dd\u30a4\u30f3\u30c8\u3002\u4f55\u6642\u3082\u306e\u3053\u3068\u306a\u304c\u3089\u51fa\u6765\u3066\u307f\u308c\u3070\u8ad6\u7406\u304c\u660e\u78ba\u3060\u3002<\/p>\n\n\n\n<div class=\"hcb_wrap\"><pre class=\"prism line-numbers lang-sql\" data-lang=\"SQL\"><code>create table rainfall(date text, value real);\n\n.mode csv\n.import rainfall.csv rainfall\n\n.header on\n\nselect\n\u00a0 \u00a0 T.year as year,\n\u00a0 \u00a0 max(T.d1) as max_d1,\n\u00a0 \u00a0 max(T.d2) as max_d2,\n\u00a0 \u00a0 max(T.d3) as max_d3\nfrom ( \u00a0-- \u30b5\u30d6\u30af\u30a8\u30ea\n\u00a0 \u00a0 select\n\u00a0 \u00a0 \u00a0 \u00a0 strftime(&#39;%Y&#39;, date) as year,\n\u00a0 \u00a0 \u00a0 \u00a0 value as d1, -- \u65e5\u96e8\u91cf\n\u00a0 \u00a0 \u00a0 \u00a0 sum(value) over (\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 rows between 1 preceding and current row\n\u00a0 \u00a0 \u00a0 \u00a0 ) as d2, \u00a0 \u00a0 -- \uff12\u65e5\u96e8\u91cf\n\u00a0 \u00a0 \u00a0 \u00a0 sum(value) over (\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 rows between 2 preceding and current row\n\u00a0 \u00a0 \u00a0 \u00a0 ) as d3 \u00a0 \u00a0 \u00a0-- \uff13\u65e5\u96e8\u91cf\n\u00a0 \u00a0 from rainfall\n) as T\ngroup by year;<\/code><\/pre><\/div>\n\n\n\n<p>\u3000\u3067\u3001\u30b3\u30de\u30f3\u30c9\u30d7\u30ed\u30f3\u30d7\u30c8\u3067<\/p>\n\n\n\n<div class=\"hcb_wrap\"><pre class=\"prism line-numbers lang-plain\"><code>sqlite3 :memory: &lt; rainfall.sql<\/code><\/pre><\/div>\n\n\n\n<p>\u3068\u3059\u308b\u3068\u3001<\/p>\n\n\n\n<div class=\"hcb_wrap\"><pre class=\"prism line-numbers lang-plain\"><code>year,max_d1,max_d2,max_d3\n2023,40.0,57.0,58.0\n2024,31.0,37.0,38.0<\/code><\/pre><\/div>\n\n\n\n<p>\u304c\u51fa\u529b\u3055\u308c\u308b\u3002\u3053\u308c\u307e\u305f\u4f7f\u3044\u65b9\u3092\u5fd8\u308c\u304c\u3061\u306a pandas \u3088\u308a\u53ef\u6210\u308a\u304a\u624b\u8efd\u3060\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u3000SQL \u306e\u304a\u52c9\u5f37\u3067\u3059\u3002\u305f\u307e\u306b\u4f7f\u308f\u306a\u3044\u3068\u8272\u3005\u5fd8\u308c\u3066\u3057\u307e\u3046\u306e\u3067\u30fb\u30fb\u30fb\u3002 \u3000\u60f3\u5b9a\u554f\u984c\u3068\u3057\u3066\uff12\u5e74\u5206\u306e\u65e5\u96e8\u91cf\u30c7\u30fc\u30bf rainfall.csv \u304b\u3089\u5404\u5e74\u306e\u6700\u5927\u65e5\u96e8\u91cf\u3001\u6700\u5927\uff12\u65e5\u96e8\u91cf\u3001\u6700\u5927\uff13\u65e5\u96e8\u91cf\u3092\u62bd\u51fa\u3059\u308b\u30af\u30a8\u30ea\u3092\u8003\u3048\u3066\u307f\u305f\u3002\u3000\u6b21\u306e\u30b9 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[24,25],"class_list":["post-605","post","type-post","status-publish","format-standard","hentry","category-it","tag-sql","tag-sqlite3"],"_links":{"self":[{"href":"https:\/\/www.flumen-jp.com\/wordpress\/index.php\/wp-json\/wp\/v2\/posts\/605","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.flumen-jp.com\/wordpress\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flumen-jp.com\/wordpress\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flumen-jp.com\/wordpress\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flumen-jp.com\/wordpress\/index.php\/wp-json\/wp\/v2\/comments?post=605"}],"version-history":[{"count":5,"href":"https:\/\/www.flumen-jp.com\/wordpress\/index.php\/wp-json\/wp\/v2\/posts\/605\/revisions"}],"predecessor-version":[{"id":614,"href":"https:\/\/www.flumen-jp.com\/wordpress\/index.php\/wp-json\/wp\/v2\/posts\/605\/revisions\/614"}],"wp:attachment":[{"href":"https:\/\/www.flumen-jp.com\/wordpress\/index.php\/wp-json\/wp\/v2\/media?parent=605"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flumen-jp.com\/wordpress\/index.php\/wp-json\/wp\/v2\/categories?post=605"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flumen-jp.com\/wordpress\/index.php\/wp-json\/wp\/v2\/tags?post=605"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}