获得每个类别的十大产品

Question

我有这样的查询

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

我的查询失败，因为结果确实很大。我正在阅读有关诸如rank之类的oracle分析功能的信息。有人可以帮我使用解析函数修改此查询。任何其他方法也可以起作用。

我指的是http://www.orafaq.com/node/55。但是无法为此获得正确的sql查询。

任何帮助，将不胜感激..我想在此停留两天:(

Answer 1

可能有一些原因不使用分析函数，而是 仅 使用分析函数：

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)