PostgreSQL周度数据汇总：如何处理首尾不完整周？

我有一张包含date_和my_daily_count字段的表，数据如下：

date_    | my_daily_count      
------------+------------------
 2023-07-18 |              1                        
 2023-07-19 |              0 
 2023-07-20 |              0 
 2023-07-21 |              2 
 2023-07-22 |              0
 2023-07-23 |              0 
 2023-07-24 |              1
 2023-07-25 |              0
 2023-07-26 |              0
 2023-07-27 |              0 
 2023-07-28 |              0
 2023-07-29 |              0
 2023-07-30 |              0
 2023-07-31 |              1
 2023-08-01 |              1
 2023-08-02 |              0
 2023-08-03 |              1 
 2023-08-04 |              0
 2023-08-05 |              0
 2023-08-06 |              0
 2023-08-07 |              0
 2023-08-08 |              0

需要生成周度汇总表，满足：

• 每行显示对应周的起始标识：首周不完整时用表中首个日期，否则用周一
• 统计该周my_daily_count的总和
• 末周不完整需在日期后标注“not complete”

当前使用的SQL无法满足需求，问题在于首周不完整时返回周一而非实际起始日，且无法标记末周是否完整：

SELECT
       DATE_TRUNC('week', date_)::DATE AS week_,
       SUM(my_daily_count) AS my_weekly_count   
FROM my_table    
GROUP BY week_
ORDER BY week_ ; 

WITH date_bounds AS (
    SELECT
        MIN(date_) AS min_date,
        MAX(date_) AS max_date,
        DATE_TRUNC('week', MAX(date_))::DATE + INTERVAL '6 days' AS week_end_of_max_date
),
weekly_groups AS (
    SELECT
        date_,
        my_daily_count,
        DATE_TRUNC('week', date_)::DATE AS week_start_monday,
        CASE WHEN DATE_TRUNC('week', date_)::DATE = DATE_TRUNC('week', (SELECT min_date FROM date_bounds))::DATE THEN 1 ELSE 0 END AS is_first_week,
        CASE WHEN DATE_TRUNC('week', date_)::DATE = DATE_TRUNC('week', (SELECT max_date FROM date_bounds))::DATE THEN 1 ELSE 0 END AS is_last_week
    FROM my_table
)
SELECT
    CASE 
        WHEN is_first_week = 1 THEN (SELECT min_date FROM date_bounds)::TEXT
        ELSE week_start_monday::TEXT 
    END || 
    CASE 
        WHEN is_last_week = 1 AND (SELECT max_date FROM date_bounds) < (SELECT week_end_of_max_date FROM date_bounds) THEN ' (not complete)'
        ELSE '' 
    END AS week_identifier,
    SUM(my_daily_count) AS my_weekly_count
FROM weekly_groups
GROUP BY week_start_monday, is_first_week, is_last_week
ORDER BY week_start_monday;

• date_bounds公共表表达式：先获取表中日期的最小、最大值，同时计算末周的完整结束日期（周一加6天），用来判断末周是否完整。
• weekly_groups公共表表达式：给每条数据标记所属周的周一起始日，以及是否属于首周、末周。
• 主查询：
  • 首周的起始标识替换为表中最早日期，其他周保留周一；
  • 如果末周的最大日期小于该周的完整结束日，说明末周不完整，添加“(not complete)”标注；
  • 按周分组求和并按周起始日排序。

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