哪些数据库厂商支持聚合型Rank()函数？求同行技术解答

Great question—this is a nuance that trips up a lot of folks moving between different database platforms. Here's what I've learned from hands-on work and vendor documentation deep dives:

• Oracle stands alone (among mainstream RDBMS) when it comes to supporting both the analytical and aggregate flavors of RANK(). Its aggregate implementation lets you calculate rankings directly within a grouped aggregation context, no window function syntax required.
• Virtually every other major RDBMS (MySQL, Snowflake, PostgreSQL, SQL Server, etc.) only supports the analytical (window function) version of RANK(). You'll always need to pair it with an OVER() clause to define partitioning and ordering—you can't drop it into a GROUP BY query as a standalone aggregate.

Common Workarounds for Aggregate-Style Ranking #

If you need to replicate Oracle's aggregate RANK() behavior in other databases, these are the go-to approaches:

• Nested window + aggregate: First compute row-level rankings with RANK() OVER(...), then wrap that in an outer query to group and aggregate the results as needed.
• Self-join with count aggregation: Use a self-join to count how many rows in a group have values greater than (or less than) the current row's value, then derive a ranking from that count.
• Custom aggregate functions (limited use cases): Some databases like PostgreSQL let you build custom aggregates to mimic this logic, but this requires database-specific development work and isn't portable.

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