クエリ時に精度を選択できるベクトル検索エンジンを構築した方法

1CREATE TABLE fruit_animal 
2ENGINE = MergeTree 
3ORDER BY word 
4AS SELECT * 
5FROM VALUES(
6  'word String, vec Array(Float64)',
7  ('apple', [-0.99105519, 1.28887844, -0.43526649, -0.98520696, 0.66154391]),
8  ('banana', [-0.69372815, 0.25587061, -0.88226235, -2.54593015, 0.05300475]),
9  ('orange', [0.93338752, 2.06571317, -0.54612565, -1.51625717, 0.69775337]),
10  ('dog', [0.72138876, 1.55757105, 2.10953259, -0.33961248, -0.62217325]),
11  ('horse', [-0.61435682, 0.48542571, 1.21091247, -0.62530446, -1.33082533])
12);

1SELECT word, L2Distance(
2  vec, [-0.88693672, 1.31532824, -0.51182908, -0.99652702, 0.59907770]
3) AS distance
4FROM fruit_animal 
5ORDER BY distance 
6LIMIT 5;

1┌─word───┬────────────distance─┐
21. │ apple  │ 0.14639757188169716 │
32. │ banana │  1.9989613690076786 │
43. │ orange │   2.039041552613732 │
54. │ horse  │  2.7555776805484813 │
65. │ dog    │   3.382295083120104 │
7   └────────┴─────────────────────┘

1CREATE TABLE fruit_animal
2(
3    word String,
4    vec QBit(Float64, 5)
5)
6ENGINE = MergeTree
7ORDER BY word;
8
9INSERT INTO fruit_animal VALUES
10('apple',  [...]),
11('banana', [...]),
12('orange', [...]),
13('dog',    [...]),
14('horse',  [...]);

1SELECT 
2  word, 
3  L2DistanceTransposed(vec, [...], 16) AS distance 
4FROM fruit_animal 
5ORDER BY distance;

1┌─word───┬────────────distance─┐
21. │ apple  │ 0.14639757188169716 │
32. │ banana │   1.998961369007679 │
43. │ orange │   2.039041552613732 │
54. │ cat    │   2.752802631487914 │
65. │ horse  │  2.7555776805484813 │
76. │ dog    │   3.382295083120104 │
8   └────────┴─────────────────────┘

1SELECT 
2  word, 
3  L2DistanceTransposed(vec, [...], 16) AS distance 
4FROM fruit_animal 
5ORDER BY distance;

1set allow_experimental_qbit_type = 1;
2ALTER TABLE dbpedia ADD COLUMN qbit QBit(Float32, 1536);
3ALTER TABLE dbpedia UPDATE qbit = vector WHERE 1;

