适用范围:按两个日期之间的差异排序

Question

我想创建一个从最近日期到最远日期排序数据的作用域。

例如，我有3个值:

<Value id: 1, date: '2012-12-20'>
<Value id: 2, date: '2012-12-28'>
<Value id: 3, date: '2012-12-31'>

然后我想对最接近给定日期的日期进行排序:2012-12-29。
我应该得到这个订单:2, 3, 1。
如果我选择2012-12-30，结果必须是:3, 2, 1。

我试过这样做:

scope :order_by_closest_date, lambda{|time| 
  select("*, (date - DATE('#{time}')) AS time").order("time ASC")
}

但是行不通。
参考:Rails 3.2.9 Ruby 1.9.3 Postgresql 9.1.4.
什么好主意吗?

Answer 1

简单查询

^{第一个例子根据初始问题使用date列。}

不确定Ruby的语法，但是一个正确的SQL语句应该是:

SELECT * 
FROM   tbl
ORDER  BY @(date_col - '2012-12-29'::date)

@为"绝对值"运营商。

永远不要使用date或time作为标识符。虽然在PostgreSQL中是允许的(有一些限制)，但这些是SQL标准中的保留字，它会导致令人困惑的错误消息和可能意想不到的错误。

优越性能

^{其余部分根据评论中的更新与timestamp列一起工作。}

对于小表或特别查询，上面的解决方案很好。对于中型或大型表，如果性能很重要，我建议使用更复杂的方法。

条件是date或timestamp列上的索引。这样的:

CREATE INDEX tbl_my_timestamp_idx ON tbl(my_timestamp);

索引就绪后，下面的查询将降低对于大表的简单查询的性能:

SELECT *
FROM  (
    (
    SELECT *
    FROM   tbl
    WHERE  my_timestamp >= '2012-12-30 11:32'::timestamp
    ORDER  BY my_timestamp
    LIMIT  3
    )
    UNION ALL
    (
    SELECT *
    FROM   tbl
    WHERE  my_timestamp < '2012-12-30 11:32'::timestamp
    ORDER  BY my_timestamp DESC
    LIMIT  3
    )
    ) x
ORDER  BY @extract('epoch' FROM (my_timestamp - '2012-12-28 11:32'::timestamp))
LIMIT  3;

• UNION ALL - SELECT两条腿周围的括号不是可选的。需要将LIMIT应用于每条腿。

• 如果您按其他列排序，则在索引中反映-在这种情况下使用多列索引。

如何?

第一个查询使用表达式作为条件。Postgres必须为每一行计算一个值，然后按结果排序并选择前几行。对于小表没有问题，但是对于大表来说非常昂贵。 O (n) ;n为表中的行数。它不能使用普通索引。加上在所有行中排序和挑选优胜者的一些不小的成本。
可以在表达式上创建索引，这将是最快的，但这只适用于用于比较的常量时间戳——这几乎不是一个现实的用例。

第二个查询根据您的时间戳在索引中找到位置，依次读取接下来几行的元组指针，并直接从表中获取它们(或者甚至直接从索引中使用9.2中仅索引扫描)。两次，一次上升，一次下降，因为我们不知道同行如何比较。但这只是2 × O(log(n))(典型的b-树查找成本)计算只针对少数预先选择的行。从小样本中挑选优胜者需要一个微不足道的常数成本。

用EXPLAIN ANALYZE测试。在对一个真实的表进行的快速测试中，对于一个有50k行的表，得到了因子1000。对于更大的表，它会不断缩放

Answer 2

try

scope :order_by_closest_date, lambda{|time| 
  select("*, DATEDIFF(date,DATE('#{time}')) AS time").order("time ASC")
}