sql - 检索每组中的最后 n 条记录 - GCP Spanner
问题描述
有一张数据表
ID CLIENT_ID TYPE VALUE DATE
-----------------------------------------------------------
1 101 original 1.2 2020-03-01T13:14:00
2 101 additional 2.7 2020-03-02T13:14:00
3 101 additional 2.65 2020-03-03T13:14:00
4 101 original 1.8 2020-03-04T13:14:00
5 102 original 1.65 2020-04-05T13:14:00
6 102 additional 2.76 2020-04-06T13:14:00
7 102 additional 2.9 2020-04-07T13:14:00
8 102 original 1.1 2020-04-08T13:14:00
9 102 additional 2.65 2020-05-09T13:14:00
10 102 original 1.36 2020-05-10T13:14:00
11 102 original 1.48 2020-05-11T13:14:00
12 102 additional 2.87 2020-05-12T13:14:00
13 101 original 1.69 2020-06-13T13:14:00
14 101 additional 2.95 2020-06-14T13:14:00
15 101 additional 2.87 2020-06-15T13:14:00
16 101 original 1.23 2020-06-16T13:14:00
我想按 CLIENT_ID、TYPE 和 DATE 检索最近的 2 条记录。
ID CLIENT_ID TYPE VALUE DATE
-----------------------------------------------------------
15 101 additional 2.87 2020-06-15T13:14:00
14 101 additional 2.95 2020-06-14T13:14:00
16 101 original 1.23 2020-06-16T13:14:00
13 101 original 1.69 2020-06-13T13:14:00
12 102 additional 2.87 2020-05-12T13:14:00
9 102 additional 2.65 2020-05-09T13:14:00
11 102 original 1.48 2020-05-11T13:14:00
10 102 original 1.36 2020-05-10T13:14:00
或聚合
ID CLIENT_ID TYPE VALUE DATE VALUE_2ND DATE_2ND
----------------------------------------------------------------------------------------------
15 101 additional 2.87 2020-06-15T13:14:00 2.95 2020-06-14T13:14:00
16 101 original 1.23 2020-06-16T13:14:00 1.69 2020-06-13T13:14:00
12 102 additional 2.87 2020-05-12T13:14:00 2.65 2020-05-09T13:14:00
11 102 original 1.48 2020-05-11T13:14:00 1.36 2020-05-10T13:14:00
不幸的是，Spanner 不支持窗口函数。
怎么可能呢？
解决方案
在标准 SQL 中，您可以将其表示为：
select t.*
from t
where t.date in (select t2.date
from t t2
where t2.client_id = t.client_id and
t2.type = t.type
order by t2.date desc
limit 1 offset 1
)
order by t.client_id, t.type, t.date;
我不是 100% 确定这在 Spanner 中有效，但值得一试。