Hybrid retrieval method and system based on vector similarity and SQL (Structured Query Language) structural constraint

Abstract

The invention discloses a hybrid retrieval method and system based on vector similarity and SQL (Structured Query Language) structured constraints, and mainly relates to the technical field of data retrieval and data processing. Comprising the following steps: preprocessing original data, constructing a vector storage space and a relational storage space, and establishing association mapping; receiving a retrieval request containing query information and structured constraints; analyzing the request to generate a query feature vector, a vector retrieval task and a structured retrieval task; cooperatively executing the two types of retrieval tasks, and obtaining candidate identifiers which simultaneously meet a similarity condition and a constraint condition; and fusing corresponding information and sorting and outputting a result. The method has the beneficial effects that deep fusion of vector semantic matching and SQL structured screening is realized, and the retrieval accuracy and efficiency are remarkably improved; quick response of mass data is supported through a dual-storage architecture and a dynamic retrieval strategy; the SQL grammar is expanded, the use threshold is lowered, and the user experience is optimized.

Description

Technical Field

[0001] This invention relates to the field of data retrieval and data processing technology, specifically a hybrid retrieval method and system based on vector similarity and SQL structured constraints. Background Technology

[0002] With the explosive growth of video content such as short videos, live streams, and film and television materials, video content retrieval has become a core requirement in the field of multimedia information processing, and is widely used in various scenarios such as content review, material management, intelligent recommendation, and copyright protection. Currently, video content retrieval has upgraded from traditional keyword matching to precise retrieval based on semantic understanding and visual features, while the rise of large-scale models has provided strong technical support for the efficient extraction of key video information (tags, summaries, frame features).

[0003] However, in practical applications, a single retrieval method is insufficient to meet the requirements of accuracy and efficiency in video content retrieval. On the one hand, while pure vector similarity retrieval can achieve semantic-level similarity matching and efficiently mine the potential associations of video tags, summaries, and frame features extracted by large models, it lacks structured constraint capabilities and cannot accurately filter videos with structured attributes such as specific format, time range, author, and resolution, easily leading to redundant search results and insufficient accuracy. On the other hand, while pure SQL structured constraint retrieval can accurately filter structured attributes, it cannot achieve semantic-level similarity matching and is difficult to handle mixed retrieval needs such as "finding videos that are semantically similar to a certain video and were published within the last 3 months".

[0004] Meanwhile, the video tags, summaries, and video frame data extracted by large models are of diverse types (text, images). The high-dimensional vectors generated after vectorization and the structured attributes of the video (release time, resolution, classification tags, etc.) belong to different data dimensions. How to achieve collaborative retrieval of the two types of data and balance retrieval efficiency and accuracy has become the core technical challenge facing the current video retrieval field.

[0005] Therefore, there is an urgent need for a hybrid retrieval method and system based on vector similarity and SQL structured constraints to solve the above problems. Summary of the Invention

[0006] The purpose of this invention is to provide a hybrid retrieval method and system based on vector similarity and SQL structured constraints. It achieves deep integration of vector semantic matching and SQL structured filtering, significantly improving retrieval accuracy and efficiency. Through a dual-storage architecture and dynamic retrieval strategy, it supports rapid response to massive amounts of data. Extended SQL syntax lowers the barrier to entry and optimizes the user experience.

[0007] To achieve the above objectives, the present invention employs the following technical solution: On the one hand, this invention provides a hybrid retrieval method based on vector similarity and SQL structured constraints, comprising the following steps: Step S1: Preprocess the original dataset, construct a vector storage space including feature vectors of each original data, a relational storage space including structured attribute information of each original data, and establish an association mapping between feature vectors and structured attribute information; Step S2: Receive a retrieval request, which includes query information for semantic matching and structured constraints for filtering; Step S3: Parse the retrieval request, generate a query feature vector based on the query information, and generate a vector retrieval task and a structured retrieval task; Step S4: Collaboratively execute vector retrieval and structured retrieval tasks to obtain a set of candidate data identifiers that simultaneously satisfy both vector similarity and structured constraints; Step S5: Based on the candidate data identifier set, obtain the corresponding feature vector information and structured attribute information from the vector storage space and relational storage space, fuse and sort the obtained information, and generate the final retrieval results.

