时间戳

首页 > 解决方案 > 我有一个查询以时间间隔选择某些数据的总和，我如何也获得这些时间间隔的总数？

sql - 我有一个查询以时间间隔选择某些数据的总和，我如何也获得这些时间间隔的总数？

问题描述

此查询在每个时间间隔产生 3 个总和：

select 
    "interval", 
    SUM("mv"."bought") as "amount_bought", 
    SUM("mv"."sold") as "amount_sold", 
    SUM("mv"."transferred") as "amount_transferred" 
from "mv_24hr_hourly_aggregate_buys_sells_transfers" as "mv" 
inner join (
    select "contract_address" 
    from "addresses" 
    where not exists (
        select 1 
        from "address_tags" 
        where "address" = "addresses"."contract_address" and "tag_id" = ?
    ) 
    order by "supply_percentage" desc
) as "ca" 
on "ca"."contract_address" = "mv"."contract_address" 
group by "interval" 
order by "interval" desc'

我怎样才能在每一行上产生这 3 个总和，也就是没有时间间隔？

电流输出示例：

      interval       | amount_bought       | amount_sold       | amount_transferred 
---------------------+---------------------+-------------------+--------------------------
 2021-05-07 22:00:00 |                   0 |                 0 |                        0
 2021-05-07 21:00:00 |                   0 |                 0 |                        0
 2021-05-07 20:00:00 |                   0 |                 0 |                        0
 2021-05-07 19:00:00 |                   0 |                 0 |                        0
 2021-05-07 18:00:00 |                   0 |                 0 |                        0
 2021-05-07 17:00:00 |                   0 |                 0 |                        0

除了我收集的当前数据外，我还在寻找每行的总购买量、销售总额和转移总额。

谢谢！

标签： sqlpostgresqlsum

解决方案

虽然grouping sets是问题的具体答案，但您的查询应该重写：

select "interval", 
       SUM(mv."bought") as amount_bought, 
       SUM(mv."sold") as amount_sold, 
       SUM(mv."transferred") as amount_transferred 
from "mv_24hr_hourly_aggregate_buys_sells_transfers" mv join
     "addresses" a
     on a."contract_address" = mv."contract_address" 
where not exists (
        select 1 
        from "address_tags" at
        where at."address" = a."contract_address" and at."tag_id" = ?
    ) 
group by grouping sets ( ("interval"), () )
order by "interval" desc nulls last;

笔记：

  • 你真的应该放弃转义的标识符。它们只是使查询更难读写。
  • 不要使用诸如interval标识符之类的 SQL 关键字。
  • 限定所有列引用。您还应该为所有表提供别名以简化这一点。
  • 子查询是不必要的。
  • 子查询中的order by不仅不必要，而且被忽略。

推荐阅读