gpt4 book ai didi

sql - 如何使用单个查询从不同列中获取前 5 个元素?

转载 作者:行者123 更新时间:2023-11-29 13:21:01 25 4
gpt4 key购买 nike

问题

我在 PostgreSQL 9.6 数据库中有下表:

 factorA | factorB | factorC | result 
---------+---------+---------+-------
1 | 5 | 100 | 1
1 | 6 | 200 | 3
2 | 6 | 60 | 6
2 | 5 | 70 | 10

表格当然要大得多,后面还有更多的因素。

现在我有两个问题:

  1. 前 5 个因素 A 值对结果的贡献有多大
  2. 前 5 个因素 B 值对结果的贡献有多大

(factorC 值只是为了表明还有其他列,但是这个问题不感兴趣)

为了回答问题 1,我们看到 sum(value) 是 20 而 sum(value where factorA = 1) 是 4 而 sum(value where factorA = 2)是16。所以我们可以计算出factorA=1贡献了4/20=20%,factorB=2贡献了16/20=80%。同样的原则也适用于因子 B。所以期望的结果是我可以从中读取这些值的东西:

{"factorA": {1: 0.2, 2: 0.8, ...}, "factorB": {5: 0.55, 6: 0.45, ...}}

用 SQL 分别回答这两个问题是没有问题的:

select * from (
select
factorA,
row_number() over (partition by 't' order by sum(result) desc) rank,
sum(result) / sum(sum(result)) over (partition by 't') contribution
from (values(1,5,100,1), (1,6,200,3), (2,6,60,6), (2,5,70,10)) x(factorA, factorB, factorC, result)
group by factorA
) x where rank <= 5

这导致:

 factorA | rank |      contribution
---------+------+-----------------------
2 | 1 | 0.80000000000000000000
1 | 2 | 0.20000000000000000000

用“factorB”替换所有“factorA”将回答第二个问题。然而问题在于,实际上该表不仅包含 4 个值,而且实际上包含数十万个值,但不是很多因素。因此,要运行查询,全表扫描几乎是不可避免的。因此查询会很慢。运行它两次,一次用于 factorA,一次用于 factorB,导致两次表扫描。我想避免这种情况,并用一个查询回答这两个问题。

我尝试过的

到目前为止我最好的尝试是:

select *
from
(
select
x.*,
dense_rank() over (order by contributionA desc) rankA,
dense_rank() over (order by contributionB desc) rankB
from (
select
factorA,
factorB,
sum(sum(result)) over (partition by factorA) / sum(sum(result)) over (partition by 't')::float contributionA,
sum(sum(result)) over (partition by factorB) / sum(sum(result)) over (partition by 't')::float contributionB
from (values(1,5,100,1), (1,6,200,3), (2,6,60,6), (2,5,70,10)) x(factorA, factorB, factorC, result)
group by factorA, factorB
) x
) x
where rankA <= 5 and rankB <= 5

这导致:

 factora | factorb | contributiona | contributionb | ranka | rankb
---------+---------+---------------+---------------+-------+-------
2 | 5 | 0.8 | 0.55 | 1 | 1
1 | 5 | 0.2 | 0.55 | 2 | 1
2 | 6 | 0.8 | 0.45 | 1 | 2
1 | 6 | 0.2 | 0.45 | 2 | 2

这是一个合理的结果,除了“前​​ 5 名”的过滤被打破,因为如果两个贡献实际上相同,那么它们共享相同的排名,因此结果将有 6 个或更多结果。这里的演示是只有“top 1”的查询,但它仍然给出了 top 2:

select *
from
(
select
x.*,
dense_rank() over (order by contributionA desc) rankA,
dense_rank() over (order by contributionB desc) rankB
from (
select
factorA,
factorB,
sum(sum(result)) over (partition by factorA) / sum(sum(result)) over (partition by 't')::float contributionA,
sum(sum(result)) over (partition by factorB) / sum(sum(result)) over (partition by 't')::float contributionB
from (values(1,5,100,1), (1,6,200,1), (2,6,60,1), (2,5,70,1)) x(factorA, factorB, factorC, result)
group by factorA, factorB
) x
) x
where rankA <= 1 and rankB <= 1

最佳答案

我想你想在之后计算排名,而不是之前:

select sum(case when rankA <= 5 then cumeFactorA end) / sum(result::float),
sum(case when rankB <= 5 then cumeFactorB end) / sum(result::float)
from (select x.*,
dense_rank() over (order by factorA desc) as rankA,
dense_rank() over (order by factorB desc) as rankB,
sum(result) over (order by factorA) as cumeFactorA,
sum(result) over (order by factorB) as cumeFactorB,
sum(result) over () as total_result
from (values(1,5,100,1), (1,6,200,1), (2,6,60,1), (2,5,70,1)) x(factorA, factorB, factorC, result)
) x
where rankA <= 5 or rankB <= 5;

关于sql - 如何使用单个查询从不同列中获取前 5 个元素?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/41782647/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com