sql - 计算PostgreSQL中的百分比

我有一个返回结果的查询

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

我的问题是

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;

现在我需要一个额外的列来显示累积计数的百分比，如下所示

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%

我试过了

 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;

但没用，谢谢你的帮助。

最佳答案

为了计算每行累积计数占总累积计数的百分比，应该对结果集运行另一个sql并计算每行的百分比。
我相信问句应该能回答你的问题。

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;

关于sql - 计算PostgreSQL中的百分比，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/41373394/