甲骨文-SELECT DENSE_RANK OVER（ORDER BY，SUM，OVER和PARTITION BY）

Question

我有一个数据库，可以从中得到很多，缺陷和数量（来自2个表）。

在略微更改了名称并删除了一些我确定对该问题并不重要的过滤器之后，我当前的工作查询如下所示（在此答案的帮助下）：

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

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

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

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

但是我得到了错误It was not possible to add the table '('.（翻译）。

当我从查询中删除其中的零件时Sum(Sum(tbl1.qtd)) OVER (PARTITION BY tbl2.lot) DESC,，SELECT DENSE_RANK() OVER(ORDER BY ...)它会运行并起作用，只是它不会按照我需要的方式对值进行排序。

我不确定问题是否可能OVER在另一个内部。我试图通过更换零件来解决问题，然后看看会发生什么，但找不到解决方案。

Answer 1

经过大量的努力我还没有弄清楚是否有可能解决内部的顺序DENSE_RANK()的OVER，但我却发现了一个解决方案在两者之间。

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。