SQL:对更多组进行 COUNT DISTINCT

Question

我有这个问题:

SELECT 
        date_trunc('week', q.order_date::date+2)::date-2 AS weekly,
        COUNT(DISTINCT q.provider_username) AS engaged_providers
FROM tbl_quotes q
GROUP BY weekly

我需要的是 COUNT(DISTINCT q.provider_username) 超过 4 周，而不仅仅是一个，而且我不想更改我的日期截断。显然我不能使用 OVER(ORDER BY ...) 因为 DISTINCT 没有为窗口函数实现。还有其他解决方案吗？

Answer 1

样本数据和期望的结果会很有帮助。对于您的问题的一种解释，我建议使用相关子查询。日期算法有点神秘，但我认为这就是您想要的:

SELECT weekly,
       (SELECT COUNT(DISTINCT q.provider_username)
        FROM tbl_quotes q
        WHERE q.order_date >= w.weekly - interval '3 week' and
              q.order_date < w.weekly + interval '1 week'
       ) as engaged_providers
FROM (SELECT DISTINCT date_trunc('week', q.order_date::date+2)::date-2 as weekly
      FROM tbl_quotes q
     ) w;