10000 added missing funciton · Bubby2015/azure-sql-db-openai@77bc060 · GitHub
[go: up one dir, main page]
Skip to content

Commit 77bc060

Browse files
yorekyorek
committed
added missing funciton
1 parent bdb27c6 commit 77bc060

File tree

1 file changed

+61
-11
lines changed

1 file changed

+61
-11
lines changed

‎python/00-setup-database.sql

Lines changed: 61 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -1,22 +1,72 @@
1-
DROP TABLE IF EXISTS dbo.document_embeddings
2-
DROP TABLE IF EXISTS dbo.documents
1+
drop table if exists dbo.document_embeddings
2+
drop table if exists dbo.documents
33
go
44

5-
CREATE TABLE dbo.documents (id INT CONSTRAINT pk__documents PRIMARY KEY IDENTITY, content NVARCHAR(MAX), embedding NVARCHAR(MAX))
6-
CREATE TABLE dbo.document_embeddings (id INT REFERENCES dbo.documents(id), vector_value_id INT, vector_value FLOAT)
5+
create table dbo.documents
6+
(
7+
id int constraint pk__documents primary key,
8+
content nvarchar(max),
9+
embedding nvarchar(max)
10+
)
11+
create table dbo.document_embeddings
12+
(
13+
id int references dbo.documents(id),
14+
vector_value_id int,
15+
vector_value float
16+
)
717
go
818

9-
CREATE CLUSTERED COLUMNSTORE INDEX csi__document_embeddings ON dbo.document_embeddings ORDER (id)
19+
create clustered columnstore index csi__document_embeddings
20+
on dbo.document_embeddings order (id)
1021
go
1122

12-
IF NOT EXISTS(SELECT * FROM sys.fulltext_catalogs WHERE [name] = 'FullTextCatalog')
13-
BEGIN
14-
CREATE FULLTEXT CATALOG [FullTextCatalog] AS DEFAULT;
15-
END
23+
if not exists(select * from sys.fulltext_catalogs where [name] = 'FullTextCatalog')
24+
begin
25+
create fulltext catalog [FullTextCatalog] as default;
26+
end
1627
go
1728

18-
CREATE FULLTEXT INDEX ON dbo.documents (content) KEY INDEX pk__documents;
29+
create fulltext index on dbo.documents (content) key index pk__documents;
1930
go
2031

21-
ALTER FULLTEXT INDEX ON dbo.documents ENABLE;
32+
alter fulltext index on dbo.documents enable;
33+
go
34+
35+
create or alter function dbo.similar_documents(@vector nvarchar(max))
36+
returns table
37+
as
38+
return
39+
with cteVector as
40+
(
41+
select
42+
cast([key] as int) as [vector_value_id],
43+
cast([value] as float) as [vector_value]
44+
from
45+
openjson(@vector)
46+
),
47+
cteSimilar as
48+
(
49+
select top (50)
50+
v2.id,
51+
1-sum(v1.[vector_value] * v2.[vector_value]) /
52+
(
53+
sqrt(sum(v1.[vector_value] * v1.[vector_value]))
54+
*
55+
sqrt(sum(v2.[vector_value] * v2.[vector_value]))
56+
) as cosine_distance
57+
from
58+
cteVector v1
59+
inner join
60+
dbo.document_embeddings v2 on v1.vector_value_id = v2.vector_value_id
61+
group by
62+
v2.id
63+
order by
64+
cosine_distance
65+
)
66+
select
67+
rank() over (order by r.cosine_distance) as rank,
68+
r.id,
69+
r.cosine_distance
70+
from
71+
cteSimilar r
2272
go

0 commit comments

Comments
 (0)
0