[0008] Preferably, in step S1, the original dataset is preprocessed, specifically as follows: The original data are analyzed using a multimodal large model to extract unstructured information, which includes at least one of semantic tags, text summaries, and keyframe images. Step S11: Perform quality optimization processing on the extracted unstructured information. The quality optimization processing includes deduplication of semantic tags, noise reduction of text summaries, filtering of keyframe images, and size unification. Step S12: Use a vectorization model to convert the unstructured information that has undergone quality optimization into feature vectors with a uniform preset dimension, and then normalize the feature vectors. Step S13: Assign a vector identifier to each feature vector, store the feature vector and its vector identifier in the vector database, and build a vector index in the vector database to support approximate nearest neighbor search, thereby constructing a vector storage space; Step S14: Store the structured attribute information of each original data as records in a relational database, and assign a data identifier to each record, thereby constructing a relational storage space; Step S15: Establish an association mapping table in the relational database, and construct the association mapping between feature vectors and structured attribute information by recording the correspondence between data identifiers and vector identifiers.

[0009] Preferably, step S11 specifically includes: A dynamic frame sampling strategy is adopted based on the duration of the original data. For original data with a duration not exceeding the first preset duration, a first preset number of key frames are sampled. For original data with a duration exceeding the first preset duration, the number of key frames sampled is increased proportionally, but the total number of samples does not exceed the second preset number. Step S12 specifically includes: The Sentence-BERT model is used to convert semantic tags and text summaries into text feature vectors, and the CLIP model is used to convert keyframe images into image feature vectors. The preset dimensions of the text feature vectors and image feature vectors are both 768-dimensional. Step S13 specifically includes: A vector index is constructed based on the HNSW algorithm, with index parameter M set to 16 and efConstruction set to 200. Cosine similarity is used as the calculation method for vector similarity.

[0010] Preferably, the retrieval request received in step S2 is in the form of an extended structured query statement including a vector similarity calculation function; the vector similarity calculation function is used to calculate the similarity between the query feature vector and the feature vector in the vector storage space, and supports specifying both the vector similarity threshold and structured constraints in the extended structured query statement. Step S3, parsing the retrieval request, further includes: The extended structured query statement is parsed to extract query information, vector similarity threshold and structured constraints for generating query feature vectors. The query information is then input into the same vectorization model as in step S1 to generate the corresponding query feature vector. The vector retrieval task is constructed based on the query feature vector and the vector similarity threshold. The structured retrieval task is constructed based on the structured constraints.

[0011] Preferably, the collaborative execution of the vector retrieval task and the structured retrieval task in step S4 specifically includes: Step S41: Evaluate the screening strength of the structured constraints; Step S42: When the structured constraints can narrow down the candidate data range to within a preset ratio, the first retrieval strategy is adopted; specifically: the structured retrieval task is executed first to obtain the first set of data identifiers that meet the structured constraints, the corresponding feature vectors are obtained through association mapping, and the vector retrieval task is executed only for the obtained corresponding feature vectors in the vector storage space to filter out the second set of data identifiers that meet the vector similarity conditions as the candidate data identifier set; Step S43: When the screening intensity of the structured constraints does not reach the preset ratio, the second retrieval strategy is adopted; specifically: the vector retrieval task and the structured retrieval task are executed to obtain the third data identifier set that satisfies the vector similarity condition and the fourth data identifier set that satisfies the structured constraints, respectively. The intersection operation is performed on the third data identifier set and the fourth data identifier set, and the intersection result is used as the candidate data identifier set.

[0012] Preferably, step S5 involves fusing and sorting the acquired information, specifically including: Step S51: For each candidate data identifier, obtain the similarity score between its corresponding feature vector and the query feature vector, as well as its corresponding structured attribute information; Step S52: Calculate the comprehensive score of each candidate data according to the preset weighted sorting algorithm. The comprehensive score is determined by weighted summation of the similarity score and the structured attribute matching score. The structured attribute matching score is calculated based on the degree of conformity between the structured attribute information of the candidate data and the structured constraints. Step S53: Sort the candidate data in descending order of the comprehensive score, and filter out duplicate candidate data. For the same original data that is repeatedly retrieved due to multiple feature vector matching, only the record with the highest comprehensive score is retained.

[0013] Preferably, it also includes a search interaction optimization step: Receive user feedback on the final search results, including adjusting the vector similarity threshold, modifying the structured constraints, or adding new structured constraints. Based on the feedback operation, steps S2 to S5 are re-executed to generate and display the updated final search results. Record user search behavior and feedback operations, and through statistical analysis, dynamically optimize the weight parameters in the weighted ranking algorithm or the system default search parameters to adapt to the search preferences of different users.

