Calculate difference between rows.
| От | Maurice Breeman |
|---|---|
| Тема | Calculate difference between rows. |
| Дата | |
| Msg-id | 000801c68ef2$0770a350$0200a8c0@libre.local обсуждение исходный текст |
| Ответы |
Re: Calculate difference between rows.
|
| Список | pgsql-novice |
Hello there.
I have a fairly dumb question (I think), but I just can't seem to find my way out of it.
I have a simple table, wich reads:
postgres=# select * from car_fix order by dat ASC;
clientnr | receiptnr | description | dat
---------------+----------------+----------------------+------------
1 | 1 | Changed Tires | 2006-04-12
4 | 4 | Changed oil | 2006-05-12
4 | 3 | Regular Checkup | 2006-05-12
2 | 2 | Changed Tires | 2006-05-12
3 | 5 | Fixed Airbag | 2006-05-12
2 | 8 | Flat Tire | 2006-06-13
1 | 7 | Broken Axis | 2006-06-13
5 | 6 | Brake problem | 2006-06-13
(8 rows)
clientnr | receiptnr | description | dat
---------------+----------------+----------------------+------------
1 | 1 | Changed Tires | 2006-04-12
4 | 4 | Changed oil | 2006-05-12
4 | 3 | Regular Checkup | 2006-05-12
2 | 2 | Changed Tires | 2006-05-12
3 | 5 | Fixed Airbag | 2006-05-12
2 | 8 | Flat Tire | 2006-06-13
1 | 7 | Broken Axis | 2006-06-13
5 | 6 | Brake problem | 2006-06-13
(8 rows)
postgres=#
Which I then narrow down to services fixes per month, using:
postgres=# select to_char(dat, 'yyyy') as year, to_char(dat, 'mm') as month,
count(receiptnr) AS fixes from car_fix group by month,year order by month;
year | month | fixes
------+-------+-----------
2006 | 04 | 1
2006 | 05 | 4
2006 | 06 | 3
(3 rows)
count(receiptnr) AS fixes from car_fix group by month,year order by month;
year | month | fixes
------+-------+-----------
2006 | 04 | 1
2006 | 05 | 4
2006 | 06 | 3
(3 rows)
postgres=#
So far, so good. But now I wanna create an SQL select statement (of VIEW) which calculates the difference between the actual row and the previous row
The output should be something like this:
year | month | fixes | increase
------+-------+----------+-----------
2006 | 04 | 1 | 0
2006 | 05 | 4 | 3
2006 | 06 | 3 | -1
------+-------+----------+-----------
2006 | 04 | 1 | 0
2006 | 05 | 4 | 3
2006 | 06 | 3 | -1
(3 rows)
Or even in percentages, but, that's no big deal, once I find out the way to calculate the difference between rows. Can somebody give me a hand?
gr
Freak
В списке pgsql-novice по дате отправления: