logo标签列表

每个类别的前十产品

sqlmysqloraclerank
5

我有一个类似于这样的查询

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个子产品。
这里的类别可以是书籍，产品可以是《哈利波特》书，子产品可以是哈利波特系列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 分析函数，例如 rank。有人能帮我使用分析函数修改这个查询吗？其他方法也可以。
我参考了 http://www.orafaq.com/node/55 ，但是无法得到正确的 SQL 查询语句。
任何帮助都将不胜感激... 我已经困在这里两天了 :(
1
你是在寻找MySql还是Oracle的解决方案？ - The Scrum Meister
1
@TopCoder：Oracle和MySQL具有不同的SQL语法。 - BoltClock
好的，既然如此我正在寻找 Oracle 查询。 - TopCoder
1
如果您发布了表的“创建表”语句以及一些示例数据，其他人可能更能够帮助您。我从来不明白为什么有些 SQL 问题会让某人费尽心思地描述他拥有的和想要的内容，而不使用 DDL 和 DML 来准确描述。 - René Nyffenegger
抱歉，但我尝试更好地提出问题。现在有人可以看一下并告诉我是否有任何不清楚的地方吗？ - TopCoder
显示剩余7条评论
2个回答
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而不是rank，这样你就永远不会得到并列的结果，或者换句话说，结果将会随机决定。如果数据不够密集（任何前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)
rowmun_rf2 和 rownum_rfm 对我返回什么？您能为此添加一点说明吗？ - TopCoder
这对我不起作用。你能验证一下吗？我无法追踪它。 - TopCoder
0

这只是猜测，但你可以从类似以下的东西开始：

drop table category_sales;

一些测试数据：

create table category_sales (
   category    varchar2(14),
   product     varchar2(14),
   subproduct  varchar2(14),
   sales       number
);

begin

  for cate in 1 .. 10 loop
  for prod in 1 .. 20 loop
  for subp in 1 .. 30 loop

      insert into category_sales values (
             'Cat '  || cate,
             'Prod ' || cate||prod,
             'Subp ' || cate||prod||subp,
              trunc(dbms_random.value(1,30 + cate - prod + subp))
      );

  end loop; end loop; end loop;

end;
/

实际查询：

select * from (
  select 
    category,
    product,
    subproduct,
    sales,
    category_sales,
    product_sales,
    top_subproduct,
    -- Finding best products within category:
    dense_rank () over (
      partition by category
      order     by product_sales desc
    ) top_product 
  from (
    select 
      -- Finding the best Subproducts within
      -- category and product:
      dense_rank () over (
         partition by category, 
                      product 
         order     by sales desc
      )                             top_subproduct,
      -- Finding the sum(sales) within a 
      -- category and prodcut
      sum(sales) over (
         partition by category, 
                      product
      )                             product_sales,
      -- Finding the sum(sales) within 
      -- category
      sum(sales) over (
         partition by category
      )                             category_sales,
      category,
      product,
      subproduct,
      sales
    from
      category_sales
  )
)
where 
--    Only best 10 Products
      top_product       <= 10 and
--    Only best 5 subproducts:
      top_subproduct    <= 5
-- "Best" categories first:
order by 
      category_sales desc,
      top_product    desc,
      top_subproduct desc;

在该查询中，列category_sales返回其记录中类别的销售总额。这意味着，相同类别的每个记录都具有相同的category_sales。需要使用此列将结果集按最佳（销售）类别排序（order by ... category_sales desc）。
同样，product_sales是类别-产品组合的销售总额。使用此列查找每个类别中最佳的n（这里是10）个产品（where top_product <= 10）。
列top_product使用dense_rank() over...分析函数“创建”。对于类别中的最佳产品，它为1，对于第二好的产品，它为2，依此类推（因此where top_product <= 10）。
列top_suproduct的计算方式与top_product类似（即使用dense_rank）。
我正在数据仓库上运行此查询，因此没有创建新表的选项。 - TopCoder
这个表格显然不是用来创建的！它应该给你一个进一步操作的想法。 - René Nyffenegger
明白了！抱歉我很无知。 - TopCoder
这三个值 category_sales、product_sales和top_subproduct分别返回什么？ - TopCoder
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接