BETWEEN,group_by,sumなどを使って期間別の情報を取得する方法

以下のようなtable構成があるとする。

・move-移動の情報が格納されている
・distance-期間内で移動した距離
・start_at, end_at-期間の始まりと終わり
・move_type-どういった移動手段で移動したか
・userと1:nの関係

参考に、laravelのmigrationfileです

```php:migration.php

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateMoveTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('move', function (Blueprint $table) {
            $table->increments('id');
            $table->double('distance',8, 2)->default(0);
            $table->integer('move_type');
            $table->timestamp('start_at')->nullable();
            $table->timestamp('end_at')->nullable();
            $table->unsignedInteger('user_id');
            $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
            $table->timestamp('created_at')->useCurrent();
            $table->timestamp('updated_at')->useCurrent();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('move');
    }
}

```

ここから、 
・WHERE ,BETWEEN
・group_by
・sum
を使って、生のsqlで様々な情報を取っていく

```sql:sample.sql
-- ユーザーの期間別合計移動距離
SELECT sum(distance) FROM move WHERE start_at BETWEEN '2018-01-01 00:00:00' AND '2020-03-01 00:00:00';
-- ユーザー1の合計移動距離
SELECT sum(distance) FROM move WHERE user_id = 1;
-- ユーザーごとの期間別合計移動距離
SELECT user_id , sum(distance) FROM move WHERE start_at BETWEEN '2018-01-01 00:00:00' AND '2020-03-01 00:00:00' GROUP BY user_id;
--ユーザー1の期間別合計移動距離
SELECT sum(distance) FROM move WHERE (user_id = 1) AND (start_at BETWEEN '2019-07-01 00:00:00' AND '2019-07-31 00:00:00') AND (end_at BETWEEN '2019-07-01 00:00:00' AND '2019-07-31 00:00:00');
--ユーザー1の期間別合計移動距離
SELECT sum(distance) FROM move WHERE (user_id = 1) AND (MONTH(start_at) = 7) AND (MONTH(end_at) = 7);
-- start_atからその月を取得
SELECT start_at , MONTH(start_at) , sum(distance) FROM move WHERE user_id = 1 GROUP BY start_at;
-- ユーザーごとの年次月別合計移動距離
SELECT user_id, YEAR (start_at), MONTH(start_at), sum(distance) FROM move WHERE user_id = 1 GROUP BY YEAR (start_at),MONTH(start_at);
--ユーザー別の月別合計移動距離
SELECT user_id,sum(distance) FROM move WHERE (MONTH(start_at) = 7) AND (MONTH(end_at) = 7) GROUP BY user_id;
--ユーザー別の年次月別合計移動距離
SELECT user_id,sum(distance) FROM move WHERE (DATE_FORMAT(start_at, '%Y/%m') = '2019/07') AND (DATE_FORMAT(end_at, '%Y/%m') = '2019/07') GROUP BY user_id;


--ユーザー1の期間別合計移動距離をstart_atとend_atでグループ化
SELECT DATE_FORMAT(start_at, '%Y/%m'), DATE_FORMAT(end_at, '%Y/%m'), sum(distance) FROM move WHERE (user_id = 1) GROUP BY DATE_FORMAT(start_at, '%Y/%m'),DATE_FORMAT(end_at, '%Y/%m');
--ユーザー1の期間別合計移動距離をstart_atでグループ化
SELECT DATE_FORMAT(start_at, '%Y/%m'), sum(distance) FROM move WHERE (user_id = 1) GROUP BY DATE_FORMAT(start_at, '%Y/%m');
--期間別合計移動距離をユーザーとstart_atでグループ化
SELECT user_id, DATE_FORMAT(start_at, '%Y/%m'), sum(distance) FROM move GROUP BY user_id, DATE_FORMAT(start_at, '%Y/%m');
--ユーザー1の期間別合計移動距離をend_atでグループ化
SELECT DATE_FORMAT(end_at, '%Y/%m'), sum(distance),move_type FROM move WHERE (user_id = 1) GROUP BY DATE_FORMAT(end_at, '%Y/%m'),move_type;


-- end_atの指定の期間の範囲でユーザーID1のmove_type別の合計移動距離
SELECT sum(distance),move_type FROM move WHERE (user_id = 1) AND (end_at BETWEEN '2019-07-01 00:00:00' AND '2019-07-31 00:00:00')  GROUP BY move_type;
```


ちなみに、一番下の

```sql:sample.sql
-- end_atの指定の期間の範囲でユーザーID1のmove_type別の合計移動距離
SELECT sum(distance),move_type FROM move WHERE (user_id = 1) AND (end_at BETWEEN '2019-07-01 00:00:00' AND '2019-07-31 00:00:00')  GROUP BY move_type;
```
をlaravelのORマッパーを使って表すと、以下のようになります。

```php:MoveInfoController.php

    public function getSummary(Request $request, $id)
    {
        $start = $request->input('start');
        $end = $request->input('end');

        $sums = DB::table('move')
            ->select('type',DB::raw('SUM(distance) as distance'))
            ->where('user_id', $id)
            ->whereBetween('end_at', [sprintf('%s 00:00:00', $start), sprintf('%s 23:59:59', $end)])
            ->groupBy('move_type')
            ->get();

        return $sums;
    }
```