返回每个分区的最大值

Question

我有这个表：

create table some_date
(
dt date,
t time without time zone,
price numeric   
)

这是一个样本数据集：

insert into some_date 
values ('2021-11-17','06:39:37.7663',2),
('2021-11-17','06:39:38.7663',5),
('2021-11-17','06:39:39.7663',0),
('2021-11-17','06:39:40.7663',9),
('2021-11-17','06:39:41.7663',3),
('2021-11-17','06:39:42.7663',7),
('2021-11-17','06:39:43.7663',5),
('2021-11-17','06:39:44.7663',6),
('2021-11-17','06:39:45.7663',3),
('2021-11-17','06:39:46.7663',1),
('2021-11-17','06:39:47.7663',2),
('2021-11-17','06:39:48.7663',5),
('2021-11-17','06:39:49.7663',8),
('2021-11-17','06:39:50.7663',9),
('2021-11-17','06:39:51.7663',4),
('2021-11-17','06:39:52.7663',5),
('2021-11-17','06:39:53.7663',6),
('2021-11-17','06:39:54.7663',6),
('2021-11-17','06:39:55.7663',7),
('2021-11-17','06:39:56.7663',8),
('2021-11-17','06:39:57.7663',9),
('2021-11-17','06:39:58.7663',7),
('2021-11-17','06:39:59.7663',8),
('2021-11-17','06:40:00.7663',9),
('2021-11-17','06:40:01.7663',1),
('2021-11-17','06:40:00.7663',9),
('2021-11-17','06:40:01.7663',1),
('2021-11-17','06:40:02.7663',9),
('2021-11-17','06:40:03.7663',1),
('2021-11-17','06:40:04.7663',9),
('2021-11-17','06:40:05.7663',1),
('2021-11-17','06:40:06.7663',9),
('2021-11-17','06:40:07.7663',1),
('2021-11-17','06:40:08.7663',9),
('2021-11-17','06:40:09.7663',1),
('2021-11-17','06:40:10.7663',9),
('2021-11-17','06:40:11.7663',1),
('2021-11-17','06:40:12.7663',9),
('2021-11-17','06:40:13.7663',1),
('2021-11-17','06:40:14.7663',9),
('2021-11-17','06:40:15.7663',1),
('2021-11-17','06:40:16.7663',9),
('2021-11-17','06:40:17.7663',1),
('2021-11-17','06:40:18.7663',9),
('2021-11-17','06:40:19.7663',1),
('2021-11-17','06:40:20.7663',9),
('2021-11-17','06:40:21.7663',1),
('2021-11-17','06:40:22.7663',9),
('2021-11-17','06:40:23.7663',1),
('2021-11-17','06:40:24.7663',9),
('2021-11-17','06:40:25.7663',1),
('2021-11-17','06:40:26.7663',9),
('2021-11-17','06:40:27.7663',1),
('2021-11-17','06:40:28.7663',9),
('2021-11-17','06:40:29.7663',1),
('2021-11-17','06:40:30.7663',9),
('2021-11-17','06:40:31.7663',1),
('2021-11-17','06:40:32.7663',9),
('2021-11-17','06:40:33.7663',1),
('2021-11-17','06:40:34.7663',9),
('2021-11-17','06:40:35.7663',1);

我想查询每分钟的数据，比如max，min。。。，所以我用row_number()和分区来设置组一分钟的

with t as (
select * , extract(hour from t) as h,  extract(minute from t) as m
from  price
), tt as (
select * , row_number() over(partition by h,m order by t) as t_sequence
from t)

现在我想通过这个‘分区’查询，怎么做？

Answer 1

我猜您想要使用的是"分组"；而不是"；"分区"：

with t as (
select * , extract(hour from t) as h,  extract(minute from t) as m
from  price
), tt as (
select * , row_number() over(partition by h,m order by t) as t_sequence
from t)

或者没有CTE:的查询

SELECT
extract(hour from t) as h,
extract(minute from t) as m, 
max(price) AS max_price, 
min(price) AS min_price
FROM some_table
GROUP BY h, m;