统计多公司协作项目各年度唯一企业数量（Excel/SQL）

需求说明 #

现有数据集包含Project（项目ID）、Company（企业ID）、start year（项目起始年）、end year（项目结束年）字段，需要实现：

• 对每个年度，统计参与至少有两家企业协作的项目的唯一企业总数
• 只要企业参与的项目在某一年处于存续期（即该年份在项目的start year到end year之间），该企业就需要被计入对应年度的统计

原查询的问题 #

原SQL仅提取了项目的起始年，没有覆盖项目存续的所有年份，导致只有起始年的企业被统计，无法满足需求：

WITH ProjectCompanyCTE AS 
(
    SELECT
        [ID projektu] AS Project_ID,
        [Ičo] AS Company_ID,
        [Rok zahájení] AS Year
    FROM
        dbo.IsvavaiProjects
    WHERE
        [ID projektu] IN (SELECT [ID projektu]
                          FROM dbo.IsvavaiProjects
                          GROUP BY [ID projektu]
                          HAVING COUNT(DISTINCT [Ičo]) >= 2)
)
SELECT
    Year,
    COUNT(DISTINCT Company_ID) AS UniqueCompaniesCount
FROM
    ProjectCompanyCTE
GROUP BY
    Year
ORDER BY
    Year;

修正后的SQL查询 #

要实现需求，需要先生成项目存续的所有年度列表，再关联企业数据，最后统计符合条件的企业：

-- 生成所有需要统计的年度范围（可根据实际数据调整起止年份）
WITH YearsCTE AS (
    SELECT MIN([Rok zahájení]) AS Year
    FROM dbo.IsvavaiProjects
    UNION ALL
    SELECT Year + 1
    FROM YearsCTE
    WHERE Year + 1 <= (SELECT MAX([Rok ukončení]) FROM dbo.IsvavaiProjects)
),
-- 筛选出有至少两家企业参与的项目
MultiCompanyProjects AS (
    SELECT [ID projektu]
    FROM dbo.IsvavaiProjects
    GROUP BY [ID projektu]
    HAVING COUNT(DISTINCT [Ičo]) >= 2
),
-- 关联年度、项目和企业，得到每个项目存续年度对应的参与企业
ProjectYearCompanies AS (
    SELECT
        y.Year,
        p.[Ičo] AS Company_ID
    FROM YearsCTE y
    JOIN dbo.IsvavaiProjects p ON y.Year BETWEEN p.[Rok zahájení] AND p.[Rok ukončení]
    WHERE p.[ID projektu] IN (SELECT [ID projektu] FROM MultiCompanyProjects)
)
-- 按年度统计唯一企业数量
SELECT
    Year,
    COUNT(DISTINCT Company_ID) AS UniqueCompaniesCount
FROM ProjectYearCompanies
GROUP BY Year
ORDER BY Year;

代码说明 #

1. YearsCTE：递归生成从项目最早起始年到最晚结束年的所有年度，确保覆盖所有需要统计的年份
2. MultiCompanyProjects：先筛选出有至少两家企业参与的项目，缩小后续统计范围
3. ProjectYearCompanies：将年度列表与项目企业数据关联，筛选出项目存续年度内的参与企业
4. 最后按年度分组，统计每个年度的唯一企业数量

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