需要前 10 名产品的帮助（按金额）- 每周

Question

我已经创建了新表并将示例数据插入到表中。我想每周获得按金额排名前 10 名的产品。我尝试了 SQL 查询，但给出了错误的数据。我在下面分享了表格、示例数据和 SQL 查询。

我有一个表：

CREATE TABLE product_table (
product_name          VARCHAR2(20),
amount                NUMBER,
datetime DATE
)

示例数据：

INSERT INTO product_table (product_name, amount, datetime)
SELECT 'P1', 10000, TRUNC(SYSDATE, 'IW') FROM DUAL UNION ALL
SELECT 'P2', 15000, TRUNC(SYSDATE, 'IW') FROM DUAL UNION ALL
SELECT 'P3', 18000, TRUNC(SYSDATE, 'IW') FROM DUAL UNION ALL
SELECT 'P4', 11000, TRUNC(SYSDATE, 'IW') FROM DUAL UNION ALL
SELECT 'P5', 13000, TRUNC(SYSDATE, 'IW') FROM DUAL UNION ALL
SELECT 'P6', 16000, TRUNC(SYSDATE, 'IW') FROM DUAL UNION ALL
SELECT 'P7', 19000, TRUNC(SYSDATE, 'IW') FROM DUAL UNION ALL
SELECT 'P8', 20000, TRUNC(SYSDATE, 'IW') FROM DUAL UNION ALL
SELECT 'P9', 24000, TRUNC(SYSDATE, 'IW') FROM DUAL UNION ALL
SELECT 'P10',26000, TRUNC(SYSDATE, 'IW') FROM DUAL UNION ALL
SELECT 'P11',34000, TRUNC(SYSDATE, 'IW') FROM DUAL UNION ALL
SELECT 'P1', 8000, TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL UNION ALL
SELECT 'P2', 17000, TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL UNION ALL
SELECT 'P3', 22000, TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL UNION ALL
SELECT 'P4', 23000, TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL UNION ALL
SELECT 'P5', 26000, TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL UNION ALL
SELECT 'P6', 34000, TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL UNION ALL
SELECT 'P7', 31000, TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL UNION ALL
SELECT 'P8', 42000, TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL UNION ALL
SELECT 'P9', 54000, TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL UNION ALL
SELECT 'P10', 14000,  TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL UNION ALL
SELECT 'P11', 19000, TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL UNION ALL
SELECT 'P1', 8000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -1) FROM DUAL UNION ALL
SELECT 'P2', 16000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -1) FROM DUAL UNION ALL
SELECT 'P3', 21000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -1) FROM DUAL UNION ALL
SELECT 'P4', 22000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -1) FROM DUAL UNION ALL
SELECT 'P5', 25000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -1) FROM DUAL UNION ALL
SELECT 'P6', 33000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -1) FROM DUAL UNION ALL
SELECT 'P7', 32000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -1) FROM DUAL UNION ALL
SELECT 'P8', 41000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -1) FROM DUAL UNION ALL
SELECT 'P9', 53000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -1) FROM DUAL UNION ALL
SELECT 'P10', 24000,  ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -1) FROM DUAL UNION ALL
SELECT 'P11', 29000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -1) FROM DUAL UNION ALL
SELECT 'P1', 7000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -2) FROM DUAL UNION ALL
SELECT 'P2', 15000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -2) FROM DUAL UNION ALL
SELECT 'P3', 20000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -2) FROM DUAL UNION ALL
SELECT 'P4', 21000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -2) FROM DUAL UNION ALL
SELECT 'P5', 24000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -2) FROM DUAL UNION ALL
SELECT 'P6', 32000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -2) FROM DUAL UNION ALL
SELECT 'P7', 31000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -2) FROM DUAL UNION ALL
SELECT 'P8', 40000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -2) FROM DUAL UNION ALL
SELECT 'P9', 52000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -2) FROM DUAL UNION ALL
SELECT 'P10', 34000,  ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -2) FROM DUAL UNION ALL
SELECT 'P11', 39000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -2) FROM DUAL UNION ALL
SELECT 'P1', 6000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -3) FROM DUAL UNION ALL
SELECT 'P2', 14000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -3) FROM DUAL UNION ALL
SELECT 'P3', 19000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -3) FROM DUAL UNION ALL
SELECT 'P4', 20000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -3) FROM DUAL UNION ALL
SELECT 'P5', 23000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -3) FROM DUAL UNION ALL
SELECT 'P6', 31000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -3) FROM DUAL UNION ALL
SELECT 'P7', 30000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -3) FROM DUAL UNION ALL
SELECT 'P8', 39000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -3) FROM DUAL UNION ALL
SELECT 'P9', 51000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -3) FROM DUAL UNION ALL
SELECT 'P10', 54000,  ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -3) FROM DUAL UNION ALL
SELECT 'P11', 69000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -3) FROM DUAL;

查询：

SELECT 
   RANK() OVER (
     ORDER BY SUM(
       CASE
       WHEN datetime >= TRUNC(SYSDATE, 'IW')
       AND  datetime <  TRUNC(SYSDATE, 'IW') + INTERVAL '7' DAY
       THEN 1
       END
     ) DESC
   ) AS rank_this_week,
   product_name,
   SUM(AMOUNT) TOTAL_AMOUNT
