这还没有用功能性答案来回答。

我将几种方法放在一起，以获取我经常玩的游戏的一些有趣统计数据。

下面的方法将计算所有比赛的总数，将一名球员与球员列表匹配，然后显示总胜/负/平局的总和。

这很棒，而且很实用。

但是，由于大众需求，我被要求调整查询以现在考虑游戏开始的日期。我想将其过滤到最后 30 天的总和。我该怎么做？

我想在花时间重写整个东西之前四处询问。最好，一切都保持不变，只是按日期过滤。

数据库的日期键是 checkSumID，它是一个 UNIX 时间戳。

private function topPlayers() {

        $topPlayersList = array();

        $playersList = DB::table('pickup_results')
            ->select(DB::raw("playerID"),
                DB::raw("COUNT(CASE WHEN gameResult = 'Win'  THEN 1 END) AS wins"),
                DB::raw("COUNT(CASE WHEN gameResult = 'Loss' THEN 1 END) AS loss"),
                DB::raw("COUNT(CASE WHEN gameResult = 'Tie' THEN 1 END) AS tie")
            )
            ->groupBy('playerID')
            ->orderBy('wins','DESC')
            ->get();

        $i = 0;

        foreach ($playersList as $playerListData) {

            if ($playerListData->wins + $playerListData->loss + $playerListData->tie >= 25) {

                $avgPick = $this->getPlayerAvgPickCount($playerListData->playerID);

                $playerRecordID = $playerListData->playerID;

                $playerNameLookup = Players::where([
                    'player_id' => $playerListData->playerID
                ])->first();

                $playerListData->playerID = $playerNameLookup->player_name;

                $topPlayersList[$i] = array(
                    'name' => $playerNameLookup->player_name,
                    'total' => +$playerListData->wins + +$playerListData->loss + +$playerListData->tie,
                    'wins' => +$playerListData->wins,
                    'loss' => +$playerListData->loss,
                    'tie' => +$playerListData->tie,
                    'percent' => +$playerListData->loss == 0 ? 0 : round(
                            (+$playerListData->wins / (+$playerListData->wins + +$playerListData->loss) * 100),
                            2
                        ) . ' %',
                    'avg_pick' => $avgPick[0]->average,
                    'player_id' => $playerRecordID
                );

                $i++;

            }

        }

        return $this->sortArray($topPlayersList,'percent','DESC');
    }

我写的一个方法做类似的事情，但更多的是在一个人的基础上，但我不确定如何在不完全重写的情况下将两者拼接在一起。

方法是这样的

private function getTotalGamesPlayed30DayWinLossTies() {

        //PickupResults::where('playerID', '=', $this->getPlayerID())->where('checkSumID', '=', Carbon::now()->subDays(30)->timestamp)->count()
        $results = PickupResults::get();

        //$results = PickupResults::where('playerID', '=', $this->getPlayerID())->get();

        $count = 0;
        $wins = 0;
        $loss = 0;
        $tie = 0;
        foreach ($results as $result) {

            if ($result->playerID === $this->playerID) {
                $timeStamp = $result->checkSumID;

                $converted = date('m/d/Y', $timeStamp / 1000);
                if (strtotime($converted) > strtotime('-30 days')) {
                    $count = $count + 1;
                    if ($result->gameResult === 'Win') {
                        $wins = $wins + 1;
                    }
                    if ($result->gameResult === 'Loss') {
                        $loss = $loss + 1;
                    }
                    if ($result->gameResult === 'Tie') {
                        $tie = $tie + 1;
                    }

                }
            }

        }

        return
            array(
                'total' => $count,
                'wins' => $wins,
                'loss' => $loss,
                'tie' => $tie,
                'percent' => $loss == 0 ? 0 : round(($wins / ( $wins + $loss) * 100 ),2) . ' %'
            );
    }

如有任何帮助，我们将不胜感激。

当使用 Arun P 的答案时

$playersList = DB::table('pickup_results')
    ->select(DB::raw("playerID"),
        DB::raw("COUNT(CASE WHEN gameResult = 'Win'  THEN 1 END) AS wins"),
        DB::raw("COUNT(CASE WHEN gameResult = 'Loss' THEN 1 END) AS loss"),
        DB::raw("COUNT(CASE WHEN gameResult = 'Tie' THEN 1 END) AS tie"))
    ->where('checksumID','<',$now)->where('checksumID','>',$thirty_days_ahead)
    ->groupBy('playerID')
    ->orderBy('wins', 'DESC')
    ->get();

它将返回 0 个结果。这是不正确的；我试图收集玩家在过去 30 天内玩过的所有游戏。不多也不少。

您可以访问http://www.Krayvok.com/t1并查看统计页面以获取工作示例。

我正在尝试获取当前排行榜，该排行榜显示所有玩家玩过的游戏总数。我想对其进行过滤，以仅显示从今天开始的过去 30 天内(滚动 30 天)玩过游戏的玩家。

最佳答案

我对您现有的查询做了一些更改，

$timestamp_from = date('Y-m-d', strtotime('-30 days'));

$playersList = DB::table('pickup_results')
    ->select(DB::raw("playerID"),
        DB::raw("COUNT(CASE WHEN gameResult = 'Win'  THEN 1 END) AS wins"),
        DB::raw("COUNT(CASE WHEN gameResult = 'Loss' THEN 1 END) AS loss"),
        DB::raw("COUNT(CASE WHEN gameResult = 'Tie' THEN 1 END) AS tie")
    )
    ->where(DB::raw("DATE((checkSumID/1000)) >= '{$timestamp_from}'"))
    ->groupBy('playerID')
    ->orderBy('wins','DESC')
    ->get();

在您的方法 getTotalGamesPlayed30DayWinLossTies 中，checkSumID 被除以 1000，我已将其包含在查询中。 checkSumID 的 where 子句已更改为比较日期。

如果您需要任何帮助，请尝试发表评论。

关于php - 按日期过滤现有查询，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/56678579/