銷售產品的事實表 - oracle

我有銷售事實表（有數量 - 價格度量）和維度（產品 - 客戶 - 時間）我的表模式： 我需要一個查詢來獲得最暢銷的產品；然後像這個例子一樣顯示每年和每月購買該產品的金額列表

Year    Month        AmountOfMoney
====    =====        =============
-       -            14040.00
1988    -            130.00
1998    March        130.00

我不使用 Oracle，但使用 STD 語法它應該可以工作：

/* aggregated sales by year-month */
SELECT
   T.CALENDAR_YEAR AS Year,
   T.CALENDAR_MONTH_DESC AS Month,
   SUM(S.AMOUNT_SOLD) AS AmountOfMoney
FROM
   SALES S
JOIN
   TIMES T
   ON T.TIME_ID = S.TIME_ID
WHERE S.PROD_ID = (SELECT PROD_ID
                  FROM (
                        SELECT 
                           S.PROD_ID,
                           SUM(S.AMOUNT_SOLD) AMOUNT
                        FROM
                           SALES S
                        GROUP BY S.PROD_ID
                        ORDER BY SUM(S.AMOUNT_SOLD) DESC
                       ) BS
                  WHERE ROWNUM = 1
                 )  
GROUP BY
   T.CALENDAR_YEAR,
   T.CALENDAR_MONTH_DESC
ORDER BY
   T.CALENDAR_YEAR,
   T.CALENDAR_MONTH_DESC;

db<>在這裡擺弄

引用自：https://dba.stackexchange.com/questions/225628