How to create a multi-column mechanism in postgresql?

Question

I have a table in postgresql containing several cars +1000000 records:

+----+--------+------+---------+-----------+-------------+------------+------------+
| id |  price | year | mileage | fuel_type |  body_type  |   brand    |   model    |
+----+--------+------+---------+-----------+-------------+------------+------------+
|  1 |   4894 | 2011 |  121842 | "Benzin"  | "Sedan"     | "Toyota"   | "Yaris"    |
|  2 |   4989 | 2012 |   33901 | "Benzin"  | "Hatchback" | "Renault"  | "Twingo"   |
|  3 |   4990 | 2013 |   55105 | "Benzin"  | "Hatchback" | "Renault"  | "Twingo"   |
|  3 |   5290 | 2013 |   20967 | "Benzin"  | "Hatchback" | "Renault"  | "Twingo"   |
|  5 |   5594 | 2008 |  121281 | "Benzin"  | "Hatchback" | "Mercedes" | "A170"     |
|  6 |   4690 | 2012 |   71303 | "Benzin"  | "Hatchback" | "Renault"  | "Twingo"   |
|  7 |   5290 | 2013 |   58300 | "Benzin"  | "Hatchback" | "Renault"  | "Twingo"   |
|  8 |   5890 | 2013 |   35732 | "Benzin"  | "Hatchback" | "Renault"  | "Twingo"   |
|  9 |   5990 | 2013 |   38777 | "Benzin"  | "Hatchback" | "Renault"  | "Twingo"   |
| 10 |   6180 | 2013 |   69491 | "Benzin"  | "Hatchback" | "VW"       | "up!"      |
| 11 |   6490 | 2012 |   72900 | "Benzin"  | "Sedan"     | "Renault"  | "Clio III" |
| 12 |   6790 | 2012 |   49541 | "Benzin"  | "Hatchback" | "Renault"  | "Clio III" |
| 13 |   6790 | 2012 |   46377 | "Benzin"  | "Hatchback" | "Renault"  | "Clio III" |
| 14 |   6790 | 2012 |   45200 | "Benzin"  | "Hatchback" | "Renault"  | "Clio III" |
| 15 |   6894 | 2007 |  108840 | "Benzin"  | "Sedan"     | "VW"       | "Golf V"   |
| 16 |   6990 | 2009 |   54200 | "Benzin"  | "Sedan"     | "Renault"  | "Mégane"   |
| 17 |   6990 | 2012 |   40652 | "Benzin"  | "Hatchback" | "Renault"  | "Clio III" |
| 18 |   6990 | 2012 |   38080 | "Benzin"  | "Sedan"     | "Renault"  | "Clio III" |
| 19 |   7290 | 2012 |   28600 | "Benzin"  | "Hatchback" | "Renault"  | "Clio III" |
| 20 |   7290 | 2013 |   52800 | "Benzin"  | "Hatchback" | "Renault"  | "Twingo"   |
+----+--------+------+---------+-----------+-------------+------------+------------+

I would like to create a recommendation mechanism that can return the 20 most “similar” matches based on some different criteria, for example. When a user searches:, brand = 'Renault' AND price < 60000 AND year > 2010I want to present outside the search result some other, more free results with other cars that are similar, but do not necessarily meet all the search criteria.

I tried to make some rule-based code in ruby, something like:

• If you are looking for “Renault Clio”, then we are “Renault Twingo” too close.
• If you have a maximum price of 8000, then order those who are closer to it
• etc .. and others.

SQL- where order by.

, , 20 , , . , , (WHERE), . , - , , , .

, , 1000 , /then - SQL-. - , , , , , postgresql?

Answer 1

.

MADlib http://madlib.incubator.apache.org/ - Postgres, . .

, , ( : , 70- 80% , , , ).

, - - , Youtube ( !): http://mmds.org/. .

Answer 2

:

deviation = abs(actual_value- expected_value)* property_weight

:

deviation = (actual_value <> expected_value)::int* property_weight

.

. Renault Twingo Hatchback 2012 50000 6000:

select *, 
    abs(price- 6000)* 100+ 
    abs(year- 2012)* 10000+ 
    abs(mileage- 50000)* 1+
    (body_type <> 'Hatchback')::int* 40000+
    (brand <> 'Renault')::int* 100000+
    (model <> 'Twingo')::int* 50000
    as recommendation
from cars
order by recommendation
limit 10;

 id | price | year | mileage | fuel_type | body_type |  brand  |  model   | recommendation 
----+-------+------+---------+-----------+-----------+---------+----------+----------------
  9 |  5990 | 2013 |   38777 | Benzin    | Hatchback | Renault | Twingo   |          22223
  8 |  5890 | 2013 |   35732 | Benzin    | Hatchback | Renault | Twingo   |          35268
  7 |  5290 | 2013 |   58300 | Benzin    | Hatchback | Renault | Twingo   |          89300
  4 |  5290 | 2013 |   20967 | Benzin    | Hatchback | Renault | Twingo   |         110033
  3 |  4990 | 2013 |   55105 | Benzin    | Hatchback | Renault | Twingo   |         116105
  2 |  4989 | 2012 |   33901 | Benzin    | Hatchback | Renault | Twingo   |         117199
 12 |  6790 | 2012 |   49541 | Benzin    | Hatchback | Renault | Clio III |         129459
 13 |  6790 | 2012 |   46377 | Benzin    | Hatchback | Renault | Clio III |         132623
 14 |  6790 | 2012 |   45200 | Benzin    | Hatchback | Renault | Clio III |         133800
 20 |  7290 | 2013 |   52800 | Benzin    | Hatchback | Renault | Twingo   |         141800
(10 rows)

, .

, , :

create table models(id serial primary key, model text, value integer);
insert into models (model, value) values
('Twingo', 10),
('Clio III', 11), -- Clio is more similar to Twingo than to Laguna
('Laguna', 18)
--- etc

, :

select cars.*, 
    abs(price- 6000)* 100+ 
    abs(year- 2012)* 10000+ 
    abs(mileage- 50000)* 1+
    (body_type <> 'Hatchback')::int* 40000+
    (brand <> 'Renault')::int* 100000+
    abs(models.value- 10)* 50000    -- 10 is a numeric value for Twingo
    as recommendation
from cars
join models using(model)
order by recommendation
limit 10;

---

. , WHERE . , , , :

select *, 
    abs(price- 6000)* 100+ 
    abs(year- 2012)* 10000+ 
    abs(mileage- 50000)* 1+
    (body_type <> 'Hatchback')::int* 40000+
    (model <> 'Twingo')::int* 50000
    as recommendation
from cars
where brand = 'Renault' -- !
order by recommendation
limit 10;   

Answer 3

" " ( , ) tsvector, "" , .

, , , . , tsvector "fulltext": 'Clio:1A Renault:2B,4C Benzin:5D'::tsvector;, tsquery, 'Renault & Clio'::tsquery;, Renault Clio, Clio Renault . , Mercedes Clio, .

.

. Clio - , (Renault), , . , () , , - , , . , -, , , , , ( ).

In the end, your task will be just that, create a function that is “smart” based on user input and will define a complex query that you can execute against the database. This is a long process, but definitely doable. Try to be smart, but not too big, and in any case tsvectorwill cover you for all text columns, which will reduce the number of columns that you have.