با Sqlite شوخی نکنیم!

1> .import --csv city.csv city
2> select count(*) from city;
31117

1select
2  century || ' century' as dates,
3  count(*) as city_count
4from history
5group by century
6order by century desc;

1┌────────────┬────────────┐
2│   dates    │ city_count │
3├────────────┼────────────┤
4│ 21 century │ 1          │
5│ 20 century │ 263        │
6│ 19 century │ 189        │
7│ 18 century │ 191        │
8│ 17 century │ 137        │
9│ ...        │ ...        │
10└────────────┴────────────┘

1.mode json
2.output city.json
3select city, country from city limit 3;

1[
2    { "city": "Amsterdam", "country": "Nederlands" },
3    { "city": "Berlin", "country": "Germany" },
4    { "city": "Helsinki", "country": "Finland" }
5]

1select
2    json_extract(value, '$.iso.code') as code,
3    json_extract(value, '$.iso.number') as num,
4    json_extract(value, '$.name') as name,
5    json_extract(value, '$.units.major.name') as unit
6from
7    json_each(readfile('currency.json'))
8;

1┌──────┬─────┬─────────────────┬──────────┐
2│ code │ num │      name       │   unit   │
3├──────┼─────┼─────────────────┼──────────┤
4│ ARS  │ 032 │ Argentine peso  | peso     │
5│ CHF  │ 756 │ Swiss Franc     │ franc    │
6│ EUR  │ 978 │ Euro            │ euro     │
7│ GBP  │ 826 │ British Pound   │ pound    │
8│ INR  │ 356 │ Indian Rupee    │ rupee    │
9│ JPY  │ 392 │ Japanese yen    │ yen      │
10│ MAD  │ 504 │ Moroccan Dirham │ dirham   │
11│ RUR  │ 643 │ Russian Rouble  │ rouble   │
12│ SOS  │ 706 │ Somali Shilling │ shilling │
13│ USD  │ 840 │ US Dollar       │ dollar   │
14└──────┴─────┴─────────────────┴──────────┘

1.load sqlite3-stats
2
3select
4  count(*) as book_count,
5  cast(avg(num_pages) as integer) as mean,
6  cast(median(num_pages) as integer) as median,
7  mode(num_pages) as mode,
8  percentile_90(num_pages) as p90,
9  percentile_95(num_pages) as p95,
10  percentile_99(num_pages) as p99
11from books;

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

1with slots as (
2  select
3    num_pages/100 as slot,
4    count(*) as book_count
5  from books
6  group by slot
7),
8max as (
9  select max(book_count) as value
10  from slots
11)
12select
13  slot,
14  book_count,
15  printf('%.' || (book_count * 30 / max.value) || 'c', '*') as bar
16from slots, max
17order by slot;

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

1.load sqlite3-vsv
2
3create virtual table temp.blocks_csv using vsv(
4    filename="ipblocks.csv",
5    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)",
6    columns=10,
7    header=on,
8    nulls=on
9);

1.timer on
2insert into blocks
3select * from blocks_csv;
4
5Run Time: real 5.176 user 4.716420 sys 0.403866

1select count(*) from blocks;
23386629
3
4Run Time: real 0.095 user 0.021972 sys 0.063716

1create table currency(
2  body text,
3  code text as (json_extract(body, '$.code')),
4  name text as (json_extract(body, '$.name'))
5);
6
7create index currency_code_idx on currency(code);
8
9insert into currency
10select value
11from json_each(readfile('currency.sample.json'));

1explain query plan
2select name from currency where code = 'EUR';
3
4QUERY PLAN
5`--SEARCH TABLE currency USING INDEX currency_code_idx (code=?)

1create virtual table books_fts
2using fts5(title, author, publisher);
3
4insert into books_fts
5select title, author, publisher from books;
6
7select
8  author,
9  substr(title, 1, 30) as title,
10  substr(publisher, 1, 10) as publisher
11from books_fts
12where books_fts match 'ann'
13limit 5;

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

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

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