查找数据库内当日期变化时的两条记录(窗口函数的使用)?

1.postgreSQL中的t_stocpf表格存有每天24小时的生产数据，该表日期字段是datemodif,时间字段是heuremodif,如何查出在日期从2024年8月22日变化到2024年8月23日时，在8月22日和8月23日的这两条记录？有没有简洁的SQL查询语句？

 

WITH ranked_data AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY datemodif ORDER BY heuremodif DESC) AS rn_desc,
ROW_NUMBER() OVER (PARTITION BY datemodif ORDER BY heuremodif ASC) AS rn_asc
FROM t_stocpf
WHERE datemodif IN ('20240825', '20240826')
)
SELECT *
FROM ranked_data
WHERE (datemodif = '20240825' AND rn_desc = 1)
OR (datemodif = '20240826' AND rn_asc = 1);

 

2.以上表格查询每天生产数据的最后一个和第一个

WITH ranked_data AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY datemodif ORDER BY heuremodif DESC) AS rn_desc,
ROW_NUMBER() OVER (PARTITION BY datemodif ORDER BY heuremodif ASC) AS rn_asc
FROM t_stocpf
)
SELECT *
FROM ranked_data
WHERE rn_desc = 1 OR rn_asc = 1 order by datemodif,heuremodif;

3.使用FIRST_VALUE,LAST_VALUE实现查询每天生产数据的最后一个和第一个

WITH data_with_values AS (
SELECT *,
FIRST_VALUE(heuremodif) OVER (PARTITION BY datemodif ORDER BY heuremodif ASC) AS first_time,
LAST_VALUE(heuremodif) OVER (PARTITION BY datemodif ORDER BY heuremodif ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_time
FROM t_stocpf
),
first_last_records AS (
SELECT DISTINCT datemodif, first_time AS heuremodif
FROM data_with_values
UNION ALL
SELECT DISTINCT datemodif, last_time AS heuremodif
FROM data_with_values
)
SELECT t_stocpf.*
FROM t_stocpf
JOIN first_last_records
ON t_stocpf.datemodif = first_last_records.datemodif
AND t_stocpf.heuremodif = first_last_records.heuremodif
ORDER BY t_stocpf.datemodif, t_stocpf.heuremodif;

4.按日期分别查每天的第一件和最后一件

WITH ranked_data AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY datemodif ORDER BY heuremodif ASC) AS rn
FROM t_stocpf
)
SELECT *
FROM ranked_data
WHERE rn = 1
ORDER BY datemodif, heuremodif;

WITH ranked_data AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY datemodif ORDER BY heuremodif DESC) AS rn
FROM t_stocpf
)
SELECT *
FROM ranked_data
WHERE rn = 1
ORDER BY datemodif, heuremodif;