时间戳

首页 > 解决方案 > 如何在postgres中查询过去四个星期的每周总和

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

问题描述

简而言之，我有一个看起来像这样的交易表 在此处输入图像描述

我试图在过去 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并续集

非常感谢任何帮助或想法

标签： javascriptnode.jspostgresqlsequelize.js

解决方案

您可以创建一个 CTE，它会生成名称和带有时区范围 ( tstzrange ) 的时间戳。然后使用operator 包含的元素将该 CTE 加入到您的表中。最后，对属于特定范围的人使用 SUM 函数。请参见此处的示例。

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;

推荐阅读