如何用百分比计算结果获得投票

rslzwgfq 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(318)

在我的laravel 5.7/mysql 5应用程序中，我有一个包含投票结果的表：

`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`vote_item_id` INT(10) UNSIGNED NOT NULL,
`user_id` INT(10) UNSIGNED NOT NULL,
`is_correct` TINYINT(1) NOT NULL DEFAULT '0',
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

其中布尔值是\正确的字段是答案是否正确。我需要得到正确答案百分比的数据。
创建此类请求

$voteItemUsersResultsCorrect = VoteItemUsersResult::    // Grouped by vote name
getByIsCorrect(true)->
getByCreatedAt($filter_voted_at_from, ' > ')->
getByCreatedAt($filter_voted_at_till, ' <= ')->
getByUserId($filterSelectedUsers)->
getByVote($filterSelectedVotes)->
getByVoteCategories($filterSelectedVoteCategories)->
getByVoteIsQuiz(true)->
getByVoteStatus('A')->
select( \DB::raw('count(vote_item_users_result.id) as count, votes.id, votes.name as vote_name') )->
orderBy('vote_name', 'asc')->
groupBy( 'votes.id' )->
groupBy( 'vote_name' )->
join(\DB::raw('vote_items'), \DB::raw('vote_items.id'), '=', \DB::raw('vote_item_users_result.vote_item_id'))->
join(\DB::raw('votes '), \DB::raw('votes.id'), '=', \DB::raw('vote_items.vote_id'))->
get();

我可以通过sql请求获得正确的票数。

SELECT count(vote_item_users_result.id)     AS count, votes.id, votes.name     AS vote_name 
    FROM `vote_item_users_result` 
    INNER JOIN vote_items on vote_items.id = vote_item_users_result.vote_item_id 
    INNER JOIN votes  on votes.id = vote_items.vote_id 
    WHERE `vote_item_users_result`.`is_correct` = '1'     AND vote_item_users_result.created_at  > '2018-08-01'      AND vote_item_users_result.created_at  <= '2018-09-22 23:59:59'      AND `votes`.`is_quiz` = '1'     AND `votes`.`status` = 'A' 
    GROUP BY `votes`.`id`, `vote_name` 
    ORDER BY `vote_name` asc

我知道如何得到第二个类似的要求 is_correct ='0'和php端，将结果与百分比计算结合起来，但我想知道是否可以在1个请求中使用eloquent完成？
如果是，怎么做？
谢谢！

mysql eloquent laravel-5

来源：https://stackoverflow.com/questions/52828729/how-to-get-votes-with-results-with-percent-calculating

1条答案

按热度按时间

8oomwypt1#

一个正确的原始mysql将使用条件聚合：

SELECT
    v.id,
    100.0 * COUNT(CASE WHEN vir.is_correct = 1 THEN 1 END) / COUNT(*) AS pct_correct,
    100.0 * COUNT(CASE WHEN vir.is_correct = 0 THEN 1 END) / COUNT(*) AS pct_incorrect
FROM votes v
INNER JOIN vote_items vi
    ON v.id = vi.vote_id
INNER JOIN vote_item_users_result vir
    ON vi.id = vir.vote_item_id 
WHERE
    vir.created_at  > '2018-08-01' AND vir.created_at  < '2018-09-23' AND
    v.is_quiz = '1' AND
    v.status = 'A'
GROUP BY
    v.id;

现在我们可以尝试为此编写laravel代码：

DB::table('vote')
    ->select('vote.id',
             DB::raw('100.0 * COUNT(CASE WHEN vir.is_correct = 1 THEN 1 END) / COUNT(*) AS pct_correct'),
             DB::raw('100.0 * COUNT(CASE WHEN vir.is_correct = 0 THEN 1 END) / COUNT(*) AS pct_incorrect'))
    ->join('vote_items', 'votes.id', '=', 'vote_items.vote_id')
    ->join('vote_item_users_result', 'vote_items.id', '=', 'vote_item_users_result.vote_item_id ')
    ->where([
        ['vote_item_users_result.created_at', '>', '2018-08-01'],
        ['vote_item_users_result.created_at', '<', '2018-09-23'],
        ['vote.is_quiz', '=', '1'],
        ['vote.status', '=', 'A']
    ])
    ->groupBy('vote.id')
    ->get();

赞(0）回复(0）举报 2021-06-20

我来回答

如何用百分比计算结果获得投票

1条答案

相关问题

热门标签

最新问答