phpのLaravelフレームワークで100行ぐらいのSELECT文を書いてみようと思い投稿させていただきました。
また私は、MySQL、php、Laravel初心者です。よろしくお願いいたします。
バージョン
- MySQL 8.0.13
- php 7.2
- Laravel 5.7
データはsakilaを使用させていただきましたm(_ _)m
参考：
- MySQLのSakila Sample Database を使ってみよう - 主夫ときどきプログラマ
- MySQL :: Sakila Sample Database :: 5.1 Tables
注意
本来LaravelのEloquentを使うとhasOne hasMany belongsToなどのリレーションの機能(Eloquent: Relationships - Laravel - The PHP Framework For Web Artisans)がものすごく便利なのですが、あえてこの機能は使わずにSQLで頑張ろうと思いました。
また、100行ぐらいとタイトルに書いてしまったのであえて長くなるような書き方にしたり、viewを使わないという冗長な書き方をしています。
また、LaravelのORMで初心者から職人へ - Qiitaでメンテしにくいコードに進むパターンと紹介されていることをそのまま行いますので、実務では決して今回の内容と同じようなコードは書いてはいけません。
よろしくお願いいたします。
全ての顧客とそれに関連する情報を色々
抽出単位
- customer_id単位で一意になるように抽出
抽出項目
- 顧客名
- 国名
- レンタルしたfilmの直近3つをカンマ区切り
- 最後にレンタルした日
- 今までレンタルした総数
- 全顧客の平均レンタル数
- country単位の平均レンタル数
- city単位の平均レンタル数
- 顧客のレンタル合計数が平均数より大きい場合は1、平均数以下の場合は0、それを 全ての顧客の平均とcountry単位の平均とcity単位の平均をカンマ区切りで表示する
例：顧客Aのレンタル合計数は全体の平均以上、country単位の平均以下、city単位の平均以上だった場合は「1,0,1」
- ↑の条件で一つでも平均以上があれば1、一つもなければ0
- rental_rateが1以上のfilmの今までの合計rate
- rental_rateが1より小さいfilmの今までの合計rate
- rental_rateが1以上のfilmの直近で借りたfilmのcategory
- ↑ただしcustomerの総レンタル数が20以上の場合のみ出力する
-
rental_rateが1より小さいfilmの直近で借りたfilmのcategory
- ↑ただしcustomerの総レンタル数が25以上の場合のみ出力する
以下のsqlを頑張ってLaravelから実行します
select cu.first_name
,cu.last_name
,co.country
,r_recently.titles
,r_recently.max_rental_date
,r_avg.cnt
,r_avg.avg_all
,r_avg.avg_country
,r_avg.avg_city
,concat(
r_avg.avg_flg
,','
,r_avg.avg_country_flg
,','
,r_avg.avg_city_flg
)
,greatest(
r_avg.avg_flg
,r_avg.avg_country_flg
,r_avg.avg_city_flg
)
,customer_rate.g_rate_sum
,customer_rate.l_rate_sum
,customer_rate.g_rate_name
,customer_rate.l_rate_name
from customer cu
left join address a on (cu.address_id = a.address_id)
left join city ci on (a.city_id = ci.city_id)
left join country co on (ci.country_id = co.country_id)
left join (
select customer_id
,group_concat(f.title order by tmp.rental_date desc, ',') titles
,max(tmp.rental_date) max_rental_date
from (
select customer_id
,inventory_id
,rental_date
,row_number() over (partition by customer_id order by rental_date desc) num
from rental
) tmp
left join inventory i on (tmp.inventory_id = i.inventory_id)
left join film f on (i.film_id = f.film_id)
where num <= 3
group by customer_id
) r_recently on (cu.customer_id = r_recently.customer_id)
left join (
select customer_id
,cnt
,avg_all
,avg_country
,avg_city
,if(cnt >= avg_all , 1, 0) avg_flg
,if(cnt >= avg_country, 1, 0) avg_country_flg
,if(cnt >= avg_city , 1, 0) avg_city_flg
from (
select customer_total.customer_id
,customer_total.cnt
,avg(cnt) over() avg_all
,avg(cnt) over(partition by co.country_id) avg_country
,avg(cnt) over(partition by ci.city_id) avg_city
from (
select r.customer_id
,count(*) cnt
from rental r
group by customer_id
) customer_total
left join customer cu on (customer_total.customer_id = cu.customer_id)
left join address a on (cu.address_id = a.address_id)
left join city ci on (a.city_id = ci.city_id)
left join country co on (ci.country_id = co.country_id)
) avg_base
) r_avg on (cu.customer_id = r_avg.customer_id)
left join (
select hoge.customer_id
,sum(if(hoge.rate_partition = 1, hoge.rental_rate, 0)) g_rate_sum
,sum(if(hoge.rate_partition = 0, hoge.rental_rate, 0)) l_rate_sum
,max(case
when hoge.rate_partition = 1
and hoge.num = 1
then g_c.name else '' end
) g_rate_name
,max(case
when hoge.rate_partition = 0
and hoge.num = 1
then l_c.name else '' end
) l_rate_name
from (
select tmp.customer_id
,tmp.rental_rate
,tmp.rate_partition
,row_number() over(
partition by tmp.customer_id
,tmp.rate_partition
order by rental_date desc
) num
,fc_g_rate.category_id g_category_id
,fc_l_rate.category_id l_category_id
from (
select customer_id
,f.film_id
,rental_date
,rental_rate
,if(f.rental_rate >= 1, 1, 0) rate_partition
,count(*) over(partition by customer_id) cnt
from rental r
left join inventory i on (r.inventory_id = i.inventory_id)
left join film f on (i.film_id = f.film_id)
) tmp
left join film_category fc_g_rate on (
tmp.film_id = fc_g_rate.film_id
and tmp.rate_partition = 1
and tmp.cnt >= 20
)
left join film_category fc_l_rate on (
tmp.film_id = fc_l_rate.film_id
and tmp.rate_partition = 0
and tmp.cnt >= 25
)
) hoge
left join category g_c on (hoge.g_category_id = g_c.category_id)
left join category l_c on (hoge.l_category_id = l_c.category_id)
group by hoge.customer_id
) customer_rate on (cu.customer_id = customer_rate.customer_id)
以下のように書き直して見ました
$rRecently = Rental::select('customer_id')
->selectRaw("group_concat(f.title order by tmp.rental_date desc, ',') titles")
->selectRaw('max(tmp.rental_date) max_rental_date')
->fromSub(
Rental::select('customer_id', 'inventory_id', 'rental_date')
->selectRaw('row_number() over (partition by customer_id order by rental_date desc) num')
->toSql(),
'tmp'
)
->leftJoin(sprintf('%s as i', Inventory::make()->getTable()), 'tmp.inventory_id', 'i.inventory_id')
->leftJoin(sprintf('%s as f', Film::make()->getTable()), 'i.film_id', 'f.film_id')
->where('num', '<=', 3)
->groupBy('customer_id');
$rAvg = Customer::select('customer_id')
->addSelect('cnt')
->addSelect('avg_all')
->addSelect('avg_country')
->addSelect('avg_city')
->selectRaw('if(cnt >= avg_all , 1, 0) avg_flg')
->selectRaw('if(cnt >= avg_country, 1, 0) avg_country_flg')
->selectRaw('if(cnt >= avg_city , 1, 0) avg_city_flg')
->fromSub(
Customer::select('customer_total.customer_id')
->addSelect('customer_total.cnt')
->selectRaw('avg(cnt) over() avg_all')
->selectRaw('avg(cnt) over(partition by co.country_id) avg_country')
->selectRaw('avg(cnt) over(partition by ci.city_id) avg_city')
->fromSub(
Rental::select('customer_id', DB::raw('count(*) cnt'))
->groupBy('customer_id')
->toSql(),
'customer_total'
)
->leftJoin(sprintf('%s as cu', Customer::make()->getTable()), 'customer_total.customer_id', 'cu.customer_id')
->leftJoin(sprintf('%s as a', Address::make()->getTable()), 'cu.address_id', 'a.address_id')
->leftJoin(sprintf('%s as ci', City::make()->getTable()), 'a.city_id', 'ci.city_id')
->leftJoin(sprintf('%s as co', Country::make()->getTable()), 'ci.country_id', 'co.country_id')
->toSql(),
'avg_base'
);
$customerRate = Customer::select('hoge.customer_id')
->selectRaw('sum(if(hoge.rate_partition = 1, hoge.rental_rate, 0)) g_rate_sum')
->selectRaw('sum(if(hoge.rate_partition = 0, hoge.rental_rate, 0)) l_rate_sum')
->selectRaw("max(case when hoge.rate_partition = 1 and hoge.num = 1 then g_c.name else '' end) g_rate_name")
->selectRaw("max(case when hoge.rate_partition = 0 and hoge.num = 1 then l_c.name else '' end) l_rate_name")
->fromSub(
Rental::select('tmp.customer_id')
->addSelect('tmp.rental_rate')
->addSelect('tmp.rate_partition')
->selectRaw('row_number() over(partition by tmp.customer_id, tmp.rate_partition order by rental_date desc) num')
->selectRaw('fc_g_rate.category_id g_category_id')
->selectRaw('fc_l_rate.category_id l_category_id')
->fromSub(
Rental::select('customer_id', 'f.film_id', 'rental_date', 'rental_rate')
->selectRaw('if(f.rental_rate >= 1, 1, 0) rate_partition')
->selectRaw('count(*) over(partition by customer_id) cnt')
->from(sprintf('%s as r', Rental::make()->getTable()))
->leftJoin(sprintf('%s as i', Inventory::make()->getTable()), 'r.inventory_id', 'i.inventory_id')
->leftJoin(sprintf('%s as f', Film::make()->getTable()), 'i.film_id', 'f.film_id')
->toSql(),
'tmp'
)
->leftJoin(sprintf('%s as fc_g_rate', FilmCategory::make()->getTable()), function (JoinClause $join) {
$join->on('tmp.film_id', 'fc_g_rate.film_id');
$join->where('tmp.rate_partition', 1);
$join->where('tmp.cnt','>=', 20);
})
->leftJoin(sprintf('%s as fc_l_rate', FilmCategory::make()->getTable()), function (JoinClause $join) {
$join->on('tmp.film_id', 'fc_l_rate.film_id');
$join->where('tmp.rate_partition', 0);
$join->where('tmp.cnt','>=', 25);
}),
'hoge'
)
->leftJoin(sprintf('%s as g_c', Category::make()->getTable()), 'hoge.g_category_id', 'g_c.category_id')
->leftJoin(sprintf('%s as l_c', Category::make()->getTable()), 'hoge.l_category_id', 'l_c.category_id')
->groupBy('hoge.customer_id');
$data = Customer::select('cu.first_name')
->addSelect('cu.last_name')
->addSelect('co.country')
->addSelect('r_recently.titles')
->addSelect('r_recently.max_rental_date')
->addSelect('r_avg.cnt')
->addSelect('r_avg.avg_all')
->addSelect('r_avg.avg_country')
->addSelect('r_avg.avg_city')
->selectRaw("concat(r_avg.avg_flg,',',r_avg.avg_country_flg,',',r_avg.avg_city_flg)")
->selectRaw('greatest(r_avg.avg_flg,r_avg.avg_country_flg,r_avg.avg_city_flg)')
->addSelect('customer_rate.g_rate_sum')
->addSelect('customer_rate.l_rate_sum')
->addSelect('customer_rate.g_rate_name')
->addSelect('customer_rate.l_rate_name')
->from(sprintf('%s as cu', Customer::make()->getTable()))
->leftJoin(sprintf('%s as a', Address::make()->getTable()), 'cu.address_id', 'a.address_id')
->leftJoin(sprintf('%s as ci', City::make()->getTable()), 'a.city_id', 'ci.city_id')
->leftJoin(sprintf('%s as co', Country::make()->getTable()), 'ci.country_id', 'co.country_id')
->leftJoinSub($rRecently->toSql(), 'r_recently', 'cu.customer_id', 'r_recently.customer_id')
->leftJoinSub($rAvg->toSql(), 'r_avg', 'cu.customer_id', 'r_avg.customer_id')
->leftJoinSub($customerRate->toSql(), 'customer_rate', 'cu.customer_id', 'customer_rate.customer_id')
->addBinding($rRecently->getBindings())
->addBinding($customerRate->getBindings())
->get()
->toArray();
$f = fopen('php://memory', 'r+');
foreach ($data as $d) fputcsv($f, $d);
rewind($f);
return response(stream_get_contents($f), 200)
->header('Content-Type', 'text/plain');
メンテナンス性を考えると普通にsql書くのとあまり変わらなさそうです
2005/06〜2005/08を月ごとに、カテゴリー単位でamountを出力
抽出単位
2005年6〜8月 x カテゴリー数となるように
ただし、カテゴリー「Action」「Animation」「Children」はさらにstore単位になるようにする
抽出条件
- 以下の条件に当てはまるものは合計amountから除くこととする
- 除外条件1 以下の全ての条件に当てはまる顧客が借りた場合
- countoryが以下のいずれか
- Brazil
- China
- Mexico
- categoryが以下のいずれか
- Action
- Drama
- 顧客単位でその月で2回以上レンタルしている場合
- countoryが以下のいずれか
- 除外条件2 以下の全ての条件に当てはまる顧客が借りた場合
- countoryが以下のいずれか
- Brazil
- China
- Mexico
- Russian Federation
- South Africa
- categoryが以下のいずれか
- Action
- Animation
- 顧客が属するcity単位で、その月のレンタル数が最低数と同等の顧客
- countoryが以下のいずれか
- 除外条件3
- cityが以下のいずれか
- Vicente Lpez
- Tandil
- Stara Zagora
- Vancouver
- Richmond Hill
- Oshawa
- Warren
- Tallahassee
- Sunnyvale
- 以下のいずれか
- categoryがAction、かつ7〜8月にレンタルされていた場合
- categoryがAnimation、かつ6〜7月にレンタルされていた場合
- ↑ただしcountryがUnited States、かつ6〜8月の場合は除外しない
- cityが以下のいずれか
- 除外条件1 以下の全ての条件に当てはまる顧客が借りた場合
抽出項目
- 月
- カテゴリー名
- store_id -> store単位カテゴリーはstore_idを、そうでないものはnull
- 合計amount
- 先月の合計amount
- 今月と先月のamountの差分
with recursive months (mon) as (
select '2005-06-01'
union
select date_add(mon, interval 1 month) added_month
from months
having added_month <= '2005-08-01'
)
, categoy_months as (
select *
from months m
cross join category c
)
, base_customer_rental as (
select cu.*
,ci.city_id
,ci.city
,co.country
,r.rental_id
,r.rental_date
,ca.name
from customer cu
left join address a on (cu.address_id = a.address_id)
left join city ci on (a.city_id = ci.city_id)
left join country co on (ci.country_id = co.country_id)
left join rental r on (cu.customer_id = r.customer_id)
left join inventory i on (r.inventory_id = i.inventory_id)
left join film f on (i.film_id = f.film_id)
left join film_category fc on (f.film_id = fc.film_id)
left join category ca on (fc.category_id = ca.category_id)
)
, zyogai_rental as (
(
select rental_id
from (
select rental_id
,count(*) over(partition by customer_id, date_format(rental_date,'%c')) cnt
from base_customer_rental
where country in ('Brazil', 'China', 'Mexico')
and name in ('Action', 'Drama')
) tmp
where cnt != 1
)
union
(
select rental_id
from (
select rental_id
,cnt
,min(cnt) over() min
from (
select rental_id
,count(*) over(partition by city_id, date_format(rental_date,'%c')) cnt
from base_customer_rental
where country in ('Brazil', 'China', 'Mexico', 'Russian Federation', 'South Africa')
and name in ('Action', 'Animation')
) tmp
) hoge
where cnt = min
)
union
(
select rental_id
from (
select rental_id
,if(name = 'Action' and date_format(rental_date,'%c') in (7,8), 1, 0) x
,if(name = 'Animation' and date_format(rental_date,'%c') in (6,7), 1, 0) y
,if(country = 'United States' and date_format(rental_date,'%c') between 6 and 8, 1, 0) z
from base_customer_rental
where city in ('Vicente Lpez', 'Tandil', 'Stara Zagora', 'Vancouver', 'Richmond Hill', 'Oshawa', 'Warren', 'Tallahassee', 'Sunnyvale')
) tmp
where x = 1 or (y = 1 and z = 0)
)
)
, rental_months as (
select sum(amount) sum_amount
,mon
,category_id
,store_partial
from (
select p.amount
,date_format(p.payment_date ,'%Y-%m-01') mon
,fc.category_id
,if(c.name is null, 0, s.store_id) store_partial
from payment p
left join staff s on (p.staff_id = s.staff_id)
left join rental r on (p.rental_id = r.rental_id)
left join inventory i on (r.inventory_id = i.inventory_id)
left join film f on (i.film_id = f.film_id)
left join film_category fc on (f.film_id = fc.film_id)
left join (
select *
from category
where name in ('Action', 'Animation', 'Children')
) c on (fc.category_id = c.category_id)
where r.rental_id not in (select * from zyogai_rental)
) tmp
group by mon, category_id, store_partial
)
select cm.mon
,cm.name
,if(rm.store_partial = 0, null, rm.store_partial)
,rm.sum_amount
,rm_zen.sum_amount
,rm.sum_amount - rm_zen.sum_amount
from categoy_months cm
left join rental_months rm on (
cm.mon = rm.mon
and cm.category_id = rm.category_id
)
left join rental_months rm_zen on (
date_add(cm.mon, interval -1 month) = rm_zen.mon
and cm.category_id = rm_zen.category_id
and rm.store_partial = rm_zen.store_partial
)
order by cm.mon, cm.category_id, rm.store_partial
以下のように書き直して見ました
$months = Model::make()
->setTable(DB::raw('dual'))
->selectRaw("'2005-06-01'")
->union(
Model::make()
->setTable(DB::raw('months'))
->selectRaw('date_add(mon, interval 1 month) added_month')
->havingRaw("added_month <= '2005-08-01'")
);
$categoyMonths = Model::make()
->setTable(DB::raw('months'))
->crossJoin(Category::make()->getTable());
$baseCustomerRental = Customer::select('cu.*')
->addSelect('ci.city_id')
->addSelect('ci.city')
->addSelect('co.country')
->addSelect('r.rental_id')
->addSelect('r.rental_date')
->addSelect('ca.name')
->from(sprintf('%s as cu', Customer::make()->getTable()))
->leftJoin(sprintf('%s as a', Address::make()->getTable()), 'cu.address_id', 'a.address_id')
->leftJoin(sprintf('%s as ci', City::make()->getTable()), 'a.city_id', 'ci.city_id')
->leftJoin(sprintf('%s as co', Country::make()->getTable()), 'ci.country_id', 'co.country_id')
->leftJoin(sprintf('%s as r', Rental::make()->getTable()), 'cu.customer_id', 'r.customer_id')
->leftJoin(sprintf('%s as i', Inventory::make()->getTable()), 'r.inventory_id', 'i.inventory_id')
->leftJoin(sprintf('%s as f', Film::make()->getTable()), 'i.film_id', 'f.film_id')
->leftJoin(sprintf('%s as fc', FilmCategory::make()->getTable()), 'f.film_id', 'fc.film_id')
->leftJoin(sprintf('%s as ca', Category::make()->getTable()), 'fc.category_id', 'ca.category_id');
$zyogaiRental = Rental::select('rental_id')
->fromSub(
Rental::select('rental_id')
->selectRaw("count(*) over(partition by customer_id, date_format(rental_date,'%c')) cnt")
->from('base_customer_rental')
->whereIn('country', ['Brazil', 'China', 'Mexico'])
->whereIn('name', ['Action', 'Drama']),
'tmp'
)
->where('cnt', '!=', 1)
->union(
Rental::select('rental_id')
->fromSub(
Rental::select('rental_id')
->addSelect('cnt')
->selectRaw('min(cnt) over() min')
->fromSub(
Rental::select('rental_id')
->selectRaw("count(*) over(partition by city_id, date_format(rental_date,'%c')) cnt")
->from('base_customer_rental')
->whereIn('country', ['Brazil', 'China', 'Mexico', 'Russian Federation', 'South Africa'])
->whereIn('name', ['Action', 'Animation']),
'tmp'
)
,'hoge'
)
->where('cnt', DB::raw('min'))
)
->union(
Rental::select('rental_id')
->fromSub(
Rental::select('rental_id')
->selectRaw("if(name = 'Action' and date_format(rental_date,'%c') in (7,8), 1, 0) x")
->selectRaw("if(name = 'Animation' and date_format(rental_date,'%c') in (6,7), 1, 0) y")
->selectRaw("if(country = 'United States' and date_format(rental_date,'%c') between 6 and 8, 1, 0) z")
->from('base_customer_rental')
->whereIn('city', ['Vicente Lpez', 'Tandil', 'Stara Zagora', 'Vancouver', 'Richmond Hill', 'Oshawa', 'Warren', 'Tallahassee', 'Sunnyvale']),
'tmp'
)
->where('x', 1)
->orWhere(function (EloquentBuilder $query) {
$query->where('y', 1)
->where('z', 0);
})
);
$rentalMonths = Category::selectRaw('sum(amount) sum_amount')
->addSelect('mon')
->addSelect('category_id')
->addSelect('store_partial')
->fromSub(
Payment::select('p.amount')
->selectRaw("date_format(p.payment_date ,'%Y-%m-01') mon")
->addSelect('fc.category_id')
->selectRaw('if(c.name is null, 0, s.store_id) store_partial')
->from(sprintf('%s as p', Payment::make()->getTable()))
->leftJoin(sprintf('%s as s', Staff::make()->getTable()), 'p.staff_id', 's.staff_id')
->leftJoin(sprintf('%s as r', Rental::make()->getTable()), 'p.rental_id', 'r.rental_id')
->leftJoin(sprintf('%s as i', Inventory::make()->getTable()), 'r.inventory_id', 'i.inventory_id')
->leftJoin(sprintf('%s as f', Film::make()->getTable()), 'i.film_id', 'f.film_id')
->leftJoin(sprintf('%s as fc', FilmCategory::make()->getTable()), 'f.film_id', 'fc.film_id')
->leftJoinSub(Category::whereIn('name', ['Action', 'Animation', 'Children']), 'c', 'fc.category_id', 'c.category_id')
->whereNotIn('r.rental_id', function (Builder $query) {
$query->from('zyogai_rental');
}),
'tmp'
)
->groupBy('mon', 'category_id', 'store_partial');
$q = Model::make()->setTable('categoy_months as cm')
->select('cm.mon')
->addSelect('cm.name')
->selectRaw('if(rm.store_partial = 0, null, rm.store_partial)')
->addSelect('rm.sum_amount')
->addSelect('rm_zen.sum_amount as sum_amount_zen')
->selectRaw('rm.sum_amount - rm_zen.sum_amount')
->leftJoin('rental_months as rm', function (JoinClause $join) {
$join->on('cm.mon', 'rm.mon');
$join->on('cm.category_id', 'rm.category_id');
})
->leftJoin('rental_months as rm_zen', function (JoinClause $join) {
$join->on(DB::raw('date_add(cm.mon, interval -1 month)'), 'rm_zen.mon');
$join->on('cm.category_id', 'rm_zen.category_id');
$join->on('rm.store_partial', 'rm_zen.store_partial');
})
->orderBy('cm.mon')
->orderBy('cm.category_id')
->orderBy('rm.store_partial');
$data = DB::select(
sprintf(<<<EOF
with recursive months (mon) as (
%s
)
, categoy_months as (
%s
)
, base_customer_rental as (
%s
)
, zyogai_rental as (
%s
)
, rental_months as (
%s
)
%s
EOF
,$months->toSql()
,$categoyMonths->toSql()
,$baseCustomerRental->toSql()
,$zyogaiRental->toSql()
,$rentalMonths->toSql()
,$q->toSql()
),
array_merge(
$months->getBindings()
,$zyogaiRental->getBindings()
,$rentalMonths->getBindings()
)
);
$f = fopen('php://memory', 'r+');
foreach ($data as $d) fputcsv($f, (array) $d);
rewind($f);
return response(stream_get_contents($f), 200)
->header('Content-Type', 'text/plain');
sqlのwith句の書き方がわからなかったのでsprintfで無理矢理sqlを組み立てていますが、もう少しクエリビルダっぽい書き方にしたかったです
対象countryごとに借りられているactorを集計
対象country
- 総レンタル数が1000以下の国の中で、レンタル数が多い順に3つの国
抽出単位
- actorで一意
抽出項目
- actorのフルネーム
- actorの今までの出演本数
- actorが一度でも出ているカテゴリーを横つなぎで表示
- ※ただしGames,Horror,Musicは非表示
- actorがAnimationに一度でも出ていた「○」、出ていなかったら「×」
- actorがChildrenに一度でも出ていた「○」、出ていなかったら「×」
- 対象countryの中で一番目にレンタル数の多い国名
- 一番目にレンタル数の多い国のactor単位の売り上げ
- 一番目にレンタル数の多い国のactor単位の売り上げの割合
- 対象countryの中で二番目にレンタル数の多い国名
- 二番目にレンタル数の多い国のactor単位の売り上げ
- 二番目にレンタル数の多い国のactor単位の売り上げの割合
- 対象countryの中で三番目にレンタル数の多い国名
- 三番目にレンタル数の多い国のactor単位の売り上げ
- 三番目にレンタル数の多い国のactor単位の売り上げの割合
抽出条件
- Actionのジャンルの映画に一度でも出ているactorが対象
- 出演作品数が20本以上のactorが対象
select concat(a.first_name,' ',a.last_name)
,hoge.cnt
,hoge.categories
,hoge.animation_flg
,hoge.children_flg
,(select country from country where country_id = fugara.first_country_id) first_country
,fugara.first_sum_amount
,concat(100 * (fugara.first_sum_amount / fugara.first_sum_country_amount), '%')
,(select country from country where country_id = fugara.second_country_id) second_country
,fugara.second_sum_amount
,concat(100 * (fugara.second_sum_amount / fugara.second_sum_country_amount), '%')
,(select country from country where country_id = fugara.third_country_id) third_country_id
,fugara.third_sum_amount
,concat(100 * (fugara.third_sum_amount / fugara.third_sum_country_amount), '%')
from actor a
left join (
select actor_id
,cnt
,categories
,if(categories like concat('%','Animation','%'), '○', '×') animation_flg
,if(categories like concat('%','Children','%'), '○', '×') children_flg
from (
select fa.actor_id
,count(*) cnt
,group_concat(
distinct (
select (
select name
from category c
where c.category_id = fc.category_id
and c.name not in ('Games','Horror','Music')
)
from film_category fc
where fc.film_id = fa.film_id
)
) categories
from film_actor fa
group by fa.actor_id
) tmp
) hoge on a.actor_id = hoge.actor_id
left join (
select actor_id
,max(if(num = 1, country_id, null)) first_country_id
,max(if(num = 1, sum_amount, null)) first_sum_amount
,max(if(num = 1, sum_country_amount, null)) first_sum_country_amount
,max(if(num = 2, country_id, null)) second_country_id
,max(if(num = 2, sum_amount, null)) second_sum_amount
,max(if(num = 2, sum_country_amount, null)) second_sum_country_amount
,max(if(num = 3, country_id, null)) third_country_id
,max(if(num = 3, sum_amount, null)) third_sum_amount
,max(if(num = 3, sum_country_amount, null)) third_sum_country_amount
from (
select piyo.num
,piyo.country_id
,piyo.sum_country_amount
,fa.actor_id
,sum(p.amount) sum_amount
from (
select row_number() over() num
,country_id
,sum_country_amount
from (
select co.country_id
,sum(amount) sum_country_amount
from country co
left join city ci on co.country_id = ci.country_id
left join address a on ci.city_id = a.city_id
left join customer cu on a.address_id = cu.address_id
left join rental r on cu.customer_id = r.customer_id
left join payment p on r.rental_id = p.rental_id
group by co.country_id
having count(*) <= 1000
order by count(*) desc
limit 3
) fuga
) piyo
left join city ci on (piyo.country_id = ci.country_id)
left join address a on (ci.city_id = a.city_id)
left join customer cu on (a.address_id = cu.address_id)
left join payment p on (cu.customer_id = p.customer_id)
left join rental r on (p.rental_id = r.rental_id)
left join inventory i on (r.inventory_id = i.inventory_id)
left join film f on (i.film_id = f.film_id)
left join film_actor fa on (f.film_id = fa.film_id)
group by piyo.num, piyo.country_id, fa.actor_id
) hogera
group by actor_id
) fugara on (a.actor_id = fugara.actor_id)
where a.actor_id in (
select a.actor_id
from actor a
left join film_actor fa on a.actor_id = fa.actor_id
left join film_category fc on fa.film_id = fc.film_id
inner join (
select *
from category
where name = 'Action'
) c on (fc.category_id = c.category_id)
)
and exists (
select 'x'
from film_actor
where actor_id = a.actor_id
having count(*) >= 20
)
order by a.actor_id
以下のように書き直して見ました
$hoge = Actor::select('actor_id')
->addSelect('cnt')
->addSelect('categories')
->selectRaw("if(categories like concat('%','Animation','%'), '○', '×') animation_flg")
->selectRaw("if(categories like concat('%','Children','%'), '○', '×') children_flg")
->fromSub(
FilmActor::select('fa.actor_id')
->selectRaw('count(*) cnt')
->selectRaw(
sprintf(
'group_concat( distinct ( %s ) ) categories',
FilmCategory::selectSub(
$hogeCategory = Category::select('name')
->from(sprintf('%s as c', Category::make()->getTable()))
->whereRaw('c.category_id = fc.category_id')
->whereNotIn('c.name', ['Games','Horror','Music']),
'a'
)
->from(sprintf('%s as fc', FilmCategory::make()->getTable()))
->whereRaw('fc.film_id = fa.film_id')
->toSql()
),
$hogeCategory->getBindings()
)
->from(sprintf('%s as fa', FilmActor::make()->getTable()))
->groupBy('fa.actor_id'),
'tmp'
);
$fugara = Actor::select('actor_id')
->selectRaw('max(if(num = 1, country_id, null)) first_country_id')
->selectRaw('max(if(num = 1, sum_amount, null)) first_sum_amount')
->selectRaw('max(if(num = 1, sum_country_amount, null)) first_sum_country_amount')
->selectRaw('max(if(num = 2, country_id, null)) second_country_id')
->selectRaw('max(if(num = 2, sum_amount, null)) second_sum_amount')
->selectRaw('max(if(num = 2, sum_country_amount, null)) second_sum_country_amount')
->selectRaw('max(if(num = 3, country_id, null)) third_country_id')
->selectRaw('max(if(num = 3, sum_amount, null)) third_sum_amount')
->selectRaw('max(if(num = 3, sum_country_amount, null)) third_sum_country_amount')
->fromSub(
Country::select('piyo.num')
->addSelect('piyo.country_id')
->addSelect('piyo.sum_country_amount')
->addSelect('fa.actor_id')
->selectRaw('sum(p.amount) sum_amount')
->fromSub(
Country::selectRaw('row_number() over() num')
->addSelect('country_id')
->addSelect('sum_country_amount')
->fromSub(
Country::select('co.country_id')
->selectRaw('sum(amount) sum_country_amount')
->from(sprintf('%s as co', Country::make()->getTable()))
->leftJoin(sprintf('%s as ci', City::make()->getTable()), 'co.country_id', 'ci.country_id')
->leftJoin(sprintf('%s as a', Address::make()->getTable()), 'ci.city_id', 'a.city_id')
->leftJoin(sprintf('%s as cu', Customer::make()->getTable()), 'a.address_id', 'cu.address_id')
->leftJoin(sprintf('%s as r', Rental::make()->getTable()), 'cu.customer_id', 'r.customer_id')
->leftJoin(sprintf('%s as p', Payment::make()->getTable()), 'r.rental_id', 'p.rental_id')
->groupBy('co.country_id')
->havingRaw('count(*) <= ?', [1000])
->orderByRaw('count(*) desc')
->limit(3),
'fuga'
),
'piyo'
)
->leftJoin(sprintf('%s as ci', City::make()->getTable()), 'piyo.country_id', 'ci.country_id')
->leftJoin(sprintf('%s as a', Address::make()->getTable()), 'ci.city_id', 'a.city_id')
->leftJoin(sprintf('%s as cu', Customer::make()->getTable()), 'a.address_id', 'cu.address_id')
->leftJoin(sprintf('%s as p', Payment::make()->getTable()), 'cu.customer_id', 'p.customer_id')
->leftJoin(sprintf('%s as r', Rental::make()->getTable()), 'p.rental_id', 'r.rental_id')
->leftJoin(sprintf('%s as i', Inventory::make()->getTable()), 'r.inventory_id', 'i.inventory_id')
->leftJoin(sprintf('%s as f', Film::make()->getTable()), 'i.film_id', 'f.film_id')
->leftJoin(sprintf('%s as fa', FilmActor::make()->getTable()), 'f.film_id', 'fa.film_id')
->groupBy('piyo.num', 'piyo.country_id', 'fa.actor_id'),
'hogera'
)
->groupBy('actor_id');
$data = Actor::selectRaw("concat(a.first_name,' ',a.last_name)")
->addSelect('hoge.cnt')
->addSelect('hoge.categories')
->addSelect('hoge.animation_flg')
->addSelect('hoge.children_flg')
->selectSub(Country::select('country')->where('country_id', DB::raw('fugara.first_country_id')), 'first_country')
->addSelect('fugara.first_sum_amount')
->selectRaw("concat(100 * (fugara.first_sum_amount / fugara.first_sum_country_amount), '%')")
->selectSub(Country::select('country')->where('country_id', DB::raw('fugara.second_country_id')), 'second_country')
->addSelect('fugara.second_sum_amount')
->selectRaw("concat(100 * (fugara.second_sum_amount / fugara.second_sum_country_amount), '%')")
->selectSub(Country::select('country')->where('country_id', DB::raw('fugara.third_country_id')), 'third_country')
->addSelect('fugara.third_sum_amount')
->selectRaw("concat(100 * (fugara.third_sum_amount / fugara.third_sum_country_amount), '%')")
->from(sprintf('%s as a', Actor::make()->getTable()))
->leftJoinSub($hoge->toSql(), 'hoge', 'a.actor_id', 'hoge.actor_id')
->leftJoinSub($fugara->toSql(), 'fugara', 'a.actor_id', 'fugara.actor_id')
->addBinding($hoge->getBindings())
->addBinding($fugara->getBindings())
->whereIn('a.actor_id', function (Builder $query) {
$query->from(sprintf('%s as a', Actor::make()->getTable()))
->select('a.actor_id')
->leftJoin(sprintf('%s as fa', FilmActor::make()->getTable()), 'a.actor_id', 'fa.actor_id')
->leftJoin(sprintf('%s as fc', FilmCategory::make()->getTable()), 'fa.film_id', 'fc.film_id')
->joinSub(Category::where('name', 'Action'), 'c', 'fc.category_id', 'c.category_id');
})
->whereRaw(
sprintf(
'exists ( %s )',
FilmActor::selectRaw("'x'")
->where('actor_id', DB::raw('a.actor_id'))
->havingRaw('count(*) >= ?')
->toSql()
),
[20]
)
->orderBy('a.actor_id')
->get()
->toArray();
$f = fopen('php://memory', 'r+');
foreach ($data as $d) fputcsv($f, $d);
rewind($f);
return response(stream_get_contents($f), 200)
->header('Content-Type', 'text/plain');
最後まで読んでいただいてありがとうございましたm(_ _)m
参考
- MySQL王国に黒船（Window関数）がやってきた！ - Qiita
- 再帰SQL -図解- - Qiita
- MySQL :: MySQL 8.0 Reference Manual :: 13.2.13 WITH Syntax (Common Table Expressions)
- MySQLで日付の切り捨て(DATE_TRUNCもどき): ギジュツメモ
- MySQL DATE_ADD() Function
- MySQL DATE_FORMAT() Function
- MySQL :: MySQL 8.0 Reference Manual :: 12.20.1 Aggregate (GROUP BY) Function Descriptions
- The Ultimate Guide to MySQL EXISTS
- Laravel Fluent Query Builder Join with subquery - Stack Overflow
- php - Laravel Eloquent inner join with multiple conditions - Stack Overflow
- PHP Array to CSV - Stack Overflow
- PHPでメモリ上に一時ファイルを作る - Qiita
- Create a CSV File for a user in PHP - Stack Overflow
- PHP - laravel5.4.30 SQL文を実行したい｜teratail
- eloquent - Laravel how to get query with bindings? - Stack Overflow
- Laravel eloquent multiple WHERE with OR AND OR and LIKE? - Stack Overflow
- php - How to do this in Laravel, subquery where in - Stack Overflow