[0014] On the other hand, the present invention also provides a hybrid retrieval system based on vector similarity and SQL structured constraints to implement the above method, comprising: The preprocessing module is used to preprocess the original dataset, construct a vector storage space and a relational storage space, and establish an association mapping; the preprocessing module further includes a multimodal information extraction unit, a vectorization processing unit, and a storage management unit; The request processing module is used to receive and parse retrieval requests including query information and structured constraints, generate query feature vectors, and separate vector retrieval tasks and structured retrieval tasks. The hybrid retrieval engine module is connected to the request processing module, the vector storage space, and the relational storage space. It is used to collaboratively execute the vector retrieval task and the structured retrieval task according to a preset retrieval strategy and output a set of candidate data identifiers. The result processing module, connected to the hybrid retrieval engine module, the vector storage space, and the relational storage space, is used to obtain corresponding information based on the candidate data identifier set, perform fusion and sorting, and generate and output the final retrieval results.

[0015] Preferably, the request processing module further includes a structured query language extension unit for supporting the parsing of extended structured query statements, including vector similarity calculation functions; The hybrid retrieval engine module further includes a strategy selection unit, which dynamically selects either a first strategy of performing structured retrieval first and then vector retrieval, or a second strategy of performing both types of retrieval in parallel, based on the filtering strength of the structured constraints. The result processing module further includes a weighted sorting unit, used to perform a weighted summation of the vector similarity score and the structured attribute matching score according to preset weights, so as to determine the final sorting order of the candidate data. Compared with the prior art, the beneficial effects of the present invention are as follows: 1. This invention integrates vector similarity retrieval and SQL structured constraint retrieval, expands the SQL syntax to add a vector similarity calculation function, realizes the joint constraint of semantic similarity matching and structured attribute filtering, significantly improves the accuracy of video retrieval, solves the drawbacks of single retrieval methods, and at the same time lowers the retrieval threshold and improves the user experience; 2. This invention adopts a dual storage architecture of "vector database + relational database", combined with HNSW vector index and SQL constraint index optimization, to achieve efficient storage and linkage retrieval of multimodal vector data and structured data, avoid the secondary merging process of traditional retrieval, greatly reduce retrieval latency, improve retrieval efficiency, and support the rapid retrieval of millions of videos and more. At the same time, the integrity and consistency of structured data are ensured through SQL constraints. 3. This invention optimizes the process of extracting and vectorizing multimodal information from large models. Through deduplication, noise reduction, normalization and frame sampling strategies, it reduces computing power consumption and data storage pressure while ensuring information accuracy. It also unifies vector dimensions, simplifies similarity calculation process, and further improves retrieval efficiency and system performance. 4. This invention designs a dynamic hybrid retrieval execution strategy, which flexibly selects the "filter first, match later" or "parallel retrieval" method according to the filtering strength of the structured constraints, balancing retrieval efficiency and recall rate. At the same time, it combines a weighted ranking algorithm to ensure the rationality and practicality of the retrieval results and adapt to the retrieval needs of different scenarios. Attached Figure Description

[0016] Figure 1 This is a flowchart of the method of the present invention; Figure 2 This is a schematic diagram of the system structure of the present invention. Detailed Implementation

[0017] The present invention will be further illustrated below with reference to specific embodiments. It should be understood that these embodiments are for illustrative purposes only and are not intended to limit the scope of the invention. Furthermore, it should be understood that after reading the teachings of this invention, those skilled in the art can make various alterations or modifications to the invention, and these equivalent forms also fall within the scope defined in this application.

[0018] In this invention, terms such as "upper," "lower," "left," "right," "front," "back," "vertical," "horizontal," "side," and "bottom" indicate the orientation or positional relationship based on the orientation or positional relationship shown in the accompanying drawings. These terms are used only to facilitate the description of the structural relationships of the various components or elements of this invention and do not specifically refer to any component or element in this invention. They should not be construed as limiting the invention.

