执业注册会计师统计与分析

前两篇介绍了 duckdb 的安装与导入，阅读量太低，大部分读者都不感兴趣。

今天再用一个实际案例介绍如何进行分析，暂告一个段落。

我们用 scrapy 从“注册会计师行业统一监管平台”获取执业注册会计师信息、会计师事务所信息（数据截止：2023-04-26)。

一共三张表：

数据下载链接：

https://wwds.lanzoum.com/iWf3M0v0s9kj

数据导入

create table cpa as select * from read_csv_auto('/home/nigo/Documents/code/cpa/cpa/user.csv');
create table agent as select * from read_csv_auto('/home/nigo/Documents/code/cpa/cpa/agent.csv');
create table relation as select * from read_csv_auto('/home/nigo/Documents/code/cpa/cpa/fensuo.csv');

选中要执行的语句（多条语句之间用分号分隔），点击“执行”按钮。

将三个文件导入为：cpa,agent,relation 三张表。

下面结合已有数据进行多维度分析（注：SQL 语句大小写不敏感，大写和小写是等同的）

执业CPA人数

select count(distinct cpa_cno) from cpa
where cpa_status!=21

( 注：cpa_status – 10:正常，31:协会代管，21:已注销， 51 ：协会代管）

执行结果：

102039

• SELECT count(distinct cpa_cno): 查询结果是cpa_cno字段的去重数量，使用count()聚合函数统计数量。distinct 关键字表示去重，只统计不同的值。

• FROM cpa: 从 cpa 表中查询数据。

• WHERE cpa_status!=21: 使用 WHERE 子句筛选条件，排除cpa_status等于 21 的记录。!= 表示不等于。

  相关知识点：

• 聚合函数：SQL 中的聚合函数是用来统计和汇总数据的函数，例如 count 、sum、 avg 、max、 min 等。在 SELECT 查询语句中，聚合函数通常和GROUP BY子句一起使用，对分组数据进行聚合计算。

• DISTINCT 关键字：用于去重，只保留不同的数据。

• WHERE 子句：用于在查询中筛选数据，只返回符合条件的数据。可以使用比较运算符（如=、!=、<、>、<=、>=）、逻辑运算符（如 AND 、OR、 NOT ）以及通配符（如%、_）等进行条件筛选。

历年注册及注销人数

历年注销人数

select year(dereg_date) 年度,count(distinct cpa_cno) 注销人数
from cpa
where dereg_date is not null
group by year(dereg_date)
order by year(dereg_date)

相关知识点 ：

• GROUP BY 子句：用于将查询结果按照指定的列进行分组。在 SELECT 查询语句中，GROUP BY 子句通常和聚合函数一起使用，对分组数据进行聚合计算。
• ORDER BY 子句：用于对查询结果进行排序。可以使用 ASC（升序，默认值）或 DESC（降序）关键字指定排序方式。
• year() 函数：用于从日期/时间中提取年份。在 duckdb 中，可以使用year()函数获取日期/时间字段的年份部分。

历年注册人数

select year(regis_date) 年度,count(distinct cpa_cno) 注册人数
from cpa
where year(regis_date)>2000
group by year(regis_date)
order by year(regis_date)

点击表格左上角区域可以选择整个表格，也可以单独选中某一行，某一列，也可以按住 Shift 键选中多行、多列。Ctrl+c 复制数据，可以直接粘贴到 excel 中。

我们用当前注师人数+注销人数 - 注册人数去计算上年的注师人数。不过越往前，数据可能越不准。

我是 2016 年入所的，当时执业注师差不多是 10 万人，非执业和执业总数差不多 20 万。这几年过去了注师还是 10 万人，非执业和执业的总共33.4万人了（ 2023年 4 月23日）。

这几年，都新增多少家上市公司了，注师人数还是不变，可见这行早已经不吸引人了。

尤其是 2021 年注销了21,644人，当年人数负增长，估计是当时清理挂靠的人员。

执业注师地域分布

select division_name 省份,count(distinct cpa_cno) 注师人数 from cpa
where cpa_status!=21
group by division_name
order by count(distinct cpa_cno) desc;

执行结果：

前 7 的北京、上海、浙江、江苏、广东、山东、四川人数就占了51.54% ，除了四川是中西部省份，其余都是沿海省份。

所以，想在事务所找工作的在这些注师人数多的地方相对更容易些。

注销注师执业年限分析

select year(dereg_date) 年度,avg(date_diff('year',regis_date,dereg_date)) 执业年数
from cpa
where dereg_date is not null and regis_date is not null
group by year(dereg_date)
order by year(dereg_date)

相关知识点 ：

• date_diff() 函数：用于计算两个日期/时间之间的差值。在 SQL 中，可以使用date_diff()函数获取两个日期/时间字段之间的差值，常见的计算类型包括 year 、month、 day 、hour、 minute 、second等。
• avg()平均值函数：在 SQL 中，可以使用avg()函数计算指定列的平均值。在本查询语句中，使用avg()函数计算每年执业年数的平均值。

执行结果：

可以看出离开事务所的执业注师的执业年限越来越大，不知道这是否代表几年前，执业注师面临的外部机遇更好，趁着年轻就走了；还是说事务所吸引人才的能力更强了。

注师人数前13大所

select cpaf_name 事务所,division_province 总部所在地,cpa_num 注师人数
from agent
where cpaf_status !=21
order by cpa_num desc limit 13

相关知识点 ：

• LIMIT 子句：用于限制查询结果的返回数量。可以指定返回记录的起始位置和返回记录的数量。在本查询语句中，只返回前 13 条记录。

执行结果：

这也就是我们平常所说的四大、八大吧，可以看出除了天健总部在杭州、中审众环总部在武汉外，其余总部都在北京和上海。

各省老大

我们统计各省注师人数最多的事务所，一般来说这个排名和收入排名差别不大。

select * from agent_province a
where 注师数>=
all(select max(注师数) from agent_province b
where a.省=b.省
)
order by 注师数 desc

这里有个子查询，需要稍微解释下：

WHERE 注师数>= ALL (SELECT max(注师数) FROM agent_province b WHERE a.省 = b.省) :使用 WHERE 子句筛选条件，其中 ALL 关键字指定在子查询中，选择最大注册会计师人数的情况下，所有记录中的注册会计师人数均不小于该最大值。子查询的作用是返回与 a 表中的省份相同的省份中最大的注册会计师人数。

执行结果：

对于毕业生来说，除了去四大、八大外，去当地规模最大的所也是很好的选择。

( 注：agent_province表为计算各个总所名称及其分部在各省的注师人数 )

计算方法如下：

drop table if exists agent_province;
create table agent_province as
with
a AS
(
        select top_name 事务所,division_name 省,'分部' 类别,cpa_num 注师数 from main.relation
        where top_name in
        (
        select cpaf_name
        from agent
        where cpaf_status !=21
        )
),
b as
(
        select cpaf_name 事务所,division_province 省,'总部' 类别,t1.cpa_num-ifnull(t2.cpa_num,0) 注师数
        from agent t1 left join main.agent_fb_cpa t2 on t1.cpaf_name=t2.top_name
        where cpaf_status !=21 order by 注师数 desc
)
select * from a
union all
select * from b

相关知识点:

• drop table if exists agent_province;如果存在表agent_province就删除。
• create table agent_province …. 将后续的查询结果创建为一张新表agent_province。
• with as 是一种常用的查询语法，用于在查询语句中创建临时命名查询，以便于在后续的查询中引用这些查询，从而简化复杂查询语句。
• union all 用于合并两个或多个 SELECT 语句的结果集，并返回一个包含所有结果的单个结果集。UNION ALL 和 UNION 的不同之处在于，UNION 会去重，即只返回不同的行，而UNION ALL不去重，即返回所有行。因此，如果两个 SELECT 语句的结果集中包含相同的行，则UNION ALL会将它们都返回，而 UNION 只返回其中一个。

结语

duckdb 的 SQL 大部份都是标准的 SQL 语句，用起来并不难。

相关的查询语句的介绍可以查看 w3school 的教程：

https://www.w3school.com.cn/sql/index.asp

也可以查看 duckdb 官方文档：

https://duckdb.org/docs

也可以看看以前我写的 mysql 相关的文章，基本上都差不多。

如果后续工作中还有一些值得分享的点，到时候再发文章。

刚入所时执业与非执业的人数是1:1 ，现在是1:2了，执业的比例在不断下降。

与此相对应的是执业人员工作劳累程度在不断上升。

在《注册会计师行业发展规划（ 2021 —2025 年）》中 2035 年远景目标提到：

到2035 年实现注册会计师行业发展水平与我国综合国力和国际地位相匹配，注册会计师行业成为全面领先、具有国际竞争力的高端现代服务业。

希望大家在这一行越来越好。