我是postgres的初学者,想获得每个月的第一行(分组依据(,但我真的不确定如何获得。
我的表order
如下:
order_id | cust_id | order_date
------------------------------------------------
order1 | cust1 | January 19, 2020, 1:00 PM
order2 | cust1 | January 30, 2020, 2:00 PM
order3 | cust1 | February 20, 2020, 3:00 PM
order4 | cust1 | February 28, 2020, 4:00 PM
order5 | cust2 | February 27, 2020, 4:00 PM
预期结果应符合:
order_id | cust_id | order_date
------------------------------------------------
order1 | cust1 | January 19, 2020, 1:00 PM
order3 | cust1 | February 20, 2020, 3:00 PM
order5 | cust2 | February 27, 2020, 4:00 PM
但我无法使用下面的查询获得上述结果,其中我获得的结果与表相同:
select distinct on (order_date)cust_id, order_date, order_id from order
group by delivery_date, customer_id, delivery_id
order by delivery_date asc
关闭。使用月份并取消group by
:
select distinct on (cust_id, date_trunc('month', order_date) ) cust_id, order_date, order_id
from order
order by cust_id, date_trunc('month', order_date), delivery_date asc
您可以使用row_number
分析函数,如下所示:
Select * from
(Select t.*,
row_number() over (partition by cust_id, date_trunc('month', order_date)
order by order_date) as rn
From your_table t) t
Where rn = 1