[0019] Example: Before executing the method steps in this embodiment, it is necessary to set up the system integration environment: 1. Import necessary dependent components and models, including the multimodal large model Qwen2.5-VL, vectorized models CLIP and Sentence-BERT, vector database Milvus, relational database MySQL, SQL parsing engine and front-end display components; 2. Create modular folders in the project to store the code files of the large model extraction module, vectorization module, retrieval engine module, storage module, and display module respectively, so as to decouple the modules and facilitate maintenance and expansion; 3. Configure the parameters of each component: Initialize the multimodal large model, set the video frame sampling interval (e.g., sample 1 frame every 10 frames), and the summary generation length threshold; configure the vectorization model, unify the vector dimension to 768 dimensions, and set the normalization parameters; configure the vector database, create the HNSW index, and set the index parameters (e.g., M=16, efConstruction=200); configure the relational database, create the video structured information table, and set SQL constraints such as primary key (video ID), unique constraint (video URL), and non-null constraint (release time, resolution); 4. Create a core system instance, establish communication links between modules, ensure efficient data transmission between modules, and complete the overall system initialization.

[0020] like Figure 1As shown, this embodiment provides a hybrid retrieval method based on vector similarity and SQL structured constraints, including the following steps: Step S1: Preprocess the original dataset, construct a vector storage space including feature vectors of each original data, a relational storage space including structured attribute information of each original data, and establish an association mapping between feature vectors and structured attribute information; Step S2: Receive a retrieval request, which includes query information for semantic matching and structured constraints for filtering; Step S3: Parse the retrieval request, generate a query feature vector based on the query information, and generate a vector retrieval task and a structured retrieval task; Step S4: Collaboratively execute vector retrieval and structured retrieval tasks to obtain a set of candidate data identifiers that simultaneously satisfy both vector similarity and structured constraints; Step S5: Based on the candidate data identifier set, obtain the corresponding feature vector information and structured attribute information from the vector storage space and relational storage space, fuse and sort the obtained information, and generate the final retrieval results.

[0021] The above steps will be explained in detail below.

[0022] Step S1 is as follows: 1. Receive user-uploaded or specified target video sets, and parse each video using the multimodal large model Qwen2.5-VL to extract video tags (5-10 tags to avoid redundancy), video text summaries (50-100 words in length, extracting the core content), and key video frames (sampled according to video duration; 5-10 frames for durations less than or equal to 5 minutes, and 10-20 frames for durations greater than 5 minutes). 2. Preprocess the extracted information: Deduplicate video tags by removing duplicate or semantically similar tags; reduce noise in the video text summary by removing meaningless words and correcting grammatical errors; unify the size of key video frames (e.g., scale them to 224×224 pixels) and remove blurry or redundant frames to ensure the accuracy and effectiveness of the extracted information. 3. The Sentence-BERT model is used, and the preprocessed video tags and video text summaries are input into the model to generate corresponding text vectors, ensuring that the vector dimension is uniformly 768-dimensional; the CLIP model is used, and the preprocessed key video frames are input into the model to generate corresponding image vectors, which are also uniformly 768-dimensional. 4. Normalize all generated vectors, mapping vector values ​​to the [0,1] interval to optimize the efficiency and accuracy of subsequent similarity calculations; assign a unique identifier (e.g., vector_id) to each vector, and establish a mapping table between vector_id and video ID for subsequent linkage with structured data; 5. Vector Data Storage: The normalized video tag vector, summary vector, and video frame vector, along with vector_id and video ID, are stored in the vector database Milvus. A vector retrieval index is built based on the HNSW index, and the similarity calculation method is set to cosine similarity, supporting approximate nearest neighbor retrieval. 6. Structured Data Storage: Create a structured video information table (video_info) in the MySQL database. Fields include video ID (primary key), video name, release time, resolution, author, category, duration, and storage path. Set SQL constraints: video ID as the primary key (ensuring uniqueness), video name and storage path as NOT NULL constraints, release time as NOT NULL constraints, resolution as an enumeration constraint (e.g., '720P', '1080P', '4K'), and author and category as regular fields. These constraints aim to ensure the integrity and consistency of the stored data, providing a reliable data foundation for subsequent accurate filtering. Simultaneously, create B-tree indexes for fields commonly used for structured constraint filtering, such as "release time" and "resolution." 7. Association Mapping: Create an association table (video_vector_map) in the relational database to store the correspondence between video IDs and vector_ids, ensuring that all corresponding vectors can be quickly retrieved by video ID and the corresponding video structured information can be quickly located by vector_id; In this embodiment, the B-tree indexes established in step S1 on fields such as "publication time" and "resolution" are directly intended to accelerate the execution of structured retrieval tasks in subsequent steps S3 and S4. When a user specifies structured constraints such as "publication time is greater than or equal to '2025-01-01'" or "resolution is equal to '1080P'" in the retrieval request, the relational database can use these pre-established B-tree indexes to quickly locate records that meet the conditions without performing a full table scan. This significantly improves the efficiency of structured filtering and is a key prerequisite for implementing the "filter first, match later" or efficient parallel retrieval strategy in step S4, thereby ensuring the overall performance of hybrid retrieval.

