movie/app/Models/Visit.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Cache;
use Illuminate\Http\Request;

class Visit extends Model
{
    protected $table = 'visits';
    protected $primaryKey = 'id';

    public $keyType = 'int';
    public $incrementing = true;
    public $timestamps = true;

    const CREATED_AT = 'created_at';
    const UPDATED_AT = null;
    const DELETED_AT = null;

    protected $guarded = [];

    /**
     * 방문자 수 등록
     */
    public function register(Request $request): ?\Shetabit\Visitor\Models\Visit
    {
        return visitor()->visit($request->user());
    }

    /**
     * 월간 방문자 수
     */
    public function getMonthlyData(): array
    {
        $sql = "
            SELECT dt
             , COUNT(n) cntNew
             , COUNT(r) cntRe
             , COUNT(*) cntTot
            FROM (SELECT DATE_FORMAT(created_at, '%Y-%m-%d') AS dt
                     , ip
                     , CASE WHEN COUNT(*) = 1 THEN 1 END AS n
                     , CASE WHEN COUNT(*) > 1 THEN 1 END AS r
                  FROM visits
                    WHERE created_at >= (LAST_DAY(NOW() - INTERVAL 1 month) + INTERVAL 1 DAY)
                    AND created_at < LAST_DAY(NOW())
                    GROUP BY DATE_FORMAT(created_at, '%Y-%m-%d')
                    , ip
                ) f
            GROUP BY dt;
        ";

        return DB::select($sql);
    }

    /**
     * 오늘 방문자 수
     */
    public function todayCount(): int
    {
        if (!$result = Cache::get('todayVisitsCount')) 
        {
            $sql = "
                SELECT COUNT(DISTINCT ip) AS total FROM visits WHERE created_at >= CURDATE() AND created_at < CURDATE() + INTERVAL 1 DAY AND ip IS NOT NULL AND ip <> '';
            ";

            $result = (int)DB::selectOne($sql)->total;

            Cache::put('todayVisitsCount', $result, ttl: 300);
        }

        return $result;
    }

    /**
     * 어제 방문자 수
     */
    public function yesterdayCount(): int
    {
        if (!$result = Cache::get('yesterdayVisitsCount')) 
        {
            $sql = "
                SELECT COUNT(DISTINCT ip) AS total FROM visits WHERE created_at >= CURDATE() - INTERVAL 1 DAY AND created_at < CURDATE() AND ip IS NOT NULL AND ip <> '';
            ";

            $result = (int)DB::selectOne($sql)->total;

            Cache::put('yesterdayVisitsCount', $result, 86400);
        }

        return $result;
    }

    /**
     * 누적 방문자 수
     */
    public function totalCount(): int
    {
        if (!$result = Cache::get('totalVisitsCount')) 
        {
            $sql = "
                SELECT COUNT(DISTINCT `ip`) AS `total` FROM `visits` WHERE `ip` IS NOT NULL AND `ip` <> '';
            ";

            $result = (int)DB::selectOne($sql)->total;

            Cache::put('totalVisitsCount', $result, 86400);
        }

        return $result;
    }
}