Preference SQL Queries
A typical preference query
A user wants to buy a car. The price must be below or equal to 18.000 Euro. The age of the car should be between 0 and 4 years. The horsepower should be as high as possible. Both preferences are equally important. The make should be BMW or Mercedes, but no Toyota or Honda. The Pareto preference on the age and the horsepower is more important than the preference on the make. This is a typical constrained preference query, i.e., a preference query with a hard constraint on the price.
In Preference SQL we write:
|
SELECT * FROM car WHERE price <= 18000 PREFERRING (age BETWEEN 0 AND 4 AND horsepower HIGHEST) PRIOR TO make IN ('bmw', 'mercedes') NOT IN ('toyota', 'honda') |
A preference query with GROUPING and BUT ONLY
A user wants an overview of the cheapest car grouped by the make, i.e., the price should be as low as possible. Furthermore, only the car with an age less or equal to 10 years have to be presented.
In Preference SQL we write:
| SELECT * FROM car PREFERRING price LOWEST GROUPING make BUT ONLY age <= 10; |
Group preferences
A group preference is a complex preference where the preferences act on the same attributes. Preference SQL provides a comprehensive group preference framework to overcome the deficits of numeric approaches. The concept provides both numeric and semantic means which can be applied to determine group preferences and to perform further evaluations based on the semantic value of preference terms. For more details we refer to WK201, MPref and WK2010, GvD.
We provide a short example. Consider a married couple which wants to buy a car. For the husband the horsepower should be as high as possible. For the wife the horsepower should be around 120 hp, but a difference of 20 hp does not matter to her (i.e., the values from 100 to 140 hp are considered as equally good).
In Preference SQL we write:
| SELECT * FROM car PREFERRING price HIGHEST AND price AROUND 120, 20 |
A more complex group preference is given by the next Preference SQL query:
|
SELECT * FROM car PREFERRING (price HIGHEST PRIOR TO make IN ('VW', 'BMW') NOT IN ('Fiat', 'Opel') AND horsepower HIGHEST) PRIOR TO (horsepower AROUND 120, 20 AND make IN ('Audi', 'Opel') AND horsepower HIGHEST) PRIOR TO (horsepower AROUND 120, 20 AND make IN ('Audi', 'Opel') NOT IN ('Fiat') AND fuel LOWEST) |
A complex Preference SQL query
The next query demonstrates a complex preference query. We want to find all cars with their owners and address who live in Augsburg or Munich. The average fuel consumption on highway and city driving must be equal or less than 6 liter / 100 km. The cars should have a mileage between 40.000 km and 50.000 km, and a price around 12.000 euro. A difference of 2.000 euro does not matter. This Pareto preference is more important than the make being a BMW or an Audi. The full-text description of the car should contain 'ABS brake'. This is equally important to the make. The preference is combined with a RANK preference on the age and the price of the car. Note that the functions in the RANK preference must be implemented in Preference SQL itself.
Furthermore, only cars with a horsepower less than 100 hp should be presented after preference evaluation (BUT ONLY). The result has to be ordered by the registration date of the cars.
|
SELECT o.id, a.id, c.id FROM owner o, address a, car c WHERE a.city = 'augsburg' OR a.city = 'munich' AND a.id = o.idaddress AND o.id = c.idowner AND (c.city_consumption + c.highway_consumption) / 2 <= 6 PREFERRING (c.mileage BETWEEN 40000 AND 50000 AND c.price AROUND 12000, 2000) PRIOR TO (c.price HIGHEST AND c.make IN ('BMW', 'Audi') AND c.description CONTAINS 'ABS brake' AND (c.age SCORE 'age_function', c.price SCORE 'price_function') RANK 'age_price_function') ) BUT ONLY c.horsepower < 100 ORDER BY c.reg_date |