前三名产品的Oracle季度SQL查询

Question

我正在处理Oracle中的一个小项目。我需要得到最畅销的三种产品，以及这一年和四个季度4-6月，7-9月，10-12月和1-3月的总金额。我已经找到了第一部分，我只是需要帮助获得每个产品的4季度总数。希望有人能帮忙，谢谢

以下是到目前为止使用的SQL命令：

select * from (
  select   "FACTQUANTITY"."PRODUCTID" as "PRODUCTID",
           "DIMPRODUCT"."PRODUCTNAME" as "PRODUCTNAME",
           sum(FACTQUANTITY.QUANTITY) as "QUANTITY" 
  from     "FACTQUANTITY" "FACTQUANTITY",
           "DIMPRODUCT" "DIMPRODUCT" 
  where    "DIMPRODUCT"."PRODUCTID"="FACTQUANTITY"."PRODUCTID" 
  group by FACTQUANTITY.PRODUCTID,
           DIMPRODUCT.PRODUCTNAME
  order by sum(FACTQUANTITY.QUANTITY) desc
)
WHERE ROWNUM <= 3; 

​

​

Answer 1

您可以从这里开始：

  select     
     trunc(fact_table.date_column,'Q') as quarter,
    "FACTQUANTITY"."PRODUCTID" ,
    "DIMPRODUCT"."PRODUCTNAME",
     sum(FACTQUANTITY.QUANTITY) as "QUANTITY" 
  from   "FACTQUANTITY" JOIN "DIMPRODUCT" 
       ON "DIMPRODUCT"."PRODUCTID"="FACTQUANTITY"."PRODUCTID" 
  group by 
      FACTQUANTITY.PRODUCTID, DIMPRODUCT.PRODUCTNAME,   trunc(fact_table.date_column,'Q')

    ;

随后，您可以：

with a as (<previous query>)
select * 
from (
  select 
       quarter, 
       productid, 
       productname, 
       quantity,
       row_number() over (partition by quarter order by quantity desc) rnk
  from a
  )
where rnk <= 3;

Answer 2

SELECT *
FROM   (
  SELECT *,
         ROW_NUMBER() OVER ( PARTITION BY quarter
                             ORDER BY quantity DESC, PRODUCTNAME ASC ) AS rn
  FROM   (
    SELECT   TRUNC( f.datetime, 'Q' ) AS quarter,
             f.PRODUCTID,
             d.PRODUCTNAME,
             sum(f.QUANTITY) AS QUANTITY
    FROM     FACTQUANTITY f
             INNER JOIN
             DIMPRODUCT d
             ON ( d.PRODUCTID = f.PRODUCTID )
    GROUP BY TRUNC( f.datetime, 'Q' ),
             FACTQUANTITY.PRODUCTID,
             DIMPRODUCT.PRODUCTNAME
  )
)
WHERE rn <= 3;