MySQL SELECT 命令计算百分比

SELECT command to calculate percentage

提问by BeaversAreDamned

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.

然后我试图显示从最高观看次数到最低观看次数的所有视频，在一个漂亮的 HTML 页面中显示其侧面的百分比。

Obviously the percentage would range from 0 - 100% (and not over) and the most popular video would probably have 100% I assume..

显然，百分比的范围是 0 - 100%（而不是结束），我认为最受欢迎的视频可能是 100%。

I have about 3,400 videos in the database. My attempts are laughable and have been scratching my head for about days now..

我在数据库中有大约 3,400 个视频。我的尝试很可笑，并且已经挠了好几天了。

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.

老实说，我什至不知道这个 SQL 查询是否正确。

I haven't even taken into consideration the videos viewsagainst all video viewsand total videos..

我还没有考虑到视频的观看次数对所有的视频观看次数和总视频..

Really stuck..

真的卡住了。。

回答by Larry Lustig

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.

您想为数据库中的每个视频计算 (video_views * 100) / (largest_views_for_any_single_video)。

The numerator is easy, it's just the video_views column. The denominator is

分子很简单，它只是 video_views 列。分母是

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.

对于观看次数最多的视频，这应该会产生 100，对于其他视频，百分比会更低，对于从未观看过的任何视频，百分比应降至 0。

回答by peacedog

To modify what sheepsimulator suggests, you might try:

要修改sheepsimulator 的建议，您可以尝试：

SELECT 
    id, 
    video_title, 
    video_views, 
    (select sum(video_views) from video_public)) as TotalViews,
    ((100 * video_views)/(select sum(video_views) from video_public)) as PercentOfViews
FROM 
    video_public 
order by 
    video_views.

Change ordering to suit your tastes, of course.

当然，更改顺序以适合您的口味。

回答by J. Polfer

Well, I'd start by thinking about what your'e looking for:

好吧，我首先考虑您要寻找的内容：

percentage of each video I have in my database based on its view count against all other videos.

我的数据库中每个视频的百分比基于其与所有其他视频的观看次数。

Basically, you want to find it's view rankfirst. Why not sort the records based on video views:

基本上，您想首先找到它的查看排名。为什么不根据视频观看次数对记录进行排序：

SELECT id, video_title, video_views
FROM video_public order by video_views DESCENDING

Now, and I think this is what you want to do, is to only show a portion of these, say, the top 10%? You want to then assign each of your records a percentile. This means that for the ordering you've assigned to the videos, you want to make the "top row" (first one returned) be given 100% and the last row returned 0%. It gives a number between 0 and 100 to each item in your resultset.

现在，我认为这就是你想要做的，是只显示其中的一部分，比如前 10%？然后您想为您的每条记录分配一个百分位数。这意味着对于您分配给视频的排序，您想让“顶行”（返回的第一个）为 100%，最后一行返回 0%。它为结果集中的每个项目提供 0 到 100 之间的数字。

Your'e percentile is computed:

计算您的百分位数：

SELECT id, 
       video_title, 
       video_views, 
       ((video_views * 100) / (select max(video_views) from video_public)) video_percentile
FROM video_public order by video_views DESCENDING

If you only want to show then the top 10%, try the following:

如果您只想显示前 10%，请尝试以下操作：

SELECT id, 
       video_title, 
       video_views, 
       ((video_views * 100) / (select max(video_views) from video_public)) video_percentile
FROM video_public
WHERE ((video_views * 100) / (select max(video_views) from video_public)) > 90
ORDER BY video_views DESCENDING

It isn't totally clear what you're looking for, but I think this could be helpful.

目前尚不清楚您在寻找什么，但我认为这可能会有所帮助。

---

---

EDIT:After looking over the comments, specifically Riven's and Larry Lustig's, and re-reading the question, I'd have to say that the sum() of the video_views is incorrect, so I went back and changed the sum()s to max()s. This will give you a percentage based upon the video viewed the most.

编辑：在查看评论，特别是 Riven 和 Larry Lustig 的评论并重新阅读问题后，我不得不说 video_views 的 sum() 不正确，所以我回去将 sum() 更改为最大（）s。这将为您提供基于观看次数最多的视频的百分比。

回答by Ben Fransen

I think it's better to calculate your percentage in your script, and just retreive the view-counts from the database.

我认为最好在脚本中计算百分比，然后从数据库中检索查看次数。

回答by Aaron Digulla

Run two selects:

运行两个选择：

 select max(video_views) FROM video_public

to get the maximum number of views and then run your other select and calculate the percentage in your script. Otherwise, the subselect might run for each result row ... which is not what you want, performance wise.

获得最大视图数，然后运行您的其他选择并计算脚本中的百分比。否则，子选择可能会为每个结果行运行......这不是你想要的，性能明智。

Also note that you must use max, not sumor count(*)since you want to know "how often has this video been watched compared to the one which I watched most". Imaging you watched every video once and one twice. What's the percentage going to be? 100%? Or 0.0000001%?

另请注意，您必须使用max, notsum或count(*)因为您想知道“与我观看次数最多的视频相比，观看此视频的频率”。想象一下，您观看了每个视频一次又一次。百分比会是多少？100%？还是 0.0000001%？

