sql - PostgreSQL: trying to find miss and mister of the last month with highest rating -

sql - PostgreSQL: trying to find miss and mister of the last month with highest rating -

at my drupal website users can rate each other , timestamped ratings stored in pref_rep table:

# select id, nice, last_rated pref_rep nice=true order last_rated desc limit 7; id | nice | last_rated ------------------------+------+---------------------------- ok152565298368 | t | 2011-07-07 14:26:38.325716 ok452217781481 | t | 2011-07-07 14:26:10.831353 ok524802920494 | t | 2011-07-07 14:25:28.961652 ok348972427664 | t | 2011-07-07 14:25:17.214928 de11873 | t | 2011-07-07 14:25:05.303104 ok335285460379 | t | 2011-07-07 14:24:39.062652 ok353639875983 | t | 2011-07-07 14:23:33.811986

also maintain gender of each user in pref_users table:

# select id, female pref_users limit 7; id | female ----------------+-------- ok351636836012 | f ok366097485338 | f ok251293359874 | t ok7848446207 | f ok335478250992 | t ok355400714550 | f ok146955222542 | t

i'm trying create 2 drupal blocks displaying "miss lastly month" , "mister lastly month", question not drupal, please don't move drupal.stackexchange.com ;-)

my question sql: how find user highest count of nice - , lastly month? have 2 queries - 1 female , 1 non-female.

using postgresql 8.4.8 / centos 5.6 , sql hard :-)

thank you! alex

update:

i've got nice suggestion cast timestamps strings in order find records lastly month (not lastly 30 days)

update2:

i've ended doing string comparison:

select r.id, count(r.id), u.first_name, u.avatar, u.city pref_rep r, pref_users u r.nice=true , to_char(current_timestamp - interval '1 month', 'iyyy-mm') = to_char(r.last_rated, 'iyyy-mm') , u.female=true , r.id=u.id grouping r.id , u.first_name, u.avatar, u.city order count(r.id) desc limit 1

say run 1 time on first day of month, , cache results, since counting votes on every page kinda useless.

first date arithmetic :

select now(), date_trunc( 'month', now() ) - '1 month'::interval, date_trunc( 'month', now() ); | ?column? | date_trunc -------------------------------+------------------------+------------------------ 2011-07-07 16:24:38.765559+02 | 2011-06-01 00:00:00+02 | 2011-07-01 00:00:00+02

ok, got bounds "last month" datetime range. need window function first rows per gender :

select * ( select *, rank( ) on (partition gender order score desc ) ( select user_id, count(*) score pref_rep nice=true , last_rated >= date_trunc( 'month', now() ) - '1 month'::interval , last_rated < date_trunc( 'month', now() ) grouping user_id) s1 bring together users using (user_id)) s2 rank=1;

note can give several rows in case of ex-aequo.

edit :

i've got nice suggestion cast timestamps strings in order find records lastly month (not lastly 30 days)

date_trunc() works much better.

if create 2 queries, you'll have create count() twice. since users can potentially vote many times other users, table larger one, scanning 1 time thing.

you can't "leave joining onto users table outer part of query too" because need genders...

query above takes 30 ms 1k users , 100k votes you'd want cache it.

sql postgresql join