Sabtu, 21 Agustus 2021

PostgreSQL mencari kata yang mirip dan Rank similarity

 You will match against a tsquery to determine if the phrase "Astounding Drama" leads to more rentals per month. Next, create a new column using the similarity function to rank the film descriptions based on this phrase.


  • Select the title and description for all DVDs from the film table.
  • Perform a full-text search by converting the description to a tsvector and match it to the phrase 'Astounding & Drama' using a tsquery in the WHERE clause.
  • Add a new column that calculates the similarity of the description with the phrase 'Astounding Drama'.
  • Sort the results by the new similarity column in descending order.

-- Select the title and description columns
SELECT  
  title, 
  description 
FROM 
  film
WHERE 
  -- Match "Astounding Drama" in the description
  to_tsvector(description) @@ 
  to_tsquery('Astounding & Drama');


titledescription
COWBOY DOOMA Astounding Drama of a Boy And a Lumberjack who must Fight a Butler in A Baloon
BIKINI BORROWERSA Astounding Drama of a Astronaut And a Cat who must Discover a Woman in The First Manned Space Station
CAMPUS REMEMBERA Astounding Drama of a Crocodile And a Mad Cow who must Build a Robot in A Jet Boat
ENCINO ELFA Astounding Drama of a Feminist And a Teacher who must Confront a Husband in A Baloon
GLASS DYINGA Astounding Drama of a Frisbee And a Astronaut who must Fight a Dog in Ancient Japan

SELECT 
  title, 
  description, 
  -- Calculate the similarity
  similarity(description, 'Astounding Drama')
FROM 
  film 
WHERE 
  to_tsvector(description) @@ 
  to_tsquery('Astounding & Drama') 
ORDER BY 
  similarity(description, 'Astounding Drama') DESC


titledescriptionsimilarity
COWBOY DOOMA Astounding Drama of a Boy And a Lumberjack who must Fight a Butler in A Baloon0.246377
GLASS DYINGA Astounding Drama of a Frisbee And a Astronaut who must Fight a Dog in Ancient Japan0.239437
CAMPUS REMEMBERA Astounding Drama of a Crocodile And a Mad Cow who must Build a Robot in A Jet Boat0.236111
ENCINO ELFA Astounding Drama of a Feminist And a Teacher who must Confront a Husband in A Baloon0.22973
BIKINI BORROWERSA Astounding Drama of a Astronaut And a Cat who must Discover a Woman in The First Manned Space Station0.195402

Tidak ada komentar:

Posting Komentar

Ad

Related Posts Plugin for WordPress, Blogger...

Label

Do it Yourself (36) soluna (28) GoPro (27) Motorcycle (24) Roda4 (23) nouvo (19) Travelling (17) Jual (13) jupiter (11) mutasi (10) Sepeda (9) King (8) Dad (5) vw (5) office (4) Atoz (2) Jasa (1) gokart (1)