sql-server - T-SQL - 用于子组分析的 Union All 的更好替代方案

Question

我有一个包含学生信息和学校成绩的数据库。然后，我尝试查询一个结果集，该结果集显示了我们学校不同学生小组的关键绩效指标。最终结果的一个非常粗略的例子是:

我尝试创建的示例表
请注意，学生组有重叠。
同一学生可以包含在全年、女性和非 FSM 等中:

现在我已经实现了这个目标。我对每个子组使用了聚合函数和 GROUP BY 子句，然后使用 UNION ALL 组合这些查询来创建表。我的代码的一个粗略示例是:

SELECT 'Whole Year' AS [Group], COUNT(student.name) AS [Total No. of Students]

UNION ALL

SELECT student.gender AS [Group], COUNT(student.name) AS [Total No. of Students]
GROUP BY student.gender

UNION ALL

SELECT student.fsm AS [Group], COUNT(student.name) AS [Total No. of Students]
GROUP BY student.fsm

问题是，我需要对许多关键绩效指标和许多子组执行此操作。使用我当前的解决方案，我的代码非常庞大，而且也不太易于管理。

我猜可能有一个更优雅的解决方案，所以如果有人能指出我正确的方向，我将非常感激。

Answer 1

首先，您需要将分组创建为行。目前它们是基于列的(它们基于 student 中的列值)。因此，请使用 UNPIVOT 运算符将列转换为行，每个分组都保存属于它的所有学生 ID。

通过将此映射放入 CTE(本质上是一个临时表)，您可以从此连接到 student 并计算每个分组的 KPI:

;
with group_cte (student_group_name, student_id)
AS
(
    select student_group, student_id
    from 
    (select e.student_id as total, 
        case when gender = 'male' then e.student_id end as males, 
        case when gender = 'females' then e.student_id end as females,
        -- your other groupings
    from student e) p
    UNPIVOT
    (student_id for student_group 
        in (total, males, females)) unpvt
)
select student_group_name, count(student_id), -- your KPIs...
from group_cte c
inner join student s
on e.student_id = c.student_id
group by student_group_name

这应该会导致更少的表扫描和更干净的 SQL。