javascript - 如何在postgres中查询过去四个星期每周的总和

Question

简而言之，我有一个看起来像这样的交易表

我试图通过过去 7 天( day by day )、过去 4 周( week by week )和过去 12 个月( month by month )查询此表。
我打算用javascript获取日期，逐个查询事务，然后在外面计算每个(周，日，月)的总和，然后创建一个数组来保存值。但我的勇气告诉我，可以通过 postgres db 查询获得大部分内容。
所以我的问题是，如果是的话，是否有可能如何。
这是我试图获取日期

const today = new Date();
const firstWeekEnd = new Date(today);
const secondWeekStart = new Date(today);
const secondWeekEnd = new Date(today);
const thirdWeekStart = new Date(today);
const thirdWeekEnd = new Date(today);
const fourthWeekStart = new Date(today);
const fourthWeekEnd = new Date(today);

function formatDate(date) {
  let dd = date.getDate();
  let mm = date.getMonth() + 1;
  const yyyy = date.getFullYear();
  if (dd < 10) { dd = `0${dd}`; }
  if (mm < 10) { mm = `0${mm}`; }
  date = `${yyyy} ${mm}= ${dd}`;
  return date;
}

firstWeekEnd.setDate(firstWeekEnd.getDate() - 6);
secondWeekStart.setDate(secondWeekStart.getDate() - 7);
secondWeekEnd.setDate(secondWeekEnd.getDate() - 13);
thirdWeekStart.setDate(thirdWeekStart.getDate() - 14);
thirdWeekEnd.setDate(thirdWeekEnd.getDate() - 20);
fourthWeekStart.setDate(fourthWeekStart.getDate() - 21);
fourthWeekEnd.setDate(fourthWeekEnd.getDate() - 27);

我正在使用 nodejs 和 sequelize
任何帮助或想法都非常感谢

Answer 1

您可以创建一个 CTE，它生成一个名称和一个时区范围 (tstzrange) 的时间戳。然后使用 element contained by operator 将该 CTE 加入您的表。最后，对落入特定范围的那些使用 SUM 函数。请参见示例 here 。

with date_ranges (range_name, range_dates) as
     ( values ('week_0', tstzrange ((now()-interval '6 days'),  now(),'[]'))
            , ('week_1', tstzrange ((now()-interval '13 days'), (now()-interval '7 days'), '[]'))
            , ('week_2', tstzrange ((now()-interval '20 days'), (now()-interval '14 days'),'[]'))
            , ('week_3', tstzrange ((now()-interval '27 days'), (now()-interval '21 days'),'[]')) 
     ) 
select range_name, range_dates, sum(amount) total_amount 
  from mytable      mt
  join date_ranges  dr
    on (mt.created_at <@ range_dates)
 group by range_name, range_dates
 order by range_name;