[SQL] Need magic for a moving statistic
Hi, I need some magic for a moving statistic that works on a rather big table starting at a given date within the table up until now. The statistic will count events allways on fridays over periods of 2 weeks before ... biweekly? So I'd like to get a line every 2 weeks for everthing between. I sadly don't know how to spell that does: collect data where insert_date between friday1 and friday1 + inteval '2 week' collect data where insert_date between friday1 + inteval '2 week' + inteval '1 second' and friday1 + inteval '4 week' collect data where insert_date between friday1 + inteval '4 week' + inteval '1 second' and friday1 + inteval '6 week' Is this possible ? regards -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] SQL moving window averages/statistics
Just 3 points ... 1) don't use "date" as a column name because it's a data type. 2) to_char(current_date, 'MM')||to_char(current_date, 'DD') is equivalent to to_char(current_date, 'MMDD') 3) you should get the same result with ... where icao='KSFO' and (EXTRACT (MONTH from date) = 9) and (EXTRACT (DAY from date) BETWEEN 23 AND 29)) group by ... Then you lost me with your 3 day idea=8-} It might be depressingly slow but depending how time critical the report is, you could do something like select distinct (date) date, (select max(dc1.tmax) from daily_climate as dc1 where dc1.date between (dc0.date - interval '1 day') and (dc0.date + interval '1 day')) as max_tmax, (select min(dc1.tmax) from daily_climate as dc1 where dc1.date between (dc0.date - interval '1 day') and (dc0.date + interval '1 day')) as min_tmax, . from daily_climate as dc0 That's just something that might get you a result. I didn't try it out. Kai Carter schrieb: I'm currently have an sql statement that selects a week of descriptive statistics for various historical weather variables, sorted by date. SELECT to_char(date, 'MM')||to_char(date, 'DD') as date, max(tmax) as max_tmax, min(tmax) as min_tmax, avg(tmax) as mean_tmax, stddev(tmax) as std_tmax, count(tmax) as count_tmax, sum(tmax) as sum_tmax, variance(tmax) as var_tmax FROM daily_climate where icao='KSFO' and (EXTRACT(MONTH from date) =9 and EXTRACT(DAY from date) = 23) or (EXTRACT(MONTH from date) = 9 and EXTRACT(DAY from date) = 24) or (EXTRACT(MONTH from date) = 9 and EXTRACT(DAY from date) = 25) or (EXTRACT(MONTH from date) = 9 and EXTRACT(DAY from date) = 26) or (EXTRACT(MONTH from date) =9 and EXTRACT(DAY from date) = 27) or (EXTRACT(MONTH from date) = 9 and EXTRACT(DAY from date) = 28) or (EXTRACT(MONTH from date) = and EXTRACT(DAY from date) = 29) group by date order by date; The problem is that I only have 36 years of data to work with, and I would prefer to have a sample of ~100 rather than 30. So the idea would be to have a sample statistics for each day made up of 3 days: the current day, the day previous and the day after. Is it possible to get this sort of a result with one select statement? Thanks in advance for your responses, Kai Carter -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Need magic for a moving statistic
In response to Andreas :
> Hi,
>
> I need some magic for a moving statistic that works on a rather big
> table starting at a given date within the table up until now.
> The statistic will count events allways on fridays over periods of 2
> weeks before ... biweekly?
> So I'd like to get a line every 2 weeks for everthing between.
>
> I sadly don't know how to spell that does:
>
> collect data where insert_date between friday1 and friday1 +
> inteval '2 week'
> collect data where insert_date between friday1 + inteval '2 week'
> + inteval '1 second' and friday1 + inteval '4 week'
> collect data where insert_date between friday1 + inteval '4 week'
> + inteval '1 second' and friday1 + inteval '6 week'
>
> Is this possible ?
I hope i understand you correctly:
test=*# select * from foo;
d | value
+---
2009-10-02 | 1
2009-10-03 | 2
2009-10-10 | 3
2009-10-16 | 4
2009-10-20 | 5
2009-10-23 | 6
2009-10-30 | 7
2009-11-05 | 8
2009-11-13 | 9
2009-11-20 |10
(10 rows)
test=*#
select
('2009-10-02'::date + period*('14 days'::interval))::date::text || ' bis ' ||
('2009-10-02'::date + (period+1)*('14 days'::interval))::date::text,
sum
from (
select
((d-'2009-10-02'::date)/14) period,
sum(value)
from foo
group by 1
) foo
order by period;
?column? | sum
---+-
2009-10-02 bis 2009-10-16 | 6
2009-10-16 bis 2009-10-30 | 15
2009-10-30 bis 2009-11-13 | 15
2009-11-13 bis 2009-11-27 | 19
(4 rows)
Or simpler:
test=*# select ((d-'2009-10-02'::date)/14) || '. period' as period, sum(value)
from foo group by 1;
period | sum
---+-
0. period | 6
1. period | 15
3. period | 19
2. period | 15
(4 rows)
HTH, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] right join problem
Hi, I'm trying to retrieve all meter_id from table meter, and for meter_id without amount, I'd like it to show nothing, 'null'. I've used right join here but it's not giving me my desired result. Is there another way to do this? Here's the query I'm trying. Thanks in advance! select m.meter_id, mp.meter_id, sum(amount_in_cents) as am from meter m right join transaction_mpark mp on (m.meter_id = mp.meter_id) where date_time between '2009-09-28 00:00:00' and '2009-10-04 23:59:59' group by m.meter_id, mp.meter_id order by m.meter_id -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] right join problem
In response to Greenhorn : > Hi, > > I'm trying to retrieve all meter_id from table meter, and for meter_id > without amount, I'd like it to show nothing, 'null'. I've used right > join here but it's not giving me my desired result. Is there another > way to do this? Here's the query I'm trying. > > Thanks in advance! > > select m.meter_id, mp.meter_id, sum(amount_in_cents) as am > from meter m > right join transaction_mpark mp on (m.meter_id = mp.meter_id) > where date_time between '2009-09-28 00:00:00' and '2009-10-04 23:59:59' > group by m.meter_id, mp.meter_id > order by m.meter_id Use a left join instead? test=*# select * from master; id | name +-- 1 | master 1 2 | master 2 (2 rows) test=*# select * from slave; id | value +--- 1 | 1 1 | 2 1 | 3 (3 rows) test=*# select m.id, s.id, sum(s.value) from master m right join slave s on (m.id=s.id) group by m.id, s.id; id | id | sum ++- 1 | 1 | 6 (1 row) test=*# select m.id, s.id, sum(s.value) from master m left join slave s on (m.id=s.id) group by m.id, s.id; id | id | sum ++- 1 | 1 | 6 2 || (2 rows) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] right join problem
In response to Greenhorn : > > > > Use a left join instead? > Hi, > Yes, but when i use a where clause on my query it only selects joined > records. :( > Thanks. Please, answer to the list and not directly to me. The WHERE-Clause works after the JOIN - so your select contains (without the WHERE) all rows. Maybe i don't understand you, can you show a simple example with data? Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
