将同一列的不同值聚合到不同列中的窗口函数/透视方式?

Question

我有一个表的层次结构：

• GrandParentFoo，具有零个或更多
• ParentFoo，具有零或更多
• ChildFoo

ParentFoo和ChildFoo都有一个Status列，共有 4 种可能的状态：

• 待定 (1)
• 活动 (2)
• 已暂停 (3)
• 完整 (4)

我正在尝试编写一个查询，该查询为我提供了以下行的任何特定GrandParentFoo的汇总：

• 祖父母
• 总父项
• 待定父级总数
• 总父项活跃
• 已暂停的父项总数
• 完成的父项总数
• 儿童福利总数
• 待定儿童福利总数
• 活跃儿童福利总数
• 已暂停的儿童福利总数
• 完成的儿童福利总数

我开始走以下道路：

select
gp.GrandParentFooId,
count(distinct pf.ParentFooId) as TotalParentFoos,
sum(case pf.Status 
when 1 then 1
else 0 end) as TotalParentFoosPending

。当我意识到这会给我一个夸大的计数时，ParentFoo记录上存在多个ChildFoo记录。

我是否必须将其写成一系列 CTE，或者是否有更清洁、更简单的方法可以做到这一点？ 似乎某种透视或窗口函数可以在这里工作，但我无法概念化它。

Answer 1

一种相对简单的方法使用条件聚合与count(distinct)：

select gp.GrandParentFooId,
count(distinct pf.ParentFooId) as TotalParentFoos,
count(distinct case when fp.status = 1 then pf.ParentFooId end) as parent_pending,
count(distinct case when fp.status = 2 then pf.ParentFooId end) as parent_active,
count(distinct case when fp.status = 3 then pf.ParentFooId end) as parent_paused,
count(distinct case when fp.status = 4 then pf.ParentFooId end) as parent_completed,
count(distinct c.ChildId) as num_children,
count(distinct case when fp.status = 1 then c.ChildId end) as child_pending,
count(distinct case when fp.status = 2 then c.ChildId end) as child_active,
count(distinct case when fp.status = 3 then c.ChildId end) as child_paused,
count(distinct case when fp.status = 4 then c.ChildId end) as child_completed
from grandparentfoo gp left join
parentfoo p
on gp.GrandParentFooId = p.GrandParentFooId left join
childfoo c
on p.ParentFooId = c.ParentFooId;

笔记：

• 孩子们可能不需要COUNT(DISTINCT)。COUNT(c.ChildId)可能就足够了。
• 对于较大的数据，我建议使用更复杂的查询以避免COUNT(DISTINCT)。