Про тип данных List
Чем больше я знакомлюсь с DuckDB, тем больше он мне нравится. Хотя основные усилия направлены на освоение С-API, тем не менее, урывками стараюсь знакомиться с новыми для меня конструкциями SQL, с которыми я не сталкивался ранее.
В DuckDB синтаксис SQL расширен несколькими нестандартными типами данных - List, Struct и Map. Нужно упомянуть, что типа данных JSON нет. Имеется значительное количество функций для работы с JSON-строками, но хранятся они как строки в VARCHAR.
Разработчики DuckDB декларируют, что List и Struct аналогичны типам данных Array и Row в Postgres. Но разница в названиях намекает на то, что это все таки разные вещи.
Я пока не прочуствовал в каких ситуациях применение этих типов данных оправдано. Это отложено на потом.
Прямо сейчас пойдет речь о List.
List предназначен для хранения массивов однотипных величин.
Проиллюстрируем работу с List простейшими примерами.
Предположим, что нам нужно организовать работу с данными для блога, в котором каждый пост может помечаться тэгами. Это можно сделать используя две таблицы posts и tags
| SQL | 1
2
| CREATE TABLE posts (postId INT, content VARCHAR);
CREATE TABLE tags (postId INT, tag VARCHAR); |
|
| SQL | 1
2
| INSERT INTO posts(postId, content) VALUES(1, 'post1'),(2, 'post2'),(3, 'post3');
INSERT INTO tags(postId, tag) VALUES(1, 'tag1a'),(1, 'tag1b'),(1, 'tag1c'),(2, 'tag2a'); |
|
| SQL | 1
2
3
4
5
6
7
8
| SELECT * FROM posts;
┌────────┬─────────┐
│ postId │ content │
├────────┼─────────┤
│ 1 │ post1 │
│ 2 │ post2 │
│ 3 │ post3 │
└────────┴─────────┘ |
|
| SQL | 1
2
3
4
5
6
7
8
9
| SELECT * FROM tags;
┌────────┬───────┐
│ postId │ tag │
├────────┼───────┤
│ 1 │ tag1a │
│ 1 │ tag1b │
│ 1 │ tag1c │
│ 2 │ tag2a │
└────────┴───────┘ |
|
| SQL | 1
2
3
4
5
6
7
8
9
10
11
12
| SELECT p.postId, p.content, t.tag FROM
posts AS p
LEFT JOIN tags AS t ON t.postId=p.postId;
┌────────┬─────────┬───────┐
│ postId │ content │ tag │
├────────┼─────────┼───────┤
│ 1 │ post1 │ tag1c │
│ 2 │ post2 │ tag2a │
│ 1 │ post1 │ tag1b │
│ 1 │ post1 │ tag1a │
│ 3 │ post3 │ │
└────────┴─────────┴───────┘ |
|
| SQL | 1
2
3
4
5
6
| CREATE TABLE postsWithTags (postId INT, content VARCHAR, tags VARCHAR[]);
INSERT INTO postsWithTags (postId, content, tags)
SELECT p.postId, p.content, list(t.tag) FROM
posts AS p
LEFT JOIN tags AS t ON t.postId=p.postId
GROUP BY p.postId, p.content; |
|
| SQL | 1
2
3
4
5
6
7
8
| SELECT * FROM postsWithTags;
┌────────┬─────────┬───────────────────────┐
│ postId │ content │ tags │
├────────┼─────────┼───────────────────────┤
│ 1 │ post1 │ [tag1c, tag1b, tag1a] │
│ 2 │ post2 │ [tag2a] │
│ 3 │ post3 │ [NULL] │
└────────┴─────────┴───────────────────────┘ |
|
Для этого воспользуемся функцией unnest()
| SQL | 1
2
3
4
5
| CREATE TABLE posts1 (postId INT, content VARCHAR);
INSERT INTO posts1(postId, content) SELECT postId,content FROM postsWithTags;
CREATE TABLE tags1 (postId INT, tag VARCHAR);
INSERT INTO tags1(postId, tag) SELECT postId, unnest(tags) FROM postsWithTags; |
|
| SQL | 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| SELECT * FROM posts1;
SELECT * FROM tags1;
┌────────┬─────────┐
│ postId │ content │
├────────┼─────────┤
│ 1 │ post1 │
│ 2 │ post2 │
│ 3 │ post3 │
└────────┴─────────┘
┌────────┬───────┐
│ postId │ tag │
├────────┼───────┤
│ 1 │ tag1c │
│ 1 │ tag1b │
│ 1 │ tag1a │
│ 2 │ tag2a │
│ 3 │ │
└────────┴───────┘ |
|
|
