Geek asks and answers

How to aggregate by unique values ​​in Postgres using window functions

(Using Postgres 9.1)

My program deals with insecticidal sprayers trying to spray a few real units based on the original target. For example, Joe's sprayer had to spray 10 targets, but he actually sprayed 7.

I am given a table, which is a summary of all planned goals (column = goals) and actual goals (column = actual), as well as some other data, including the name of the sprayer. Here is the diagram:

CREATE TABLE spray_summary
(
 id character varying(1),
 target integer,
 ref_id character varying(1),
 actual integer,
 sprayer character varying(25)
)

The data is a denormalized connection between (id, target) and (ref_id, actual, sprayer), but right now this table is all I need to work with. Here are the full results:

SELECT * FROM spray_summary

+ ---- + -------- + -------- + -------- + --------- +
| id | target | ref_id | actual | sprayer |
+----+--------+--------+--------+---------+
| a  |      1 | "l"    |     10 | "Joe"   |
| a  |      1 | "m"    |     10 | "Joe"   |
| a  |      1 | "p"    |     10 | "Joe"   |
| c  |      3 | "n"    |     10 | "Joe"   |
| c  |      3 | "o"    |     10 | "Joe"   |
+----+--------+--------+--------+---------+

, id "a" - " ", "c" . , SUM , SUM "" . :

SELECT SUM(target) targets, SUM(actual) actuals, sprayer FROM spray_summary GROUP BY sprayer

:

+--------+--------+---------+
| target | actual | sprayer |
+--------+--------+---------+
|      9 |     50 | "Joe"   |
+--------+--------+---------+

(5 * 10 = 50) , , . , "" id , :

SELECT SUM(target) OVER(PARTITION BY sprayer, id),
sprayer,
SUM(actual)
FROM spray_summary
GROUP BY sprayer, target, id

:

+--------+--------+---------+
| target | actual | sprayer |
+--------+--------+---------+
|      1 |     30 | "Joe"   |
|      3 |     20 | "Joe"   |
+--------+--------+---------+

! :

+--------+--------+---------+
| target | actual | sprayer |
+--------+--------+---------+
|      4 |     50 | "Joe"   |
+--------+--------+---------+

, , , GROUP BY, . ? , , , SUM , . .

.

EDIT: , , , , . SQL , , , , .

Postgres , SQL.

+3
1

SQL Fiddle

select sum(target) as target, sum(actual) as actual, sprayer
from (
    select
        target,
        sum(actual) as actual,
        sprayer
    from spray_summary
    group by id, target, sprayer
) s
group by sprayer
order by sprayer
;
 target | actual | sprayer 
--------+--------+---------
      4 |     50 | joe
0

More articles:


All Articles