我有這個(gè) 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我需要使用此表對(duì)每天的訪問量進(jìn)行求和以獲得過去 3 個(gè)月的總網(wǎng)頁瀏覽量，我的意思是結(jié)果：January= 3 visitsFebruary = 3 visitsMarch = 2 visits
2 回答
開滿天機(jī)
TA貢獻(xiàn)1786條經(jīng)驗(yàn) 獲得超13個(gè)贊
希望這樣的事情能夠幫助你。
在你的Post模型內(nèi)部：
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貢獻(xiàn)1853條經(jīng)驗(yàn) 獲得超18個(gè)贊
您可以運(yùn)行下一個(gè) 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;
結(jié)果：
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 關(guān)注
- 172 瀏覽
添加回答
舉報(bào)
0/150
提交
取消