sql - 如何在不分块的情况下按周分组(查询 _trailing_ 4 周)

Question

我有一个工作数据库，我想按周计算过去一个月的工作。我当前的查询执行了，但它按日历周而不是我想要的尾随周分块:

SELECT date_trunc('week', scheduled_for::date) AS weekly,
   COUNT(*)           
FROM jobs_job WHERE state = 'complete' AND scheduled_for > NOW() - INTERVAL '1 month' 
GROUP BY weekly
ORDER BY weekly;

我怎样才能改变它，使每个“每周”周期正好是 7 天？

Answer 1

你可能想使用 extract week

SELECT EXTRACT(isoyear FROM scheduled_for) as yearly,
       EXTRACT(WEEK FROM scheduled_for) as weekly,
       COUNT (*)
FROM jobs_job WHERE state = 'complete' AND scheduled_for > NOW() - INTERVAL '1 month' 
GROUP BY EXTRACT(isoyear FROM scheduled_for),
         EXTRACT(WEEK FROM scheduled_for)
ORDER BY EXTRACT(isoyear FROM scheduled_for),
         EXTRACT(WEEK FROM scheduled_for);