PostgreSQL 查询 : Column Sum for the latest available date of each month

Question

给定一个如下所示的 pSQL 表:

date       | data
2015-01-23 | 15
2015-01-23 | 11
2015-02-25 | 15
2015-02-25 | 11
2015-01-25 | 24
2015-01-25 | 2
2015-01-25 | 13
2015-01-29 | 5
2015-02-28 | 12
2015-02-28 | 1
2015-05-15 | 12
2015-05-16 | 1

如何获取每个月最后可用日期的数据总和？结果示例:

date       | data
2015-01-29 | 5
2015-02-28 | 13
2015-05-16 | 1

这是我到目前为止尝试过的:

SELECT year,month,max(day),sum(data) FROM
  (
    SELECT
      date,
      date_part('year', date)  AS year,
      date_part('month', date) AS month,
      date_part('day', date)   AS day,
      sum(data)                AS tdata
    FROM table a
    GROUP BY date, date_part('year', date), date_part('month', date), date_part('day', date)
    ORDER BY year ASC, month ASC, day ASC
  ) dataq
GROUP BY year,month

我从中得到的总和似乎是错误的。

Answer 1

您应该在内部查询中计算总和，按一天分组。在外部查询中选择一个月中的最后一天:

select distinct on (year, month) 
    make_date(year::int, month::int, day::int) as date, 
    data
from (
    select 
        date_part('year', date) as year,
        date_part('month', date) as month,
        date_part('day', date) as day,
        sum(data) as data
    from my_table
    group by date
    ) s
order by year, month, day desc

    date    | data 
------------+------
 2015-01-29 |    5
 2015-02-28 |   13
 2015-05-16 |    1
(3 rows)