PostgreSQL中按周分组并按日期去重的SQL优化方案咨询

可以用**窗口函数+CTE（公共表表达式）**替代嵌套子查询，让逻辑分层更清晰，同时保持查询效率：

1. 每日去重处理 #

用ROW_NUMBER()窗口函数按自然日分组，给每日的记录编号，仅保留每组的第一条记录（可通过排序规则指定保留哪条，比如id最小或最新创建的）：

WITH daily_unique AS (
    SELECT 
        created_dt,
        data,
        -- 按日期分组，给每组记录编号
        ROW_NUMBER() OVER (PARTITION BY DATE(created_dt) ORDER BY id) AS rn
    FROM analytics
)

这里PARTITION BY DATE(created_dt)确保按天分组，ORDER BY id表示优先保留id最小的记录，若要保留最新记录，可改为ORDER BY created_dt DESC。

2. 按周聚合统计 #

基于去重后的每日数据，按周分组并提取data字段内的customers和payments求和：

SELECT
    -- 按周分组，以ISO周起始日为例，不同数据库语法略有差异
    DATE_TRUNC('week', created_dt) AS week_start,
    -- 提取JSON字段并求和（以PostgreSQL为例）
    SUM((data->>'customers')::INT) AS total_customers,
    SUM((data->>'payments')::NUMERIC) AS total_payments
FROM daily_unique
WHERE rn = 1 -- 仅保留每日第一条记录
GROUP BY week_start
ORDER BY week_start;

不同数据库语法适配 #

• MySQL：JSON字段提取用data->>'$.customers'（8.0+支持），按周分组可使用DATE_FORMAT(created_dt, '%Y-%u')或STR_TO_DATE(CONCAT(YEAR(created_dt), '-', WEEK(created_dt), '-1'), '%Y-%u-%w')获取周起始日。
• SQL Server：JSON字段提取用JSON_VALUE(data, '$.customers')，按周分组用DATEADD(week, DATEDIFF(week, 0, created_dt), 0)获取周起始日。

简化替代方案（若每日重复记录的data内容一致） #

如果同一日期的所有重复记录中，data字段的customers和payments值完全相同，可直接用DISTINCT ON（PostgreSQL专属）或GROUP BY跳过窗口函数：

-- PostgreSQL 专属写法
SELECT
    DATE_TRUNC('week', created_dt) AS week_start,
    SUM((data->>'customers')::INT) AS total_customers,
    SUM((data->>'payments')::NUMERIC) AS total_payments
FROM (
    SELECT DISTINCT ON (DATE(created_dt)) created_dt, data
    FROM analytics
    ORDER BY DATE(created_dt), id
) AS daily_unique
GROUP BY week_start
ORDER BY week_start;

这种写法逻辑更扁平化，比嵌套子查询更易读和维护。

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