Oracle - SELECT DENSE_RANK OVER(ORDER BY、SUM、OVER 和 PARTITION BY) [英] Oracle - SELECT DENSE_RANK OVER (ORDER BY, SUM, OVER and PARTITION BY)

问题描述

我有一个数据库，可以在其中获取批次、缺陷和数量(来自 2 个表).

I have a database where I get lots, defects and quantities (from 2 tables).

稍微更改名称并删除一些我确定对问题不重要的过滤器后，我当前的工作查询如下所示(在 这个答案):

After changing the names slightly and removing some filters which I made sure weren't important for the question, my current working query looks like the following (with the help of this answer):

WITH subquery AS (
  SELECT * FROM (
    SELECT tbl2.lot
    FROM db.tbl1 tbl1, db.tbl2 tbl2
    WHERE tbl2.key = tbl1.key
    GROUP BY tbl2.lot
    ORDER BY Sum(tbl1.qtd) DESC, tbl2.lot
  ) WHERE ROWNUM <= 10
) SELECT tbl2.lot, tbl1.defect, tbl1.desc, Sum(tbl1.qtd)
FROM db.tbl1 tbl1, db.tbl2 tbl2, subquery
WHERE tbl2.lot = subquery.lot
  AND tbl2.key = tbl1.key
GROUP BY tbl2.lot, tbl1.defect, tbl1.desc
ORDER BY Sum(Sum(tbl1.qtd)) OVER (PARTITION BY tbl2.lot) DESC, Sum(tbl1.qtd) DESC, tbl2.lot, tbl1.defect, tbl1.desc

我正在尝试进一步改进查询，我得到了这个解决方案来优化它，这正是我所需要的结合两个答案时出现错误.

I'm trying to improve the query a little more and I got this solution to optimize it which is what I needed but I'm getting an error when combining both answers.

在我看来，解决方案应该是以下查询:

In my head the solution should be the following query:

SELECT *
FROM (
  SELECT DENSE_RANK() OVER (ORDER BY Sum(Sum(tbl1.qtd)) OVER (PARTITION BY tbl2.lot) DESC, Sum(tbl1.qtd) DESC, tbl2.lot, tbl1.defect, tbl1.desc) rnk, tbl2.lot, tbl1.defect, tbl1.desc, Sum(tbl1.qtd)
  FROM db.tbl1 tbl1, db.tbl2 tbl2
  WHERE tbl2.key = tbl1.key
  GROUP BY tbl2.lot, tbl1.defect, tbl1.desc
  ORDER BY Sum(Sum(tbl1.qtd)) OVER (PARTITION BY tbl2.lot) DESC, Sum(tbl1.qtd) DESC, tbl2.lot, tbl1.defect, tbl1.desc
)
WHERE rnk <= 10
ORDER BY rnk

但我收到错误 无法添加表 '('.(已翻译).

But I get the error It was not possible to add the table '('. (translated).

当我删除 SELECT DENSE_RANK() OVER(ORDER BY ...) 来自它运行和工作的查询，除非它没有按照我需要的方式对值进行排序.

When I remove the part Sum(Sum(tbl1.qtd)) OVER (PARTITION BY tbl2.lot) DESC, inside the SELECT DENSE_RANK() OVER(ORDER BY ...) from the query it runs and works except it doesn't order the values the way I need.

我不确定问题是否可能出在另一个OVER 中.我试图通过更换零件来弄清楚会发生什么，但无法找到解决方案.

I'm not sure if the problem might be having an OVER inside another one. I tried to figure out by changing parts and see what would happen but wasn't able to find the solution.

推荐答案

经过多次尝试，我仍然没有弄清楚是否可以修复 DENSE_RANK() 中的顺序OVER 但我确实找到了介于两者之间的解决方案.

After a lot of trying I still haven't figure out if it's possible to fix the order inside the DENSE_RANK()'s OVER but I did found out a solution in between the two.

SELECT lot, def, qtd
FROM (
  SELECT DENSE_RANK() OVER (ORDER BY qtd_lot DESC) rnk, lot, def, qtd
  FROM (
    SELECT tbl2.lot lot, tbl1.def def, Sum(tbl1.qtd) qtd, Sum(Sum(tbl1.qtd)) OVER (PARTITION BY tbl2.lot) qtd_lot
    FROM db.tbl1 tbl1, db.tbl2 tbl2
    WHERE tbl2.key = tbl1.key
    GROUP BY tbl2.lot, tbl1.def
  )
)
WHERE rnk <= 10
ORDER BY rnk, qtd DESC, lot, def

它不如我尝试的解决方案好，但比我以前的工作代码要好.我所做的是将 Sum(Sum(tbl1.qtd)) OVER (PARTITION BY tbl2.lot) 从 DENSE_RANK() 中移出，然后添加名称qtd_lot.

It's not as good as the solution that I was trying but it is better than my previous working code. What I did was move the Sum(Sum(tbl1.qtd)) OVER (PARTITION BY tbl2.lot) out of the DENSE_RANK() and then add it with the name qtd_lot.

这篇关于Oracle - SELECT DENSE_RANK OVER(ORDER BY、SUM、OVER 和 PARTITION BY)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！