将PostgreSQL查询转换为Oracle：统计每日修改最多的文件扩展名

问题分析 #

需求为按日期统计每个文件扩展名的修改次数，再筛选出每日修改次数最多的文件扩展名。原PostgreSQL查询中的字符串处理函数需替换为Oracle兼容语法，同时可通过窗口函数优化查询效率。

表结构与测试数据 #

先在Oracle环境中创建表并插入测试数据：

create table files
(
id              int primary key,
date_modified   date,
file_name       varchar(50)
);

insert into files values (1    ,   to_date('2021-06-03','yyyy-mm-dd'), 'thresholds.svg');
insert into files values (2    ,   to_date('2021-06-01','yyyy-mm-dd'), 'redrag.py');
insert into files values (3    ,   to_date('2021-06-03','yyyy-mm-dd'), 'counter.pdf');
insert into files values (4    ,   to_date('2021-06-06','yyyy-mm-dd'), 'reinfusion.py');
insert into files values (5    ,   to_date('2021-06-06','yyyy-mm-dd'), 'tonoplast.docx');
insert into files values (6    ,   to_date('2021-06-01','yyyy-mm-dd'), 'uranian.pptx');
insert into files values (7    ,   to_date('2021-06-03','yyyy-mm-dd'), 'discuss.pdf');
insert into files values (8    ,   to_date('2021-06-06','yyyy-mm-dd'), 'nontheologically.pdf');
insert into files values (9    ,   to_date('2021-06-01','yyyy-mm-dd'), 'skiagrams.py');
insert into files values (10,   to_date('2021-06-04','yyyy-mm-dd'), 'flavors.py');
insert into files values (11,   to_date('2021-06-05','yyyy-mm-dd'), 'nonv.pptx');
insert into files values (12,   to_date('2021-06-01','yyyy-mm-dd'), 'under.pptx');
insert into files values (13,   to_date('2021-06-02','yyyy-mm-dd'), 'demit.csv');
insert into files values (14,   to_date('2021-06-02','yyyy-mm-dd'), 'trailings.pptx');
insert into files values (15,   to_date('2021-06-04','yyyy-mm-dd'), 'asst.py');
insert into files values (16,   to_date('2021-06-03','yyyy-mm-dd'), 'pseudo.pdf');
insert into files values (17,   to_date('2021-06-03','yyyy-mm-dd'), 'unguarded.jpeg');
insert into files values (18,   to_date('2021-06-06','yyyy-mm-dd'), 'suzy.docx');
insert into files values (19,   to_date('2021-06-06','yyyy-mm-dd'), 'anitsplentic.py');
insert into files values (20,   to_date('2021-06-03','yyyy-mm-dd'), 'tallies.py');

转换后的基础Oracle查询 #

替换PostgreSQL字符串函数，调整为Oracle兼容语法：

with cte as
(
    select 
        date_modified,
        SUBSTR(file_name, INSTR(file_name, '.') + 1) as file_ext,
        count(1) as cnt
    from files
    group by date_modified, SUBSTR(file_name, INSTR(file_name, '.') + 1)
)
select 
    date_modified,
    listagg(file_ext, ',' order by file_ext desc) as extension,
    max(cnt) as the_count
from cte c1
where cnt = (select max(cnt) from cte c2 where c1.date_modified = c2.date_modified)
group by date_modified
order by date_modified;

高效优化版（窗口函数实现） #

使用RANK()窗口函数替代关联子查询，减少重复计算，提升查询效率：

with stats as
(
    select 
        date_modified,
        SUBSTR(file_name, INSTR(file_name, '.') + 1) as file_ext,
        count(1) as cnt,
        RANK() over (partition by date_modified order by count(1) desc) as rnk
    from files
    group by date_modified, SUBSTR(file_name, INSTR(file_name, '.') + 1)
),
top_ext as
(
    select date_modified, file_ext, cnt from stats where rnk = 1
)
select 
    date_modified,
    listagg(file_ext, ',' order by file_ext desc) as extension,
    max(cnt) as the_count
from top_ext
group by date_modified
order by date_modified;

关键说明 #

• 字符串处理：Oracle用INSTR替代PostgreSQL的position，SUBSTR替代substring，语法逻辑一致但函数名不同。
• 窗口函数优化：RANK()会给每日相同最高次数的扩展名都标记为1，避免遗漏并列情况；相比原查询的关联子查询，窗口函数只需扫描一次统计结果，效率更高。
• LISTAGG函数：Oracle和PostgreSQL均支持该函数，用于将并列的扩展名拼接成字符串，满足需求。

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