回答by Drevak

select id, ((video_views * 100) / (select sum(views) from videos)) view_percent   from video_public

this will give you what percentage of the total views has each video.

这将为您提供每个视频在总观看次数中的百分比。

回答by Tebo

Try this it works in MySQL now.

试试这个它现在在 MySQL 中工作。

select id, video, views, ((views / (select sum(views) from video)) * 100) as Percentagess
from video

This is the table:

这是表：

CREATE TABLE IF NOT EXISTS `video` (
  `ID` int(11) NOT NULL,
  `Video` varchar(50) NOT NULL,
  `Views` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `video`
--

INSERT INTO `video` (`ID`, `Video`, `Views`) VALUES
(1, 'Hulk', 20),
(2, 'Hyman', 30),
(3, 'The King', 24);

The Code below works in SQL Server:

下面的代码适用于 SQL Server：

It is a cursor with a temporary table i just wrote.

这是一个带有我刚写的临时表的游标。

declare @total int set @total = (select sum(Views) from video)

声明 @total int set @total =（从视频中选择 sum(Views)）

declare @videoid int
declare @video varchar(50)
declare @views int

declare @percentage decimal(18, 2)


IF  EXISTS (SELECT * FROM tempdb.sys.tables WHERE NAME LIKE '%tmp%')
DROP TABLE #tmp


--create temporary table
CREATE TABLE #tmp (VideoID int, VideoTitle varchar(50), Views int, Percentage decimal(18, 2))

DECLARE @videoPercent CURSOR
SET @videoPercent = CURSOR FOR 
    select id, video, views
    from video

OPEN @videoPercent

FETCH NEXT FROM @videoPercent INTO @videoid, @video, @views
WHILE @@FETCH_STATUS = 0
begin

    set @percentage = (convert(decimal(18,2), @views) / convert(decimal(18,2), @total)) * 100; 

    insert into #tmp(VideoID, VideoTitle, Views, Percentage)
    values(@videoid, @video, @views, @percentage);

    FETCH NEXT FROM @videoPercent INTO @videoid, @video, @views
end

select * from #tmp

CLOSE @videoPercent
DEALLOCATE @videoPercent

This is the table:

这是表：

CREATE TABLE [dbo].[Video](
    [ID] [int] NOT NULL,
    [Video] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Views] [int] NOT NULL
)

Fill it in with data and u are ready to go. Enjoy yourself.

用数据填充它，你就可以开始了。玩的开心。

回答by Riven

If you'd want calculate the percentage in 1 query, use:

如果您想计算 1 个查询中的百分比，请使用：

SELECT `vp1`.`id`, `vp1`.`video_views` * 100 / (SELECT MAX(`vp2`.`video_views`) FROM video_public AS `vp2`) FROM video_public AS `vp1`

Ofcourse it would be much more efficient to store the intermediate result in PHP (or a SQL variable) and pass it to the next query

当然，将中间结果存储在 PHP（或 SQL 变量）中并将其传递给下一个查询会更有效率

$phpmax <= SELECT MAX(`vp2`.`video_views`) FROM video_public AS `vp2`
SELECT `vp1`.`id`, `vp1`.`video_views` * 100 / {$phpmax} ) FROM video_public AS `vp1`

==> Everybody using SUM(views) in the query has the wrong results !! The highest ranking video should result in 100%, not a percentage of the view count of all videos combined, therefore you must use MAX(views)

==> 在查询中使用 SUM(views) 的每个人都有错误的结果！！排名最高的视频应该是 100%，而不是所有视频总观看次数的百分比，因此您必须使用 MAX(views)

回答by Stuart

Something to bear in mind... so far all answers include subqueries, which I don't think are necessary. These will be evaluated for EACH row in your table!

要记住的事情......到目前为止，所有答案都包括子查询，我认为这不是必需的。这些将针对表中的每一行进行评估！

Rather do this externally, e.g.

而是在外部执行此操作，例如

Instead of this:

取而代之的是：

select id, ((video_views * 100) / (select sum(views) from videos)) view_percent
from video_public

Do this:

做这个：

declare @totalviews int
select @totalviews = sum(views) from videos

select id, (video_views * 100 / @totalviews) view_percent
from video_public

That should run faster, and have less of an impact on your database.

这应该运行得更快，并且对您的数据库的影响更小。

回答by Dennis Allen

You will not end up with your most viewed video at 100% unless only one of your videos gets viewed, but it will give you the view count, ratio of views of this video over all viewings, and a percent of times this has been viewed compared to all videos.

除非只有一个视频被观看，否则您不会以 100% 获得观看次数最多的视频，但它会为您提供观看次数、该视频的观看次数占所有观看次数的比例以及观看次数的百分比与所有视频相比。

---

---

SELECT 
    Videos.id                     AS VideoID,
    video_views                   AS ViewCount,
    video_views / Total.ViewCount AS ViewRatio,
    Convert(varchar(6),Round(100.00 * video_views / Total.ViewCount, 2)) + '%' AS ViewPercentage
FROM 
    video_public AS Videos
CROSS JOIN 
    (SELECT Convert(float,Sum(video_views)) AS ViewCount FROM video_public) AS Total
ORDER BY
    video_views DESC