SELECT命令计算百分比 [英] SELECT command to calculate percentage
问题描述
我想根据其对所有其他视频的观看次数,获取数据库中每个视频的百分比。
我随后尝试显示从最高视图计数到最低的所有视频,在一个漂亮的HTML页面内显示其百分比。
显然,百分比范围从0 - 100%(而不是超过),最流行的视频可能会有100%的假设。
我在数据库中有大约3,400个视频。我的尝试是可笑的,并已经抓了我的头大约几天现在..
我的表看起来类似这个。
video_public
id | video_title | video_views
尝试:
SELECT
id,
video_views * 100 /(SELECT COUNT(*)FROM video_public)
FROM`video_public` stat
说实话,我甚至不知道这个SQL查询是否正确。
甚至没有考虑所有视频观看次数和视频总数的视频观看次数。
真的卡住了。
解决方案:
您想要为数据库中的每个视频计算(video_views * 100)/(largest_views_for_any_single_video)。
分子很容易,它只是video_views列。分母是
SELECT MAX(video_views)FROM video_public
所以,把它放在一起,你会得到:
(video_views * 100)/(SELECT MAX(video_views)
FROM video_public))from video_public
这应该为观看次数最多的视频产生100次,其他视频的百分比更低,从未观看过的视频会降低到0次。
I'm trying to get the percentage of each video I have in my database based on its view count against all other videos.
I'm then trying to display all the videos from highest view count to lowest, displaying its percentage on its side inside a nice HTML page.
Obviously the percentage would range from 0 - 100% (and not over) and the most popular video would probably have 100% I assume..
I have about 3,400 videos in the database. My attempts are laughable and have been scratching my head for about days now..
My table looks something similar to this.
video_public id | video_title | video_views
Attempt:
SELECT id, video_views * 100 / (SELECT COUNT(*) FROM video_public) FROM `video_public` stat
To be honest I don't even know if this SQL query is right.
I haven't even taken into consideration the videos views against all video views and total videos..
Really stuck..
解决方案Okay, based on the clarification of your question:
You want to calculated (video_views * 100) / (largest_views_for_any_single_video) for each video in the database.
The numerator is easy, it's just the video_views column. The denominator is
SELECT MAX(video_views) FROM video_public
So, put it together and you get:
SELECT video_title, ((video_views * 100) / (SELECT MAX(video_views) FROM video_public)) FROM video_public
That should produce 100 for the most-viewed video(s), and lower percentages for other videos, down to 0 for anything never viewed.
这篇关于SELECT命令计算百分比的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!