🕳️ 👩🏼‍⚕️ 🕢 SQLiteはおもちゃではありません 🍸 ⛴️ 🍎

SQLiteが日常業務に最適な理由はここにあります。あなたが開発者、アナリスト、テスター、管理者、または製品マネージャーであるかどうかは関係ありません。

手始めに、いくつかの既知の事実：

SQLiteは、世界で最も広く使用されているデータベース管理システムであり、一般的なすべてのオペレーティングシステムに含まれています。
サーバーなしで動作します。
開発者の場合、アプリケーションに直接埋め込まれます。
他のすべての人にとって-1つのファイル（Windowsではsqlite3.exe、Linux / macOSではsqlite3）の便利なコンソール（REPL）。

コンソール、インポートおよびエクスポート

コンソールはSQLiteのキラー機能です。Excelよりも強力なデータ分析ツールであり、パンダよりもはるかにシンプルです。CSVからのデータは1つのコマンドで読み込まれ、テーブルは自動的に作成されます。

> .import --csv city.csv city
> select count(*) from city;
1117

基本的なSQL機能がサポートされており、コンソールは結果を素敵な表に表示します。高度なSQL機能もありますが、それらについては後で詳しく説明します。

select
  century || '- ' as dates,
  count(*) as city_count
from history
group by century
order by century desc;

┌──────────┬────────────┐
│  dates   │ city_count │
├──────────┼────────────┤
│ 21-  │ 1          │
│ 20-  │ 263        │
│ 19-  │ 189        │
│ 18-  │ 191        │
│ 17-  │ 137        │
│ 16-  │ 79         │
│ 15-  │ 39         │
│ 14-  │ 38         │
│ 13-  │ 27         │
│ 12-  │ 44         │
│ 11-  │ 8          │
│ 10-  │ 6          │
│ 9-   │ 4          │
│ 5-   │ 1          │
│ 3-   │ 1          │
└──────────┴────────────┘

一連のデータエクスポート形式：sql、csv、json、さらにはマークダウンとhtml。すべてはいくつかのコマンドで行われます：

.mode json
.output city.json
select city, foundation_year, timezone from city limit 10;
.shell cat city.json

[{"city":"","foundation_year":1969,"timezone":"UTC+3"},
{"city":"","foundation_year":1857,"timezone":"UTC+3"},
{"city":"-","foundation_year":1830,"timezone":"UTC+7"},
{"city":"","foundation_year":1913,"timezone":"UTC+7"},
{"city":"","foundation_year":1730,"timezone":"UTC+7"},
{"city":"","foundation_year":1846,"timezone":"UTC+7"},
{"city":"","foundation_year":1709,"timezone":"UTC+7"},
{"city":"","foundation_year":1942,"timezone":"UTC+7"},
{"city":"","foundation_year":1748,"timezone":"UTC+7"},
{"city":"","foundation_year":1736,"timezone":"UTC+7"}]

JSONをネイティブに操作する

JSONの解析と変換にはSQLiteほど便利なものはありません。通常のテーブルであるかのように、ファイルから直接データを選択できます。または、テーブルにロードして、必要に応じてそこから選択します。

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 │       name        │    rate    │ default │
├──────┼───────────────────┼────────────┼─────────┤
│ AZN  │             │ 0.023107   │ 0       │
│ BYR  │   │ 0.034966   │ 0       │
│ EUR  │               │ 0.011138   │ 0       │
│ GEL  │     │ 0.0344     │ 0       │
│ KGS  │      │ 1.131738   │ 0       │
│ KZT  │              │ 5.699857   │ 0       │
│ RUR  │              │ 1.0        │ 1       │
│ UAH  │             │ 0.380539   │ 0       │
│ USD  │            │ 0.013601   │ 0       │
│ UZS  │       │ 142.441417 │ 0       │
└──────┴───────────────────┴────────────┴─────────┘

JSONがどれほど広大であるかは関係ありません。ネストの属性は、次のように選択できます。

select
  json_extract(value, '$.id') as id,
  json_extract(value, '$.name') as name
from
  json_tree(readfile('industry.sample.json'))
where
  path like '$[%].industries'
;

┌────────┬──────────────────────┐
│   id   │         name         │
├────────┼──────────────────────┤
│ 7.538  │ -   │
│ 7.539  │ -        │
│ 7.540  │          │
│ 9.399  │        │
│ 9.400  │    │
│ 9.401  │   │
│ 43.641 │                 │
│ 43.646 │           │
│ 43.647 │                  │
└────────┴──────────────────────┘

CTEおよびセット操作

, Common Table Expressions ( WITH

) , . ( parent_id

) — WITH

. «» .

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;