[0023] As one possible implementation of this embodiment, the structured data table contains records r1{publish_time=2025-02-01, resolution=1080P, video_id=V001}, r2{publish_time=2024-12-15, resolution=720P, video_id=V002}, r3{publish_time=2025-03-10, resolution=1080P, video_id=V003}, and r4{publish_time=2024-11-20, resolution=480P, video_id=V004}, as well as the B-tree index structure idx_time for the publish_time field and the B-tree index structure idx_res for the resolution field: The user's search request contains the condition "publication time greater than or equal to 2025-01-01". The database uses the B-tree index idx_time on the publish_time field to search from the root node. After comparing about 3 layers of index nodes, it directly locates the leaf node position that meets the condition and retrieves records r1 and r3. There is no need to scan r2 and r4 that do not meet the condition. The user's search request includes the condition "resolution equals 1080P". The database uses the B-tree index idx_res on the resolution field to directly locate the records r1 and r3 that meet the condition, without needing to scan r2 and r4. The user's search request contains two conditions: "publication time greater than or equal to 2025-01-01" and "resolution equal to 1080P". The database obtains the set of records that meet each condition through the idx_time and idx_res indexes respectively. After taking the intersection, the records r1 and r3 that meet both conditions are obtained. The amount of data scanned is reduced from 4 records in the whole table to 2 records. When a user's search request includes the condition "publication time less than 2024-12-01", the database uses the idx_time index to locate the record r4 that meets the condition, without needing to scan other records. In summary, record r2 was skipped in the corresponding index search because its publication time of 2024-12-15 did not meet the condition of "greater than or equal to 2025-01-01" and its resolution of 720P did not meet the condition of "equal to 1080P". Record r4 was only indexed and located in searches where the publication time was less than 2024-12-01. Records r1 and r3 could be quickly indexed and located under multiple structured constraints without the need for a full table scan, which significantly improved the efficiency of structured filtering.

[0024] Steps S2-S3 are as follows: 1. Expanded SQL syntax, adding a new vector similarity calculation function: vector_similarity(query_vector, video_vector); The above function takes the query vector and the video vector as parameters and returns the cosine similarity value between the two (range [0,1]). 2. Configure the SQL parsing engine to support parsing mixed query statements that include vector similarity conditions and structured constraints; 3. Parse the user's input search request, break it down into vector search conditions (such as query_vector, similarity threshold) and SQL structured constraints (such as publication time, resolution), and generate the corresponding search task.

[0025] Step S4 specifically includes: 1. Determine the screening strength of structured constraints: If structured constraints (such as resolution='4K' and release time greater than or equal to '2025-12-01') can significantly narrow down the candidate set (the number of candidates after screening is expected to be less than or equal to 10% of the total number of videos), then SQL structured retrieval will be performed first. This involves querying the list of video IDs that meet the conditions through MySQL, then retrieving the corresponding vectors from the vector database based on the list of video IDs, calculating the similarity with the query vectors, and filtering out videos with a similarity greater than or equal to the threshold. 2. If the structured constraints are complex or the filtering strength is weak (the number of candidate sets after filtering is expected to be greater than 10% of the total number of videos), a parallel retrieval strategy is adopted. This involves simultaneously performing vector similarity retrieval (searching for vector_ids in the vector database whose similarity to the query vectors is greater than or equal to the threshold, and associating them to obtain video IDs) and SQL structured retrieval (querying video IDs that meet the conditions). Then, the intersection of the two video ID lists is performed to obtain candidate video IDs that meet both conditions. 3. During the retrieval process, the HNSW index of the vector database and the SQL index of the relational database are used to optimize the retrieval speed and ensure that the retrieval latency is controlled within milliseconds (the retrieval latency for millions of video data is less than 500ms).

