时间戳

首页 > 解决方案 > 将列中的数组与另一个查询的计数结果相乘

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

问题描述

我是 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}

我将如何将每所学校的计数与 probs 列中的每个数字相乘。例如：我们有学校“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;

标签： sqlarrayspostgresqljoinaggregate-functions

解决方案

要将数组的元素与常数相乘，您需要再次取消嵌套、相乘和聚合。一些警告等待着。考虑：

最好使用ARRAY 构造函数。

也就是说，并对您未公开的表格设计做出一些假设，我还将简化计数：

到达：

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<>在这里摆弄

我建议这种简单的形式，在 SELECT 列表中仅针对 Postgres 10 或更高版本使用 set-returning 函数，因为：

推荐阅读