使用SQL DB for i的行的最高百分比

I am trying to get a return that gives me the top 80% of the values returned. Using SQL DB for i the TOP clause will not work. I have seen some examples of using Count() in a nested select statement, but I am not sure how that is supposed to fit into the query I have already written. I already have 2 sub-query's so I need to find out how it would fit, or if it would work. Here is what I have so far:

我试图得到一个返回,给我返回的前80%的值。使用SQL DB for i TOP子句将不起作用。我已经看到在嵌套的select语句中使用Count()的一些例子,但我不确定它应该如何适合我已经编写的查询。我已经有2个子查询,所以我需要找出它是如何适合的,或者它是否适用。这是我到目前为止:

Select  CATEGORY,
    LINE,
    ITEM#,
    Units
From    D*****.*****ST
Inner Join  (Select DW******.*****FO.ITEM,
                    Sum (SALES_UNITS) As Units,
                    CATEGORY
            From    DW*******.*****FO
            Inner Join  (Select CATEGORY,
                                DW****.******RY.ITEM
                         From   DW****.******RY
                         Where  CATEGORY='BRAKES') As CA***ST
            On DW*******.*****FO.ITEM=CA*****.***M              
            Where   ("DATE" between current date -1 years and current date) And (SALES > 5.00)
            Group By    DW*******.******O.ITEM,
                        CATEGORY) As Units_List
On      DW****.*****ST.**EM#=U*********.***M
Group By    CATEGORY,
            LINE,
            ITEM#,
            Units
Order By    Units DESC

So somewhere in here would be the nested Count() clause I'm assuming, I'm just not sure where it fits in the grand scheme of things. I'm still learning some of the intermediate SQL stuff, so I'm sorry if the question seems a little simple.

所以这里的某个地方就是我假设的嵌套Count()子句,我只是不确定它在宏观方案中的适用范围。我还在学习一些中级SQL的东西,所以如果问题看起来有点简单,我很抱歉。

3 个解决方案

#1

I would suggest using window functions. I find your query hard to follow, but here is the idea:

我建议使用窗口函数。我发现你的查询难以理解,但这是一个想法:

select t.*
from (select t.*,
             row_number() over (order by units desc) as seqnum,
             count(*) over () as totnum
      from (<view that gets you all the data you want>
           ) t
     ) t
 where seqnum <= 0.8*totnum

The idea is to use window functions to get the total count and also the ranking (I use row_number(), rank() might be more appropriate if you have ties). You can then just use a where clause to get the values you want.

我们的想法是使用窗口函数来获得总计数和排名(如果你有关系,我使用row_number(),rank()可能更合适)。然后,您可以使用where子句来获取所需的值。

#2

Well, first of all, your sub-selects are unnecessary, and I find it easier to read with a simpler join statement. If you collapse your sub-queries, techniques with count(*) will be easier to incorporate.

好吧,首先,你的子选择是不必要的,我发现使用更简单的连接语句更容易阅读。如果您折叠子查询,则使用count(*)的技术将更容易合并。

Next, there is an issue with adding count to an existing query. If you are already using count, then adding a join and counting may mess up both counts, as the join creates a cartesian product, and count and sum may get the wrong answer. You don't have count or sum yet, so you don't have to worry about that pitfall.

接下来,向现有查询添加计数存在问题。如果你已经在使用count,那么添加连接和计数可能会使两个计数都搞乱,因为连接会创建一个笛卡尔积,而count和sum可能会得到错误的答案。你还没有数数或总和,所以你不必担心这个陷阱。

Select  RY.CATEGORY, ST.LINE, ST.ITEM#, FO.Units
From    D*****.*****ST ST
  Inner Join DW*******.*****FO FO On ST.ITEM# = FO.ITEM
  Inner Join DW****.******RY RY On FO.ITEM = RY.ITEM
    And ("DATE" between current date -1 years and current date)
    And (SALES > 5.00)
  Inner Join D*****.*****ST ST_J On ST.LINE = ST_J.LINE And ST.ITEM# = ST_J.ITEM#
  Inner Join DW*******.*****FO FO On ST_J.ITEM# = FO_J.ITEM
Where FO_J.Units >= FO.Units
Group By    RY.CATEGORY, ST.LINE, ST.ITEM#, FO.Units
Having      Count(FO_J.Units) < 0.8 * (Select Count(*)
      From D*****.*****ST ST_J On ST.LINE = ST_J.LINE And ST.ITEM# = ST_J.ITEM#
      Inner Join DW*******.*****FO FO On ST_J.ITEM# = FO_J.ITEM)
Order By    FO.Units DESC

#3

This is general Oracle example and maybe some help to you. I'm not sure which database you are using:

这是一般的Oracle示例,可能对您有所帮助。我不确定您使用的是哪个数据库:

SELECT deptno, ename, sal
     , PERCENT_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS prcnt
  FROM scott.emp
ORDER BY prcnt DESC, sal, ename
/

DEPTNO      ENAME   SAL     PRCNT
--------------------------------
10          MILLER  1300    1
...
20          ADAMS   1100    0.75
30          MARTIN  1250    0.6
...
10          CLARK   2450    0.5
...
30          TURNER  1500    0.4

#1