[0026] Step S5 is as follows: 1. Information Acquisition: Based on the candidate video ID, query the corresponding video structure information (name, release time, resolution, author, etc.) from the relational database, query the corresponding similarity score from the vector database, and associate it with the video tags, summaries, and keyframe information extracted by the large model; 2. Weighted Ranking and Overall Score Calculation: A weighted ranking algorithm is used to rank the candidate videos. The system presets a similarity score weight of 0.7 and a structured attribute matching score weight of 0.3. The structured attribute matching score is calculated as follows: For each structured constraint specified in the search request, determine the degree to which the attribute values ​​of the candidate data match it. In this embodiment, the structured attribute matching score is provided by a vector database; The overall score for each candidate video is calculated using the following formula: ; The system sorts candidate videos from highest to lowest based on their overall scores. The weights mentioned above (0.7 and 0.3) are the system default values ​​and can be pre-configured according to the business needs of different application scenarios. They can also be dynamically optimized based on subsequent statistical analysis of user search behavior. As one possible implementation of this embodiment, candidate videos v1{id=V001, similarity score=0.92, release time=2025-02-01, resolution=1080P, tag=food, overall score=0.944}, v2{id=V002, similarity score=0.88, release time=2025-03-10, resolution=1080P, tag=scenery, overall score=0.916}, v3{id=V003, similarity score=0.95, release time=2024-12-20, resolution=1080P, tag=food, overall score=0.815}, and v4{id=V001, similarity score=0.89, release time=2025-02-01, resolution=1080P, tag=food, overall score=0.923} are used. Candidate video v1's release date of 2025-02-01 satisfies the constraint "release date ≥ 2025-01-01", its resolution of 1080P satisfies the constraint "resolution = 1080P", its structured attribute matching score is 1.0, and combined with its similarity score of 0.92, the overall score is calculated to be 0.944, which is a high matching degree candidate; Candidate video v2's release date of 2025-03-10 meets the time constraint, its resolution of 1080P meets the resolution constraint, its structured attribute matching score is 1.0, but its similarity score of 0.88 is relatively low, and its overall score is 0.916, ranking it after v1. Candidate video v3 has a similarity score as high as 0.95, but its release date of 2024-12-20 does not meet the time constraint condition of "≥2025-01-01". Its structured attribute matching score is only 0.5, and its comprehensive score is 0.815, ranking last. Candidate videos v4 and v1 are the same original video (both with ID V001). Because multiple feature vectors were matched and retrieved repeatedly, v4's overall score of 0.923 is lower than v1's 0.944. According to the deduplication rule, only the record with the highest overall score, v1, is retained, and v4 is filtered out. In summary, candidate video v3 was ranked lower due to its low overall score caused by not meeting the structured constraints; candidate video v4 was filtered out due to repeated retrieval and low score; the final ranking results are v1, v2, and v3, with v1 being the candidate video with the highest overall score and returned to the user first.

[0027] 3. Result Filtering: Filter duplicate results (if the same video is retrieved multiple times due to multiple feature vector matching, only the record with the highest comprehensive score is retained), and delete invalid or inaccessible videos, finally generating an ordered list of search results.

[0028] Results display and interaction optimization: 1. The front-end display component receives the final search results and displays the video name, release time, resolution, similarity score, video tags, summary preview, and keyframe thumbnails in a list format. Users can click to view the full video and detailed information. 2. Users can adjust the similarity threshold (e.g., 0.6-0.9), supplement or modify the structured constraints (e.g., add author filtering), and click the secondary search button to re-execute the search process to obtain results that better meet their needs. When users adjust the similarity threshold or modify the structured constraints, the system will recalculate the similarity score and structured attribute matching score based on the new conditions, and generate new ranking results according to the preset weighted ranking algorithm. 3. Record user search behavior (such as commonly used similarity thresholds, structured constraints, and search keywords). Through simple statistical analysis, dynamically optimize the default weight parameters in the weighted ranking algorithm (such as the weight ratio of similarity score to structured attribute matching degree) and the system's default search parameters to better adapt to the search preferences of different user groups and improve the search experience.

[0029] like Figure 2 As shown, this embodiment also provides a hybrid retrieval system based on vector similarity and SQL structured constraints, including: The preprocessing module is used to preprocess the original dataset, construct a vector storage space and a relational storage space, and establish an association mapping; the preprocessing module further includes a multimodal information extraction unit, a vectorization processing unit, and a storage management unit; The request processing module is used to receive and parse retrieval requests including query information and structured constraints, generate query feature vectors, and separate vector retrieval tasks and structured retrieval tasks. The hybrid retrieval engine module is connected to the request processing module, the vector storage space, and the relational storage space. It is used to collaboratively execute the vector retrieval task and the structured retrieval task according to a preset retrieval strategy and output a set of candidate data identifiers. The result processing module, connected to the hybrid retrieval engine module, the vector storage space, and the relational storage space, is used to obtain corresponding information based on the candidate data identifier set, perform fusion and sorting, and generate and output the final retrieval results.

