获得最新的组和由ID

Question

我停留在获得最新的独特的书的价值的总和

这是表看起来像

book_id  ordered_at(unix timestamp)         value 
1   1440630635 - assume this is July 1, 2015 8:00:00     250 
1   1440630635 - assume this is July 15, 2015 8:00:00      150 
1   1440630635 - assume this is July 30, 2015 8:00:00      100 
2   1440630635- assume this is July 5, 2015 8:00:00      200 
2   1440630635- assume this is July 20, 2015 8:00:00      300 
2   1440630635- assume this is July 30, 2015 8:00:00      200 

给出一个时间戳，我想通过本书

说，如果给定的时间戳是7月31日，2015年00:00:00获得最新订单的总价值，

查询应返回的300的和值，因为7月30日是最近的书1和2以及

1   1440630635 - assume this is July 30, 2015 8:00:00      100 
2   1440630635- assume this is July 30, 2015 8:00:00      200 

如果给定的时间戳2015年7月22日00:00:00

的查询应返回的450的和值，因为7月15日是最近期的预订1和7月20日是最近的书2

1   1440630635 - assume this is July 15, 2015 8:00:00      150 
2   1440630635- assume this is July 20, 2015 8:00:00      300 

编辑：我只寻找最最近和比给定日期更短

Answer 1

您可以在子查询中进行汇总，以便在上述日期（7月22日或7月31日或其他日期）之前找出每本书的最大日期。只有当日期更改为“2015年7月31日00:00:00”总结发现匹配子查询

-- your table structure may be different. This example serves just as a guide 
create table test (
    book_id int, 
    ordered_at datetime, 
    book_value int 
); 

insert into test values 
(1, '2015-07-01 08:00:00', 250), 
(1, '2015-07-15 08:00:00', 150), 
(1, '2015-07-30 08:00:00', 100), 
(2, '2015-07-05 08:00:00', 200), 
(2, '2015-07-20 08:00:00', 300), 
(2, '2015-07-30 08:00:00', 200); 

-- Result of this will be 450 
select sum(book_value) 
from test a 
inner join 
-- sub-query that gets the latest data for each book 
(
    select book_id, max(ordered_at) max_ordered_at 
    from test 
    where ordered_at < '2015-07-22 00:00:00' 
    group by book_id 
) b 
    on a.book_id = b.book_id and a.ordered_at = b.max_ordered_at; 

的记录，你的结果应该是300

如果愿望要查找与给定日期最接近的总计，可以使用以下查询：

-- Result: 450 
select sum(book_value) 
from test a 
inner join 
(
    select book_id 
      ,min(abs(unix_timestamp(ordered_at) - unix_timestamp('2015-07-22 00:00:00'))) as latest 
    from test 
    group by book_id 
) b 
    on a.book_id = b.book_id 
    and abs(unix_timestamp(a.ordered_at) - unix_timestamp('2015-07-22 00:00:00')) = b.latest;