FROM PRODUCT_TABLE
GROUP BY product_name, amount
ORDER BY AMOUNT DESC
FETCH FIRST 10 ROWS ONLY;

结果：

此查询未显示正确的前 10 名产品（按金额）- 每周排名

db<>fiddle

需要以下格式的最终​​输出结果：

Answer 1

你还没有说出你的预期输出是什么；但是，您似乎想要按每周金额订购，因此您需要按 rank_this_week 列订购，而不是在 GROUP BY 中包含 amount子句：

SELECT RANK() OVER (
         ORDER BY SUM(
           CASE
           WHEN datetime >= TRUNC(SYSDATE, 'IW')
           AND  datetime <  TRUNC(SYSDATE, 'IW') + INTERVAL '7' DAY
           THEN amount
           END
         ) DESC
       ) AS rank_this_week,
       product_name,
       SUM(
         CASE
         WHEN datetime >= TRUNC(SYSDATE, 'IW')
         AND  datetime <  TRUNC(SYSDATE, 'IW') + INTERVAL '7' DAY
         THEN amount
         END
       ) AS total_amount_this_week,
       SUM(AMOUNT) TOTAL_AMOUNT
FROM PRODUCT_TABLE
GROUP BY product_name
ORDER BY rank_this_week
FETCH FIRST 10 ROWS ONLY;

对于样本数据，输出：

<表类=“s-表”>
<标题>

RANK_THIS_WEEK
PRODUCT_NAME
TOTAL_AMOUNT_THIS_WEEK
TOTAL_AMOUNT

<正文>

1
P11
34000
190000

2
P10
26000
152000

3
P9
24000
234000

4
P8
20000
182000

5
P7
19000
143000

6
P3
18000
100000

7
P6
16000
146000

8
P2
15000
77000

9
P5
13000
111000

10
P4
11000
97000

db>>fiddle 此处

Answer 2

在您发表评论并显示所需结果后，这里有一个选项。阅读代码中的注释。

SQL> with
  2  -- Each DATES CTE returns desired "date" value
  3  dates_tw  as (select to_char(sysdate, 'yyyy iw') this_week                 from dual),
  4  dates_pw  as (select to_char(sysdate - 7, 'yyyy iw') previous_week         from dual),
  5  dates_lm  as (select trunc(add_months(sysdate, -1), 'mm') last_month       from dual),
  6  dates_ma2 as (select trunc(add_months(sysdate, -2), 'mm') two_months_ago   from dual),
  7  dates_ma3 as (select trunc(add_months(sysdate, -3), 'mm') three_months_ago from dual),

---

  8  sums as
  9  -- compute total amount for reach "date" value from DATES CTEs, hence CROSS JOIN.
 10  -- As DATES CTEs contain a single row, that shouldn't be too bad (performance wise)
 11    (select p.product_name,
 12        sum(case when to_char(p.datetime, 'yyyy iw') = tw.this_week         then amount else 0 end) sum_tw,
 13        sum(case when to_char(p.datetime, 'yyyy iw') = pw.previous_week     then amount else 0 end) sum_pw,
 14        sum(case when trunc(p.datetime, 'mm')        = lm.last_month        then amount else 0 end) sum_lm,
 15        sum(case when trunc(p.datetime, 'mm')        = ma2.two_months_ago   then amount else 0 end) sum_ma2,
 16        sum(case when trunc(p.datetime, 'mm')        = ma3.three_months_ago then amount else 0 end) sum_ma3
 17      from product_table p cross join dates_tw tw
 18                           cross join dates_pw pw
 19                           cross join dates_lm lm
 20                           cross join dates_ma2 ma2
 21                           cross join dates_ma3 ma3
 22      group by product_name
 23    ),

---

 24  ranks as
 25  -- rank products per each total amount
 26    (select s.product_name,
 27       rank() over (order by sum_tw desc) rnk_tw,
 28       rank() over (order by sum_pw desc) rnk_pw,
 29       rank() over (order by sum_lm desc) rnk_lm,
 30       rank() over (order by sum_ma2 desc) rnk_ma2,
 31       rank() over (order by sum_ma3 desc) rnk_ma3
 32     from sums s
 33    )

---

 34  -- finally, return only product that "now" rank as the first 5 (or as many as you want),
 35  -- and display their rank for other dates
 36  select rnk_tw as "rank this week",
 37         product_name,
 38         rnk_pw as "previous week",
 39         rnk_lm as "last month",
 40         rnk_ma2 as "2 months ago",
 41         rnk_ma3 as "3 months ago"
 42  from ranks
 43  where rnk_tw <= 5
 44  order by rnk_tw;

rank this week PRODUCT_NAME         previous week last month 2 months ago 3 months ago
-------------- -------------------- ------------- ---------- ------------ ------------
             1 P11                              8          5            3            1
             2 P10                             10          7            4            2
             3 P9                               1          1            1            3
             4 P8                               2          2            2            4
             5 P7                               4          4            6            6

SQL>