SQL:对更多组进行不同的计数

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

我需要的是数(不同的q.provider_username)超过4周，而不仅仅是一个，而且我不想改变我的日期-中继。很明显，我不能超过(订购.)因为没有为窗口函数实现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;