Subject | help for a query where i need percentage calculation |
---|---|
Author | ndirondello marco |
Post date | 2012-05-10T12:50:55Z |
hi all, i have this table
TABLE DATA
(
ID bigint NOT NULL,
TIME timestamp NOT NULL,
SOURCE_ID bigint,
VALUE double precision NOT NULL,
);
i need to do a query or a procedure to group data by SOURCE_ID and to get
SOURCE_ID, Max(time), percentage of rows where value < 5, percentage of rows where value < 10
my problem is how calculate the last 2 fields
so if i have this data (timeX are timestamp)
1, "time0", 100, 3
2, "time1", 200, 7
3, "time2", 100, 11
4, "time3", 100, 9
5, "time4", 200, 14
i need percentage0 for source_id = 100
1 (# rows with value < 5 and source_id = 100) / 3 (# rows with source_id = 100) = 0.6
percentage1 for source_id = 100
2 (# rows with value < 10 and source_id = 100) / 3 (# rows with source_id = 100) = 0.3
percentage0 for source_id = 200
0 (# rows with value < 5 and source_id = 200) / 2 (# rows with source_id = 200) = 0
i need percentage0 for source_id = 100
1 (# rows with value < 10 and source_id = 200) / 2 (# rows with source_id = 200) = 0.5
so my query need to return
100, maxtime0, 0.6, 0.3
200, maxtime1, 0, 0.5
how can i do that query? i hope that you understand my example
my table have a lot of data (about 100 milions of record) so i need also that the query is fastest
thanks
TABLE DATA
(
ID bigint NOT NULL,
TIME timestamp NOT NULL,
SOURCE_ID bigint,
VALUE double precision NOT NULL,
);
i need to do a query or a procedure to group data by SOURCE_ID and to get
SOURCE_ID, Max(time), percentage of rows where value < 5, percentage of rows where value < 10
my problem is how calculate the last 2 fields
so if i have this data (timeX are timestamp)
1, "time0", 100, 3
2, "time1", 200, 7
3, "time2", 100, 11
4, "time3", 100, 9
5, "time4", 200, 14
i need percentage0 for source_id = 100
1 (# rows with value < 5 and source_id = 100) / 3 (# rows with source_id = 100) = 0.6
percentage1 for source_id = 100
2 (# rows with value < 10 and source_id = 100) / 3 (# rows with source_id = 100) = 0.3
percentage0 for source_id = 200
0 (# rows with value < 5 and source_id = 200) / 2 (# rows with source_id = 200) = 0
i need percentage0 for source_id = 100
1 (# rows with value < 10 and source_id = 200) / 2 (# rows with source_id = 200) = 0.5
so my query need to return
100, maxtime0, 0.6, 0.3
200, maxtime1, 0, 0.5
how can i do that query? i hope that you understand my example
my table have a lot of data (about 100 milions of record) so i need also that the query is fastest
thanks