1Row 1:
2──────
3title:                Apollo program
4text:                 The Apollo program, also known as Project Apollo, was the third United States human spaceflight program carried out by the National Aeronautics and Space Administration (NASA), which accomplished landing the first humans on the Moon from 1969 to 1972. First conceived during Dwight D. Eisenhower's administration as a three-man spacecraft to follow the one-man Project Mercury which put the first Americans in space, Apollo was later dedicated to President John F.
5num_concepts_matched: 4
6min_distance:         0.82420665
7avg_distance:         1.0207901149988174
8
9Row 2:
10──────
11title:                Apollo 8
12text:                 Apollo 8, the second human spaceflight mission in the United States Apollo space program, was launched on December 21, 1968, and became the first manned spacecraft to leave Earth orbit, reach the Earth's Moon, orbit it and return safely to Earth.
13num_concepts_matched: 4
14min_distance:         0.8285278
15avg_distance:         1.0357224345207214
16
17Row 3:
18──────
19title:                Lunar Orbiter 1
20text:                 The Lunar Orbiter 1 robotic (unmanned) spacecraft, part of the Lunar Orbiter Program, was the first American spacecraft to orbit the Moon.  It was designed primarily to photograph smooth areas of the lunar surface for selection and verification of safe landing sites for the Surveyor and Apollo missions. It was also equipped to collect selenodetic, radiation intensity, and micrometeoroid impact data.The spacecraft was placed in an Earth parking orbit on August 10, 1966 at 19:31 (UTC).
21num_concepts_matched: 4
22min_distance:         0.94581836
23avg_distance:         1.0584313124418259
24
25Row 4:
26──────
27title:                Apollo (spacecraft)
28text:                 The Apollo spacecraft was composed of three parts designed to accomplish the American Apollo program's goal of landing astronauts on the Moon by the end of the 1960s and returning them safely to Earth.  The expendable (single-use) spacecraft consisted of a combined Command/Service Module (CSM) and a Lunar Module (LM).
29num_concepts_matched: 4
30min_distance:         0.9643517
31avg_distance:         1.0367188602685928
32
33Row 5:
34──────
35title:                Surveyor 1
36text:                 Surveyor 1 was the first lunar soft-lander in the unmanned  Surveyor program of the National Aeronautics and Space Administration (NASA, United States). This lunar soft-lander gathered data about the lunar surface that would be needed for the manned Apollo Moon landings that began in 1969.
37num_concepts_matched: 4
38min_distance:         0.9738264
39avg_distance:         1.0988530814647675
40
41Row 6:
42──────
43title:                Spaceflight
44text:                 Spaceflight (also written space flight) is ballistic flight into or through outer space. Spaceflight can occur with spacecraft with or without humans on board. Examples of human spaceflight include the Russian Soyuz program, the U.S. Space shuttle program, as well as the ongoing International Space Station. Examples of unmanned spaceflight include space probes that leave Earth orbit, as well as satellites in orbit around Earth, such as communications satellites.
45num_concepts_matched: 4
46min_distance:         0.9831049
47avg_distance:         1.060678943991661
48
49Row 7:
50──────
51title:                Skylab
52text:                 Skylab was a space station launched and operated by NASA and was the United States' first space station. Skylab orbited the Earth from 1973 to 1979, and included a workshop, a solar observatory, and other systems. It was launched unmanned by a modified Saturn V rocket, with a weight of 169,950 pounds (77 t).  Three manned missions to the station, conducted between 1973 and 1974 using the Apollo Command/Service Module (CSM) atop the smaller Saturn IB, each delivered a three-astronaut crew.
53num_concepts_matched: 4
54min_distance:         0.99155205
55avg_distance:         1.0769911855459213
56
57Row 8:
58──────
59title:                Orbital spaceflight
60text:                 An orbital spaceflight (or orbital flight) is a spaceflight in which a spacecraft is placed on a trajectory where it could remain in space for at least one orbit. To do this around the Earth, it must be on a free trajectory which has an altitude at perigee (altitude at closest approach) above 100 kilometers (62 mi) (this is, by at least one convention, the boundary of space).  To remain in orbit at this altitude requires an orbital speed of ~7.8 km/s.
61num_concepts_matched: 4
62min_distance:         1.0075209
63avg_distance:         1.085978478193283
64
65Row 9:
66───────
67title:                Dragon (spacecraft)
68text:                 Dragon is a partially reusable spacecraft developed by SpaceX, an American private space transportation company based in Hawthorne, California. Dragon is launched into space by the SpaceX Falcon 9 two-stage-to-orbit launch vehicle, and SpaceX is developing a crewed version called the Dragon V2.During its maiden flight in December 2010, Dragon became the first commercially built and operated spacecraft to be recovered successfully from orbit.
69num_concepts_matched: 4
70min_distance:         1.0222818
71avg_distance:         1.0942841172218323
72
73Row 10:
74───────
75title:                Space capsule
76text:                 A space capsule is an often manned spacecraft which has a simple shape for the main section, without any wings or other features to create lift during atmospheric reentry.Capsules have been used in most of the manned space programs to date, including the world's first manned spacecraft Vostok and Mercury, as well as in later Soviet Voskhod, Soyuz, Zond/L1, L3, TKS, US Gemini, Apollo Command Module, Chinese Shenzhou and US, Russian and Indian manned spacecraft currently being developed.
77num_concepts_matched: 4
78min_distance:         1.0262821
79avg_distance:         1.0882147550582886

1Row 1:
2──────
3title:                Apollo 8
4text:                 Apollo 8, the second human spaceflight mission in the United States Apollo space program, was launched on December 21, 1968, and became the first manned spacecraft to leave Earth orbit, reach the Earth's Moon, orbit it and return safely to Earth.
5num_concepts_matched: 4
6min_distance:         0.9924246668815613
7avg_distance:         0.9929515272378922
8
9Row 2:
10──────
11title:                Apollo program
12text:                 The Apollo program, also known as Project Apollo, was the third United States human spaceflight program carried out by the National Aeronautics and Space Administration (NASA), which accomplished landing the first humans on the Moon from 1969 to 1972. First conceived during Dwight D. Eisenhower's administration as a three-man spacecraft to follow the one-man Project Mercury which put the first Americans in space, Apollo was later dedicated to President John F.
13num_concepts_matched: 4
14min_distance:         0.9924481511116028
15avg_distance:         0.9929344654083252
16
17Row 3:
18──────
19title:                Apollo 5
20text:                 Apollo 5 was the first unmanned flight of the Apollo Lunar Module (LM), which would later carry astronauts to the lunar surface. It lifted off on January 22, 1968, with a Saturn IB rocket on an Earth-orbital flight.
21num_concepts_matched: 4
22min_distance:         0.9925317764282227
23avg_distance:         0.9930042922496796
24
25Row 4:
26──────
27title:                Apollo (spacecraft)
28text:                 The Apollo spacecraft was composed of three parts designed to accomplish the American Apollo program's goal of landing astronauts on the Moon by the end of the 1960s and returning them safely to Earth.  The expendable (single-use) spacecraft consisted of a combined Command/Service Module (CSM) and a Lunar Module (LM).
29num_concepts_matched: 4
30min_distance:         0.9926576018333435
31avg_distance:         0.9928816854953766
32
33Row 5:
34──────
35title:                Lunar Orbiter 1
36text:                 The Lunar Orbiter 1 robotic (unmanned) spacecraft, part of the Lunar Orbiter Program, was the first American spacecraft to orbit the Moon.  It was designed primarily to photograph smooth areas of the lunar surface for selection and verification of safe landing sites for the Surveyor and Apollo missions. It was also equipped to collect selenodetic, radiation intensity, and micrometeoroid impact data.The spacecraft was placed in an Earth parking orbit on August 10, 1966 at 19:31 (UTC).
37num_concepts_matched: 4
38min_distance:         0.9926905632019043
39avg_distance:         0.9929626882076263
40
41Row 6:
42──────
43title:                Spaceflight
44text:                 Spaceflight (also written space flight) is ballistic flight into or through outer space. Spaceflight can occur with spacecraft with or without humans on board. Examples of human spaceflight include the Russian Soyuz program, the U.S. Space shuttle program, as well as the ongoing International Space Station. Examples of unmanned spaceflight include space probes that leave Earth orbit, as well as satellites in orbit around Earth, such as communications satellites.
45num_concepts_matched: 4
46min_distance:         0.9927355647087097
47avg_distance:         0.992923766374588
48
49Row 7:
50──────
51title:                Surveyor 1
52text:                 Surveyor 1 was the first lunar soft-lander in the unmanned  Surveyor program of the National Aeronautics and Space Administration (NASA, United States). This lunar soft-lander gathered data about the lunar surface that would be needed for the manned Apollo Moon landings that began in 1969.
53num_concepts_matched: 4
54min_distance:         0.9927787184715271
55avg_distance:         0.9931300282478333
56
57Row 8:
58──────
59title:                Orbital spaceflight
60text:                 An orbital spaceflight (or orbital flight) is a spaceflight in which a spacecraft is placed on a trajectory where it could remain in space for at least one orbit. To do this around the Earth, it must be on a free trajectory which has an altitude at perigee (altitude at closest approach) above 100 kilometers (62 mi) (this is, by at least one convention, the boundary of space).  To remain in orbit at this altitude requires an orbital speed of ~7.8 km/s.
61num_concepts_matched: 4
62min_distance:         0.9927811026573181
63avg_distance:         0.9929587244987488
64
65Row 9:
66───────
67title:                DSE-Alpha
68text:                 Deep Space Expedition Alpha (DSE-Alpha), is the name given to the mission proposed in 2005 to take the first space tourists to fly around the Moon. The mission is organized by Space Adventures Ltd., a commercial spaceflight company. The plans involve a modified Soyuz capsule docking with a booster rocket in Earth orbit which then sends the spacecraft on a free return circumlunar trajectory that circles around the Moon once.
69num_concepts_matched: 4
70min_distance:         0.9928749799728394
71avg_distance:         0.9931814968585968
72
73Row 10:
74───────
75title:                Luna programme
76text:                 The Luna programme (from the Russian word Луна "Luna" meaning "Moon"), occasionally called Lunik or Lunnik by western media, was a series of robotic spacecraft missions sent to the Moon by the Soviet Union between 1959 and 1976. Fifteen were successful, each designed as either an orbiter or lander, and accomplished many firsts in space exploration.
77num_concepts_matched: 4
78min_distance:         0.9929065704345703
79avg_distance:         0.9930566549301147