[0030] The request processing module further includes a structured query language extension unit to support the parsing of extended structured query statements, including vector similarity calculation functions; The hybrid retrieval engine module further includes a strategy selection unit, which dynamically selects either a first strategy of performing structured retrieval first and then vector retrieval, or a second strategy of performing both types of retrieval in parallel, based on the filtering strength of the structured constraints. The results processing module further includes a weighted sorting unit, which is used to perform a weighted summation of the vector similarity score and the structured attribute matching score according to preset weights, so as to determine the final sorting order of the candidate data.

[0031] The above is a detailed description of the preferred embodiments of the present invention, but the present invention is not limited to the embodiments described. Those skilled in the art can make various equivalent modifications or substitutions without departing from the spirit of the present invention, and these equivalent modifications or substitutions are all included within the scope defined by the claims of this application.

Claims

1. A hybrid retrieval method based on vector similarity and SQL structured constraints, characterized in that, Includes the following steps: Step S1: Preprocess the original dataset, construct a vector storage space including feature vectors of each original data, a relational storage space including structured attribute information of each original data, and establish an association mapping between feature vectors and structured attribute information; Step S2: Receive a retrieval request, which includes query information for semantic matching and structured constraints for filtering; Step S3: Parse the retrieval request, generate a query feature vector based on the query information, and generate a vector retrieval task and a structured retrieval task; Step S4: Collaboratively execute vector retrieval and structured retrieval tasks to obtain a set of candidate data identifiers that simultaneously satisfy both vector similarity and structured constraints; Step S5: Based on the candidate data identifier set, obtain the corresponding feature vector information and structured attribute information from the vector storage space and relational storage space, fuse and sort the obtained information, and generate the final retrieval results.

2. The hybrid retrieval method based on vector similarity and SQL structured constraints according to claim 1, characterized in that, Step S1 involves preprocessing the original dataset, specifically as follows: The original data are analyzed using a multimodal large model to extract unstructured information, which includes at least one of semantic tags, text summaries, and keyframe images. Step S11: Perform quality optimization processing on the extracted unstructured information. The quality optimization processing includes deduplication of semantic tags, noise reduction of text summaries, filtering of keyframe images, and size unification. Step S12: Use a vectorization model to convert the unstructured information that has undergone quality optimization into feature vectors with a uniform preset dimension, and then normalize the feature vectors. Step S13: Assign a vector identifier to each feature vector, store the feature vector and its vector identifier in the vector database, and build a vector index in the vector database to support approximate nearest neighbor search, thereby constructing a vector storage space; Step S14: Store the structured attribute information of each original data as records in a relational database, and assign a data identifier to each record, thereby constructing a relational storage space; Step S15: Establish an association mapping table in the relational database, and construct the association mapping between feature vectors and structured attribute information by recording the correspondence between data identifiers and vector identifiers.

3. The hybrid retrieval method based on vector similarity and SQL structured constraints according to claim 2, characterized in that, Step S11 specifically includes: A dynamic frame sampling strategy is adopted based on the duration of the original data. For original data with a duration not exceeding the first preset duration, a first preset number of key frames are sampled. For original data with a duration exceeding the first preset duration, the number of key frames sampled is increased proportionally, but the total number of samples does not exceed the second preset number. Step S12 specifically includes: The Sentence-BERT model is used to convert semantic tags and text summaries into text feature vectors, and the CLIP model is used to convert keyframe images into image feature vectors. The preset dimensions of the text feature vectors and image feature vectors are both 768-dimensional. Step S13 specifically includes: A vector index is constructed based on the HNSW algorithm, with index parameter M set to 16 and efConstruction set to 200. Cosine similarity is used as the calculation method for vector similarity.

4. The hybrid retrieval method based on vector similarity and SQL structured constraints according to claim 1, characterized in that, The retrieval request received in step S2 is in the form of an extended structured query statement including a vector similarity calculation function; the vector similarity calculation function is used to calculate the similarity between the query feature vector and the feature vector in the vector storage space, and supports specifying both the vector similarity threshold and structured constraints in the extended structured query statement. Step S3, parsing the retrieval request, further includes: The extended structured query statement is parsed to extract query information, vector similarity threshold and structured constraints for generating query feature vectors. The query information is then input into the same vectorization model as in step S1 to generate the corresponding query feature vector. The vector retrieval task is constructed based on the query feature vector and the vector similarity threshold. The structured retrieval task is constructed based on the structured constraints.

