Dynamic self join-CodePudding

Consider the following model, consisting of one table cars with 3 columns:

class Car(Model):
    owner_id = IntegerField()
    name = CharField()
    power = IntegerField()

The input can be from 1 up to 5 car names (no duplicates). How do you get the owners of all cars (partial match not allowed), up to 5, and sort them by the total horse power?

Here's a graphical representation of what I'd like to achieve based on the data below, also available in this SQL fiddle:

create table cars(owner_id integer, name varchar(21), power integer);
insert into cars values (101,'bmw',300);
insert into cars values (101,'audi',200);
insert into cars values (101,'mercedes',100);
insert into cars values (102,'bmw',250);
insert into cars values (102,'mercedes',400);
insert into cars values (103,'bmw',200);
insert into cars values (103,'audi',100);
insert into cars values (103,'mercedes',190);

               ┌──────────┐
'bmw'          │          │       owner_id|total_power
'mercedes' ───►│  query   ├───►   --------|-----------
               │          │            102|        650
               └──────────┘            101|        400
                                       103|        390
               ┌──────────┐
'bmw'          │          │       owner_id|total_power
'mercedes' ───►│  query   ├───►   --------|-----------
'audi'         │          │            101|        600
               └──────────┘            103|        490

With 2 cars, I tried this (it doesn't work):

select owner_id, sum(power) as total_power
from cars c1
inner join cars c2 on c1.owner_id = c2.owner_id
where c1.name = 'bmw' and c2.name = 'audi'
group by owner_id
order by total_power desc

I have no idea:

if there's a better way to write this for 5 cars or if 5 joins are the only way
how to make the sum of all power
how to write this in peewee

For non-peewee answers, I'm using SQLite.

Thanks

CodePudding user response：

Use a CTE that returns the input car names to filter the table and use aggregation to get the total power for each owner_id.

Place the final filter in the HAVING clause:

WITH cte AS (VALUES ('bmw'), ('mercedes'))
SELECT owner_id, SUM(power) power
FROM cars
WHERE name IN cte
GROUP BY owner_id
HAVING COUNT(DISTINCT name) = (SELECT COUNT(*) FROM cte)
ORDER BY SUM(power) DESC;

See the demo.

CodePudding user response：

For SQLITE, how about something like following -

with data as 
  (select *,
  'name'||dense_rank() over (order by name) name_cnt,
  'power'||dense_rank() over (order by name) power_cnt
  from cars where name in ('bmw','audi')
 )
 select 
 owner_id, 
 max(case when name_cnt = 'name1' then name end) name1,
 max(case when power_cnt = 'power1' then power end)  power1,
 max(case when name_cnt = 'name2' then name end)  name2,
 max(case when power_cnt = 'power2' then power end)  power2,
 sum(power) total_power
 from data
group by owner_id
order by owner_id;

SQL fiddle here.

However, this does not distinct if a owner has two cars of same model/name - total though given is correct.

Updated solution to exclude owners that do not have ALL cars in the list.

with data as 
(select *,
'name'||dense_rank() over (order by name) name_cnt,
'power'||dense_rank() over (order by name) power_cnt
from cars where name in ('bmw','audi','mercedes','porsche','honda')
 ), d1 as (
 select 
 owner_id, count(distinct name) n1, count(distinct name_cnt) n2
 from data
 group by owner_id
), d2 as (
  select * from data where owner_id in
  (select owner_id from d1 
  where n1 = (select max(n2) from d1))
 ) 
select 
 owner_id, 
 max(case when name_cnt = 'name1' then name end) name1,
 max(case when power_cnt = 'power1' then power end)  power1,
 max(case when name_cnt = 'name2' then name end)  name2,
 max(case when power_cnt = 'power2' then power end)  power2,
 max(case when name_cnt = 'name3' then name end)  name3,
 max(case when power_cnt = 'power3' then power end)  power3,
 max(case when name_cnt = 'name4' then name end)  name4,
 max(case when power_cnt = 'power4' then power end)  power4,
 max(case when name_cnt = 'name5' then name end)  name5,
 max(case when power_cnt = 'power5' then power end)  power5,
 sum(power) total_power
 from d2
group by owner_id
order by owner_id;

SQL fiddle.