-- set session optimizer_switch='derived_merge=off,batched_key_access=on,mrr=on,mrr_cost_based=off,hash_join=on,block_nested_loop=off';
set session optimizer_switch='derived_merge=off';
explain analyze
with recursive dates as
(
select date'2005-07-01' as date
union all
select date + interval 1 day
from dates
where date < date'2005-07-31'
)
, t0 as (
select store_id
, date(rental_date) as rental_date
, date(return_date) as return_date
from inventory i
join rental r using(inventory_id)
where r.rental_date >= date'2005-07-01' and r.rental_date < date'2005-08-01'
or r.return_date >= date'2005-07-01' and r.return_date < date'2005-08-01'
)
, ren as (
select rental_date
, count(case when store_id = 1 then 1 end) as c1
, count(case when store_id = 2 then 1 end) as c2
, count(*) as ct
from t0
where rental_date >= date'2005-07-01'and rental_date < date'2005-08-01'
group by rental_date
)
, ret as (
select return_date
, count(case when store_id = 1 then 1 end) as r1
, count(case when store_id = 2 then 1 end) as r2
, count(*) as rt
from t0
where return_date >= date'2005-07-01'and return_date < date'2005-08-01'
group by return_date
)
select d.date
, ifnull(c.c1,'0') as rented_at_store_1
, ifnull(r.r1,'0') as returned_at_store_1
, ifnull(c.c2,'0') as rented_at_store_2
, ifnull(r.r2,'0') as returned_at_store_2
, ifnull(c.ct,'0') as total_rented
, ifnull(r.rt,'0') as total_returned
from dates d
left join ren c on c.rental_date = d.date
left join ret r on r.return_date = d.date
order by 1
;
-- select @@optimizer_switch \G
show status like '%cost';
/*
select count(*)
from inventory i
join rental r on r.inventory_id = i.inventory_id
where r.rental_date >= date'2005-07-01' and r.rental_date < date'2005-08-01'
or r.return_date >= date'2005-07-01' and r.return_date < date'2005-08-01'
;
select * from rental limit 5;
select * from staff limit 5;
select * from inventory limit 5;
select store_id, count(*)
from inventory
group by store_id;
*/
Telegram: DB developers channel