5. The hybrid retrieval method based on vector similarity and SQL structured constraints according to claim 1, characterized in that, Step S4 involves the collaborative execution of vector retrieval and structured retrieval tasks, specifically including: Step S41: Evaluate the screening strength of the structured constraints; Step S42: When the structured constraints can narrow down the candidate data range to within a preset ratio, the first retrieval strategy is adopted; specifically: the structured retrieval task is executed first to obtain the first set of data identifiers that meet the structured constraints, the corresponding feature vectors are obtained through association mapping, and the vector retrieval task is executed only for the obtained corresponding feature vectors in the vector storage space to filter out the second set of data identifiers that meet the vector similarity conditions as the candidate data identifier set; Step S43: When the screening intensity of the structured constraints does not reach the preset ratio, the second retrieval strategy is adopted; specifically: the vector retrieval task and the structured retrieval task are executed to obtain the third data identifier set that satisfies the vector similarity condition and the fourth data identifier set that satisfies the structured constraints, respectively. The intersection operation is performed on the third data identifier set and the fourth data identifier set, and the intersection result is used as the candidate data identifier set.

6. The hybrid retrieval method based on vector similarity and SQL structured constraints according to claim 1, characterized in that, Step S5 involves fusing and sorting the acquired information, specifically including: Step S51: For each candidate data identifier, obtain the similarity score between its corresponding feature vector and the query feature vector, as well as its corresponding structured attribute information; Step S52: Calculate the comprehensive score of each candidate data according to the preset weighted sorting algorithm. The comprehensive score is determined by weighted summation of the similarity score and the structured attribute matching score. The structured attribute matching score is calculated based on the degree of conformity between the structured attribute information of the candidate data and the structured constraints. Step S53: Sort the candidate data in descending order of the comprehensive score, and filter out duplicate candidate data. For the same original data that is repeatedly retrieved due to multiple feature vector matching, only the record with the highest comprehensive score is retained.

7. The hybrid retrieval method based on vector similarity and SQL structured constraints according to claim 1, characterized in that, It also includes steps to optimize the search interaction: Receive user feedback on the final search results, including adjusting the vector similarity threshold, modifying the structured constraints, or adding new structured constraints. Based on the feedback operation, steps S2 to S5 are re-executed to generate and display the updated final search results. Record user search behavior and feedback operations, and through statistical analysis, dynamically optimize the weight parameters in the weighted ranking algorithm or the system default search parameters to adapt to the search preferences of different users.

8. A hybrid retrieval system based on vector similarity and SQL structured constraints, used to implement the method of any one of claims 1 to 7, characterized in that, include: The preprocessing module is used to preprocess the original dataset, construct vector storage space and relational storage space, and establish association mapping; The preprocessing module further includes a multimodal information extraction unit, a vectorization processing unit, and a storage management unit; The request processing module is used to receive and parse retrieval requests including query information and structured constraints, generate query feature vectors, and separate vector retrieval tasks and structured retrieval tasks. The hybrid retrieval engine module is connected to the request processing module, the vector storage space, and the relational storage space. It is used to collaboratively execute the vector retrieval task and the structured retrieval task according to a preset retrieval strategy and output a set of candidate data identifiers. The result processing module, connected to the hybrid retrieval engine module, the vector storage space, and the relational storage space, is used to obtain corresponding information based on the candidate data identifier set, perform fusion and sorting, and generate and output the final retrieval results.

9. A hybrid retrieval system based on vector similarity and SQL structured constraints according to claim 8, characterized in that, The request processing module further includes a structured query language extension unit, used to support the parsing of extended structured query statements including vector similarity calculation functions; The hybrid retrieval engine module further includes a strategy selection unit, which dynamically selects either a first strategy of performing structured retrieval first and then vector retrieval, or a second strategy of performing both types of retrieval in parallel, based on the filtering strength of the structured constraints. The result processing module further includes a weighted sorting unit, which is used to perform a weighted summation of the vector similarity score and the structured attribute matching score according to preset weights, so as to determine the final sorting order of the candidate data.

Images