如何通过分区获取不同typeid下每月的第一条员工数据？

现有表结构及数据 #

CREATE TABLE employee
(
    joining_date date,
    employee_type character varying,
    name character varying ,
    typeid integer
);
insert into employee VALUES
    ('2021-08-12','as','hjasghg', 1),
    ('2021-08-13', 'Rs', 'sa', 1),
    ('2021-08-14','asktyuk','hjasg', 1),
    ('2021-09-12','as','hjasghg', 1),
    ('2021-09-13', 'Rs', 'sa', 1),
    ('2021-09-14','asktyuk','hjasg', 1),
    ('2022-08-02','as','hjasghg', 2),
    ('2022-08-03','as','hjasghg', 2),
    ('2022-08-04', 'Rs', 'sa', 2),
    ('2022-08-05','asktyuk','hjasg', 2);

需求与问题 #

需求是获取不同typeid下每个月的第一条数据的所有列，预期得到日期为2021-08-12、2021-09-12、2022-08-02的三条数据，但尝试的SQL仅返回两条：

with cte_a as
    (select row_number() over (partition by typeid order by joining_date asc) sno, * from employee)
select * from cte_a where sno = 1;

问题原因 #

原SQL的PARTITION BY仅按typeid分组，ROW_NUMBER()会给同一个typeid的所有数据按日期排序，取sno=1只能拿到每个typeid的最早一条数据，无法区分月份，因此typeid=1的9月数据被遗漏。

正确解法 #

需要将PARTITION BY的分组条件改为typeid+月份，用DATE_TRUNC('month', joining_date)提取日期的月份部分，确保每个typeid下的每个月份单独分组：

WITH cte_a AS (
    SELECT 
        ROW_NUMBER() OVER (
            PARTITION BY typeid, DATE_TRUNC('month', joining_date) 
            ORDER BY joining_date ASC
        ) AS sno,
        * 
    FROM employee
)
SELECT * FROM cte_a WHERE sno = 1;

执行后即可得到预期的三条数据：

• typeid=1，2021年8月的第一条：2021-08-12
• typeid=1，2021年9月的第一条：2021-09-12
• typeid=2，2022年8月的第一条：2022-08-02

内容的提问来源于stack exchange，提问作者codeanonym