在 postgresql 中计算百分比
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41373394/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calculate Percentage in postgresql
提问by John
I have a query which returns the results
我有一个返回结果的查询
time t_count cumulative_t_count zone hour
_____ _____ _________________ ____ ____
1 10 10 1 1
2 20 30 1 1
3 30 60 1 1
4 60 120 1 1
My query will be like
我的查询会像
select time,t_count,sum(t_count) over (order by time) as
cumulative_t_count ,zone,hour from (select distinct
time,count(distinct num_id) as t_count,zone,hour from (select * from
public.table1 where day=1 and time>0 order by time)as A group by
time,hour,zone order by hour,zone )B where zone=1 and hour=1;
Now I want an additional column which shows the percentage of cumulative count like this
现在我想要一个额外的列来显示这样的累积计数的百分比
time t_count cumulative_t_count zone hour percentage_cumulative count
_____ _____ _________________ ____ ____ ____________________________
1 10 10 1 1 10%
2 20 30 1 1 30%
3 30 60 1 1 60%
4 40 100 1 1 100%
I tried with
我试过
select time,t_count,sum(t_count) over (order by time) as
cumulative_t_count ,cumulative_t_count/sum(t_count) as percentage_cumulative count,zone,hour from (select distinct
time,count(distinct num_id) as t_count,zone,hour from (select * from
public.table1 where day=1 and time>0 order by time)as A group by
time,hour,zone order by hour,zone )B where zone=1 and hour=1;
But it did not work.Any help is appreciated.
但它没有工作。任何帮助表示赞赏。
采纳答案by misterrabinhalder
In order to calculate the percentage of cumulative count each row contributes to the total cumulative count, you should run another SQL on your result set and calculate percentage for each row.
为了计算每行累积计数占总累积计数的百分比，您应该在结果集上运行另一个 SQL 并计算每行的百分比。
I believe query should answer the question you're asking for.
我相信 query 应该回答你要问的问题。
SELECT time,
t_count,
cumulative_t_count,
zone,
hour,
(((cumulative_t_count)/(sum(cumulative_t_count) OVER (ORDER BY time))) * 100) AS percentage_cumulative_count
FROM
(SELECT
time,
t_count,
SUM(t_count) OVER (ORDER BY time) AS cumulative_t_count,
zone,
hour
FROM
(
SELECT DISTINCT
time,
COUNT(DISTINCT num_id) AS t_count,
zone,
hour
FROM
(
SELECT
*
FROM
public.table1
WHERE
day = 1
AND time > 0
ORDER BY
time
)
AS a
GROUP BY
time,
hour,
zone
ORDER BY
hour,
zone
) b
WHERE
zone = 1
AND hour = 1) x;
回答by P?????
Find the maximum t_countvalue and divide each value by the max value to find percentage
找到最大值t_count并将每个值除以最大值以找到百分比
Try this
试试这个
SELECT time,
t_count,
Sum(t_count) OVER(ORDER BY time) AS cumulative_t_count,
zone,
hour,
( t_count / Max(t_count) OVER() ) * 100 AS percentage_cumulative,
...
