Гибридный поиск FTS5 + векторы с keyword-first и RRF

Title:

URL Source: https://raw.githubusercontent.com/asg017/sqlite-vec/main/examples/nbc-headlines/3_search.ipynb

Markdown Content: { "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# NBC News Headlines: Exploring Hybrod FTS5 + Vector Search\n", "\n", "This notebooks explore a few different ways one could combine FTS5 and vector search results, when querying \n", "[FTS5](https://www.sqlite.org/fts5.html) and\n", "[`sqlite-vec`](https://github.com/asg017/sqlite-vec) virtual table.\n", "\n", "This dataset is a small list of headines scraped from NBC News, found in the [`./1_scrape.ipynb`](./1_scrape.ipynb) notebook.\n", "To see how the `fts_articles` and `vec_articles` tables were created, see the [`./3_search.ipynb`](./3_search.ipynb) notebook." ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [ { "data": { "text/html": [ "<div>\n", "<table>\n", "<thead>\n", "<tr style=\"text-align: center;\">\n", "<th>\n", "vec_version()\n", "</th>\n", "<th>\n", "lembed_version()\n", "</th>\n", "</tr>\n", "</thead>\n", "<tbody>\n", "<tr>\n", "<td style=\"text-align: left;\">\n", "v0.1.3-alpha.2\n", "</td>\n", "<td style=\"text-align: left;\">\n", "v0.0.1-alpha.8\n", "</td>\n", "</tr>\n", "</tbody>\n", "</table>\n", "<div style=\"text-align: right;\">\n", "1 row × 2 columns\n", "</div>\n", "</div>\n" ], "text/plain": [ "\u001b[0m┌\u001b[0m\u001b[0m────────────────\u001b[0m\u001b[0m┬\u001b[0m\u001b[0m──────────────────\u001b[0m\u001b[0m┐\n", "\u001b[0m\u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m\u001b[0m\u001b[1mvec_version()\u001b[0m \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m\u001b[0m\u001b[1mlembed_version()\u001b[0m\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m\n", "\u001b[0m\u001b[0m\u001b[0m├\u001b[0m\u001b[0m────────────────\u001b[0m\u001b[0m┼\u001b[0m\u001b[0m──────────────────\u001b[0m\u001b[0m┤\n", "\u001b[0m\u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mv0.1.3-alpha.2\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mv0.0.1-alpha.8 \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m\n", "\u001b[0m\u001b[0m\u001b[0m└\u001b[0m\u001b[0m────────────────\u001b[0m\u001b[0m┴\u001b[0m\u001b[0m──────────────────\u001b[0m\u001b[0m┘\n", "\u001b[0m\u001b[0m" ] }, "execution_count": 1, "metadata": {}, "output_type": "execute_result" } ], "source": [ ".open tmp-artic2.db\n", "\n", ".load ../../dist/vec0\n", ".load ./lembed0\n", "\n", "insert into lembed_models(name, model)\n", " values (\n", " 'default',\n", " lembed_model_from_file('snowflake-arctic-embed-m-v1.5.d70deb40.f16.gguf')\n", " );\n", "\n", "select vec_version(), lembed_version();" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Full-text Search Only\n", "\n", "A simple FTS query on the `fts_articles` virutal table can be made like so:" ] }, { "cell_type": "code", "execution_count": 19, "metadata": {}, "outputs": [ { "data": { "text/html": [ "<div>\n", "<table>\n", "<thead>\n", "<tr style=\"text-align: center;\">\n", "<th>\n", "rowid\n", "</th>\n", "<th>\n", "headline\n", "</th>\n", "<th>\n", "rank\n", "</th>\n", "</tr>\n", "</thead>\n", "<tbody>\n", "<tr>\n", "<td >\n", "4666\n", "</td>\n", "<td style=\"text-align: left;\">\n", "Kamala Harris visits Planned Parenthood clinic\n", "</td>\n", "<td >\n", "-18.9139950477264\n", "</td>\n", "</tr>\n", "<tr>\n", "<td >\n", "6521\n", "</td>\n", "<td style=\"text-align: left;\">\n", "Former Marine sentenced to 9 years in prison for firebombing Planned Parenthood clinic\n", "</td>\n", "<td >\n", "-14.807022703838651\n", "</td>\n", "</tr>\n", "</tbody>\n", "</table>\n", "<div style=\"text-align: right;\">\n", "2 rows × 3 columns\n", "</div>\n", "</div>\n" ], "text/plain": [ "\u001b[0m┌\u001b[0m\u001b[0m───────\u001b[0m\u001b[0m┬\u001b[0m\u001b[0m────────────────────────────────────────────────────────────────────────────────────────\u001b[0m\u001b[0m┬\u001b[0m\u001b[0m─────────────────────\u001b[0m\u001b[0m┐\n", "\u001b[0m\u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m\u001b[0m\u001b[1mrowid\u001b[0m\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m\u001b[0m\u001b[1mheadline\u001b[0m \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m\u001b[0m\u001b[1mrank\u001b[0m \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m\n", "\u001b[0m\u001b[0m\u001b[0m├\u001b[0m\u001b[0m───────\u001b[0m\u001b[0m┼\u001b[0m\u001b[0m────────────────────────────────────────────────────────────────────────────────────────\u001b[0m\u001b[0m┼\u001b[0m\u001b[0m─────────────────────\u001b[0m\u001b[0m┤\n", "\u001b[0m\u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 4666\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mKamala Harris visits Planned Parenthood clinic \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m -18.9139950477264\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m\n", "\u001b[0m\u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 6521\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mFormer Marine sentenced to 9 years in prison for firebombing Planned Parenthood clinic\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m-14.807022703838651\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m\n", "\u001b[0m\u001b[0m\u001b[0m└\u001b[0m\u001b[0m───────\u001b[0m\u001b[0m┴\u001b[0m\u001b[0m────────────────────────────────────────────────────────────────────────────────────────\u001b[0m\u001b[0m┴\u001b[0m\u001b[0m─────────────────────\u001b[0m\u001b[0m┘\n", "\u001b[0m\u001b[0m" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ ".param set query planned parenthood\n", "\n", "select\n", " rowid,\n", " headline,\n", " rank\n", "from fts_articles\n", "where headline match :query\n", "order by rank\n", "limit 10;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The `rank` column is the negative BM25 score of the query + document. " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Vector Search Only\n", "\n", "A KNN vector search can be made on the `vec_articles` virtual table like so:" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "data": { "text/html": [ "<div>\n", "<table>\n", "<thead>\n", "<tr style=\"text-align: center;\">\n", "<th>\n", "article_id\n", "</th>\n", "<th>\n", "headline\n", "</th>\n", "<th>\n", "distance\n", "</th>\n", "</tr>\n", "</thead>\n", "<tbody>\n", "<tr>\n", "<td >\n", "4666\n", "</td>\n", "<td style=\"text-align: left;\">\n", "Kamala Harris visits Planned Parenthood clinic\n", "</td>\n", "<td >\n", "0.492593914270401\n", "</td>\n", "</tr>\n", "<tr>\n", "<td >\n", "13928\n", "</td>\n", "<td style=\"text-align: left;\">\n", "After Dobbs decision, more women are managing their own abortions\n", "</td>\n", "<td >\n", "0.5789032578468323\n", "</td>\n", "</tr>\n", "<tr>\n", "<td >\n", "12636\n", "</td>\n", "<td style=\"text-align: left;\">\n", "Transforming Healthcare\n", "</td>\n", "<td >\n", "0.5822411179542542\n", "</td>\n", "</tr>\n", "<tr>\n", "<td >\n", "6979\n", "</td>\n", "<td style=\"text-align: left;\">\n", "A timeline of Trump&#39;s many, many positions on abortion\n", "</td>\n", "<td >\n", "0.6101462841033936\n", "</td>\n", "</tr>\n", "<tr>\n", "<td >\n", "7038\n", "</td>\n", "<td style=\"text-align: left;\">\n", "How a network of abortion pill providers works together in the wake of new threats\n", "</td>\n", "<td >\n", "0.6196886897087097\n", "</td>\n", "</tr>\n", "<tr>\n", "<td >\n", "6914\n", "</td>\n", "<td style=\"text-align: left;\">\n", "&#39;Major hurdles&#39;: The reality check behind Biden&#39;s big abortion promise\n", "</td>\n", "<td >\n", "0.6198344826698303\n", "</td>\n", "</tr>\n", "<tr>\n", "<td >\n", "6794\n", "</td>\n", "<td style=\"text-align: left;\">\n", "Trump&#39;s conflicting abortion stances are coming back to haunt him — and his party\n", "</td>\n", "<td >\n", "0.6198986768722534\n", "</td>\n", "</tr>\n", "<tr>\n", "<td >\n", "7381\n", "</td>\n", "<td style=\"text-align: left;\">\n", "Where abortion rights could be on the ballot this fall: From the Politics Desk\n", "</td>\n", "<td >\n", "0.6201764345169067\n", "</td>\n", "</tr>\n", "<tr>\n", "<td >\n", "6871\n", "</td>\n", "<td style=\"text-align: left;\">\n", "How the Biden campaign quickly mobilized on Trump&#39;s abortion stance\n", "</td>\n", "<td >\n", "0.633980393409729\n", "</td>\n", "</tr>\n", "<tr>\n", "<td >\n", "5496\n", "</td>\n", "<td style=\"text-align: left;\">\n", "Battle over abortion heats up in Arizona — and could be on the 2024 ballot\n", "</td>\n", "<td >\n", "0.6341449022293091\n", "</td>\n", "</tr>\n", "</tbody>\n", "</table>\n", "<div style=\"text-align: right;\">\n", "10 rows × 3 columns\n", "</div>\n", "</div>\n" ], "text/plain": [ "\u001b[0m┌\u001b[0m\u001b[0m────────────\u001b[0m\u001b[0m┬\u001b[0m\u001b[0m────────────────────────────────────────────────────────────────────────────────────\u001b[0m\u001b[0m┬\u001b[0m\u001b[0m────────────────────\u001b[0m\u001b[0m┐\n", "\u001b[0m\u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m\u001b[0m\u001b[1marticle_id\u001b[0m\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m\u001b[0m\u001b[1mheadline\u001b[0m \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m\u001b[0m\u001b[1mdistance\u001b[0m \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m\n", "\u001b[0m\u001b[0m\u001b[0m├\u001b[0m\u001b[0m────────────\u001b[0m\u001b[0m┼\u001b[0m\u001b[0m────────────────────────────────────────────────────────────────────────────────────\u001b[0m\u001b[0m┼\u001b[0m\u001b[0m────────────────────\u001b[0m\u001b[0m┤\n", "\u001b[0m\u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 4666\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mKamala Harris visits Planned Parenthood clinic \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 0.492593914270401\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m\n", "\u001b[0m\u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 13928\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mAfter Dobbs decision, more women are managing their own abortions \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m0.5789032578468323\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m\n", "\u001b[0m\u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 12636\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mTransforming Healthcare \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m0.5822411179542542\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m\n", "\u001b[0m\u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 6979\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mA timeline of Trump's many, many positions on abortion \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m0.6101462841033936\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m\n", "\u001b[0m\u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 7038\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mHow a network of abortion pill providers works together in the wake of new threats\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m0.6196886897087097\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m\n", "\u001b[0m\u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 6914\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m'Major hurdles': The reality check behind Biden's big abortion promise \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m0.6198344826698303\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m\n", "\u001b[0m\u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 6794\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mTrump's conflicting abortion stances are coming back to haunt him — and his party \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m0.6198986768722534\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m\n", "\u001b[0m\u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 7381\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mWhere abortion rights could be on the ballot this fall: From the Politics Desk \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m0.6201764345169067\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m\n", "\u001b[0m\u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 6871\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mHow the Biden campaign quickly mobilized on Trump's abortion stance \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 0.633980393409729\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m\n", "\u001b[0m\u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 5496\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mBattle over abortion heats up in Arizona — and could be on the 2024 ballot \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m0.6341449022293091\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m\n", "\u001b[0m\u001b[0m\u001b[0m└\u001b[0m\u001b[0m────────────\u001b[0m\u001b[0m┴\u001b[0m\u001b[0m────────────────────────────────────────────────────────────────────────────────────\u001b[0m\u001b[0m┴\u001b[0m\u001b[0m────────────────────\u001b[0m\u001b[0m┘\n", "\u001b[0m\u001b[0m" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ ".param set query planned parenthood\n", "\n", "select\n", " article_id,\n", " articles.headline,\n", " distance\n", "from vec_articles\n", "left join articles on articles.rowid = vec_articles.article_id\n", "where headline_embedding match lembed(:query)\n", " and k = 10;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The `distance` column is the L2 distance between the query vector and the headline embedding. \n", "\n", "The rest of this notebook explore different ways of combining these FTS5 and vector search results. \n", "The core queries are similar, and only really different on different `JOIN` or `ORDER BY` techniques." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Combination Technique #1: Keyword-first\n", "\n", "In many search-engine cases, you may way to display keyword matches first, and supplement the rest wih with vector search results. \n", "This makes some intuitive sense — keyword matches are what uses expect, but you'll want to display more result if there are only a few matching documents. \n" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [ { "data": { "text/html": [ "<div>\n", "<table>\n", "<thead>\n", "<tr style=\"text-align: center;\">\n", "<th>\n", "id\n", "</th>\n", "<th>\n", "headline\n", "</th>\n", "<th>\n", "match_type\n", "</th>\n", "<th>\n", "article_id\n", "</th>\n", "<th>\n", "rank_number\n", "</th>\n", "<th>\n", "score\n", "</th>\n", "</tr>\n", "</thead>\n", "<tbody>\n", "<tr>\n", "<td >\n", "10098\n", "</td>\n", "<td style=\"text-align: left;\">\n", "Kamala Harris says abortion bans are creating &#39;a health care crisis&#39;\n", "</td>\n", "<td style=\"text-align: left;\">\n", "fts\n", "</td>\n", "<td >\n", "10098\n", "</td>\n", "<td >\n", "1\n", "</td>\n", "<td >\n", "-10.678829270936067\n", "</td>\n", "</tr>\n", "<tr>\n", "<td >\n", "9776\n", "</td>\n", "<td style=\"text-align: left;\">\n", "States with abortion bans saw birth control prescriptions fall post-Dobbs, study finds\n", "</td>\n", "<td style=\"text-align: left;\">\n", "fts\n", "</td>\n", "<td >\n", "9776\n", "</td>\n", "<td >\n", "2\n", "</td>\n", "<td >\n", "-10.016316725971112\n", "</td>\n", "</tr>\n", "<tr>\n", "<td >\n", "2292\n", "</td>\n", "<td style=\"text-align: left;\">\n", "Ohio GOP Senate candidates pitch federal abortion bans even after voters protected reproductive rights\n", "</td>\n", "<td style=\"text-align: left;\">\n", "fts\n", "</td>\n", "<td >\n", "2292\n", "</td>\n", "<td >\n", "3\n", "</td>\n", "<td >\n", "-9.7149595994016\n", "</td>\n", "</tr>\n", "<tr>\n", "<td >\n", "452\n", "</td>\n", "<td style=\"text-align: left;\">\n", "64K women and girls became pregnant due to rape in states with abortion bans, study estimates\n", "</td>\n", "<td style=\"text-align: left;\">\n", "fts\n", "</td>\n", "<td >\n", "452\n", "</td>\n", "<td >\n", "4\n", "</td>\n", "<td >\n", "-9.163558569425538\n", "</td>\n", "</tr>\n", "<tr>\n", "<td >\n", "9187\n", "</td>\n", "<td style=\"text-align: left;\">\n", "Abortion bans drive away up to half of young talent, CNBC/Generation Lab youth survey finds\n", "</td>\n", "<td style=\"text-align: left;\">\n", "fts\n", "</td>\n", "<td >\n", "9187\n", "</td>\n", "<td >\n", "5\n", "</td>\n", "<td >\n", "-9.163558569425538\n", "</td>\n", "</tr>\n", "<tr>\n", "<td >\n", "6989\n", "</td>\n", "<td style=\"text-align: left;\">\n", "Trump says abortion restrictions should be left to states, dodging a national ban\n", "</td>\n", "<td style=\"text-align: left;\">\n", "vec\n", "</td>\n", "<td >\n", "6989\n", "</td>\n", "<td >\n", "1\n", "</td>\n", "<td >\n", "0.4930749833583832\n", "</td>\n", "</tr>\n", "<tr>\n", "<td >\n", "13928\n", "</td>\n", "<td style=\"text-align: left;\">\n", "After Dobbs decision, more women are managing their own abortions\n", "</td>\n", "<td style=\"text-align: left;\">\n", "vec\n", "</td>\n", "<td >\n", "13928\n", "</td>\n", "<td >\n", "2\n", "</td>\n", "<td >\n", "0.5120846629142761\n", "</td>\n", "</tr>\n", "<tr>\n", "<td >\n", "11822\n", "</td>\n", "<td style=\"text-align: left;\">\n", "Iowa now bans most abortions after about 6 weeks\n", "</td>\n", "<td style=\"text-align: left;\">\n", "vec\n", "</td>\n", "<td >\n", "11822\n", "</td>\n", "<td >\n", "3\n", "</td>\n", "<td >\n", "0.512569785118103\n", "</td>\n", "</tr>\n", "<tr>\n", "<td >\n", "7381\n", "</td>\n", "<td style=\"text-align: left;\">\n", "Where abortion rights could be on the ballot this fall: From the Politics Desk\n", "</td>\n", "<td style=\"text-align: left;\">\n", "vec\n", "</td>\n", "<td >\n", "7381\n", "</td>\n", "<td >\n", "4\n", "</td>\n", "<td >\n", "0.5168291926383972\n", "</td>\n", "</tr>\n", "<tr>\n", "<td >\n", "14009\n", "</td>\n", "<td style=\"text-align: left;\">\n", "Trump signals openness to banning abortion pill\n", "</td>\n", "<td style=\"text-align: left;\">\n", "vec\n", "</td>\n", "<td >\n", "14009\n", "</td>\n", "<td >\n", "5\n", "</td>\n", "<td >\n", "0.5288293957710266\n", "</td>\n", "</tr>\n", "<tr>\n", "<td >\n", "4426\n", "</td>\n", "<td style=\"text-align: left;\">\n", "Medication abortions rose in year after Dobbs decision, report finds\n", "</td>\n", "<td style=\"text-align: left;\">\n", "vec\n", "</td>\n", "<td >\n", "4426\n", "</td>\n", "<td >\n", "6\n", "</td>\n", "<td >\n", "0.5305097699165344\n", "</td>\n", "</tr>\n", "<tr>\n", "<td >\n", "4328\n", "</td>\n", "<td style=\"text-align: left;\">\n", "Trump signals support for a national 15-week abortion ban\n", "</td>\n", "<td style=\"text-align: left;\">\n", "vec\n", "</td>\n", "<td >\n", "4328\n", "</td>\n", "<td >\n", "7\n", "</td>\n", "<td >\n", "0.532848060131073\n", "</td>\n", "</tr>\n", "<tr>\n", "<td >\n", "6979\n", "</td>\n", "<td style=\"text-align: left;\">\n", "A timeline of Trump&#39;s many, many positions on abortion\n", "</td>\n", "<td style=\"text-align: left;\">\n", "vec\n", "</td>\n", "<td >\n", "6979\n", "</td>\n", "<td >\n", "8\n", "</td>\n", "<td >\n", "0.533357560634613\n", "</td>\n", "</tr>\n", "<tr>\n", "<td >\n", "2092\n", "</td>\n", "<td style=\"text-align: left;\">\n", "For the first time in years, Sen. Graham hasn&#39;t introduced a national abortion ban\n", "</td>\n", "<td style=\"text-align: left;\">\n", "vec\n", "</td>\n", "<td >\n", "2092\n", "</td>\n", "<td >\n", "9\n", "</td>\n", "<td >\n", "0.5336830615997314\n", "</td>\n", "</tr>\n", "<tr>\n", "<td >\n", "6794\n", "</td>\n", "<td style=\"text-align: left;\">\n", "Trump&#39;s conflicting abortion stances are coming back to haunt him — and his party\n", "</td>\n", "<td style=\"text-align: left;\">\n", "vec\n", "</td>\n", "<td >\n", "6794\n", "</td>\n", "<td >\n", "10\n", "</td>\n", "<td >\n", "0.5347095131874084\n", "</td>\n", "</tr>\n", "</tbody>\n", "</table>\n", "<div style=\"text-align: right;\">\n", "15 rows × 6 columns\n", "</div>\n", "</div>\n" ], "text/plain": [ "\u001b[0m┌\u001b[0m\u001b[0m───────\u001b[0m\u001b[0m┬\u001b[0m\u001b[0m────────────────────────────────────────────────────────────────────────────────────────────────────────\u001b[0m\u001b[0m┬\u001b[0m\u001b[0m────────────\u001b[0m\u001b[0m┬\u001b[0m\u001b[0m────────────\u001b[0m\u001b[0m┬\u001b[0m\u001b[0m─────────────\u001b[0m\u001b[0m┬\u001b[0m\u001b[0m─────────────────────\u001b[0m\u001b[0m┐\n", "\u001b[0m\u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m\u001b[0m\u001b[1mid\u001b[0m \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m\u001b[0m\u001b[1mheadline\u001b[0m \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m\u001b[0m\u001b[1mmatch_type\u001b[0m\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m\u001b[0m\u001b[1marticle_id\u001b[0m\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m\u001b[0m\u001b[1mrank_number\u001b[0m\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m\u001b[0m\u001b[1mscore\u001b[0m \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m\n", "\u001b[0m\u001b[0m\u001b[0m├\u001b[0m\u001b[0m───────\u001b[0m\u001b[0m┼\u001b[0m\u001b[0m────────────────────────────────────────────────────────────────────────────────────────────────────────\u001b[0m\u001b[0m┼\u001b[0m\u001b[0m────────────\u001b[0m\u001b[0m┼\u001b[0m\u001b[0m────────────\u001b[0m\u001b[0m┼\u001b[0m\u001b[0m─────────────\u001b[0m\u001b[0m┼\u001b[0m\u001b[0m─────────────────────\u001b[0m\u001b[0m┤\n", "\u001b[0m\u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m10098\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mKamala Harris says abortion bans are creating 'a health care crisis' \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mfts \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 10098\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 1\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m-10.678829270936067\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m\n", "\u001b[0m\u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 9776\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mStates with abortion bans saw birth control prescriptions fall post-Dobbs, study finds \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mfts \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 9776\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 2\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m-10.016316725971112\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m\n", "\u001b[0m\u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 2292\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mOhio GOP Senate candidates pitch federal abortion bans even after voters protected reproductive rights\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mfts \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 2292\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 3\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m -9.7149595994016\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m\n", "\u001b[0m\u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 452\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m64K women and girls became pregnant due to rape in states with abortion bans, study estimates \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mfts \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 452\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 4\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m -9.163558569425538\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m\n", "\u001b[0m\u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 9187\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mAbortion bans drive away up to half of young talent, CNBC/Generation Lab youth survey finds \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mfts \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 9187\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 5\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m -9.163558569425538\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m\n", "\u001b[0m\u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 6989\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mTrump says abortion restrictions should be left to states, dodging a national ban \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mvec \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 6989\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 1\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 0.4930749833583832\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m\n", "\u001b[0m\u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m13928\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mAfter Dobbs decision, more women are managing their own abortions \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mvec \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 13928\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 2\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 0.5120846629142761\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m\n", "\u001b[0m\u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m11822\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mIowa now bans most abortions after about 6 weeks \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mvec \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 11822\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 3\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 0.512569785118103\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m\n", "\u001b[0m\u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 7381\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mWhere abortion rights could be on the ballot this fall: From the Politics Desk \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mvec \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 7381\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 4\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 0.5168291926383972\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m\n", "\u001b[0m\u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m14009\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mTrump signals openness to banning abortion pill \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mvec \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 14009\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 5\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 0.5288293957710266\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m\n", "\u001b[0m\u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 4426\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mMedication abortions rose in year after Dobbs decision, report finds \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mvec \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 4426\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 6\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 0.5305097699165344\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m\n", "\u001b[0m\u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 4328\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mTrump signals support for a national 15-week abortion ban \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mvec \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 4328\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 7\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 0.532848060131073\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m\n", "\u001b[0m\u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 6979\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mA timeline of Trump's many, many positions on abortion \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mvec \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 6979\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 8\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 0.533357560634613\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m\n", "\u001b[0m\u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 2092\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mFor the first time in years, Sen. Graham hasn't introduced a national abortion ban \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mvec \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 2092\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 9\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 0.5336830615997314\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m\n", "\u001b[0m\u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 6794\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mTrump's conflicting abortion stances are coming back to haunt him — and his party \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0mvec \u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 6794\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 10\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m \u001b[0m\u001b[0m\u001b[0m 0.5347095131874084\u001b[0m \u001b[0m\u001b[0m│\u001b[0m\u001b[0m\n", "\u001b[0m\u001b[0m\u001b[0m└\u001b[0m\u001b[0m───────\u001b[0m\u001b[0m┴\u001b[0m\u001b[0m────────────────────────────────────────────────────────────────────────────────────────────────────────\u001b[0m\u001b[0m┴\u001b[0m\u001b[0m────────────\u001b[0m\u001b[0m┴\u001b[0m\u001b[0m────────────\u001b[0m\u001b[0m┴\u001b[0m\u001b[0m─────────────\u001b[0m\u001b[0m┴\u001b[0m\u001b[0m─────────────────────\u001b[0m\u001b[0m┘\n", "\u001b[0m\u001b[0m" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ ".param set query abortion bans\n", ".param set k 10\n", "\n", "\n", "with fts_matches as (\n", " select\n", " rowid as article_id,\n", " row_number() over (order by rank) as rank_number,\n", " rank as score\n", " from fts_articles\n", " where headline match :query\n", " limit :k\n", "),\n", "vec_matches as (\n", " select\n", " article_id,\n", " row_number() over (order by distance) as rank_number,\n", " distance as score\n", " from vec_articles\n", " where\n", " headline_embedding match lembed(:query)\n", " and k = :k\n", " order by distance\n", "),\n", "combined as (\n", " select 'fts' as match_type, * from fts_matches\n", " union all\n", " select 'vec' as match_type, * from vec_matches\n", "),\n", "final as (\n", " select\n", " articles.id,\n", " articles.headline,\n", " combined.*\n", " from combined\n", " left join articles on articles.rowid = combined.article_id\n", ")\n", "select * from final;\n", "\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We do this with a verbose CTE: one step for the FTS5 query, another for the vector search, one to \"combine\" the results with a `UNION ALL`, and one last one to `LEFT JOIN` back to the base `articles` table to get the headline.\n", "\n", "Here we have 5 FTS results and 10 additional vector results. This seems pretty natural, a fallback to vector search when keywords matches lack a bit.\n", "\n", "One note: this example doesn't do any de-duplication, so you may get the same results twice. So you may want to add a `DISTINCT` or `GROUP BY` somehwere to handle that. " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Combination Technique #2: Reciprocal Rank Fusion (RRF)\n", "\n", "[Reciprocal Rank Fusion](https://learn.microsoft.com/en-us/azure/search/hybrid-search-ranking) \n", "is another combi