Wie kann ich Statistiken der letzten 7 Tage, des letzten Monats usw. anzeigen? ⇐ MySql

[Guest]

Ich muss Spielerstatistiken für den letzten Tag, die letzte Woche, den letzten Monat und insgesamt anzeigen. Die Statistiken werden in einem PlayerShip-Modell gespeichert. Ich habe Tabellen, die Statistiken für jeden einzelnen Spieler anzeigen, die gleichen 10–12 Statistiken für jeden Zeitraum.
Ich hatte Migrationen, die eine Datenbankstruktur für die Tabelle bildeten, aus der diese Daten abgerufen werden. Das Feld „updated_at“ ist ein Zeitstempel, der jedes Mal erneuert wird, wenn der Code ausgelöst wird. So etwas kann nicht die gleichen Statistiken aus verschiedenen Zeiträumen abrufen, da es einen Cron-Job gibt, der jeden Tag diesen Dienstcode ausführt, der auch die Statistiken aktualisiert.
Spalten außer der Konto-ID und der Spieler-ID Name sind Statistiken, die berechnet werden. Kann ich die Statistiken so speichern, wie sie zu einem anderen Datum waren, und sie dann so anzeigen, als wären sie der letzte Tag, die letzte Woche usw.? Ich muss nur einen Weg finden, Statistiken zwischen den Zeiträumen abzurufen.
Tabellenschema:

Code: Select all

('id', 'bigint(20) unsigned'),
('ship_id', 'bigint(20) unsigned'),
('battles_played', 'int(11)'),
('wins_count', 'int(11)'),
('damage_dealt', 'bigint(20)'),
('average_damage', 'bigint(20)'),
('frags', 'int(11)'),
('xp', 'int(11)'),
('survival_rate', 'double'),
('created_at', 'timestamp'),
('updated_at', 'timestamp'),
('account_id', 'bigint(20) unsigned'),
('player_name', 'varchar(255)'),
('distance', 'int(11)'),
('pve_battles', 'int(11)'),
('pve_wins', 'int(11)'),
('pve_frags', 'int(11)'),
('pve_xp', 'int(11)'),
('pve_survived_battles', 'int(11)'),
('pvp_battles', 'int(11)'),
('pvp_wins', 'int(11)'),
('pvp_frags', 'int(11)'),
('pvp_xp', 'int(11)'),
('pvp_survived_battles', 'int(11)'),
('club_frags', 'int(11)'),
('club_xp', 'int(11)'),
('club_battles', 'int(11)'),
('club_wins', 'int(11)'),
('club_survived_battles', 'int(11)'),
('rank_battles', 'int(11)'),
('rank_wins', 'int(11)'),
('rank_frags', 'int(11)'),
('rank_xp', 'int(11)'),
('rank_survived_battles', 'int(11)'),
('wn8', 'int(11)'),
('ship_name', 'varchar(255)'),
('ship_type', 'varchar(255)'),
('ship_tier', 'int(11)'),
('ship_nation', 'varchar(255)'),
('total_player_wins', 'int(11)'),
('last_battle_time', 'int(10) unsigned'),
('capture', 'int(11)'),
('defense', 'int(11)'),
('spotted', 'int(11)')

Servicecode:

Code: Select all

public function getTopPlayersLast24Hours()
{
$last24Hours = now()->subDays(1);

return PlayerShip::select('account_id', DB::raw('MAX(player_name) as player_name'), DB::raw('MAX(total_player_wn8) as total_player_wn8'))
->where('ship_tier', '>', 5)
->where('battles_played', '>', 5)
->where('updated_at', '', 5)
->where('battles_played', '>', 30)
->where('updated_at', '', 5)
->where('battles_played', '>', 120)
->where('updated_at', '>=', $lastMonth)
->groupBy('account_id')
->orderByDesc('total_player_wn8')
->limit(10)
->get()
->map(function ($player) {
return [
'name' => $player->player_name,
'wid' => $player->account_id,
'wn8' =>  $player->total_player_wn8,
];
})
->toArray();
}

public function getTopPlayersOverall()
{

$overall = now()->subDays(29);

return PlayerShip::select('account_id', DB::raw('MAX(player_name) as player_name'), DB::raw('MAX(total_player_wn8) as total_player_wn8'))
->where('ship_tier', '>', 5)
->where('battles_played', '>', 500)
->where('updated_at', '>=', $overall)
->groupBy('account_id')
->orderByDesc('total_player_wn8')
->limit(10)
->get()
->map(function ($player) {
return [
'name' => $player->player_name,
'wid' => $player->account_id,
'wn8' => $player->total_player_wn8,
];
})
->toArray();
}

//get stats for each player, based on a period: 24, 7, 30, overall
public function getPlayerStatsLastDay($account_id)
{
$playerStatistics = PlayerShip::select(
'battles_played as battles',
'wins_count as wins',
'ship_tier as tier',
'survival_rate as survived',
'damage_dealt as damage',
'frags as frags',
'xp as xp',
'spotted as spotted',
'capture as capture',
'defend as defend',
'wn8 as wn8'
)
->where('account_id', $account_id)
->where('updated_at', '>=', now()->subDay())
->first();
Log::info($playerStatistics);

return $playerStatistics ? $playerStatistics->toArray() : [];
}
public function getPlayerStatsLastWeek($account_id)
{
$playerStatistics = PlayerShip::select(
'battles_played as battles',
'wins_count as wins',
'ship_tier as tier',
'survival_rate as survived',
'damage_dealt as damage',
'frags as frags',
'xp as xp',
'spotted as spotted',
'capture as capture',
'defend as defend',
'wn8 as wn8'
)
->where('account_id', $account_id)
->where('updated_at', '>=', now()->subWeek())
->first();

Log::info($playerStatistics);
return $playerStatistics ? $playerStatistics->toArray() : [];
}

public function getPlayerStatsLastMonth($account_id)
{
$playerStatistics = PlayerShip::select(
'battles_played as battles',
'wins_count as wins',
'ship_tier as tier',
'survival_rate as survived',
'damage_dealt as damage',
'frags as frags',
'xp as xp',
'spotted as spotted',
'capture as capture',
'defend as defend',
'wn8 as wn8'
)
->where('account_id', $account_id)
->where('updated_at', '>=', now()->subMonth())
->first();
Log::info($playerStatistics);

return $playerStatistics ? $playerStatistics->toArray() : [];
}

public function getPlayerStatsOverall($account_id)
{
$playerStatistics = PlayerShip::select(
'battles_played as battles',
'wins_count as wins',
'ship_tier as tier',
'survival_rate as survived',
'damage_dealt as damage',
'frags as frags',
'xp as xp',
'spotted as spotted',
'capture as capture',
'defend as defend',
'wn8 as wn8'
)
->where('account_id', $account_id)
->first();
Log::info($playerStatistics);

return $playerStatistics ? $playerStatistics->toArray() : [];
}