PostgreSQL:使用每天每个名称的最后一个条目计算列中值的出现次数

Question

我有一个看起来像这样的表:

id |   name   |   time               |    measurement
---+----------+----------------------+----------------
10 |   abc    | 2015-02-13 20:12:34  |        2
9  |   abc    | 2015-02-13 19:12:34  |        1
8  |   xyz    | 2015-02-13 18:12:34  |        1
7  |   abc    | 2015-02-13 17:12:34  |        0
6  |   abc    | 2015-02-12 20:12:34  |        0
5  |   pqr    | 2015-02-12 19:12:34  |        2
4  |   pqr    | 2015-02-12 18:12:34  |        1
3  |   xyz    | 2015-02-12 17:12:34  |        0
2  |   pqr    | 2015-02-12 16:12:34  |        1
1  |   abc    | 2015-02-12 15:12:34  |        0

我只需要选择最近 N 天的每个名称每天的最后一个条目，其中 N 是一个常数:

id |   name   |   time               |    measurement
---+----------+----------------------+----------------
10 |   abc    | 2015-02-13 20:12:34  |        2
8  |   xyz    | 2015-02-13 18:12:34  |        1
6  |   abc    | 2015-02-12 20:12:34  |        0
5  |   pqr    | 2015-02-12 19:12:34  |        2
3  |   xyz    | 2015-02-12 17:12:34  |        0

然后统计measurement中每个不同值每天出现的次数:

day         |   value   |  count
------------+-----------+----------
2015-02-13  |     2     |    1
2015-02-13  |     1     |    1
2015-02-12  |     2     |    1
2015-02-12  |     0     |    1

执行此操作的最佳方法是什么？

Answer 1

SQL Fiddle

select
    to_char(day, 'YYYY-MM-DD') as day,
    measurement as value,
    count(*) as count
from (
    select distinct on (1, name)
        date_trunc('day', time) as day, measurement
    from t
    order by 1, name, time desc
) s
group by 1, 2
order by 1, 2 desc