gpt4 book ai didi

sql - 将列中的数组与另一个查询的计数结果相乘

转载 作者:行者123 更新时间:2023-11-29 12:02:01 34 4
gpt4 key购买 nike

我是 Postgres 和数据库的新手,所以很抱歉。我运行了一个查询,以从一张表中获取每所学校的学生人数。现在我有下表:

school_probs:

school_code(PK bigint) schoolName(text) probs(numeric)
1 CAA {0.05,0.08,0.18,0.3,0.11,0.28}
2 CAS {0.06,0.1,0.295,0.36,0.12,0.065}
3 CBA {0.05,0.11,0.35,0.32,0.12,0.05}
4 CL {0.07,0.09,0.24,0.4,0.06,0.09}

我将如何将每所学校的计数与概率列中的每个数字相乘。例如:我们学校的学生总数为“CAA” 如果它是 198,那么概率分布将是(0.05*198、0.08*198、0.18*198、0.3*198、0.11*198、0.28*198)。根据结果​​,我可以给学生打分。

我获取计数的查询如下:

SELECT simulated_records.school, COUNT(simulated_records.school) as studentCount INTO CountSchool
FROM simulated_records, school_probs
WHERE simulated_records.school = school_probs.school
GROUP BY simulated_records.school;

最佳答案

要将数组的元素与常量相乘,您需要再次取消嵌套、相乘和聚合。一些注意事项等待。考虑:

最好使用 ARRAY constructor .

也就是说,对您未公开的表格设计做出一些假设,我也会简化计数:

到达:

SELECT *, ARRAY(SELECT unnest(p.probs) * r.student_ct) AS scaled_probs
FROM school_probs p
LEFT JOIN (
SELECT school, COUNT(*)::int AS student_ct
FROM simulated_records
GROUP BY 1
) r USING (school);

或者,将 NULL 数组表示为 NULL 数组:

SELECT *
FROM school_probs p
LEFT JOIN (
SELECT school, COUNT(*)::int AS student_ct
FROM simulated_records
GROUP BY 1
) r USING (school)
LEFT JOIN LATERAL (
SELECT ARRAY(SELECT unnest(p.probs) * r.student_ct) AS scaled_probs
) p1 ON p.probs IS NOT NULL;

db<> fiddle here

我建议仅针对 Postgres 10 或更高版本在 SELECT 列表中使用设置返回函数的简单形式,因为:

关于sql - 将列中的数组与另一个查询的计数结果相乘,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/53146468/

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