我有一张桌子:
| Time | Metric A | Metric B | Value |
|---|---|---|---|
| A | A | A | 1 |
| A | A | B | 2 |
| A | A | C | 1 |
| B | A | A | 1 |
| B | A | B | 2 |
| B | A | C | 1 |
我想要得到最后一个，并按指标A总结表格，但有时时间会变得疯狂，按时间分组不是一个选项.
我想要的是获得每个指标A的指标B的最后一个值.有什么关于如何确保我获得指标A的指标B的最后插入值的提示吗？
| Time | Metric A | Metric B | Value |
|---|---|---|---|
| A | A | A | 1 |
| A | A | B | 2 |
| A | A | C | 1 |
您可以使用RANK()或Dense_RANK()来实现您想要的结果.
create table table1 (
time timestamp,
metric_a varchar(1),
metric_b varchar(1),
value integer
);
insert into table1 values
(current_timestamp, 'A','A',1),
(current_timestamp, 'A','B',2),
(current_timestamp, 'A','C',1),
(current_timestamp - interval '10 minutes', 'A','A',1),
(current_timestamp - interval '10 minutes', 'A','B',2),
(current_timestamp - interval '10 minutes', 'A','C',1),
(current_timestamp, 'B','A',2),
(current_timestamp, 'B','B',3),
(current_timestamp, 'B','C',4),
(current_timestamp - interval '10 minutes', 'B','A',2),
(current_timestamp - interval '10 minutes', 'B','B',3),
(current_timestamp - interval '10 minutes', 'B','C',4);
select time, metric_a, metric_b, value
from (
select *,
dense_rank() over (partition by metric_a, metric_b order by time desc) as rnk
from table1
)z
where rnk = 1;
| time | metric_a | metric_b | value |
|---|---|---|---|
| 2023-08-14T15:13:49.623Z | A | A | 1 |
| 2023-08-14T15:13:49.623Z | A | B | 2 |
| 2023-08-14T15:13:49.623Z | A | C | 1 |
| 2023-08-14T15:13:49.623Z | B | A | 2 |
| 2023-08-14T15:13:49.623Z | B | B | 3 |
| 2023-08-14T15:13:49.623Z | B | C | 4 |