Binding tables with fulltext search query?-CodePudding

I run into a problem. I have three tables: product, where is stored his price and name. Then table Query with atributtes like description of searched words and their frequency(so there are no duplicates). And table UsersQuery, where each of the searched words of the user are stored.

PRODUCT
id
price
name

QUERY
id
description_query
number_of_freq

USERSQUERY
id
query_id FK
user_id  FK
timestamp

I have to calculate for each month in a given year and subsequent years (January 2018, February 2018,…), calculate the ratio between those search queries that contain the product name and those that do not. If the given ratio is not defined for the given month, the output should be NULL.

Do you guys know how it would be possible? So far I just have this

select q.description_query,
to_char(uq.timestamp, 'YYYY-MM') as year_month
from usersquery as uq
join query as q ON q.id = uq.query_id;

But I dont really know how to bind table with products, just with his atributte name. Should I use some sort of fulltext search using tsvector?

CodePudding user response：

-- table is case insensitive so use product,query, user_query. please refer manual 4.1 lexical structure.
demo

I hope I understand correctly. The number_of_freq refer to the time the query contain the product name. and if number_of_freqtext = 0 means that this query don't contain the product key word. basically a generate_series to generate date series data(later for left or right join), count filter function to count the freq is 0.

final code:

WITH cte AS (
    SELECT
        to_char(querytimestamp, 'YYYY-MM') AS tochar1,
        count(number_of_freq) AS count_all,
        count(number_of_freq) FILTER (WHERE number_of_freq = 0) AS count_0
    FROM
        query
        JOIN user_query uq ON query.query_id = uq.query_id
    WHERE
        querytimestamp >= '2021-01-01 00:00' at time zone 'UTC'
        AND querytimestamp <= '2022-12-31 23:59' at time zone 'UTC'
    GROUP BY
        1
),
cte2 (
    yearmonth
) AS (
    SELECT
        to_char(g, 'YYYY-MM')
    FROM
        generate_series('2021-01-01', '2022-12-31', interval '1 month') g
)
SELECT
    yearmonth,
    cte.*,
    round(cte.count_0::numeric / count_all, 2)
FROM
    cte
    RIGHT JOIN cte2 ON cte.tochar1 = yearmonth;

updated demo

About count the frequency of the word. full text search won't help. Since full text search will parse 'product.id' as 'product.id'. You may need regexp split string functions.

refer count frequency demo to solve the words count frequency issue: