Oracle 21C中如何在Pivot子句中使用Count统计出勤

我正在使用Oracle 21C，查询涉及三个表，建表及插入数据的SQL如下：

Create Table vol_att
( prim_key integer NOT NULL,
 event_fkey integer,
 contact_fkey integer,
 attendance_type varchar2(256),
 CONSTRAINT vol_att_pk PRIMARY KEY (prim_key)
);
Insert All
   Into vol_att(prim_key, event_fkey, contact_fkey, attendance_type)
   Values(1, 1, 801, 'I')
   Into vol_att(prim_key, event_fkey, contact_fkey, attendance_type)
   Values(2, 1, 234, 'Z')
   Into vol_att(prim_key, event_fkey, contact_fkey, attendance_type)
   Values(3, 2, 258, 'I')
   Into vol_att(prim_key, event_fkey, contact_fkey, attendance_type)
   Values(4, 2, 234, 'Z')
   Select 1 from DUAL;
   
Create Table vol_con
( prim_key integer NOT NULL,
 last_name varchar2(256),
 first_name varchar2(256),
 CONSTRAINT vol_con_pk PRIMARY KEY (prim_key)
);
Insert All
   Into vol_con(prim_key, last_name, first_name)
   Values(234, 'Potter', 'Harry')
   Into vol_con(prim_key, last_name, first_name)
   Values(258, 'Weasley', 'Ron')
   Into vol_con(prim_key, last_name, first_name)
   Values(801, 'Granger', 'Hermione')
   Into vol_con(prim_key, last_name, first_name)
   Values(500, 'Malfoy', 'Draco')
   Select 1 from DUAL;
   
CREATE TABLE vol_evn
( prim_key integer NOT NULL,
 event_date date,
 CONSTRAINT vol_evn_pk PRIMARY KEY (prim_key)
);
Insert All
   Into vol_evn(prim_key, event_date)
   Values(1, to_date('30-mar-2023', 'dd-mon-yyyy'))
   Into vol_evn(prim_key, event_date)
   Values(2, to_date('18-apr-2023', 'dd-mon-yyyy'))
   Select 1 from DUAL;

以下代码用于生成用户出勤明细报表：

select
    *
from
    (select
        vc.last_name
      , va.attendance_type
      , ve.event_date
    from
        vol_att va
            full outer join vol_evn ve
              on va.event_fkey = ve.prim_key
            full outer join vol_con vc
              on va.contact_fkey = vc.prim_key
    )
pivot (
    max(attendance_type) for event_date in 
        (to_date('2023-03-30', 'yyyy-mm-dd') Mar_30_2023, 
         to_date('2023-04-18', 'yyyy-mm-dd') Apr_18_2023
        )
      )
order by
    last_name;

该查询的输出结果如下：

需求 #

现在需要生成一份统计非空出勤记录的汇总报表，格式如下：

修改方案 #

通过调整透视查询的统计逻辑和数据源关联方式，可以实现需求：

1. 移除子查询中的vc.last_name，无需按用户维度统计
2. 将透视函数中的max(attendance_type)替换为count(attendance_type)，自动过滤空值并统计有效出勤数
3. 使用inner join关联vol_att和vol_evn（仅保留有出勤记录的关联数据）
4. 用'' as ""填充第一列空值，匹配目标报表格式

修改后的SQL代码：

select
    '' as ""
from
    (select
        ve.event_date
      , va.attendance_type
    from
        vol_att va
        inner join vol_evn ve 
          on va.event_fkey = ve.prim_key
    )
pivot (
    count(attendance_type) for event_date in 
        (to_date('2023-03-30', 'yyyy-mm-dd') Mar_30_2023, 
         to_date('2023-04-18', 'yyyy-mm-dd') Apr_18_2023
        )
      );

该查询会直接输出目标格式的汇总报表，统计每个日期的非空出勤记录数量。

内容的提问来源于stack exchange，提问作者user3138025