sql - PostgreSQL:试图找到上个月评分最高的小姐和先生

Question

在 my Drupal website用户可以互相评分，这些带时间戳的评分存储在 pref_rep 表中:

# select id, nice, last_rated from pref_rep where nice=true
  order by 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

我还在 pref_users 表中保留了每个用户的性别:

# select id, female from pref_users limit 7;
       id       | female
----------------+--------
 OK351636836012 | f
 OK366097485338 | f
 OK251293359874 | t
 OK7848446207   | f
 OK335478250992 | t
 OK355400714550 | f
 OK146955222542 | t

我正在尝试创建 2 个显示“上个月小姐”和“上个月先生”的 Drupal block ，但我的问题与 Drupal 无关，所以请不要将其移至 drupal.stackexchange.com ;-)

我的问题是关于 SQL 的:如何找到 nice 计数最高的用户 - 以及上个月的用户？我会有 2 个查询 - 一个针对女性，一个针对非女性。

使用 PostgreSQL 8.4.8/CentOS 5.6 和 SQL 有时很难:-)

谢谢!亚历克斯

更新:

我有一个很好的建议，可以将时间戳转换为字符串，以便查找上个月(而不是过去 30 天)的记录

更新 2:

我结束了字符串比较:

select r.id,
        count(r.id),
        u.first_name,
        u.avatar,
        u.city
from pref_rep r, pref_users u where
        r.nice=true and
        to_char(current_timestamp - interval '1 month', 'IYYY-MM') =
        to_char(r.last_rated, 'IYYY-MM') and
        u.female=true and
        r.id=u.id
group by r.id , u.first_name, u.avatar, u.city
order by count(r.id) desc
limit 1

Answer 1

假设您在每个月的第一天运行一次并缓存结果，因为在每个页面上计算选票有点没用。

首先是一些日期算法:

SELECT now(), 
       date_trunc( 'month', now() ) - '1 MONTH'::INTERVAL, 
       date_trunc( 'month', now() );

              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

好的，我们得到了“上个月”日期时间范围的界限。现在我们需要一些窗口函数来获取每个性别的第一行:

SELECT * FROM (
   SELECT *, rank( ) over (partition by gender order by score desc ) 
   FROM (
      SELECT user_id, count(*) AS score FROM pref_rep 
      WHERE nice=true 
      AND last_rated >= date_trunc( 'month', now() ) - '1 MONTH'::INTERVAL
      AND last_rated <  date_trunc( 'month', now() )
      GROUP BY user_id) s1 
   JOIN users USING (user_id)) s2 
WHERE rank=1;

请注意，这可以为您提供多行以防万一。

编辑:

I've got a nice suggestion to cast timestamps to strings in order to find records for the last month (not for the last 30 days)

date_trunc() 效果更好。

如果您进行 2 次查询，则必须进行两次 count()。由于用户可能会为其他用户多次投票，因此该表可能会更大，因此扫描一次是一件好事。

您不能“也将返回到用户表的连接留在查询的外部”，因为您需要性别...

上面的查询需要大约 30 毫秒，有 1000 个用户和 100000 个选票，所以你肯定想缓存它。