获得每个类别的前10名产品(Get top 10 products for every category)

获得每个类别的前10名产品(Get top 10 products for every category)

我有一个这样的查询

SELECT t.category, tc.product, tc.sub-product, count(*) as sales FROM tg t, ttc tc WHERE t.value = tc.value GROUP BY t.category, tc.product, tc.sub-product;

现在在我的查询中，我希望获得每个类别的前10名产品（按销售额排名），对于每个类别，我需要前5个子类别（按销售额排名）

你可以假设问题陈述是这样的：

通过销售获得每个类别的前10名产品，并且每个产品通过销售获得前5名子产品。

这里的类别可以是书籍 产品可以是Harry Porter的书 次产品可以是HarryPorter系列5

示例输入数据格式

category |product |subproduct |Sales [count (*)] abc test1 test11 120 abc test1 test11 100 abc test1 test11 10 abc test1 test11 10 abc test1 test11 10 abc test1 test11 10 abc test1 test12 10 abc test1 test13 8 abc test1 test14 6 abc test1 test15 5 abc test2 test21 80 abc test2 test22 60 abc test3 test31 50 abc test3 test32 40 abc test4 test41 30 abc test4 test42 20 abc test5 test51 10 abc test5 test52 5 abc test6 test61 5 | | | bcd test2 test22 10 xyz test3 test31 5 xyz test3 test32 3 xyz test4 test41 2

输出将是“

top 5 rf for (abc) -> abc,test1(289) abc,test2 (140), abc test3 (90), abc test4(50) , abc test5 (15) top 5 rfm for (abc,test1) -> test11(260),test12(10),test13(8),test14(6),test15(5) and so on

我的查询失败，因为结果真的很大。 我正在阅读有关级别的oracle分析函数。 有人可以帮助我使用分析函数修改此查询。 任何其他方法也可以工作。

我指的是这个http://www.orafaq.com/node/55 。 但无法得到一个正确的SQL查询。

任何帮助将不胜感激..我喜欢坚持2天在这:(

I have a query which is something like this

SELECT t.category, tc.product, tc.sub-product, count(*) as sales FROM tg t, ttc tc WHERE t.value = tc.value GROUP BY t.category, tc.product, tc.sub-product;

Now in my query I want to get top 10 products for every category (top by sales ) and for every category I need top 5 sub category (top by sales)

You can assume the problem statement as something like this :

Get top 10 products for each category by sales and for each product get top 5 sub-products by sales .

Here category can be Books Product can be Harry Porter book sub productcan be HarryPorter series 5

Sample input data format

category |product |subproduct |Sales [count (*)] abc test1 test11 120 abc test1 test11 100 abc test1 test11 10 abc test1 test11 10 abc test1 test11 10 abc test1 test11 10 abc test1 test12 10 abc test1 test13 8 abc test1 test14 6 abc test1 test15 5 abc test2 test21 80 abc test2 test22 60 abc test3 test31 50 abc test3 test32 40 abc test4 test41 30 abc test4 test42 20 abc test5 test51 10 abc test5 test52 5 abc test6 test61 5 | | | bcd test2 test22 10 xyz test3 test31 5 xyz test3 test32 3 xyz test4 test41 2

Output would be "

top 5 rf for (abc) -> abc,test1(289) abc,test2 (140), abc test3 (90), abc test4(50) , abc test5 (15) top 5 rfm for (abc,test1) -> test11(260),test12(10),test13(8),test14(6),test15(5) and so on

My query is failing because results are really huge . I am reading about oracle analytic functions like rank. Can someone help me modifying this query using analytical functions. Any other approach can also work.

I am referring to this http://www.orafaq.com/node/55. But unable to get a right sql query for this.

Any help would be appreciated..I am like stuck for 2 days on this :(

最满意答案

可能有理由不使用分析功能，而是单独使用分析功能：

select am, rf, rfm, rownum_rf2, rownum_rfm from ( -- 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 products where rownum_rf2 <= 10 -- top 10 products order by am, rownum_rf2, rownum_rfm;

我用rownum而不是等级，所以你永远不会得到关系，换句话说，关系会随机决定。 如果数据不够密集（在前10个产品中少于5个子产品 - 它可能会显示其他产品的子产品），这也不起作用。 但是，如果数据密集（建立大型数据库），查询应该可以正常工作。

---

下面进行两次数据传递，但在每种情况下都会返回正确的结果。 再次，这是一个无等级查询。

select am, rf, rfm, count_rf, count_rfm, rownum_rf, rownum_rfm from ( -- 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 5 where rownum_rfm <= 5 order by am, rownum_rf, rownum_rfm;

列：

count_rf : count of sales by product count_rfm : count of sales by subproduct rownum_rf : product rank within category (rownumber - without ties) rownum_rfm : subproduct rank within product (without ties)

There are probably reasons not to use analytical functions, but using analytical functions alone:

select am, rf, rfm, rownum_rf2, rownum_rfm from ( -- 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 products where rownum_rf2 <= 10 -- top 10 products order by am, rownum_rf2, rownum_rfm;

I used rownum instead of rank so you don't ever get ties, or in other words, ties will be randomly decided. This also doesn't work if the data is not dense enough (less than 5 subproducts in any of the top 10 products - it may show subproducts from some other products instead). But if the data is dense (large established database), the query should work fine.

---

The below makes two passes of the data, but returns correct results in each case. Again, this is a rank-without-ties query.

select am, rf, rfm, count_rf, count_rfm, rownum_rf, rownum_rfm from ( -- 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 5 where rownum_rfm <= 5 order by am, rownum_rf, rownum_rfm;

columns:

count_rf : count of sales by product count_rfm : count of sales by subproduct rownum_rf : product rank within category (rownumber - without ties) rownum_rfm : subproduct rank within product (without ties)