如何用SQL统计跨多文件的名称在最大ID文件及其他文件的出现次数？

测试数据 #

创建测试表及插入数据的SQL如下：

create table tablex (fileID int, file varchar(10), name varchar(10));
insert into tablex (fileID, file, name) values 
(1, 'file1' , 'AAA'),(1, 'file1' , 'AAA'),(1, 'file1' , 'AAA'),
(2, 'file2' , 'AAA'),(2, 'file2' , 'AAA'),(2, 'file2' , 'AAA'),
(1, 'file1' , 'BBB'),(1, 'file1' , 'BBB'),
(2, 'file2' , 'BBB'),(2, 'file2' , 'BBB'),
(3, 'file3' , 'BBB'),(3, 'file3' , 'BBB'),
(1, 'file1' , 'CCC'),(1, 'file1' , 'CCC');

表tablex的数据如下：

需求说明 #

仅筛选出出现在多个不同fileID对应文件中的name，然后统计：

1. 每个name在其所属最大fileID文件中的出现次数
2. 该name在其他fileID文件中的总出现次数

示例结果：

• AAA - 在file2中出现3次，在其他文件中出现3次
• BBB - 在file3中出现2次，在其他文件中出现4次

现有实现 #

我尝试用窗口函数实现，但需要加distinct才能得到正确结果，不确定这是不是最优方案：

select distinct t.name, 
       count(t.name) over (partition by t.name) countAll,
       count(t.name) over (partition by t.name, fileId) countLatestFile,
       count(t.name) over (partition by t.name) - count(t.name) over (partition by t.name, fileId) countOthers
from tablex t
join (select name from tablex group by name having count(distinct fileId)  > 1) tdups
on t.name = tdups.name;

思路1：CTE预统计+关联查询 #

先通过CTE分别统计每个name的总次数、最大fileID，以及各fileID下的出现次数，最后关联计算结果，全程不用窗口函数：

WITH name_stats AS (
    -- 统计每个name的总次数、最大fileID，同时筛选多fileID的name
    SELECT 
        name,
        COUNT(*) AS total_count,
        MAX(fileID) AS max_file_id
    FROM tablex
    GROUP BY name
    HAVING COUNT(DISTINCT fileID) > 1
),
file_name_count AS (
    -- 统计每个name在各fileID下的出现次数
    SELECT 
        name,
        fileID,
        COUNT(*) AS file_count
    FROM tablex
    GROUP BY name, fileID
)
SELECT 
    ns.name,
    fnc.file_count AS max_file_count,
    ns.total_count - fnc.file_count AS other_file_count
FROM name_stats ns
JOIN file_name_count fnc 
    ON ns.name = fnc.name AND ns.max_file_id = fnc.fileID;

思路2：窗口函数标记最大fileID+分组统计 #

用窗口函数给每个name的最大fileID打标记，之后直接分组计算两类次数：

WITH marked_data AS (
    SELECT 
        name,
        -- 标记当前行是否属于该name的最大fileID
        CASE WHEN fileID = MAX(fileID) OVER (PARTITION BY name) THEN 1 ELSE 0 END AS is_max_file
    FROM tablex
),
filtered_names AS (
    -- 筛选出存在多个fileID的name
    SELECT DISTINCT name
    FROM tablex
    GROUP BY name
    HAVING COUNT(DISTINCT fileID) > 1
)
SELECT 
    md.name,
    SUM(CASE WHEN md.is_max_file = 1 THEN 1 ELSE 0 END) AS max_file_count,
    SUM(CASE WHEN md.is_max_file = 0 THEN 1 ELSE 0 END) AS other_file_count
FROM marked_data md
JOIN filtered_names fn ON md.name = fn.name
GROUP BY md.name;

思路3：子查询嵌套实现 #

如果数据库不支持CTE，可以用子查询嵌套完成：

SELECT 
    t1.name,
    (SELECT COUNT(*) FROM tablex t2 WHERE t2.name = t1.name AND t2.fileID = t1.max_file_id) AS max_file_count,
    t1.total_count - (SELECT COUNT(*) FROM tablex t2 WHERE t2.name = t1.name AND t2.fileID = t1.max_file_id) AS other_file_count
FROM (
    SELECT 
        name,
        COUNT(*) AS total_count,
        MAX(fileID) AS max_file_id
    FROM tablex
    GROUP BY name
    HAVING COUNT(DISTINCT fileID) > 1
) t1;

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