postgresql 使用SQL从给定数据集中查找最高能耗

c9x0cxw0 于 2023-11-18 发布在 PostgreSQL

关注(0)|答案(3)|浏览(126)

查找Meta/Facebook数据中心总能耗最高的日期。输出日期沿着所有数据中心的总能耗
这是3个给定的数据集

fb_eu_energy
date    consumption
2020-01-01  400
2020-01-02  350
2020-01-03  500
2020-01-04  500
2020-01-07  600

fb_asia_energy
2020-01-01  400
2020-01-02  400
2020-01-04  675
2020-01-05  1200
2020-01-06  750
2020-01-07  400

fb_na_energy
date    consumption
2020-01-01  250
2020-01-02  375
2020-01-03  600
2020-01-06  500
2020-01-07  250

我的解决方案

select date, sum(consumption) as total_consumption
from (select * from fb_eu_energy
union all
select * from fb_asia_energy
union all
select * from fb_na_energy
) as output
Group by output.date
order by total_consumption desc
limit 2

型
我有这个当前的解决方案，但我觉得如果使用限制2是作弊有点，并不会为其他数据集工作。任何解决方案，以解决这个问题，使用max函数，而不必改变我的答案很大？

postgresql

来源：https://stackoverflow.com/questions/71683654/using-sql-to-find-the-highest-energy-consumption-from-given-dataset

3条答案

按热度按时间

i2loujxw1#

从PostgreSQL 13开始，你可以使用标准的SQL子句FETCH FIRST ROW WITH TIES来获取最大能源消耗的日期：

select date, sum(consumption) as total_consumption
from 
(
  select * from fb_eu_energy
  union all
  select * from fb_asia_energy
  union all
  select * from fb_na_energy
) as output
group by output.date
order by total_consumption desc
fetch first row with ties;

字符串

赞(0）回复(0）举报 2023-11-18

bgtovc5b2#

使用dense_rank（）函数：

with cte as
(
select date, sum(consumption) as total, 
      dense_rank() over (order by sum(consumption) desc) as rn 
   from
        (select * from fb_eu_energy  
        union all 
        select * from fb_asia_energy 
        union all 
        select * from fb_na_energy) as src
        group by date
        )
select date, total from cte where rn = 1

字符串

赞(0）回复(0）举报 2023-11-18

jyztefdp3#

Select date, C
From (
Select  date,  SUM(consumption)C
From(
select date, consumption from fb_eu_energy
Union All
select date, consumption from fb_asia_energy
Union All
select date, consumption from fb_na_energy
)T  Group By Date 
) t1
where t1.c=(Select  top 1  SUM(consumption)C
            From(
            select date, consumption from fb_eu_energy
            Union All
            select date, consumption from fb_asia_energy
            Union All
            select date, consumption from fb_na_energy
            )T  Group By Date 
            Order By SUM(consumption) desc)

字符串

赞(0）回复(0）举报 2023-11-18

我来回答

postgresql 使用SQL从给定数据集中查找最高能耗

3条答案

相关问题

热门标签

最新问答