sql - 如何选择每月、每年和每周的最新条目？

Question

我有一个数据表历史。我想生成我需要的图表，

1. 每天的最后一个条目
2. 每周最后一个条目
3. 每个月的最后一个条目
4. 每年的最后一个条目

根据所选期间类型在图表上显示值。

使用下面的 sql 代码，我可以获取每天的最后一个条目。

SELECT t.created_on,
     t.earned_value,
     t.planned_value,
     t.budgeted_cost
FROM history t
JOIN (SELECT MAX(tt.created_on) 'maxtimestamp'
FROM history tt
GROUP BY date(tt.created_on)) m ON m.maxtimestamp = t.created_on

如何获取其他三个结果？

Answer 1

您可以使用 window function 非常优雅地解决这个问题:

SELECT created_on, earned_value, planned_value, budgeted_cost
FROM (
  SELECT created_on, earned_value, planned_value, budgeted_cost,
         rank() OVER (PARTITION BY extract(year from created_on),
                                   extract(doy from created_on)
                      ORDER BY created_on DESC) r
  FROM history) sub
WHERE r = 1
ORDER BY created_on;

对于周和月时间段，您只需将 extract(doy from created_on) 参数更改为 extract(week ... (doy = day of the year ，从 1 月 1 日开始计算)。对于年度数据，您只需要第一个 extract()，因此您应该删除第二个 PARTITION 项。