Go to Qiita Advent Calendar 2024 Top
LoginSignup
21
11

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

MySQL CasualAdvent Calendar 2018

Day 22
@okumurakengoinQiitadonユーザー会

100行ぐらいのSQLをLaravelのEloquent/Query Builderで頑張る

Last updated at Posted at 2018-12-22

phpのLaravelフレームワークで100行ぐらいのSELECT文を書いてみようと思い投稿させていただきました。
また私は、MySQL、php、Laravel初心者です。よろしくお願いいたします。

バージョン

  • MySQL 8.0.13
  • php 7.2
  • Laravel 5.7

データはsakilaを使用させていただきましたm(_ _)m
参考：

【作成したコードはこちら】

注意

本来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');

Screen Shot 2018-12-22 at 17.48.26.png

メンテナンス性を考えると普通にsql書くのとあまり変わらなさそうです

2005/06〜2005/08を月ごとに、カテゴリー単位でamountを出力

抽出単位
2005年6〜8月 x カテゴリー数となるように
ただし、カテゴリー「Action」「Animation」「Children」はさらにstore単位になるようにする

抽出条件

  • 以下の条件に当てはまるものは合計amountから除くこととする
    • 除外条件1 以下の全ての条件に当てはまる顧客が借りた場合
      • countoryが以下のいずれか
        • Brazil
        • China
        • Mexico
      • categoryが以下のいずれか
        • Action
        • Drama
      • 顧客単位でその月で2回以上レンタルしている場合
    • 除外条件2 以下の全ての条件に当てはまる顧客が借りた場合
      • countoryが以下のいずれか
        • Brazil
        • China
        • Mexico
        • Russian Federation
        • South Africa
      • categoryが以下のいずれか
        • Action
        • Animation
      • 顧客が属するcity単位で、その月のレンタル数が最低数と同等の顧客
    • 除外条件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月の場合は除外しない

抽出項目

  • 月
  • カテゴリー名
  • 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');

Screen Shot 2018-12-22 at 17.49.42.png

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');

Screen Shot 2018-12-22 at 17.51.12.png

最後まで読んでいただいてありがとうございましたm(_ _)m

参考

21
11
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
21
11

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?