Oracle查询各Item最高频Quantity及次数（优先不使用子查询）

假设你的源数据表名为item_quantity，可根据实际表名替换。

无嵌套子查询写法（适配Oracle 12c及以上版本） #

完全不使用子查询，利用Oracle原生MATCH_RECOGNIZE特性实现：

SELECT 
  Item, 
  most_frequent_quantity AS "Most Frequent Quantity", 
  cnt AS "Count"
FROM item_quantity
MATCH_RECOGNIZE (
  PARTITION BY Item
  ORDER BY COUNT(*) DESC
  MEASURES 
    Quantity AS most_frequent_quantity,
    COUNT(*) AS cnt
  ONE ROW PER MATCH
  PATTERN (T)
  DEFINE T AS 1=1
  GROUP BY Item, Quantity
);

兼容低版本写法 #

如果你的Oracle版本低于12c，可使用以下写法，仅含一层逻辑简单的内层分组，性能开销极低：

SELECT
  Item,
  Quantity AS "Most Frequent Quantity",
  cnt AS "Count"
FROM (
  SELECT
    Item,
    Quantity,
    COUNT(*) cnt,
    RANK() OVER(PARTITION BY Item ORDER BY COUNT(*) DESC) rk
  FROM item_quantity
  GROUP BY Item, Quantity
)
WHERE rk = 1;

注：如果某一Item存在多个出现次数并列最高的Quantity，上述写法会返回所有并列项，若仅需要返回其中一个，将RANK()替换为ROW_NUMBER()即可。

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