sql - 如何每天选择超过1条记录？

Question

这是一个 postgresql 问题。

PostgreSQL 8.3.3 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-9).

表格看起来像:

date_time           other_column
2012-11-01 00:00:00 ...
2012-11-02 01:00:00 ...
2012-11-02 02:00:00 ...
2012-11-02 03:00:00 ...
2012-11-02 04:00:00 ...
2012-11-03 05:00:00 ...
2012-11-03 06:00:00 ...
2012-11-05 00:00:00 ...
2012-11-07 00:00:00 ...
2012-11-07 00:00:00 ...
...

我想从特定日期范围内选择每天最多 3 条记录。

比如我想最多选择3条从2012-11-02到2012-11-05的记录。预期结果将是:

date_time           other_column
2012-11-02 01:00:00 ...
2012-11-02 02:00:00 ...
2012-11-02 03:00:00 ...
2012-11-03 05:00:00 ...
2012-11-03 06:00:00 ...
2012-11-05 00:00:00 ...

我在这上面花了几个小时，但仍然无法弄清楚。请帮我。 :(

更新:我目前试的sql每天只能select一条记录:

SELECT DISTINCT ON (TO_DATE(SUBSTRING((date_time || '') FROM 1 FOR 10), 'YYYY-MM-DD')) *
FROM myTable
WHERE  date_time >=  '20121101 00:00:00'  
AND  date_time <= '20121130 23:59:59'

Answer 1

I want to select at most 3 records per day from a specific date range.

SELECT date_time, other_column
FROM  (
   SELECT *, row_number() OVER (PARTITION BY date_time::date) AS rn
   FROM   tbl
   WHERE  date_time >= '2012-11-01 0:0'
   AND    date_time <  '2012-12-01 0:0'
   ) x
WHERE  rn < 4;

要点

• 使用窗口函数row_number() .根据问题，rank() 或 dense_rank() 是错误的 - 可能会选择超过 3 strip 有时间戳重复的记录。

• 由于您没有定义每天需要哪些 行，因此正确的答案是不要在窗口函数中包含 ORDER BY 子句。为您提供与问题匹配的任意选择。

• 我从

  中更改了您的 WHERE 子句

  WHERE  date_time >= '20121101 00:00:00'  
  AND    date_time <= '20121130 23:59:59'
  

  到

  WHERE  date_time >=  '2012-11-01 0:0'  
  AND    date_time <   '2012-12-01 0:0'
  

  对于像 '20121130 23:59:59.123' 这样的极端情况，您的语法会失败。

  @Craig 建议的内容:

  date_time::date BETWEEN '2012-11-02' AND '2012-11-05'
  

  .. 可以正常工作，但在性能方面是一种反模式。如果您在表达式中对数据库列应用强制转换或函数，则无法使用普通索引。

PostgreSQL 8.3 解决方案

最佳解决方案:Upgrade to a more recent version, preferably to the current version 9.2.

其他解决方案:

只有几天你可以使用UNION ALL:

SELECT date_time, other_column
FROM   tbl t1
WHERE  date_time >= '2012-11-01 0:0'
AND    date_time <  '2012-11-02 0:0'
LIMIT  3
)
UNION ALL 
(
SELECT date_time, other_column
FROM   tbl t1
WHERE  date_time >= '2012-11-02 0:0'
AND    date_time <  '2012-11-03 0:0'
LIMIT  3
)
...

括号在这里不是可选的。

对于 更多天，generate_series() 有变通办法 - 就像我发布的 here (including a link to more) .

在我们有窗口函数之前，我可能已经用 plpgsql 函数 解决了这个问题:

CREATE OR REPLACE FUNCTION x.f_foo (date, date, integer
                         , OUT date_time timestamp, OUT other_column text)
  RETURNS SETOF record AS
$BODY$
DECLARE
    _last_day date;          -- remember last day
    _ct       integer := 1;  -- count
BEGIN

FOR date_time, other_column IN
   SELECT t.date_time, t.other_column
   FROM   tbl t
   WHERE  t.date_time >= $1::timestamp
   AND    t.date_time <  ($2 + 1)::timestamp
   ORDER  BY t.date_time::date
LOOP
   IF date_time::date = _last_day THEN
      _ct := _ct + 1;
   ELSE
      _ct := 1;
   END IF;

   IF _ct <= $3 THEN
      RETURN NEXT;
   END IF;

   _last_day := date_time::date;
END LOOP;

END;
$BODY$ LANGUAGE plpgsql STABLE STRICT;

COMMENT ON FUNCTION f_foo(date3, date, integer) IS 'Return n rows per day
$1 .. date_from (incl.)
$2 .. date_to  (incl.)
$3 .. maximim rows per day';

调用:

SELECT * FROM f_foo('2012-11-01', '2012-11-05', 3);