postgresql - Postgres - 按月求和/计数

Question

我的逻辑每天监视调用的多个实体nodes并收集有关它们的数据。数据如下所示：

table node_snapshots:

id.         node_id.         mined.         mined_amount.           created_at
373.        2.               0.             0.                      2021-06-23 00:00:02
372.        4.               1.             1109851671.             2021-06-23 00:00:02
371.        3.               0.             0.                      2021-06-23 00:00:02
370.        2.               0.             0.                      2021-06-22 00:00:02
369.        4.               0.             0.                      2021-06-22 00:00:02
368.        3.               1.             1109851671.             2021-06-22 00:00:02
368.        2.               0.             0.                      2021-06-21 00:00:02
367.        4.               0.             0.                      2021-06-21 00:00:02
366.        3.               0.             0.                      2021-06-21 00:00:02
365.        2.               1.             1109851671.             2021-06-20 00:00:02
364.        4.               0.             0.                      2021-06-20 00:00:02
363.        3.               0.             0.                      2021-06-20 00:00:02
...

现在我想获得以下信息：

• 每天/每周/每月的节点子集 (fe (1,2,3)) 的所有已开采/开采量的总和是多少？

像这样的东西：

date.                      sum_mined.       sum_mined_amount.
2021-06-30 00:00:02        5.               5549258355.             
2021-05-30 00:00:02        2.               2219703342.
2021-04-30 00:00:02        3.               3329555013.                    
...

我试过了

SELECT  
   date_trunc('month', s.created_at) as date, 
   sum(CAST (s.mined AS DOUBLE PRECISION)) sum_mined, 
   sum(CAST (s.mined_amount AS DOUBLE PRECISION)) sum_mined_amount 
FROM node_snapshots s 
INNER JOIN ( 
   SELECT 
      node_id, 
      max(created_at) maxdate 
   FROM node_snapshots 
   WHERE node_id in (1,2,3,4) 
   GROUP by node_id, date_trunc('month', created_at) 
) g ON 
   g.node_id = s.node_id AND g.maxdate = s.created_at 
GROUP BY date 
ORDER BY date desc 
LIMIT 72;

但这仅适用于每小时的时间范围。

标签： postgresql