如何在PostgreSQL中按周五为周起始标记月度第1-5周？

问题描述 #

我有大量日期数据，需要生成以周五为周起始日的月度周报，但编写的PostgreSQL查询无法正确标记月度内的第1至4/5周。

原查询语句：

SELECT
rank() over(PARTITION by EXTRACT(ISODOW FROM log_barang.log_date::date) in (6)) "week",
EXTRACT(ISODOW FROM log_barang.log_date::date) dayy,
to_char(log_barang.log_date::date, 'TMDay') as dayname,
log_barang.log_date::date as tanggal
FROM
log_barang
WHERE
EXTRACT(MONTH from log_barang.log_date)=4 AND
EXTRACT(YEAR from log_barang.log_date)=2024 
GROUP BY
dayy, tanggal
ORDER BY
tanggal asc

查询结果的周数始终为1，无法按预期划分周数。期望的结果示例：

原查询问题分析 #

原查询的PARTITION BY条件是EXTRACT(ISODOW FROM log_date::date) in (6)，这会生成一个布尔值（是否为周六），导致仅分成两个分区。rank()在每个分区内从1开始计数，最终所有行的周数都显示为1，完全没实现按周分组的逻辑。

修正后的查询语句 #

根据期望的周数划分逻辑（当月起始至第一个周五为第1周，周六起进入第2周，以此类推），可以通过统计当前日期前的周五数量来生成周数：

SELECT
  -- 统计当前日期之前的周五数量，加1得到周数
  sum(CASE WHEN EXTRACT(ISODOW FROM tanggal) = 5 THEN 1 ELSE 0 END) 
    OVER (ORDER BY tanggal ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) + 1 AS week,
  EXTRACT(ISODOW FROM tanggal)::int AS dayy,
  to_char(tanggal, 'TMDay') AS dayname,
  tanggal
FROM (
  SELECT log_date::date AS tanggal
  FROM log_barang
  WHERE EXTRACT(MONTH FROM log_date) = 4 
    AND EXTRACT(YEAR FROM log_date) = 2024
) t
ORDER BY tanggal ASC;

逻辑说明 #

1. 子查询先筛选出2024年4月的所有日期并转换为date类型
2. 窗口函数sum(...) OVER (...)按日期顺序，统计当前日期之前所有行中属于周五（ISODOW=5）的数量
3. 统计结果加1，得到当前日期所在的周数：
   • 当月第一个周五及之前的日期，之前没有周五，所以周数为1
   • 第一个周五之后的日期，每经过一个周五，周数加1

这样就能完全匹配你给出的期望结果。

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