┌──────┬─────────────────────────────────────┬───────┐
│  id  │               name                  │ level │
├──────┼─────────────────────────────────────┼───────┤
│ 113  │                               │ 1     │
│ 1    │ ,                       │ 2     │
│ 1586 │ ,             │ 2     │
│ 1588 │ ,  ,    │ 3     │
│ 78   │ ,  ,    │ 3     │
│ 212  │ ,  ,  │ 3     │
│ ...  │ ...                                 │ ...   │
└──────┴─────────────────────────────────────┴───────┘

? : UNION

, INTERSECT

, EXCEPT

.

select employer_id
from employer_area
where area_id = 1

except

select employer_id
from employer_area
where area_id = 2;

? — :

alter table vacancy
add column salary_net integer as (
  case when salary_gross = true then
    round(salary_from/1.13)
  else
    salary_from
  end
);

, :

select
  substr(name, 1, 40) as name,
  salary_net
from vacancy
where
  salary_currency = 'RUR'
  and salary_net is not null
limit 10;

┌──────────────────────────────────────────┬────────────┐
│                   name                   │ salary_net │
├──────────────────────────────────────────┼────────────┤
│  - (Delphi)     │ 40000      │
│   Scala / Java ( Senio │ 60000      │
│ Java / Kotlin Developer                  │ 150000     │
│   1                      │ 150000     │
│  C#                           │ 53097      │
│  1                           │ 80000      │
│ Java -  (Middle, Senior)      │ 100000     │
│  C#/ .NET                     │ 70796      │
│  / QA engineer (  │ 45000      │
│                                    │ 17699      │
└──────────────────────────────────────────┴────────────┘

.

? : , , , . , . , ( ).

.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;

┌────────────┬──────┬────────┬──────┬─────┬─────┬──────┐
│ book_count │ mean │ median │ mode │ p90 │ p95 │ p99  │
├────────────┼──────┼────────┼──────┼─────┼─────┼──────┤
│ 1483       │ 349  │ 295    │ 256  │ 640 │ 817 │ 1199 │
└────────────┴──────┴────────┴──────┴─────┴─────┴──────┘

. SQLite - . , — . .

, . , :

sqlite-plus (GitHub)

. . , :

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;

┌──────┬────────────┬────────────────────────────────┐
│ slot │ book_count │              bar               │
├──────┼────────────┼────────────────────────────────┤
│ 0    │ 116        │ *********                      │
│ 1    │ 254        │ ********************           │
│ 2    │ 376        │ ****************************** │
│ 3    │ 285        │ **********************         │
│ 4    │ 184        │ **************                 │
│ 5    │ 90         │ *******                        │
│ 6    │ 54         │ ****                           │
│ 7    │ 41         │ ***                            │
│ 8    │ 31         │ **                             │
│ 9    │ 15         │ *                              │
│ 10   │ 11         │ *                              │
│ 11   │ 12         │ *                              │
│ 12   │ 2          │ *                              │
│ 13   │ 5          │ *                              │
│ 14   │ 3          │ *                              │
│ 15   │ 1          │ *                              │
│ 17   │ 1          │ *                              │
│ 18   │ 2          │ *                              │
└──────┴────────────┴────────────────────────────────┘

SQLite ( — ). INSERT

240 . CSV ( ) — 2 .

.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
);

.timer on
insert into blocks
select * from blocks_csv;

Run Time: real 5.176 user 4.716420 sys 0.403866

select count(*) from blocks;
3386629

Run Time: real 0.095 user 0.021972 sys 0.063716

, SQLite , . . write-ahead log ( ) . — , .

SQLite . , sqlite.org SQLite , (~200 ). 700 , 95% .

,

, «» . .

SQLite : json json_extract()

:

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'));

explain query plan
select name from currency where code = 'RUR';

QUERY PLAN
`--SEARCH TABLE currency USING INDEX currency_code_idx (code=?)

. WITH RECURSIVE

, :

simple-graph (GitHub)

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;

┌─────────────────────┬────────────────────────────────┬────────────┐
│       author        │             title              │ publisher  │
├─────────────────────┼────────────────────────────────┼────────────┤
│ Ruby Ann Boxcar     │ Ruby Ann's Down Home Trailer P │ Citadel    │
│ Ruby Ann Boxcar     │ Ruby Ann's Down Home Trailer P │ Citadel    │
│ Lynne Ann DeSpelder │ The Last Dance: Encountering D │ McGraw-Hil │
│ Daniel Defoe        │ Robinson Crusoe                │ Ann Arbor  │
│ Ann Thwaite         │ Waiting for the Party: The Lif │ David R. G │
└─────────────────────┴────────────────────────────────┴────────────┘

in-memory ? :

db = sqlite3.connect(":memory:")

db = sqlite3.connect("file::memory:?cache=shared")

( PostgreSQL). UPSERT

, UPDATE FROM

generate_series()

. R-Tree . , fuzzy- . SQLite «» .

, SQLite . , !

, SQLite — @sqliter

SQLiteはおもちゃではありません

コンソール、インポートおよびエクスポート

JSONをネイティブに操作する

CTEおよびセット操作

.

,

More articles: