如何计算指定时间段内至少有一次事件的周数占比？

嘿，我完全懂你的需求——就是要统计每个区域在给定时间段里，至少有1次事件的周数占总周数的百分比对吧？你的思路方向是对的，但问题出在没对周数做去重处理，导致同一个周里有多个事件时会被重复计数，这就是为什么会出现超过100%的结果啦。

原查询的问题分析 #

你原来的sum(case when ... then 1 else 0 end)是把每个事件都算一次，比如某周有5个事件，这里就会加5而不是加1，分子被放大，自然百分比就超了。我们需要的是按周计数，而不是按事件计数。

正确的解决思路 #

核心是先对每个区域的“事件周”去重，统计出有事件的唯一周数，再除以时间段内的总周数，就能得到正确的占比。这里推荐两种写法：

写法一：直接用COUNT(DISTINCT)

SELECT 
    a.location,
    -- 统计有事件的唯一周数
    COUNT(DISTINCT date_trunc('week', h.started_at)) AS weeks_with_happenings,
    -- 计算时间段内的总周数（用INTERVAL更严谨，比除以7准确）
    (({{end}} - {{start}}) / INTERVAL '1 week')::INTEGER AS total_weeks,
    -- 计算百分比，保留两位小数
    ROUND(
        COUNT(DISTINCT date_trunc('week', h.started_at))::FLOAT 
        / (({{end}} - {{start}}) / INTERVAL '1 week')::INTEGER 
        * 100,
        2
    ) AS listing_rate_percentage
FROM areas a
-- 用LEFT JOIN确保没有任何事件的区域也会被统计（占比0%）
LEFT JOIN happenings h 
    ON h.primary_area_id = a.id
    AND h.started_at BETWEEN {{start}} AND {{end}}
GROUP BY a.location

写法二：用CTE先去重再统计（更清晰）

如果觉得上面的写法有点紧凑，可以用CTE先把每个区域的事件周去重，再统计：

WITH area_event_weeks AS (
    SELECT 
        a.location,
        -- 去重每个区域的事件周
        DISTINCT date_trunc('week', h.started_at) AS event_week
    FROM areas a
    LEFT JOIN happenings h 
        ON h.primary_area_id = a.id
        AND h.started_at BETWEEN {{start}} AND {{end}}
)
SELECT 
    location,
    COUNT(event_week) AS weeks_with_happenings,
    (({{end}} - {{start}}) / INTERVAL '1 week')::INTEGER AS total_weeks,
    ROUND(
        COUNT(event_week)::FLOAT 
        / (({{end}} - {{start}}) / INTERVAL '1 week')::INTEGER 
        * 100,
        2
    ) AS listing_rate_percentage
FROM area_event_weeks
GROUP BY location

关键细节说明 #

1. COUNT(DISTINCT)：这是解决重复计数的核心，它会把同一个周的多个事件合并成一次计数。
2. LEFT JOIN：替换原来的JOIN，这样即使某个区域在整个时间段里没有任何事件，也会被纳入统计，此时占比为0%，不会漏掉数据。
3. 总周数计算：用INTERVAL '1 week'来计算总周数，比直接除以7更严谨，因为时间段可能不是刚好7的整数倍（比如跨月的情况）。

备注：内容来源于stack exchange，提问作者DavidM