可能有一些原因不使用分析函数，而是 仅 使用分析函数：
select am, rf, rfm, rownum_rf2, rownum_rfmfrom( -- the 3nd level takes the subproduct ranks, and for each equally ranked -- subproduct, it produces the product ranking select am, rf, rfm, rownum_rfm, row_number() over (partition by rownum_rfm order by rownum_rf) rownum_rf2 from ( -- the 2nd level ranks (without ties) the products within -- categories, and subproducts within products simultaneosly select am, rf, rfm, row_number() over (partition by am order by count_rf desc) rownum_rf, row_number() over (partition by am, rf order by count_rfm desc) rownum_rfm from ( -- inner most query counts the records by subproduct -- using regular group-by. at the same time, it uses -- the analytical sum() over to get the counts by product select tg.am, ttc.rf, ttc.rfm, count(*) count_rfm, sum(count(*)) over (partition by tg.am, ttc.rf) count_rf from tg inner join ttc on tg.value = ttc.value group by tg.am, ttc.rf, ttc.rfm ) X ) Y -- at level 3, we drop all but the top 5 subproducts per product where rownum_rfm <= 5 -- top 5 subproducts) Z-- the filter on the final query retains only the top 10 productswhere rownum_rf2 <= 10 -- top 10 productsorder by am, rownum_rf2, rownum_rfm;
我使用rownum而不是等级，因此您永远不会获得联系，换句话说，联系将是随机决定的。如果数据不够密集（前10个产品中的任何5个子产品少于-
可能显示其他产品的子产品），这也将不起作用。但是，如果数据密集（建立的数据库很大），则查询应该可以正常工作。
下面的数据进行了两次传递，但在每种情况下均返回正确的结果。同样，这是一个无联系等级查询。
select am, rf, rfm, count_rf, count_rfm, rownum_rf, rownum_rfmfrom( -- next join the top 10 products to the data again to get -- the subproduct counts select tg.am, tg.rf, ttc.rfm, tg.count_rf, tg.rownum_rf, count(*) count_rfm, ROW_NUMBER() over (partition by tg.am, tg.rf order by 1 desc) rownum_rfm from ( -- first rank all the products select tg.am, tg.value, ttc.rf, count(*) count_rf, ROW_NUMBER() over (order by 1 desc) rownum_rf from tg inner join ttc on tg.value = ttc.value group by tg.am, tg.value, ttc.rf order by count_rf desc ) tg inner join ttc on tg.value = ttc.value and tg.rf = ttc.rf -- filter the inner query for the top 10 products only where rownum_rf <= 10 group by tg.am, tg.rf, ttc.rfm, tg.count_rf, tg.rownum_rf) X-- filter where the subproduct rank is in top 5where rownum_rfm <= 5order by am, rownum_rf, rownum_rfm;
列：
count_rf : count of sales by productcount_rfm : count of sales by subproductrownum_rf : product rank within category (rownumber - without ties)rownum_rfm : subproduct rank within product (without ties)
