newbie_girl newbie_girl - 1 month ago 13
SQL Question

How to return percentage in PostgreSQL?

CREATE TEMPORARY TABLE

CREATE TEMP TABLE percentage(
gid SERIAL,
zoom smallint NOT NULL,
x smallint NOT NULL,
y smallint NOT NULL
);


INSERT DATA

INSERT INTO percentage(zoom, x, y) VALUES
(0,5,20),
(0,5,21), (0,5,21),
(0,5,22), (0,5,22), (0,5,22),
(0,5,23), (0,5,23), (0,5,23), (0,5,23),
(0,5,24), (0,5,24), (0,5,24), (0,5,24), (0,5,24),
(1,5,20),
(1,5,21), (1,5,21),
(1,5,22), (1,5,22), (1,5,22),
(1,5,23), (1,5,23), (1,5,23), (1,5,23),
(1,5,24), (1,5,24), (1,5,24), (1,5,24), (1,5,24);


How many times certain tile shows up (tile is represented by x and y)

SELECT zoom, x, y, count(*) AS amount
FROM percentage
GROUP BY zoom,x,y
ORDER BY zoom, amount;


Result:

zoom | x | y | amount
------+---+----+--------
0 | 5 | 20 | 1
0 | 5 | 21 | 2
0 | 5 | 22 | 3
0 | 5 | 23 | 4
0 | 5 | 24 | 5
1 | 5 | 20 | 1
1 | 5 | 21 | 2
1 | 5 | 22 | 3
1 | 5 | 23 | 4
1 | 5 | 24 | 5
(10 rows)


Question

How to get back percentage of each tile (x and y) for certain zoom, or in other words, how many times have the certain tile showed up for certain zoom?

Wanted result:

zoom | x | y | amount | percentage
------+---+----+--------+-----------
0 | 5 | 20 | 1 | 6.667
0 | 5 | 21 | 2 | 13.333
0 | 5 | 22 | 3 | 20
0 | 5 | 23 | 4 | 26.667
0 | 5 | 24 | 5 | 33.333
1 | 5 | 20 | 1 | 6.667
1 | 5 | 21 | 2 | 13.333
1 | 5 | 22 | 3 | 20
1 | 5 | 23 | 4 | 26.667
1 | 5 | 24 | 5 | 33.333
(10 rows)


*This is just a sample data, percentages are not supposed to be the same, except as a pure coincidence!

Answer

If am not wrong you are looking for this

SELECT zoom,x,y,
       amount,
       ( amount / Cast(Sum(amount) OVER(partition BY zoom) AS FLOAT) ) * 100 as amt_percentage
FROM   (SELECT zoom,x, y,
               Count(*) AS amount
        FROM   percentage
        GROUP  BY zoom,x,y) a 

Or even

SELECT zoom,x,y,
       Count(*) AS amount,
       ( Count(*) / Cast(Sum(Count(*))OVER(partition BY zoom) AS FLOAT) ) * 100 AS amt_percentage
FROM   percentage
GROUP  BY zoom,x,y