我有這個 post_views 表created_at post_id ip========== ======= =======01-01-2020 3 127.0.0.101-01 2020 5 127.0.0.102-01 2020 5 127.0.0.103-01 2020 5 222.33.44.5506-02 2020 3 222.33.44.5506-02 2020 3 127.0.0.110-02 2020 5 33.44.55.6602-03 2020 3 22.33.65.2202-03 2020 3 22.33.65.2202-03 2020 5 11.44.55.66我需要使用此表對每天的訪問量進行求和以獲得過去 3 個月的總網頁瀏覽量，我的意思是結果：January= 3 visitsFebruary = 3 visitsMarch = 2 visits
2 回答
開滿天機
TA貢獻1786條經驗 獲得超13個贊
希望這樣的事情能夠幫助你。
在你的Post模型內部：
public function getLastThreeMonthViewsByIP($ip)
{
$data = [];
// loop 3 months
for ($i = -3; $i < 0; $i++)
{
$timestamp = strtotime("$i month");
$monthNumber = date('n', $timestamp);
// from this post
$result = $this->views()
// in this month
->whereMonth('created_at', $monthNumber)
// from this ip
->where('ip', $ip)
// group IP
->groupBy('ip')
// count all
->selectRaw('count(*) AS total')
// and return first
->first();
// if there are any results, add to data
if ($result)
{
$monthName = date('F', $timestamp);
$data[] = [
'monthNumber' => $monthNumber,
'monthName' => $monthName,
'total' => $result->total,
];
}
}
return $data;
}
慕容森
TA貢獻1853條經驗 獲得超18個贊
您可以運行下一個 SQL 查詢來生成此信息：
SELECT month, count(*) AS total
FROM (
SELECT DATE_FORMAT(created_at, '%Y-%m') AS month
FROM post_views
GROUP BY month, ip
) AS calculated
GROUP BY month;
結果：
2020-01, 3
2020-02, 3
2020-03, 2
要使其與 Laravel 一起使用：
$result = DB::raw("
SELECT month, count(*) AS total
FROM (
SELECT DATE_FORMAT(created_at, '%Y-%m') AS month
FROM `post_views`
WHERE `created_at` >= DATE_FORMAT(now() - interval ? month, '%Y-%m-01')
GROUP BY DATE_FORMAT(`created_at`, '%Y-%m'), ip
) AS calculated
GROUP BY `month`
", [3]); // last 3 months
dd($result); // to see results
希望有幫助！
- 2 回答
- 0 關注
- 156 瀏覽
添加回答
舉報
0/150
提交
取消