SQL与Pareto Frontier

Question

表位于照片中

问:太阳能和风能往往是互补的，因为当云层很少，太阳最好能到达太阳能电池板时，风力就会减弱。我们在这部分的目标是找出在太阳能和风能生产之间取得最佳平衡的状态。在这里，我们将状态定义为“最好的”，如果它存在于太阳能和风能生产的帕累托前沿。换句话说，如果没有其他州同时产生更多的太阳能和更多的风能，那么状态就是帕累托最优状态，而帕累托边界是帕累托最优状态的集合。编写一个返回整个Pareto边界的查询。结果应该是形态(状态、太阳能、风能)的三倍，状态是边疆州的名称，太阳能和风能是MWh的太阳能和风能生产。将MWh中太阳能和风能生产总量之和的结果按降序排序。

请向我解释pareto frontier以及我如何在sql中应用它。

我只知道怎么做：

SELECT state, solar, wind 
FROM energy 
WHERE solar+wind = (SELECT max(solar+wind) 
                    FROM energy); 

这并不能给出完整的结果

​

Answer 1

点a属于Pareto边界当且仅当没有其他点b

b.wind >= a.wind AND b.solar >= a.solar AND ... 
AND (b.wind > a.wind OR b.solar > a.solar OR ...)

这些条件加在一起可以改写为：

b.wind >= a.wind AND b.solar >= a.solar AND ... 
    AND (b.wind + b.solar + ... > a.wind + a.solar + ...)

因此，SQL是(根据您的需要更改名称)：

SELECT * FROM states a 
    WHERE NOT EXISTS (
        SELECT * FROM states b 
        WHERE b.wind >= a.wind AND b.solar >= a.solar 
        AND b.wind + b.solar > a.wind + a.solar
    )

我非常肯定，从这一点上来说，订购是很简单的。

下面是一个小例子：

create table states (stateid int, wind double, solar double, primary key (stateid))

truncate table states;

insert into states values (1, 100, 30), (2, 120, 50), (3, 150, 30), (4, 100, 60), (5, 150, 40);

select * from states a 
where not exists (
    select * from states b 
    where b.wind >= a.wind and b.solar >= a.solar and b.wind + b.solar > a.wind + a.solar
)

Answer 2

看起来很容易，在这种情况下，我们可以使用秩()函数来查找结果。我们也许可以像您一样使用MAX()函数，但会使查询变得非常缓慢和复杂。检查此查询，它将使您的生活变得轻松。

SELECT state, solar, wind, RANK() OVER(partition by state order by (solar+wind) desc) AS rank
FROM energy