{"id":319769,"date":"2021-03-17T15:01:35","date_gmt":"2021-03-17T15:01:35","guid":{"rendered":"http:\/\/savepearlharbor.com\/?p=319769"},"modified":"-0001-11-30T00:00:00","modified_gmt":"-0001-11-29T21:00:00","slug":"","status":"publish","type":"post","link":"https:\/\/savepearlharbor.com\/?p=319769","title":{"rendered":"SQLite \u2014 \u043d\u0435 \u0438\u0433\u0440\u0443\u0448\u043a\u0430"},"content":{"rendered":"\n<div class=\"post__text post__text_v2\" id=\"post-content-body\">\n<p>\u0420\u0430\u0441\u0441\u043a\u0430\u0437\u044b\u0432\u0430\u044e, \u043f\u043e\u0447\u0435\u043c\u0443 SQLite \u043e\u0442\u043b\u0438\u0447\u043d\u043e \u043f\u043e\u0434\u043e\u0439\u0434\u0435\u0442 \u0432\u0430\u043c \u0432 \u043f\u043e\u0432\u0441\u0435\u0434\u043d\u0435\u0432\u043d\u043e\u0439 \u0440\u0430\u0431\u043e\u0442\u0435. \u0418 \u043d\u0435\u0432\u0430\u0436\u043d\u043e, \u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u0447\u0438\u043a \u0432\u044b, \u0430\u043d\u0430\u043b\u0438\u0442\u0438\u043a, \u0442\u0435\u0441\u0442\u0438\u0440\u043e\u0432\u0449\u0438\u043a, \u0430\u0434\u043c\u0438\u043d \u0438\u043b\u0438 \u043f\u0440\u043e\u0434\u0430\u043a\u0442-\u043c\u0435\u043d\u0435\u0434\u0436\u0435\u0440.<\/p>\n<p>\u0414\u043b\u044f \u0437\u0430\u0442\u0440\u0430\u0432\u043a\u0438 \u043d\u0435\u0441\u043a\u043e\u043b\u044c\u043a\u043e \u0438\u0437\u0432\u0435\u0441\u0442\u043d\u044b\u0445 \u0444\u0430\u043a\u0442\u043e\u0432:<\/p>\n<ul>\n<li>\n<p>SQLite \u2014 \u0441\u0430\u043c\u0430\u044f \u0440\u0430\u0441\u043f\u0440\u043e\u0441\u0442\u0440\u0430\u043d\u0435\u043d\u043d\u0430\u044f \u0421\u0423\u0411\u0414 \u0432 \u043c\u0438\u0440\u0435, \u0432\u043a\u043b\u044e\u0447\u0435\u043d\u0430 \u0432\u043e \u0432\u0441\u0435 \u043f\u043e\u043f\u0443\u043b\u044f\u0440\u043d\u044b\u0435 \u041e\u0421.<\/p>\n<\/li>\n<li>\n<p>\u0420\u0430\u0431\u043e\u0442\u0430\u0435\u0442 \u0431\u0435\u0437 \u0441\u0435\u0440\u0432\u0435\u0440\u0430.<\/p>\n<\/li>\n<li>\n<p>\u0414\u043b\u044f \u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u0447\u0438\u043a\u043e\u0432 \u2014 \u0432\u0441\u0442\u0440\u0430\u0438\u0432\u0430\u0435\u0442\u0441\u044f \u043f\u0440\u044f\u043c\u043e \u0432 \u043f\u0440\u0438\u043b\u043e\u0436\u0435\u043d\u0438\u0435.<\/p>\n<\/li>\n<li>\n<p>\u0414\u043b\u044f \u0432\u0441\u0435\u0445 \u043e\u0441\u0442\u0430\u043b\u044c\u043d\u044b\u0445 \u2014 \u0443\u0434\u043e\u0431\u043d\u0430\u044f \u043a\u043e\u043d\u0441\u043e\u043b\u044c (REPL) \u043e\u0434\u043d\u0438\u043c \u0444\u0430\u0439\u043b\u043e\u043c (sqlite3.exe \u043d\u0430 Windows, sqlite3 \u0432 Linux \/ macOS).<\/p>\n<\/li>\n<\/ul>\n<h2>\u041a\u043e\u043d\u0441\u043e\u043b\u044c, \u0438\u043c\u043f\u043e\u0440\u0442 \u0438 \u044d\u043a\u0441\u043f\u043e\u0440\u0442<\/h2>\n<p>\u041a\u043e\u043d\u0441\u043e\u043b\u044c \u2014&nbsp;\u044d\u0442\u043e \u043a\u0438\u043b\u043b\u0435\u0440-\u0444\u0438\u0447\u0430 SQLite: \u0431\u043e\u043b\u0435\u0435 \u043c\u043e\u0449\u043d\u044b\u0439 \u0438\u043d\u0441\u0442\u0440\u0443\u043c\u0435\u043d\u0442 \u0430\u043d\u0430\u043b\u0438\u0437\u0430 \u0434\u0430\u043d\u043d\u044b\u0445, \u0447\u0435\u043c Excel, \u0438 \u0441\u0438\u043b\u044c\u043d\u043e \u0431\u043e\u043b\u0435\u0435 \u043f\u0440\u043e\u0441\u0442\u043e\u0439, \u0447\u0435\u043c \u043a\u0430\u043a\u043e\u0439-\u043d\u0438\u0431\u0443\u0434\u044c pandas. \u0414\u0430\u043d\u043d\u044b\u0435 \u0438\u0437 CSV \u0437\u0430\u0433\u0440\u0443\u0436\u0430\u044e\u0442\u0441\u044f \u043e\u0434\u043d\u043e\u0439 \u043a\u043e\u043c\u0430\u043d\u0434\u043e\u0439, \u0442\u0430\u0431\u043b\u0438\u0446\u0430 \u0441\u043e\u0437\u0434\u0430\u0435\u0442\u0441\u044f \u0430\u0432\u0442\u043e\u043c\u0430\u0442\u0438\u0447\u0435\u0441\u043a\u0438:<\/p>\n<pre><code class=\"javascript\">&gt; .import --csv city.csv city &gt; select count(*) from city; 1117<\/code><\/pre>\n<p>\u041f\u043e\u0434\u0434\u0435\u0440\u0436\u0438\u0432\u0430\u044e\u0442\u0441\u044f \u0431\u0430\u0437\u043e\u0432\u044b\u0435 SQL-\u0444\u0438\u0447\u0438, \u0430 \u0440\u0435\u0437\u0443\u043b\u044c\u0442\u0430\u0442 \u043a\u043e\u043d\u0441\u043e\u043b\u044c \u043f\u043e\u043a\u0430\u0437\u044b\u0432\u0430\u0435\u0442 \u0432 \u043f\u0440\u0438\u044f\u0442\u043d\u043e\u0439 \u0442\u0430\u0431\u043b\u0438\u0447\u043a\u0435. \u041f\u0440\u043e\u0434\u0432\u0438\u043d\u0443\u0442\u044b\u0435 SQL-\u0444\u0438\u0447\u0438 \u0442\u043e\u0436\u0435 \u0435\u0441\u0442\u044c, \u043d\u043e \u043e \u043d\u0438\u0445 \u0447\u0443\u0442\u044c \u043f\u043e\u0437\u0436\u0435. <\/p>\n<pre><code class=\"sql\">select   century || '-\u0439 \u0432\u0435\u043a' as dates,   count(*) as city_count from history group by century order by century desc;<\/code><\/pre>\n<pre><code class=\"vala\">\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510 \u2502  dates   \u2502 city_count \u2502 \u251c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524 \u2502 21-\u0439 \u0432\u0435\u043a \u2502 1          \u2502 \u2502 20-\u0439 \u0432\u0435\u043a \u2502 263        \u2502 \u2502 19-\u0439 \u0432\u0435\u043a \u2502 189        \u2502 \u2502 18-\u0439 \u0432\u0435\u043a \u2502 191        \u2502 \u2502 17-\u0439 \u0432\u0435\u043a \u2502 137        \u2502 \u2502 16-\u0439 \u0432\u0435\u043a \u2502 79         \u2502 \u2502 15-\u0439 \u0432\u0435\u043a \u2502 39         \u2502 \u2502 14-\u0439 \u0432\u0435\u043a \u2502 38         \u2502 \u2502 13-\u0439 \u0432\u0435\u043a \u2502 27         \u2502 \u2502 12-\u0439 \u0432\u0435\u043a \u2502 44         \u2502 \u2502 11-\u0439 \u0432\u0435\u043a \u2502 8          \u2502 \u2502 10-\u0439 \u0432\u0435\u043a \u2502 6          \u2502 \u2502 9-\u0439 \u0432\u0435\u043a  \u2502 4          \u2502 \u2502 5-\u0439 \u0432\u0435\u043a  \u2502 1          \u2502 \u2502 3-\u0439 \u0432\u0435\u043a  \u2502 1          \u2502 \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518<\/code><\/pre>\n<p>\u041a\u0443\u0447\u0430 \u0444\u043e\u0440\u043c\u0430\u0442\u043e\u0432 \u0432\u044b\u0433\u0440\u0443\u0437\u043a\u0438 \u0434\u0430\u043d\u043d\u044b\u0445: sql, csv, json, \u0434\u0430\u0436\u0435 markdown \u0438 html. \u0412\u0441\u0435 \u0434\u0435\u043b\u0430\u0435\u0442\u0441\u044f \u043f\u0430\u0440\u043e\u0439 \u043a\u043e\u043c\u0430\u043d\u0434:<\/p>\n<pre><code class=\"sql\">.mode json .output city.json select city, foundation_year, timezone from city limit 10; .shell cat city.json<\/code><\/pre>\n<pre><code class=\"json\">[{\"city\":\"\u0410\u0434\u044b\u0433\u0435\u0439\u0441\u043a\",\"foundation_year\":1969,\"timezone\":\"UTC+3\"}, {\"city\":\"\u041c\u0430\u0439\u043a\u043e\u043f\",\"foundation_year\":1857,\"timezone\":\"UTC+3\"}, {\"city\":\"\u0413\u043e\u0440\u043d\u043e-\u0410\u043b\u0442\u0430\u0439\u0441\u043a\",\"foundation_year\":1830,\"timezone\":\"UTC+7\"}, {\"city\":\"\u0410\u043b\u0435\u0439\u0441\u043a\",\"foundation_year\":1913,\"timezone\":\"UTC+7\"}, {\"city\":\"\u0411\u0430\u0440\u043d\u0430\u0443\u043b\",\"foundation_year\":1730,\"timezone\":\"UTC+7\"}, {\"city\":\"\u0411\u0435\u043b\u043e\u043a\u0443\u0440\u0438\u0445\u0430\",\"foundation_year\":1846,\"timezone\":\"UTC+7\"}, {\"city\":\"\u0411\u0438\u0439\u0441\u043a\",\"foundation_year\":1709,\"timezone\":\"UTC+7\"}, {\"city\":\"\u0413\u043e\u0440\u043d\u044f\u043a\",\"foundation_year\":1942,\"timezone\":\"UTC+7\"}, {\"city\":\"\u0417\u0430\u0440\u0438\u043d\u0441\u043a\",\"foundation_year\":1748,\"timezone\":\"UTC+7\"}, {\"city\":\"\u0417\u043c\u0435\u0438\u043d\u043e\u0433\u043e\u0440\u0441\u043a\",\"foundation_year\":1736,\"timezone\":\"UTC+7\"}]<\/code><\/pre>\n<h2>\u041d\u0430\u0442\u0438\u0432\u043d\u0430\u044f \u0440\u0430\u0431\u043e\u0442\u0430 \u0441 JSON<\/h2>\n<p>\u041d\u0435\u0442 \u043d\u0438\u0447\u0435\u0433\u043e \u0443\u0434\u043e\u0431\u043d\u0435\u0435 SQLite \u0434\u043b\u044f \u0430\u043d\u0430\u043b\u0438\u0437\u0430 \u0438 \u043f\u0440\u0435\u043e\u0431\u0440\u0430\u0437\u043e\u0432\u0430\u043d\u0438\u044f JSON. \u041c\u043e\u0436\u043d\u043e \u0441\u0435\u043b\u0435\u043a\u0442\u0438\u0442\u044c \u0434\u0430\u043d\u043d\u044b\u0435 \u043d\u0430\u043f\u0440\u044f\u043c\u0443\u044e \u0438\u0437 \u0444\u0430\u0439\u043b\u0430, \u043a\u0430\u043a \u0431\u0443\u0434\u0442\u043e \u044d\u0442\u043e \u043e\u0431\u044b\u0447\u043d\u0430\u044f \u0442\u0430\u0431\u043b\u0438\u0446\u0430. \u0418\u043b\u0438 \u0437\u0430\u0433\u0440\u0443\u0437\u0438\u0442\u044c \u0432 \u0442\u0430\u0431\u043b\u0438\u0446\u0443 \u0438 \u0441\u0435\u043b\u0435\u043a\u0442\u0438\u0442\u044c \u043e\u0442\u0442\u0443\u0434\u0430 \u2014 \u043a\u0430\u043a \u0432\u0430\u043c \u0443\u0434\u043e\u0431\u043d\u0435\u0435.<\/p>\n<pre><code class=\"sql\">select   json_extract(value, '$.code') as code,   json_extract(value, '$.name') as name,   json_extract(value, '$.rate') as rate,   json_extract(value, '$.default') as \"default\" from   json_each(readfile('currency.sample.json')) ;<\/code><\/pre>\n<pre><code class=\"markdown\">\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510 \u2502 code \u2502       name        \u2502    rate    \u2502 default \u2502 \u251c\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524 \u2502 AZN  \u2502 \u041c\u0430\u043d\u0430\u0442\u044b            \u2502 0.023107   \u2502 0       \u2502 \u2502 BYR  \u2502 \u0411\u0435\u043b\u043e\u0440\u0443\u0441\u0441\u043a\u0438\u0435 \u0440\u0443\u0431\u043b\u0438 \u2502 0.034966   \u2502 0       \u2502 \u2502 EUR  \u2502 \u0415\u0432\u0440\u043e              \u2502 0.011138   \u2502 0       \u2502 \u2502 GEL  \u2502 \u0413\u0440\u0443\u0437\u0438\u043d\u0441\u043a\u0438\u0439 \u043b\u0430\u0440\u0438   \u2502 0.0344     \u2502 0       \u2502 \u2502 KGS  \u2502 \u041a\u0438\u0440\u0433\u0438\u0437\u0441\u043a\u0438\u0439 \u0441\u043e\u043c    \u2502 1.131738   \u2502 0       \u2502 \u2502 KZT  \u2502 \u0422\u0435\u043d\u0433\u0435             \u2502 5.699857   \u2502 0       \u2502 \u2502 RUR  \u2502 \u0420\u0443\u0431\u043b\u0438             \u2502 1.0        \u2502 1       \u2502 \u2502 UAH  \u2502 \u0413\u0440\u0438\u0432\u043d\u044b            \u2502 0.380539   \u2502 0       \u2502 \u2502 USD  \u2502 \u0414\u043e\u043b\u043b\u0430\u0440\u044b           \u2502 0.013601   \u2502 0       \u2502 \u2502 UZS  \u2502 \u0423\u0437\u0431\u0435\u043a\u0441\u043a\u0438\u0439 \u0441\u0443\u043c     \u2502 142.441417 \u2502 0       \u2502 \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518<\/code><\/pre>\n<p>\u041d\u0435\u0432\u0430\u0436\u043d\u043e, \u043d\u0430\u0441\u043a\u043e\u043b\u044c\u043a\u043e \u0440\u0430\u0437\u0432\u0435\u0441\u0438\u0441\u0442\u044b\u0439 JSON \u2014 \u043c\u043e\u0436\u043d\u043e \u0432\u044b\u0431\u0440\u0430\u0442\u044c \u0430\u0442\u0440\u0438\u0431\u0443\u0442\u044b \u043b\u044e\u0431\u043e\u0439 \u0432\u043b\u043e\u0436\u0435\u043d\u043d\u043e\u0441\u0442\u0438: <\/p>\n<pre><code class=\"sql\">select   json_extract(value, '$.id') as id,   json_extract(value, '$.name') as name from   json_tree(readfile('industry.sample.json')) where   path like '$[%].industries' ;<\/code><\/pre>\n<pre><code class=\"markdown\">\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510 \u2502   id   \u2502         name         \u2502 \u251c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524 \u2502 7.538  \u2502 \u0418\u043d\u0442\u0435\u0440\u043d\u0435\u0442-\u043f\u0440\u043e\u0432\u0430\u0439\u0434\u0435\u0440   \u2502 \u2502 7.539  \u2502 \u0418\u0422-\u043a\u043e\u043d\u0441\u0430\u043b\u0442\u0438\u043d\u0433        \u2502 \u2502 7.540  \u2502 \u0420\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u043a\u0430 \u041f\u041e        \u2502 \u2502 9.399  \u2502 \u041c\u043e\u0431\u0438\u043b\u044c\u043d\u0430\u044f \u0441\u0432\u044f\u0437\u044c      \u2502 \u2502 9.400  \u2502 \u0424\u0438\u043a\u0441\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u0430\u044f \u0441\u0432\u044f\u0437\u044c  \u2502 \u2502 9.401  \u2502 \u041e\u043f\u0442\u043e\u0432\u043e\u043b\u043e\u043a\u043e\u043d\u043d\u0430\u044f \u0441\u0432\u044f\u0437\u044c \u2502 \u2502 43.641 \u2502 \u0410\u0443\u0434\u0438\u0442                \u2502 \u2502 43.646 \u2502 \u0421\u0442\u0440\u0430\u0445\u043e\u0432\u0430\u043d\u0438\u0435          \u2502 \u2502 43.647 \u2502 \u0411\u0430\u043d\u043a                 \u2502 \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518<\/code><\/pre>\n<h2>CTE \u0438 \u043e\u043f\u0435\u0440\u0430\u0446\u0438\u0438 \u043d\u0430\u0434 \u043c\u043d\u043e\u0436\u0435\u0441\u0442\u0432\u0430\u043c\u0438<\/h2>\n<p>\u0420\u0430\u0437\u0443\u043c\u0435\u0435\u0442\u0441\u044f, \u043f\u043e\u0434\u0434\u0435\u0440\u0436\u0438\u0432\u0430\u044e\u0442\u0441\u044f Common Table Expressions (\u043a\u043e\u043d\u0441\u0442\u0440\u0443\u043a\u0446\u0438\u044f <code>WITH<\/code>) \u0438 \u0434\u0436\u043e\u0439\u043d\u044b, \u0442\u0443\u0442 \u0434\u0430\u0436\u0435 \u043f\u0440\u0438\u043c\u0435\u0440\u044b \u043f\u0440\u0438\u0432\u043e\u0434\u0438\u0442\u044c \u043d\u0435 \u0431\u0443\u0434\u0443. \u0410 \u0435\u0441\u043b\u0438 \u0434\u0430\u043d\u043d\u044b\u0435 \u0438\u0435\u0440\u0430\u0440\u0445\u0438\u0447\u043d\u044b\u0435 (\u0442\u0430\u0431\u043b\u0438\u0446\u0430 \u0441\u0441\u044b\u043b\u0430\u0435\u0442\u0441\u044f \u0441\u0430\u043c\u0430 \u043d\u0430 \u0441\u0435\u0431\u044f \u0447\u0435\u0440\u0435\u0437 \u0441\u0442\u043e\u043b\u0431\u0435\u0446 \u0432\u0440\u043e\u0434\u0435 <code>parent_id<\/code>) \u2014&nbsp;\u043f\u043e\u043c\u043e\u0436\u0435\u0442 \u0440\u0435\u043a\u0443\u0440\u0441\u0438\u0432\u043d\u044b\u0439 <code>WITH<\/code>. \u0418\u0435\u0440\u0430\u0440\u0445\u0438\u044e \u043b\u044e\u0431\u043e\u0433\u043e \u0443\u0440\u043e\u0432\u043d\u044f \u043c\u043e\u0436\u043d\u043e \u00ab\u0440\u0430\u0437\u043c\u043e\u0442\u0430\u0442\u044c\u00bb&nbsp;\u043e\u0434\u043d\u0438\u043c \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u043c.<\/p>\n<pre><code class=\"sql\">with recursive tmp(id, name, level) as (   select id, name, 1 as level   from area   where parent_id is null    union all    select     area.id,     tmp.name || ', ' || area.name as name,     tmp.level + 1 as level   from area     join tmp on area.parent_id = tmp.id )  select * from tmp;<\/code><\/pre>\n<pre><code class=\"markdown\">\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510 \u2502  id  \u2502               name                  \u2502 level \u2502 \u251c\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524 \u2502 113  \u2502 \u0420\u043e\u0441\u0441\u0438\u044f                              \u2502 1     \u2502 \u2502 1    \u2502 \u0420\u043e\u0441\u0441\u0438\u044f, \u041c\u043e\u0441\u043a\u0432\u0430                      \u2502 2     \u2502 \u2502 1586 \u2502 \u0420\u043e\u0441\u0441\u0438\u044f, \u0421\u0430\u043c\u0430\u0440\u0441\u043a\u0430\u044f \u043e\u0431\u043b\u0430\u0441\u0442\u044c           \u2502 2     \u2502 \u2502 1588 \u2502 \u0420\u043e\u0441\u0441\u0438\u044f, \u0421\u0430\u043c\u0430\u0440\u0441\u043a\u0430\u044f \u043e\u0431\u043b\u0430\u0441\u0442\u044c, \u041a\u0438\u043d\u0435\u043b\u044c   \u2502 3     \u2502 \u2502 78   \u2502 \u0420\u043e\u0441\u0441\u0438\u044f, \u0421\u0430\u043c\u0430\u0440\u0441\u043a\u0430\u044f \u043e\u0431\u043b\u0430\u0441\u0442\u044c, \u0421\u0430\u043c\u0430\u0440\u0430   \u2502 3     \u2502 \u2502 212  \u2502 \u0420\u043e\u0441\u0441\u0438\u044f, \u0421\u0430\u043c\u0430\u0440\u0441\u043a\u0430\u044f \u043e\u0431\u043b\u0430\u0441\u0442\u044c, \u0422\u043e\u043b\u044c\u044f\u0442\u0442\u0438 \u2502 3     \u2502 \u2502 ...  \u2502 ...                                 \u2502 ...   \u2502 \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518<\/code><\/pre>\n<p>\u041c\u043d\u043e\u0436\u0435\u0441\u0442\u0432\u0430? \u041d\u0435\u0442 \u043f\u0440\u043e\u0431\u043b\u0435\u043c: <code>UNION<\/code>, <code>INTERSECT<\/code>, <code>EXCEPT<\/code> \u043a \u0432\u0430\u0448\u0438\u043c \u0443\u0441\u043b\u0443\u0433\u0430\u043c.<\/p>\n<pre><code class=\"sql\">select employer_id from employer_area where area_id = 1  except  select employer_id from employer_area where area_id = 2;<\/code><\/pre>\n<figure class=\"full-width\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/habrastorage.org\/getpro\/habr\/upload_files\/50b\/350\/173\/50b35017373cb161ec8367866ad9fe0e.png\" width=\"1600\" height=\"580\"><figcaption><\/figcaption><\/figure>\n<p>\u0425\u043e\u0442\u0438\u0442\u0435 \u0440\u0430\u0441\u0441\u0447\u0438\u0442\u0430\u0442\u044c \u043e\u0434\u0438\u043d \u0441\u0442\u043e\u043b\u0431\u0435\u0446 \u043d\u0430 \u043e\u0441\u043d\u043e\u0432\u0430\u043d\u0438\u0438 \u043d\u0435\u0441\u043a\u043e\u043b\u044c\u043a\u0438\u0445 \u0434\u0440\u0443\u0433\u0438\u0445? \u041f\u043e\u0436\u0430\u043b\u0443\u0439\u0441\u0442\u0430 \u2014 \u0432\u044b\u0447\u0438\u0441\u043b\u044f\u0435\u043c\u044b\u0435 \u0441\u0442\u043e\u043b\u0431\u0446\u044b:<\/p>\n<pre><code class=\"sql\">alter table vacancy add column salary_net integer as (   case when salary_gross = true then     round(salary_from\/1.13)   else     salary_from   end );<\/code><\/pre>\n<p>\u0412\u044b\u0447\u0438\u0441\u043b\u044f\u0435\u043c\u044b\u0439 \u0441\u0442\u043e\u043b\u0431\u0435\u0446 \u0432 \u0441\u0435\u043b\u0435\u043a\u0442\u0435 \u0434\u043e\u0441\u0442\u0443\u043f\u0435\u043d \u0442\u043e\u0447\u043d\u043e \u0442\u0430\u043a \u0436\u0435, \u043a\u0430\u043a \u043e\u0431\u044b\u0447\u043d\u044b\u0439:<\/p>\n<pre><code class=\"sql\">select   substr(name, 1, 40) as name,   salary_net from vacancy where   salary_currency = 'RUR'   and salary_net is not null limit 10;<\/code><\/pre>\n<pre><code class=\"vala\">\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510 \u2502                   name                   \u2502 salary_net \u2502 \u251c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524 \u2502 \u0412\u0435\u0434\u0443\u0449\u0438\u0439 \u0438\u043d\u0436\u0435\u043d\u0435\u0440-\u043f\u0440\u043e\u0433\u0440\u0430\u043c\u043c\u0438\u0441\u0442 (Delphi)     \u2502 40000      \u2502 \u2502 \u0412\u0435\u0434\u0443\u0449\u0438\u0439 \u043f\u0440\u043e\u0433\u0440\u0430\u043c\u043c\u0438\u0441\u0442 Scala \/ Java ( Senio \u2502 60000      \u2502 \u2502 Java \/ Kotlin Developer                  \u2502 150000     \u2502 \u2502 \u0412\u0435\u0434\u0443\u0449\u0438\u0439 \u0430\u043d\u0430\u043b\u0438\u0442\u0438\u043a 1\u0421                      \u2502 150000     \u2502 \u2502 \u0420\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u0447\u0438\u043a C#                           \u2502 53097      \u2502 \u2502 \u041f\u0440\u043e\u0433\u0440\u0430\u043c\u043c\u0438\u0441\u0442 1\u0421                           \u2502 80000      \u2502 \u2502 Java - \u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u0447\u0438\u043a (Middle, Senior)      \u2502 100000     \u2502 \u2502 \u041f\u0440\u043e\u0433\u0440\u0430\u043c\u043c\u0438\u0441\u0442 C#\/ .NET                     \u2502 70796      \u2502 \u2502 \u0422\u0435\u0441\u0442\u0438\u0440\u043e\u0432\u0449\u0438\u043a \u041f\u041e\/ QA engineer (\u0440\u0443\u0447\u043d\u043e\u0435 \u0442\u0435\u0441\u0442 \u2502 45000      \u2502 \u2502 \u041a\u0443\u0440\u044c\u0435\u0440                                   \u2502 17699      \u2502 \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518<\/code><\/pre>\n<h2>\u041c\u0430\u0442. \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0430<\/h2>\n<p>\u041e\u043f\u0438\u0441\u0430\u0442\u0435\u043b\u044c\u043d\u0430\u044f \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0430? \u0417\u0430\u043f\u0440\u043e\u0441\u0442\u043e: \u0441\u0440\u0435\u0434\u043d\u0435\u0435, \u043c\u0435\u0434\u0438\u0430\u043d\u0430, \u043f\u0440\u043e\u0446\u0435\u043d\u0442\u0438\u043b\u0438, \u0441\u0442\u0430\u043d\u0434\u0430\u0440\u0442\u043d\u043e\u0435 \u043e\u0442\u043a\u043b\u043e\u043d\u0435\u043d\u0438\u0435 \u0438 \u0432\u043e\u0442 \u044d\u0442\u043e \u0432\u0441\u0435. \u041f\u0440\u0430\u0432\u0434\u0430, \u043f\u0440\u0438\u0434\u0435\u0442\u0441\u044f \u043f\u043e\u0434\u043a\u043b\u044e\u0447\u0438\u0442\u044c \u0431\u0438\u0431\u043b\u0438\u043e\u0442\u0435\u043a\u0443 \u0441\u043e \u0441\u0442\u0430\u0442. \u0444\u0443\u043d\u043a\u0446\u0438\u044f\u043c\u0438, \u043d\u043e \u044d\u0442\u043e \u0442\u043e\u0436\u0435 \u043e\u0434\u043d\u0430 \u043a\u043e\u043c\u0430\u043d\u0434\u0430 (\u0438 \u043e\u0434\u0438\u043d \u0444\u0430\u0439\u043b).<\/p>\n<pre><code class=\"sql\">.load sqlite3-stats  select   count(*) as book_count,   cast(avg(num_pages) as integer) as mean,   cast(median(num_pages) as integer) as median,   mode(num_pages) as mode,   percentile_90(num_pages) as p90,   percentile_95(num_pages) as p95,   percentile_99(num_pages) as p99 from books;<\/code><\/pre>\n<pre><code class=\"vala\">\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2510 \u2502 book_count \u2502 mean \u2502 median \u2502 mode \u2502 p90 \u2502 p95 \u2502 p99  \u2502 \u251c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2524 \u2502 1483       \u2502 349  \u2502 295    \u2502 256  \u2502 640 \u2502 817 \u2502 1199 \u2502 \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2518<\/code><\/pre>\n<blockquote>\n<p><strong>\u041b\u0438\u0440\u0438\u0447\u0435\u0441\u043a\u043e\u0435 \u043e\u0442\u0441\u0442\u0443\u043f\u043b\u0435\u043d\u0438\u0435<\/strong>. SQLite \u0442\u0440\u0430\u0434\u0438\u0446\u0438\u043e\u043d\u043d\u043e \u0431\u0435\u0434\u043d\u0430 \u0444\u0443\u043d\u043a\u0446\u0438\u044f\u043c\u0438 \u043f\u043e \u0441\u0440\u0430\u0432\u043d\u0435\u043d\u0438\u044e \u0441 \u043a\u0430\u043a\u0438\u043c-\u043d\u0438\u0431\u0443\u0434\u044c \u043f\u043e\u0441\u0442\u0433\u0440\u0435\u0441\u043e\u043c. \u041d\u043e \u0438\u0445 \u043b\u0435\u0433\u043a\u043e \u0434\u043e\u0431\u0430\u0432\u0438\u0442\u044c, \u0447\u0435\u043c \u043c\u043d\u043e\u0433\u0438\u0435 \u0438 \u0437\u0430\u043d\u0438\u043c\u0430\u044e\u0442\u0441\u044f \u2014&nbsp;\u043a\u0430\u0436\u0434\u044b\u0439 \u043a\u0442\u043e \u0432\u043e \u0447\u0442\u043e \u0433\u043e\u0440\u0430\u0437\u0434. \u041f\u043e\u043b\u0443\u0447\u0430\u0435\u0442\u0441\u044f \u043b\u0435\u0433\u043a\u0438\u0439 \u0431\u0430\u0440\u0434\u0430\u043a.<\/p>\n<p>\u041f\u043e\u044d\u0442\u043e\u043c\u0443 \u044f \u0440\u0435\u0448\u0438\u043b \u0441\u0434\u0435\u043b\u0430\u0442\u044c \u043d\u043e\u0440\u043c\u0430\u043b\u044c\u043d\u044b\u0439 \u043d\u0430\u0431\u043e\u0440 \u0431\u0438\u0431\u043b\u0438\u043e\u0442\u0435\u043a, \u0441 \u0440\u0430\u0437\u0434\u0435\u043b\u0435\u043d\u0438\u0435\u043c \u043f\u043e \u043f\u0440\u0435\u0434\u043c\u0435\u0442\u043d\u043e\u0439 \u043e\u0431\u043b\u0430\u0441\u0442\u0438 \u0438 \u0430\u0432\u0442\u043e\u043c\u0430\u0442\u0438\u0447\u0435\u0441\u043a\u043e\u0439 \u0441\u0431\u043e\u0440\u043a\u043e\u0439 \u0434\u043b\u044f \u0432\u0441\u0435\u0445 \u041e\u0421. \u041f\u043e\u043a\u0430 \u0431\u0438\u0431\u043b\u0438\u043e\u0442\u0435\u043a \u0442\u0430\u043c \u043d\u0435\u043c\u043d\u043e\u0433\u043e, \u043d\u043e \u0441\u043a\u043e\u0440\u043e \u043f\u0440\u0438\u0431\u0430\u0432\u0438\u0442\u0441\u044f:<\/p>\n<p><a href=\"https:\/\/github.com\/nalgeon\/sqlite-plus\/\" rel=\"noopener noreferrer nofollow\">sqlite-plus (GitHub)<\/a><\/p>\n<\/blockquote>\n<p>\u0410 \u0432\u043e\u0442 \u0435\u0449\u0435 \u043e \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0435. \u041c\u043e\u0436\u043d\u043e \u043d\u0430\u0440\u0438\u0441\u043e\u0432\u0430\u0442\u044c \u0440\u0430\u0441\u043f\u0440\u0435\u0434\u0435\u043b\u0435\u043d\u0438\u0435 \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0439 \u043f\u0440\u044f\u043c\u043e \u0432 \u043a\u043e\u043d\u0441\u043e\u043b\u0438. \u0421\u043c\u043e\u0442\u0440\u0438\u0442\u0435, \u043a\u0430\u043a\u0430\u044f \u043c\u0438\u043b\u043e\u0442\u0430:<\/p>\n<pre><code class=\"sql\">with slots as (   select     num_pages\/100 as slot,     count(*) as book_count   from books   group by slot ), max as (   select max(book_count) as value   from slots ) select   slot,   book_count,   printf('%.' || (book_count * 30 \/ max.value) || 'c', '*') as bar from slots, max order by slot;<\/code><\/pre>\n<pre><code class=\"vala\">\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510 \u2502 slot \u2502 book_count \u2502              bar               \u2502 \u251c\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524 \u2502 0    \u2502 116        \u2502 *********                      \u2502 \u2502 1    \u2502 254        \u2502 ********************           \u2502 \u2502 2    \u2502 376        \u2502 ****************************** \u2502 \u2502 3    \u2502 285        \u2502 **********************         \u2502 \u2502 4    \u2502 184        \u2502 **************                 \u2502 \u2502 5    \u2502 90         \u2502 *******                        \u2502 \u2502 6    \u2502 54         \u2502 ****                           \u2502 \u2502 7    \u2502 41         \u2502 ***                            \u2502 \u2502 8    \u2502 31         \u2502 **                             \u2502 \u2502 9    \u2502 15         \u2502 *                              \u2502 \u2502 10   \u2502 11         \u2502 *                              \u2502 \u2502 11   \u2502 12         \u2502 *                              \u2502 \u2502 12   \u2502 2          \u2502 *                              \u2502 \u2502 13   \u2502 5          \u2502 *                              \u2502 \u2502 14   \u2502 3          \u2502 *                              \u2502 \u2502 15   \u2502 1          \u2502 *                              \u2502 \u2502 17   \u2502 1          \u2502 *                              \u2502 \u2502 18   \u2502 2          \u2502 *                              \u2502 \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518<\/code><\/pre>\n<h2>\u0411\u044b\u0441\u0442\u0440\u043e\u0434\u0435\u0439\u0441\u0442\u0432\u0438\u0435<\/h2>\n<p>SQLite \u0441\u043f\u043e\u043a\u043e\u0439\u043d\u043e \u0440\u0430\u0431\u043e\u0442\u0430\u0435\u0442 \u0441 \u0434\u0435\u0441\u044f\u0442\u043a\u0430\u043c\u0438 \u043c\u0438\u043b\u043b\u0438\u043e\u043d\u043e\u0432 \u0437\u0430\u043f\u0438\u0441\u0435\u0439 (\u0441 \u0441\u043e\u0442\u043d\u044f\u043c\u0438 \u0442\u043e\u0436\u0435 \u2014&nbsp;\u044f \u043f\u0440\u043e\u0432\u0435\u0440\u044f\u043b). \u041e\u0431\u044b\u0447\u043d\u044b\u0435 <code>INSERT<\/code> \u0434\u0430\u044e\u0442 \u043d\u0430 \u043c\u043e\u0435\u043c \u043d\u043e\u0443\u0442\u0435 \u043e\u043a\u043e\u043b\u043e 240 \u0442\u044b\u0441\u044f\u0447 \u0437\u0430\u043f\u0438\u0441\u0435\u0439 \u0432 \u0441\u0435\u043a\u0443\u043d\u0434\u0443. \u0410 \u0435\u0441\u043b\u0438 \u043f\u043e\u0434\u043a\u043b\u044e\u0447\u0438\u0442\u044c \u0438\u0441\u0445\u043e\u0434\u043d\u044b\u0439 CSV \u043a\u0430\u043a \u0432\u0438\u0440\u0442\u0443\u0430\u043b\u044c\u043d\u0443\u044e \u0442\u0430\u0431\u043b\u0438\u0446\u0443 (\u0442\u0430\u043a\u0430\u044f \u0441\u043f\u0435\u0446\u0438\u0430\u043b\u044c\u043d\u0430\u044f \u0444\u0438\u0447\u0430) \u2014&nbsp;\u0435\u0449\u0435 \u0432 2 \u0440\u0430\u0437\u0430 \u0431\u044b\u0441\u0442\u0440\u0435\u0435.<\/p>\n<pre><code class=\"sql\">.load sqlite3-vsv create virtual table temp.blocks_csv using vsv(     filename=\"ipblocks.csv\",     schema=\"create table x(network text, geoname_id integer, registered_country_geoname_id integer, represented_country_geoname_id integer, is_anonymous_proxy integer, is_satellite_provider integer, postal_code text, latitude real, longitude real, accuracy_radius integer)\",     columns=10,     header=on,     nulls=on );<\/code><\/pre>\n<pre><code class=\"javascript\">.timer on insert into blocks select * from blocks_csv;  Run Time: real 5.176 user 4.716420 sys 0.403866<\/code><\/pre>\n<pre><code class=\"javascript\">select count(*) from blocks; 3386629  Run Time: real 0.095 user 0.021972 sys 0.063716<\/code><\/pre>\n<p>\u0421\u0440\u0435\u0434\u0438 \u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u0447\u0438\u043a\u043e\u0432 \u0440\u0430\u0441\u043f\u0440\u043e\u0441\u0442\u0440\u0430\u043d\u0435\u043d\u043e \u043c\u043d\u0435\u043d\u0438\u0435, \u0447\u0442\u043e SQLite \u043d\u0435 \u043f\u043e\u0434\u0445\u043e\u0434\u0438\u0442 \u0434\u043b\u044f \u0432\u0435\u0431\u0430, \u043f\u043e\u0442\u043e\u043c\u0443 \u0447\u0442\u043e \u043f\u043e\u0434\u0434\u0435\u0440\u0436\u0438\u0432\u0430\u0435\u0442 \u0442\u043e\u043b\u044c\u043a\u043e \u043e\u0434\u043d\u043e\u0433\u043e \u043a\u043b\u0438\u0435\u043d\u0442\u0430. \u042d\u0442\u043e \u043c\u0438\u0444. \u0412 \u0440\u0435\u0436\u0438\u043c\u0435 write-ahead log (\u0441\u0442\u0430\u043d\u0434\u0430\u0440\u0442\u043d\u0430\u044f \u0444\u0438\u0447\u0430 \u0441\u043e\u0432\u0440\u0435\u043c\u0435\u043d\u043d\u044b\u0445 \u0421\u0423\u0411\u0414) \u0447\u0438\u0442\u0430\u0442\u0435\u043b\u0435\u0439 \u043c\u043e\u0436\u0435\u0442 \u0431\u044b\u0442\u044c \u0441\u043a\u043e\u043b\u044c\u043a\u043e \u0443\u0433\u043e\u0434\u043d\u043e. \u041f\u0438\u0441\u0430\u0442\u0435\u043b\u044c \u2014&nbsp;\u043e\u0434\u0438\u043d, \u043d\u043e \u0447\u0430\u0441\u0442\u043e \u0431\u043e\u043b\u044c\u0448\u0435 \u0438 \u043d\u0435 \u043d\u0430\u0434\u043e.<\/p>\n<p>SQLite \u043e\u0442\u043b\u0438\u0447\u043d\u043e \u043f\u043e\u0434\u0445\u043e\u0434\u0438\u0442 \u0434\u043b\u044f \u043d\u0435\u0431\u043e\u043b\u044c\u0448\u0438\u0445 \u0441\u0430\u0439\u0442\u043e\u0432 \u0438 \u043f\u0440\u0438\u043b\u043e\u0436\u0435\u043d\u0438\u0439. \u041d\u0430\u043f\u0440\u0438\u043c\u0435\u0440, <a href=\"https:\/\/sqlite.org\/\" rel=\"noopener noreferrer nofollow\">sqlite.org<\/a> \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u0435\u0442 SQLite \u0432 \u043a\u0430\u0447\u0435\u0441\u0442\u0432\u0435 \u0431\u0430\u0437\u044b, \u043d\u0435 \u0437\u0430\u043c\u043e\u0440\u0430\u0447\u0438\u0432\u0430\u044f\u0441\u044c \u0441 \u043e\u043f\u0442\u0438\u043c\u0438\u0437\u0430\u0446\u0438\u0435\u0439 (~200 \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432 \u043d\u0430 \u0441\u0442\u0440\u0430\u043d\u0438\u0446\u0443). \u041f\u0440\u0438 \u044d\u0442\u043e\u043c \u0443 \u043d\u0435\u0433\u043e 700\u041a \u0432\u0438\u0437\u0438\u0442\u043e\u0432 \u0432 \u043c\u0435\u0441\u044f\u0446, \u0430 \u0440\u0430\u0431\u043e\u0442\u0430\u0435\u0442 \u0431\u044b\u0441\u0442\u0440\u0435\u0435 95% \u0441\u0430\u0439\u0442\u043e\u0432.<\/p>\n<h2>\u0414\u043e\u043a\u0443\u043c\u0435\u043d\u0442\u044b, \u0433\u0440\u0430\u0444\u044b \u0438 \u043f\u043e\u0438\u0441\u043a<\/h2>\n<p>\u041f\u043e\u0434\u0434\u0435\u0440\u0436\u0438\u0432\u0430\u044e\u0442\u0441\u044f \u0447\u0430\u0441\u0442\u0438\u0447\u043d\u044b\u0435 \u0438\u043d\u0434\u0435\u043a\u0441\u044b \u0438 \u0438\u043d\u0434\u0435\u043a\u0441\u044b \u043f\u043e \u0432\u044b\u0440\u0430\u0436\u0435\u043d\u0438\u044f\u043c, \u043a\u0430\u043a \u0432 \u00ab\u0431\u043e\u043b\u044c\u0448\u0438\u0445\u00bb \u0421\u0423\u0411\u0414. \u041c\u043e\u0436\u043d\u043e \u0434\u0430\u0436\u0435 \u0441\u0442\u0440\u043e\u0438\u0442\u044c \u0438\u043d\u0434\u0435\u043a\u0441\u044b \u043d\u0430 \u0432\u0438\u0440\u0442\u0443\u0430\u043b\u044c\u043d\u044b\u0445 \u0441\u0442\u043e\u043b\u0431\u0446\u0430\u0445.<\/p>\n<p>\u0422\u0430\u043a SQLite \u043c\u043e\u0436\u043d\u043e \u043f\u0440\u0435\u0432\u0440\u0430\u0442\u0438\u0442\u044c \u0445\u043e\u0442\u044c \u0432 \u0434\u043e\u043a\u0443\u043c\u0435\u043d\u0442\u043d\u0443\u044e \u0411\u0414: \u0445\u0440\u0430\u043d\u0438\u0442\u044c \u0441\u044b\u0440\u043e\u0439 json \u0438 \u0441\u0442\u0440\u043e\u0438\u0442\u044c \u0438\u043d\u0434\u0435\u043a\u0441\u044b \u043f\u043e <code>json_extract()<\/code> \u043d\u0430 \u043d\u0443\u0436\u043d\u044b\u0445 \u0441\u0442\u043e\u043b\u0431\u0446\u0430\u0445:<\/p>\n<pre><code class=\"sql\">create table currency(   body text,   code text as (json_extract(body, '$.code')),   name text as (json_extract(body, '$.name')) );  create index currency_code_idx on currency(code);  insert into currency select value from json_each(readfile('currency.sample.json'));<\/code><\/pre>\n<pre><code class=\"sql\">explain query plan select name from currency where code = 'RUR';  QUERY PLAN `--SEARCH TABLE currency USING INDEX currency_code_idx (code=?)<\/code><\/pre>\n<p>\u041c\u043e\u0436\u043d\u043e \u0438 \u043a\u0430\u043a \u0433\u0440\u0430\u0444\u043e\u0432\u0443\u044e \u0431\u0430\u0437\u0443 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u044c. \u0422\u0443\u0442 \u0443\u0436\u0435 \u043f\u0440\u0438\u0434\u0435\u0442\u0441\u044f \u043b\u0438\u0431\u043e \u0437\u043b\u044b\u0435 <code>WITH RECURSIVE<\/code> \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u044c, \u043b\u0438\u0431\u043e \u0434\u043e\u0431\u0430\u0432\u0438\u0442\u044c \u0449\u0435\u043f\u043e\u0442\u043a\u0443 \u043f\u0440\u043e\u0433\u0440\u0430\u043c\u043c\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f: <\/p>\n<p><a href=\"https:\/\/github.com\/dpapathanasiou\/simple-graph\" rel=\"noopener noreferrer nofollow\">simple-graph (GitHub)<\/a><\/p>\n<p>\u041f\u043e\u043b\u043d\u043e\u0442\u0435\u043a\u0441\u0442\u043e\u0432\u044b\u0439 \u043f\u043e\u0438\u0441\u043a \u0440\u0430\u0431\u043e\u0442\u0430\u0435\u0442 \u0438\u0437 \u043a\u043e\u0440\u043e\u0431\u043a\u0438:<\/p>\n<pre><code class=\"sql\">create virtual table books_fts using fts5(title, author, publisher);  insert into books_fts select title, author, publisher from books;  select   author,   substr(title, 1, 30) as title,   substr(publisher, 1, 10) as publisher from books_fts where books_fts match 'ann' limit 5;<\/code><\/pre>\n<pre><code class=\"markdown\">\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510 \u2502       author        \u2502             title              \u2502 publisher  \u2502 \u251c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524 \u2502 Ruby Ann Boxcar     \u2502 Ruby Ann's Down Home Trailer P \u2502 Citadel    \u2502 \u2502 Ruby Ann Boxcar     \u2502 Ruby Ann's Down Home Trailer P \u2502 Citadel    \u2502 \u2502 Lynne Ann DeSpelder \u2502 The Last Dance: Encountering D \u2502 McGraw-Hil \u2502 \u2502 Daniel Defoe        \u2502 Robinson Crusoe                \u2502 Ann Arbor  \u2502 \u2502 Ann Thwaite         \u2502 Waiting for the Party: The Lif \u2502 David R. G \u2502 \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518<\/code><\/pre>\n<p>\u0410 \u0445\u043e\u0442\u0438\u0442\u0435 in-memory \u0431\u0430\u0437\u0443 \u0434\u043b\u044f \u043f\u0440\u043e\u043c\u0435\u0436\u0443\u0442\u043e\u0447\u043d\u044b\u0445 \u0432\u044b\u0447\u0438\u0441\u043b\u0435\u043d\u0438\u0439? \u041e\u0434\u043d\u0430 \u0441\u0442\u0440\u043e\u0447\u043a\u0430 \u043a\u043e\u0434\u0430 \u043d\u0430 \u043f\u0438\u0442\u043e\u043d\u0435:<\/p>\n<pre><code class=\"python\">db = sqlite3.connect(\":memory:\")<\/code><\/pre>\n<p>\u041c\u043e\u0436\u043d\u043e \u0434\u0430\u0436\u0435 \u043e\u0431\u0440\u0430\u0449\u0430\u0442\u044c\u0441\u044f \u043a \u043d\u0435\u0439 \u0438\u0437 \u043d\u0435\u0441\u043a\u043e\u043b\u044c\u043a\u0438\u0445 \u0441\u043e\u0435\u0434\u0438\u043d\u0435\u043d\u0438\u0439:<\/p>\n<pre><code class=\"sql\">db = sqlite3.connect(\"file::memory:?cache=shared\")<\/code><\/pre>\n<h2>\u0418 \u0435\u0449\u0435 \u043c\u043d\u043e\u0433\u043e \u0432\u0441\u0435\u0433\u043e<\/h2>\n<p>\u0415\u0441\u0442\u044c \u043d\u0430\u0432\u043e\u0440\u043e\u0447\u0435\u043d\u043d\u044b\u0435 \u043e\u043a\u043e\u043d\u043d\u044b\u0435 \u0444\u0443\u043d\u043a\u0446\u0438\u0438 (\u0432 \u0442\u043e\u0447\u043d\u043e\u0441\u0442\u0438 \u043a\u0430\u043a \u0432 PostgreSQL). <code>UPSERT<\/code>, <code>UPDATE FROM<\/code> \u0438 <code>generate_series()<\/code>. R-Tree \u0438\u043d\u0434\u0435\u043a\u0441\u044b. \u0420\u0435\u0433\u0435\u043a\u0441\u043f\u044b, fuzzy-\u043f\u043e\u0438\u0441\u043a \u0438 \u0433\u0435\u043e. \u041f\u043e \u0444\u0438\u0447\u0430\u043c SQLite \u043f\u043e\u0441\u043e\u043f\u0435\u0440\u043d\u0438\u0447\u0430\u0435\u0442 \u0441 \u043b\u044e\u0431\u043e\u0439 \u00ab\u0432\u0437\u0440\u043e\u0441\u043b\u043e\u0439\u00bb \u0421\u0423\u0411\u0414.<\/p>\n<p>\u041d\u0430\u0434\u0435\u044e\u0441\u044c, \u044d\u0442\u0430 \u0441\u0442\u0430\u0442\u044c\u044f \u0432\u0434\u043e\u0445\u043d\u043e\u0432\u0438\u0442 \u0432\u0430\u0441 \u043f\u0440\u0438\u043c\u0435\u043d\u0438\u0442\u044c SQLite \u0432 \u0441\u0432\u043e\u0438\u0445 \u0437\u0430\u0434\u0430\u0447\u0430\u0445. \u0421\u043f\u0430\u0441\u0438\u0431\u043e, \u0447\u0442\u043e \u043f\u0440\u043e\u0447\u0438\u0442\u0430\u043b\u0438!<\/p>\n<p><em>\u0415\u0441\u043b\u0438 \u0438\u043d\u0442\u0435\u0440\u0435\u0441\u043d\u043e, \u043a\u0430\u043a \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u044c SQLite \u043d\u0430 \u043f\u043e\u043b\u043d\u0443\u044e \u2014 \u043f\u043e\u0434\u043f\u0438\u0441\u044b\u0432\u0430\u0439\u0442\u0435\u0441\u044c \u043d\u0430 \u043a\u0430\u043d\u0430\u043b <\/em><a href=\"https:\/\/t.me\/sqliter\" rel=\"noopener noreferrer nofollow\"><em>@sqliter<\/em><\/a><\/p>\n<\/div>\n<p> \u0441\u0441\u044b\u043b\u043a\u0430 \u043d\u0430 \u043e\u0440\u0438\u0433\u0438\u043d\u0430\u043b \u0441\u0442\u0430\u0442\u044c\u0438 <a href=\"https:\/\/habr.com\/ru\/post\/547448\/\"> https:\/\/habr.com\/ru\/post\/547448\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"\n<div class=\"post__text post__text_v2\" id=\"post-content-body\">\n<p>\u0420\u0430\u0441\u0441\u043a\u0430\u0437\u044b\u0432\u0430\u044e, \u043f\u043e\u0447\u0435\u043c\u0443 SQLite \u043e\u0442\u043b\u0438\u0447\u043d\u043e \u043f\u043e\u0434\u043e\u0439\u0434\u0435\u0442 \u0432\u0430\u043c \u0432 \u043f\u043e\u0432\u0441\u0435\u0434\u043d\u0435\u0432\u043d\u043e\u0439 \u0440\u0430\u0431\u043e\u0442\u0435. \u0418 \u043d\u0435\u0432\u0430\u0436\u043d\u043e, \u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u0447\u0438\u043a \u0432\u044b, \u0430\u043d\u0430\u043b\u0438\u0442\u0438\u043a, \u0442\u0435\u0441\u0442\u0438\u0440\u043e\u0432\u0449\u0438\u043a, \u0430\u0434\u043c\u0438\u043d \u0438\u043b\u0438 \u043f\u0440\u043e\u0434\u0430\u043a\u0442-\u043c\u0435\u043d\u0435\u0434\u0436\u0435\u0440.<\/p>\n<p>\u0414\u043b\u044f \u0437\u0430\u0442\u0440\u0430\u0432\u043a\u0438 \u043d\u0435\u0441\u043a\u043e\u043b\u044c\u043a\u043e \u0438\u0437\u0432\u0435\u0441\u0442\u043d\u044b\u0445 \u0444\u0430\u043a\u0442\u043e\u0432:<\/p>\n<ul>\n<li>\n<p>SQLite \u2014 \u0441\u0430\u043c\u0430\u044f \u0440\u0430\u0441\u043f\u0440\u043e\u0441\u0442\u0440\u0430\u043d\u0435\u043d\u043d\u0430\u044f \u0421\u0423\u0411\u0414 \u0432 \u043c\u0438\u0440\u0435, \u0432\u043a\u043b\u044e\u0447\u0435\u043d\u0430 \u0432\u043e \u0432\u0441\u0435 \u043f\u043e\u043f\u0443\u043b\u044f\u0440\u043d\u044b\u0435 \u041e\u0421.<\/p>\n<\/li>\n<li>\n<p>\u0420\u0430\u0431\u043e\u0442\u0430\u0435\u0442 \u0431\u0435\u0437 \u0441\u0435\u0440\u0432\u0435\u0440\u0430.<\/p>\n<\/li>\n<li>\n<p>\u0414\u043b\u044f \u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u0447\u0438\u043a\u043e\u0432 \u2014 \u0432\u0441\u0442\u0440\u0430\u0438\u0432\u0430\u0435\u0442\u0441\u044f \u043f\u0440\u044f\u043c\u043e \u0432 \u043f\u0440\u0438\u043b\u043e\u0436\u0435\u043d\u0438\u0435.<\/p>\n<\/li>\n<li>\n<p>\u0414\u043b\u044f \u0432\u0441\u0435\u0445 \u043e\u0441\u0442\u0430\u043b\u044c\u043d\u044b\u0445 \u2014 \u0443\u0434\u043e\u0431\u043d\u0430\u044f \u043a\u043e\u043d\u0441\u043e\u043b\u044c (REPL) \u043e\u0434\u043d\u0438\u043c \u0444\u0430\u0439\u043b\u043e\u043c (sqlite3.exe \u043d\u0430 Windows, sqlite3 \u0432 Linux \/ macOS).<\/p>\n<\/li>\n<\/ul>\n<h2>\u041a\u043e\u043d\u0441\u043e\u043b\u044c, \u0438\u043c\u043f\u043e\u0440\u0442 \u0438 \u044d\u043a\u0441\u043f\u043e\u0440\u0442<\/h2>\n<p>\u041a\u043e\u043d\u0441\u043e\u043b\u044c \u2014&nbsp;\u044d\u0442\u043e \u043a\u0438\u043b\u043b\u0435\u0440-\u0444\u0438\u0447\u0430 SQLite: \u0431\u043e\u043b\u0435\u0435 \u043c\u043e\u0449\u043d\u044b\u0439 \u0438\u043d\u0441\u0442\u0440\u0443\u043c\u0435\u043d\u0442 \u0430\u043d\u0430\u043b\u0438\u0437\u0430 \u0434\u0430\u043d\u043d\u044b\u0445, \u0447\u0435\u043c Excel, \u0438 \u0441\u0438\u043b\u044c\u043d\u043e \u0431\u043e\u043b\u0435\u0435 \u043f\u0440\u043e\u0441\u0442\u043e\u0439, \u0447\u0435\u043c \u043a\u0430\u043a\u043e\u0439-\u043d\u0438\u0431\u0443\u0434\u044c pandas. \u0414\u0430\u043d\u043d\u044b\u0435 \u0438\u0437 CSV \u0437\u0430\u0433\u0440\u0443\u0436\u0430\u044e\u0442\u0441\u044f \u043e\u0434\u043d\u043e\u0439 \u043a\u043e\u043c\u0430\u043d\u0434\u043e\u0439, \u0442\u0430\u0431\u043b\u0438\u0446\u0430 \u0441\u043e\u0437\u0434\u0430\u0435\u0442\u0441\u044f \u0430\u0432\u0442\u043e\u043c\u0430\u0442\u0438\u0447\u0435\u0441\u043a\u0438:<\/p>\n<pre><code class=\"javascript\">&gt; .import --csv city.csv city &gt; select count(*) from city; 1117<\/code><\/pre>\n<p>\u041f\u043e\u0434\u0434\u0435\u0440\u0436\u0438\u0432\u0430\u044e\u0442\u0441\u044f \u0431\u0430\u0437\u043e\u0432\u044b\u0435 SQL-\u0444\u0438\u0447\u0438, \u0430 \u0440\u0435\u0437\u0443\u043b\u044c\u0442\u0430\u0442 \u043a\u043e\u043d\u0441\u043e\u043b\u044c \u043f\u043e\u043a\u0430\u0437\u044b\u0432\u0430\u0435\u0442 \u0432 \u043f\u0440\u0438\u044f\u0442\u043d\u043e\u0439 \u0442\u0430\u0431\u043b\u0438\u0447\u043a\u0435. \u041f\u0440\u043e\u0434\u0432\u0438\u043d\u0443\u0442\u044b\u0435 SQL-\u0444\u0438\u0447\u0438 \u0442\u043e\u0436\u0435 \u0435\u0441\u0442\u044c, \u043d\u043e \u043e \u043d\u0438\u0445 \u0447\u0443\u0442\u044c \u043f\u043e\u0437\u0436\u0435. <\/p>\n<pre><code class=\"sql\">select   century || '-\u0439 \u0432\u0435\u043a' as dates,   count(*) as city_count from history group by century order by century desc;<\/code><\/pre>\n<pre><code class=\"vala\">\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510 \u2502  dates   \u2502 city_count \u2502 \u251c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524 \u2502 21-\u0439 \u0432\u0435\u043a \u2502 1          \u2502 \u2502 20-\u0439 \u0432\u0435\u043a \u2502 263        \u2502 \u2502 19-\u0439 \u0432\u0435\u043a \u2502 189        \u2502 \u2502 18-\u0439 \u0432\u0435\u043a \u2502 191        \u2502 \u2502 17-\u0439 \u0432\u0435\u043a \u2502 137        \u2502 \u2502 16-\u0439 \u0432\u0435\u043a \u2502 79         \u2502 \u2502 15-\u0439 \u0432\u0435\u043a \u2502 39         \u2502 \u2502 14-\u0439 \u0432\u0435\u043a \u2502 38         \u2502 \u2502 13-\u0439 \u0432\u0435\u043a \u2502 27         \u2502 \u2502 12-\u0439 \u0432\u0435\u043a \u2502 44         \u2502 \u2502 11-\u0439 \u0432\u0435\u043a \u2502 8          \u2502 \u2502 10-\u0439 \u0432\u0435\u043a \u2502 6          \u2502 \u2502 9-\u0439 \u0432\u0435\u043a  \u2502 4          \u2502 \u2502 5-\u0439 \u0432\u0435\u043a  \u2502 1          \u2502 \u2502 3-\u0439 \u0432\u0435\u043a  \u2502 1          \u2502 \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518<\/code><\/pre>\n<p>\u041a\u0443\u0447\u0430 \u0444\u043e\u0440\u043c\u0430\u0442\u043e\u0432 \u0432\u044b\u0433\u0440\u0443\u0437\u043a\u0438 \u0434\u0430\u043d\u043d\u044b\u0445: sql, csv, json, \u0434\u0430\u0436\u0435 markdown \u0438 html. \u0412\u0441\u0435 \u0434\u0435\u043b\u0430\u0435\u0442\u0441\u044f \u043f\u0430\u0440\u043e\u0439 \u043a\u043e\u043c\u0430\u043d\u0434:<\/p>\n<pre><code class=\"sql\">.mode json .output city.json select city, foundation_year, timezone from city limit 10; .shell cat city.json<\/code><\/pre>\n<pre><code class=\"json\">[{\"city\":\"\u0410\u0434\u044b\u0433\u0435\u0439\u0441\u043a\",\"foundation_year\":1969,\"timezone\":\"UTC+3\"}, {\"city\":\"\u041c\u0430\u0439\u043a\u043e\u043f\",\"foundation_year\":1857,\"timezone\":\"UTC+3\"}, {\"city\":\"\u0413\u043e\u0440\u043d\u043e-\u0410\u043b\u0442\u0430\u0439\u0441\u043a\",\"foundation_year\":1830,\"timezone\":\"UTC+7\"}, {\"city\":\"\u0410\u043b\u0435\u0439\u0441\u043a\",\"foundation_year\":1913,\"timezone\":\"UTC+7\"}, {\"city\":\"\u0411\u0430\u0440\u043d\u0430\u0443\u043b\",\"foundation_year\":1730,\"timezone\":\"UTC+7\"}, {\"city\":\"\u0411\u0435\u043b\u043e\u043a\u0443\u0440\u0438\u0445\u0430\",\"foundation_year\":1846,\"timezone\":\"UTC+7\"}, {\"city\":\"\u0411\u0438\u0439\u0441\u043a\",\"foundation_year\":1709,\"timezone\":\"UTC+7\"}, {\"city\":\"\u0413\u043e\u0440\u043d\u044f\u043a\",\"foundation_year\":1942,\"timezone\":\"UTC+7\"}, {\"city\":\"\u0417\u0430\u0440\u0438\u043d\u0441\u043a\",\"foundation_year\":1748,\"timezone\":\"UTC+7\"}, {\"city\":\"\u0417\u043c\u0435\u0438\u043d\u043e\u0433\u043e\u0440\u0441\u043a\",\"foundation_year\":1736,\"timezone\":\"UTC+7\"}]<\/code><\/pre>\n<h2>\u041d\u0430\u0442\u0438\u0432\u043d\u0430\u044f \u0440\u0430\u0431\u043e\u0442\u0430 \u0441 JSON<\/h2>\n<p>\u041d\u0435\u0442 \u043d\u0438\u0447\u0435\u0433\u043e \u0443\u0434\u043e\u0431\u043d\u0435\u0435 SQLite \u0434\u043b\u044f \u0430\u043d\u0430\u043b\u0438\u0437\u0430 \u0438 \u043f\u0440\u0435\u043e\u0431\u0440\u0430\u0437\u043e\u0432\u0430\u043d\u0438\u044f JSON. \u041c\u043e\u0436\u043d\u043e \u0441\u0435\u043b\u0435\u043a\u0442\u0438\u0442\u044c \u0434\u0430\u043d\u043d\u044b\u0435 \u043d\u0430\u043f\u0440\u044f\u043c\u0443\u044e \u0438\u0437 \u0444\u0430\u0439\u043b\u0430, \u043a\u0430\u043a \u0431\u0443\u0434\u0442\u043e \u044d\u0442\u043e \u043e\u0431\u044b\u0447\u043d\u0430\u044f \u0442\u0430\u0431\u043b\u0438\u0446\u0430. \u0418\u043b\u0438 \u0437\u0430\u0433\u0440\u0443\u0437\u0438\u0442\u044c \u0432 \u0442\u0430\u0431\u043b\u0438\u0446\u0443 \u0438 \u0441\u0435\u043b\u0435\u043a\u0442\u0438\u0442\u044c \u043e\u0442\u0442\u0443\u0434\u0430 \u2014 \u043a\u0430\u043a \u0432\u0430\u043c \u0443\u0434\u043e\u0431\u043d\u0435\u0435.<\/p>\n<pre><code class=\"sql\">select   json_extract(value, '$.code') as code,   json_extract(value, '$.name') as name,   json_extract(value, '$.rate') as rate,   json_extract(value, '$.default') as \"default\" from   json_each(readfile('currency.sample.json')) ;<\/code><\/pre>\n<pre><code class=\"markdown\">\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510 \u2502 code \u2502       name        \u2502    rate    \u2502 default \u2502 \u251c\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524 \u2502 AZN  \u2502 \u041c\u0430\u043d\u0430\u0442\u044b            \u2502 0.023107   \u2502 0       \u2502 \u2502 BYR  \u2502 \u0411\u0435\u043b\u043e\u0440\u0443\u0441\u0441\u043a\u0438\u0435 \u0440\u0443\u0431\u043b\u0438 \u2502 0.034966   \u2502 0       \u2502 \u2502 EUR  \u2502 \u0415\u0432\u0440\u043e              \u2502 0.011138   \u2502 0       \u2502 \u2502 GEL  \u2502 \u0413\u0440\u0443\u0437\u0438\u043d\u0441\u043a\u0438\u0439 \u043b\u0430\u0440\u0438   \u2502 0.0344     \u2502 0       \u2502 \u2502 KGS  \u2502 \u041a\u0438\u0440\u0433\u0438\u0437\u0441\u043a\u0438\u0439 \u0441\u043e\u043c    \u2502 1.131738   \u2502 0       \u2502 \u2502 KZT  \u2502 \u0422\u0435\u043d\u0433\u0435             \u2502 5.699857   \u2502 0       \u2502 \u2502 RUR  \u2502 \u0420\u0443\u0431\u043b\u0438             \u2502 1.0        \u2502 1       \u2502 \u2502 UAH  \u2502 \u0413\u0440\u0438\u0432\u043d\u044b            \u2502 0.380539   \u2502 0       \u2502 \u2502 USD  \u2502 \u0414\u043e\u043b\u043b\u0430\u0440\u044b           \u2502 0.013601   \u2502 0       \u2502 \u2502 UZS  \u2502 \u0423\u0437\u0431\u0435\u043a\u0441\u043a\u0438\u0439 \u0441\u0443\u043c     \u2502 142.441417 \u2502 0       \u2502 \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518<\/code><\/pre>\n<p>\u041d\u0435\u0432\u0430\u0436\u043d\u043e, \u043d\u0430\u0441\u043a\u043e\u043b\u044c\u043a\u043e \u0440\u0430\u0437\u0432\u0435\u0441\u0438\u0441\u0442\u044b\u0439 JSON \u2014 \u043c\u043e\u0436\u043d\u043e \u0432\u044b\u0431\u0440\u0430\u0442\u044c \u0430\u0442\u0440\u0438\u0431\u0443\u0442\u044b \u043b\u044e\u0431\u043e\u0439 \u0432\u043b\u043e\u0436\u0435\u043d\u043d\u043e\u0441\u0442\u0438: <\/p>\n<pre><code class=\"sql\">select   json_extract(value, '$.id') as id,   json_extract(value, '$.name') as name from   json_tree(readfile('industry.sample.json')) where   path like '$[%].industries' ;<\/code><\/pre>\n<pre><code class=\"markdown\">\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510 \u2502   id   \u2502         name         \u2502 \u251c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524 \u2502 7.538  \u2502 \u0418\u043d\u0442\u0435\u0440\u043d\u0435\u0442-\u043f\u0440\u043e\u0432\u0430\u0439\u0434\u0435\u0440   \u2502 \u2502 7.539  \u2502 \u0418\u0422-\u043a\u043e\u043d\u0441\u0430\u043b\u0442\u0438\u043d\u0433        \u2502 \u2502 7.540  \u2502 \u0420\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u043a\u0430 \u041f\u041e        \u2502 \u2502 9.399  \u2502 \u041c\u043e\u0431\u0438\u043b\u044c\u043d\u0430\u044f \u0441\u0432\u044f\u0437\u044c      \u2502 \u2502 9.400  \u2502 \u0424\u0438\u043a\u0441\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u0430\u044f \u0441\u0432\u044f\u0437\u044c  \u2502 \u2502 9.401  \u2502 \u041e\u043f\u0442\u043e\u0432\u043e\u043b\u043e\u043a\u043e\u043d\u043d\u0430\u044f \u0441\u0432\u044f\u0437\u044c \u2502 \u2502 43.641 \u2502 \u0410\u0443\u0434\u0438\u0442                \u2502 \u2502 43.646 \u2502 \u0421\u0442\u0440\u0430\u0445\u043e\u0432\u0430\u043d\u0438\u0435          \u2502 \u2502 43.647 \u2502 \u0411\u0430\u043d\u043a                 \u2502 \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518<\/code><\/pre>\n<h2>CTE \u0438 \u043e\u043f\u0435\u0440\u0430\u0446\u0438\u0438 \u043d\u0430\u0434 \u043c\u043d\u043e\u0436\u0435\u0441\u0442\u0432\u0430\u043c\u0438<\/h2>\n<p>\u0420\u0430\u0437\u0443\u043c\u0435\u0435\u0442\u0441\u044f, \u043f\u043e\u0434\u0434\u0435\u0440\u0436\u0438\u0432\u0430\u044e\u0442\u0441\u044f Common Table Expressions (\u043a\u043e\u043d\u0441\u0442\u0440\u0443\u043a\u0446\u0438\u044f <code>WITH<\/code>) \u0438 \u0434\u0436\u043e\u0439\u043d\u044b, \u0442\u0443\u0442 \u0434\u0430\u0436\u0435 \u043f\u0440\u0438\u043c\u0435\u0440\u044b \u043f\u0440\u0438\u0432\u043e\u0434\u0438\u0442\u044c \u043d\u0435 \u0431\u0443\u0434\u0443. \u0410 \u0435\u0441\u043b\u0438 \u0434\u0430\u043d\u043d\u044b\u0435 \u0438\u0435\u0440\u0430\u0440\u0445\u0438\u0447\u043d\u044b\u0435 (\u0442\u0430\u0431\u043b\u0438\u0446\u0430 \u0441\u0441\u044b\u043b\u0430\u0435\u0442\u0441\u044f \u0441\u0430\u043c\u0430 \u043d\u0430 \u0441\u0435\u0431\u044f \u0447\u0435\u0440\u0435\u0437 \u0441\u0442\u043e\u043b\u0431\u0435\u0446 \u0432\u0440\u043e\u0434\u0435 <code>parent_id<\/code>) \u2014&nbsp;\u043f\u043e\u043c\u043e\u0436\u0435\u0442 \u0440\u0435\u043a\u0443\u0440\u0441\u0438\u0432\u043d\u044b\u0439 <code>WITH<\/code>. \u0418\u0435\u0440\u0430\u0440\u0445\u0438\u044e \u043b\u044e\u0431\u043e\u0433\u043e \u0443\u0440\u043e\u0432\u043d\u044f \u043c\u043e\u0436\u043d\u043e \u00ab\u0440\u0430\u0437\u043c\u043e\u0442\u0430\u0442\u044c\u00bb&nbsp;\u043e\u0434\u043d\u0438\u043c \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u043c.<\/p>\n<pre><code class=\"sql\">with recursive tmp(id, name, level) as (   select id, name, 1 as level   from area   where parent_id is null    union all    select     area.id,     tmp.name || ', ' || area.name as name,     tmp.level + 1 as level   from area     join tmp on area.parent_id = tmp.id )  select * from tmp;<\/code><\/pre>\n<pre><code class=\"markdown\">\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510 \u2502  id  \u2502               name                  \u2502 level \u2502 \u251c\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524 \u2502 113  \u2502 \u0420\u043e\u0441\u0441\u0438\u044f                              \u2502 1     \u2502 \u2502 1    \u2502 \u0420\u043e\u0441\u0441\u0438\u044f, \u041c\u043e\u0441\u043a\u0432\u0430                      \u2502 2     \u2502 \u2502 1586 \u2502 \u0420\u043e\u0441\u0441\u0438\u044f, \u0421\u0430\u043c\u0430\u0440\u0441\u043a\u0430\u044f \u043e\u0431\u043b\u0430\u0441\u0442\u044c           \u2502 2     \u2502 \u2502 1588 \u2502 \u0420\u043e\u0441\u0441\u0438\u044f, \u0421\u0430\u043c\u0430\u0440\u0441\u043a\u0430\u044f \u043e\u0431\u043b\u0430\u0441\u0442\u044c, \u041a\u0438\u043d\u0435\u043b\u044c   \u2502 3     \u2502 \u2502 78   \u2502 \u0420\u043e\u0441\u0441\u0438\u044f, \u0421\u0430\u043c\u0430\u0440\u0441\u043a\u0430\u044f \u043e\u0431\u043b\u0430\u0441\u0442\u044c, \u0421\u0430\u043c\u0430\u0440\u0430   \u2502 3     \u2502 \u2502 212  \u2502 \u0420\u043e\u0441\u0441\u0438\u044f, \u0421\u0430\u043c\u0430\u0440\u0441\u043a\u0430\u044f \u043e\u0431\u043b\u0430\u0441\u0442\u044c, \u0422\u043e\u043b\u044c\u044f\u0442\u0442\u0438 \u2502 3     \u2502 \u2502 ...  \u2502 ...                                 \u2502 ...   \u2502 \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518<\/code><\/pre>\n<p>\u041c\u043d\u043e\u0436\u0435\u0441\u0442\u0432\u0430? \u041d\u0435\u0442 \u043f\u0440\u043e\u0431\u043b\u0435\u043c: <code>UNION<\/code>, <code>INTERSECT<\/code>, <code>EXCEPT<\/code> \u043a \u0432\u0430\u0448\u0438\u043c \u0443\u0441\u043b\u0443\u0433\u0430\u043c.<\/p>\n<pre><code class=\"sql\">select employer_id from employer_area where area_id = 1  except  select employer_id from employer_area where area_id = 2;<\/code><\/pre>\n<figure class=\"full-width\"><figcaption><\/figcaption><\/figure>\n<p>\u0425\u043e\u0442\u0438\u0442\u0435 \u0440\u0430\u0441\u0441\u0447\u0438\u0442\u0430\u0442\u044c \u043e\u0434\u0438\u043d \u0441\u0442\u043e\u043b\u0431\u0435\u0446 \u043d\u0430 \u043e\u0441\u043d\u043e\u0432\u0430\u043d\u0438\u0438 \u043d\u0435\u0441\u043a\u043e\u043b\u044c\u043a\u0438\u0445 \u0434\u0440\u0443\u0433\u0438\u0445? \u041f\u043e\u0436\u0430\u043b\u0443\u0439\u0441\u0442\u0430 \u2014 \u0432\u044b\u0447\u0438\u0441\u043b\u044f\u0435\u043c\u044b\u0435 \u0441\u0442\u043e\u043b\u0431\u0446\u044b:<\/p>\n<pre><code class=\"sql\">alter table vacancy add column salary_net integer as (   case when salary_gross = true then     round(salary_from\/1.13)   else     salary_from   end );<\/code><\/pre>\n<p>\u0412\u044b\u0447\u0438\u0441\u043b\u044f\u0435\u043c\u044b\u0439 \u0441\u0442\u043e\u043b\u0431\u0435\u0446 \u0432 \u0441\u0435\u043b\u0435\u043a\u0442\u0435 \u0434\u043e\u0441\u0442\u0443\u043f\u0435\u043d \u0442\u043e\u0447\u043d\u043e \u0442\u0430\u043a \u0436\u0435, \u043a\u0430\u043a \u043e\u0431\u044b\u0447\u043d\u044b\u0439:<\/p>\n<pre><code class=\"sql\">select   substr(name, 1, 40) as name,   salary_net from vacancy where   salary_currency = 'RUR'   and salary_net is not null limit 10;<\/code><\/pre>\n<pre><code class=\"vala\">\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510 \u2502                   name                   \u2502 salary_net \u2502 \u251c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524 \u2502 \u0412\u0435\u0434\u0443\u0449\u0438\u0439 \u0438\u043d\u0436\u0435\u043d\u0435\u0440-\u043f\u0440\u043e\u0433\u0440\u0430\u043c\u043c\u0438\u0441\u0442 (Delphi)     \u2502 40000      \u2502 \u2502 \u0412\u0435\u0434\u0443\u0449\u0438\u0439 \u043f\u0440\u043e\u0433\u0440\u0430\u043c\u043c\u0438\u0441\u0442 Scala \/ Java ( Senio \u2502 60000      \u2502 \u2502 Java \/ Kotlin Developer                  \u2502 150000     \u2502 \u2502 \u0412\u0435\u0434\u0443\u0449\u0438\u0439 \u0430\u043d\u0430\u043b\u0438\u0442\u0438\u043a 1\u0421                      \u2502 150000     \u2502 \u2502 \u0420\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u0447\u0438\u043a C#                           \u2502 53097      \u2502 \u2502 \u041f\u0440\u043e\u0433\u0440\u0430\u043c\u043c\u0438\u0441\u0442 1\u0421                           \u2502 80000      \u2502 \u2502 Java - \u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u0447\u0438\u043a (Middle, Senior)      \u2502 100000     \u2502 \u2502 \u041f\u0440\u043e\u0433\u0440\u0430\u043c\u043c\u0438\u0441\u0442 C#\/ .NET                     \u2502 70796      \u2502 \u2502 \u0422\u0435\u0441\u0442\u0438\u0440\u043e\u0432\u0449\u0438\u043a \u041f\u041e\/ QA engineer (\u0440\u0443\u0447\u043d\u043e\u0435 \u0442\u0435\u0441\u0442 \u2502 45000      \u2502 \u2502 \u041a\u0443\u0440\u044c\u0435\u0440                                   \u2502 17699      \u2502 \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518<\/code><\/pre>\n<h2>\u041c\u0430\u0442. \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0430<\/h2>\n<p>\u041e\u043f\u0438\u0441\u0430\u0442\u0435\u043b\u044c\u043d\u0430\u044f \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0430? \u0417\u0430\u043f\u0440\u043e\u0441\u0442\u043e: \u0441\u0440\u0435\u0434\u043d\u0435\u0435, \u043c\u0435\u0434\u0438\u0430\u043d\u0430, \u043f\u0440\u043e\u0446\u0435\u043d\u0442\u0438\u043b\u0438, \u0441\u0442\u0430\u043d\u0434\u0430\u0440\u0442\u043d\u043e\u0435 \u043e\u0442\u043a\u043b\u043e\u043d\u0435\u043d\u0438\u0435 \u0438 \u0432\u043e\u0442 \u044d\u0442\u043e \u0432\u0441\u0435. \u041f\u0440\u0430\u0432\u0434\u0430, \u043f\u0440\u0438\u0434\u0435\u0442\u0441\u044f \u043f\u043e\u0434\u043a\u043b\u044e\u0447\u0438\u0442\u044c \u0431\u0438\u0431\u043b\u0438\u043e\u0442\u0435\u043a\u0443 \u0441\u043e \u0441\u0442\u0430\u0442. \u0444\u0443\u043d\u043a\u0446\u0438\u044f\u043c\u0438, \u043d\u043e \u044d\u0442\u043e \u0442\u043e\u0436\u0435 \u043e\u0434\u043d\u0430 \u043a\u043e\u043c\u0430\u043d\u0434\u0430 (\u0438 \u043e\u0434\u0438\u043d \u0444\u0430\u0439\u043b).<\/p>\n<pre><code class=\"sql\">.load sqlite3-stats  select   count(*) as book_count,   cast(avg(num_pages) as integer) as mean,   cast(median(num_pages) as integer) as median,   mode(num_pages) as mode,   percentile_90(num_pages) as p90,   percentile_95(num_pages) as p95,   percentile_99(num_pages) as p99 from books;<\/code><\/pre>\n<pre><code class=\"vala\">\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2510 \u2502 book_count \u2502 mean \u2502 median \u2502 mode \u2502 p90 \u2502 p95 \u2502 p99  \u2502 \u251c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2524 \u2502 1483       \u2502 349  \u2502 295    \u2502 256  \u2502 640 \u2502 817 \u2502 1199 \u2502 \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2518<\/code><\/pre>\n<blockquote>\n<p><strong>\u041b\u0438\u0440\u0438\u0447\u0435\u0441\u043a\u043e\u0435 \u043e\u0442\u0441\u0442\u0443\u043f\u043b\u0435\u043d\u0438\u0435<\/strong>. SQLite \u0442\u0440\u0430\u0434\u0438\u0446\u0438\u043e\u043d\u043d\u043e \u0431\u0435\u0434\u043d\u0430 \u0444\u0443\u043d\u043a\u0446\u0438\u044f\u043c\u0438 \u043f\u043e \u0441\u0440\u0430\u0432\u043d\u0435\u043d\u0438\u044e \u0441 \u043a\u0430\u043a\u0438\u043c-\u043d\u0438\u0431\u0443\u0434\u044c \u043f\u043e\u0441\u0442\u0433\u0440\u0435\u0441\u043e\u043c. \u041d\u043e \u0438\u0445 \u043b\u0435\u0433\u043a\u043e \u0434\u043e\u0431\u0430\u0432\u0438\u0442\u044c, \u0447\u0435\u043c \u043c\u043d\u043e\u0433\u0438\u0435 \u0438 \u0437\u0430\u043d\u0438\u043c\u0430\u044e\u0442\u0441\u044f \u2014&nbsp;\u043a\u0430\u0436\u0434\u044b\u0439 \u043a\u0442\u043e \u0432\u043e \u0447\u0442\u043e \u0433\u043e\u0440\u0430\u0437\u0434. \u041f\u043e\u043b\u0443\u0447\u0430\u0435\u0442\u0441\u044f \u043b\u0435\u0433\u043a\u0438\u0439 \u0431\u0430\u0440\u0434\u0430\u043a.<\/p>\n<p>\u041f\u043e\u044d\u0442\u043e\u043c\u0443 \u044f \u0440\u0435\u0448\u0438\u043b \u0441\u0434\u0435\u043b\u0430\u0442\u044c \u043d\u043e\u0440\u043c\u0430\u043b\u044c\u043d\u044b\u0439 \u043d\u0430\u0431\u043e\u0440 \u0431\u0438\u0431\u043b\u0438\u043e\u0442\u0435\u043a, \u0441 \u0440\u0430\u0437\u0434\u0435\u043b\u0435\u043d\u0438\u0435\u043c \u043f\u043e \u043f\u0440\u0435\u0434\u043c\u0435\u0442\u043d\u043e\u0439 \u043e\u0431\u043b\u0430\u0441\u0442\u0438 \u0438 \u0430\u0432\u0442\u043e\u043c\u0430\u0442\u0438\u0447\u0435\u0441\u043a\u043e\u0439 \u0441\u0431\u043e\u0440\u043a\u043e\u0439 \u0434\u043b\u044f \u0432\u0441\u0435\u0445 \u041e\u0421. \u041f\u043e\u043a\u0430 \u0431\u0438\u0431\u043b\u0438\u043e\u0442\u0435\u043a \u0442\u0430\u043c \u043d\u0435\u043c\u043d\u043e\u0433\u043e, \u043d\u043e \u0441\u043a\u043e\u0440\u043e \u043f\u0440\u0438\u0431\u0430\u0432\u0438\u0442\u0441\u044f:<\/p>\n<p><a href=\"https:\/\/github.com\/nalgeon\/sqlite-plus\/\" rel=\"noopener noreferrer nofollow\">sqlite-plus (GitHub)<\/a><\/p>\n<\/blockquote>\n<p>\u0410 \u0432\u043e\u0442 \u0435\u0449\u0435 \u043e \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0435. \u041c\u043e\u0436\u043d\u043e \u043d\u0430\u0440\u0438\u0441\u043e\u0432\u0430\u0442\u044c \u0440\u0430\u0441\u043f\u0440\u0435\u0434\u0435\u043b\u0435\u043d\u0438\u0435 \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0439 \u043f\u0440\u044f\u043c\u043e \u0432 \u043a\u043e\u043d\u0441\u043e\u043b\u0438. \u0421\u043c\u043e\u0442\u0440\u0438\u0442\u0435, \u043a\u0430\u043a\u0430\u044f \u043c\u0438\u043b\u043e\u0442\u0430:<\/p>\n<pre><code class=\"sql\">with slots as (   select     num_pages\/100 as slot,     count(*) as book_count   from books   group by slot ), max as (   select max(book_count) as value   from slots ) select   slot,   book_count,   printf('%.' || (book_count * 30 \/ max.value) || 'c', '*') as bar from slots, max order by slot;<\/code><\/pre>\n<pre><code class=\"vala\">\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510 \u2502 slot \u2502 book_count \u2502              bar               \u2502 \u251c\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524 \u2502 0    \u2502 116        \u2502 *********                      \u2502 \u2502 1    \u2502 254        \u2502 ********************           \u2502 \u2502 2    \u2502 376        \u2502 ****************************** \u2502 \u2502 3    \u2502 285        \u2502 **********************         \u2502 \u2502 4    \u2502 184        \u2502 **************                 \u2502 \u2502 5    \u2502 90         \u2502 *******                        \u2502 \u2502 6    \u2502 54         \u2502 ****                           \u2502 \u2502 7    \u2502 41         \u2502 ***                            \u2502 \u2502 8    \u2502 31         \u2502 **                             \u2502 \u2502 9    \u2502 15         \u2502 *                              \u2502 \u2502 10   \u2502 11         \u2502 *                              \u2502 \u2502 11   \u2502 12         \u2502 *                              \u2502 \u2502 12   \u2502 2          \u2502 *                              \u2502 \u2502 13   \u2502 5          \u2502 *                              \u2502 \u2502 14   \u2502 3          \u2502 *                              \u2502 \u2502 15   \u2502 1          \u2502 *                              \u2502 \u2502 17   \u2502 1          \u2502 *                              \u2502 \u2502 18   \u2502 2          \u2502 *                              \u2502 \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518<\/code><\/pre>\n<h2>\u0411\u044b\u0441\u0442\u0440\u043e\u0434\u0435\u0439\u0441\u0442\u0432\u0438\u0435<\/h2>\n<p>SQLite \u0441\u043f\u043e\u043a\u043e\u0439\u043d\u043e \u0440\u0430\u0431\u043e\u0442\u0430\u0435\u0442 \u0441 \u0434\u0435\u0441\u044f\u0442\u043a\u0430\u043c\u0438 \u043c\u0438\u043b\u043b\u0438\u043e\u043d\u043e\u0432 \u0437\u0430\u043f\u0438\u0441\u0435\u0439 (\u0441 \u0441\u043e\u0442\u043d\u044f\u043c\u0438 \u0442\u043e\u0436\u0435 \u2014&nbsp;\u044f \u043f\u0440\u043e\u0432\u0435\u0440\u044f\u043b). \u041e\u0431\u044b\u0447\u043d\u044b\u0435 <code>INSERT<\/code> \u0434\u0430\u044e\u0442 \u043d\u0430 \u043c\u043e\u0435\u043c \u043d\u043e\u0443\u0442\u0435 \u043e\u043a\u043e\u043b\u043e 240 \u0442\u044b\u0441\u044f\u0447 \u0437\u0430\u043f\u0438\u0441\u0435\u0439 \u0432 \u0441\u0435\u043a\u0443\u043d\u0434\u0443. \u0410 \u0435\u0441\u043b\u0438 \u043f\u043e\u0434\u043a\u043b\u044e\u0447\u0438\u0442\u044c \u0438\u0441\u0445\u043e\u0434\u043d\u044b\u0439 CSV \u043a\u0430\u043a \u0432\u0438\u0440\u0442\u0443\u0430\u043b\u044c\u043d\u0443\u044e \u0442\u0430\u0431\u043b\u0438\u0446\u0443 (\u0442\u0430\u043a\u0430\u044f \u0441\u043f\u0435\u0446\u0438\u0430\u043b\u044c\u043d\u0430\u044f \u0444\u0438\u0447\u0430) \u2014&nbsp;\u0435\u0449\u0435 \u0432 2 \u0440\u0430\u0437\u0430 \u0431\u044b\u0441\u0442\u0440\u0435\u0435.<\/p>\n<pre><code class=\"sql\">.load sqlite3-vsv create virtual table temp.blocks_csv using vsv(<\/code><\/pre>\n<\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-319769","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/319769","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=319769"}],"version-history":[{"count":0,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/319769\/revisions"}],"wp:attachment":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=319769"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=319769"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=319769"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}