如何每周获取前10个数据(本周、上周、上月、2个月前、3个月前)

Question

我想检索前10个产品-每周

Rank     This week product   Previous week rank   Last month rank   2 month ago rank     3 month ago rank
1             Watch                  2                    3                    1               4
2             Radio                  3                    2                    4               5
3             Pen                    4                    5                    6               7
4
5
6
7
8
9
10

请帮助我了解如何从SQL查询中获取此类数据。感谢

Answer 1

假设您有一个表：

CREATE TABLE your_table (
product          VARCHAR2(20),
some_date_column DATE
)

然后，您可以在RANK分析函数中使用条件聚合：

SELECT product,
RANK() OVER (
ORDER BY COUNT(
CASE
WHEN some_date_column >= TRUNC(SYSDATE, 'IW')
AND  some_date_column <  TRUNC(SYSDATE, 'IW') + INTERVAL '7' DAY
THEN 1
END
) DESC
) AS rank_this_week,
RANK() OVER (
ORDER BY COUNT(
CASE
WHEN some_date_column >= TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY
AND  some_date_column <  TRUNC(SYSDATE, 'IW')
THEN 1
END
) DESC
) AS rank_last_week,
RANK() OVER (
ORDER BY COUNT(
CASE
WHEN some_date_column >= ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -1)
AND  some_date_column <  TRUNC(SYSDATE, 'MM')
THEN 1
END
) DESC
) AS rank_last_month,
RANK() OVER (
ORDER BY COUNT(
CASE
WHEN some_date_column >= ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -2)
AND  some_date_column <  ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -1)
THEN 1
END
) DESC
) AS rank_two_month,
RANK() OVER (
ORDER BY COUNT(
CASE
WHEN some_date_column >= ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -3)
AND  some_date_column <  ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -2)
THEN 1
END
) DESC
) AS rank_three_month
FROM   your_table
WHERE  some_date_column < TRUNC(SYSDATE, 'IW') + INTERVAL '7' DAY
AND    some_date_column >= ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -3)
GROUP BY product
ORDER BY rank_this_week
FETCH FIRST 10 ROWS WITH TIES;

对于样本数据：

INSERT INTO your_table (product, some_date_column)
SELECT 'A', TRUNC(SYSDATE, 'IW') FROM DUAL CONNECT BY LEVEL <=  1 UNION ALL
SELECT 'B', TRUNC(SYSDATE, 'IW') FROM DUAL CONNECT BY LEVEL <=  2 UNION ALL
SELECT 'C', TRUNC(SYSDATE, 'IW') FROM DUAL CONNECT BY LEVEL <=  3 UNION ALL
SELECT 'D', TRUNC(SYSDATE, 'IW') FROM DUAL CONNECT BY LEVEL <=  4 UNION ALL
SELECT 'E', TRUNC(SYSDATE, 'IW') FROM DUAL CONNECT BY LEVEL <=  5 UNION ALL
SELECT 'F', TRUNC(SYSDATE, 'IW') FROM DUAL CONNECT BY LEVEL <=  6 UNION ALL
SELECT 'G', TRUNC(SYSDATE, 'IW') FROM DUAL CONNECT BY LEVEL <=  7 UNION ALL
SELECT 'H', TRUNC(SYSDATE, 'IW') FROM DUAL CONNECT BY LEVEL <=  8 UNION ALL
SELECT 'I', TRUNC(SYSDATE, 'IW') FROM DUAL CONNECT BY LEVEL <=  9 UNION ALL
SELECT 'J', TRUNC(SYSDATE, 'IW') FROM DUAL CONNECT BY LEVEL <= 10 UNION ALL
SELECT 'K', TRUNC(SYSDATE, 'IW') FROM DUAL CONNECT BY LEVEL <= 11 UNION ALL
SELECT 'A', TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL CONNECT BY LEVEL <=  3 UNION ALL
SELECT 'B', TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL CONNECT BY LEVEL <=  3 UNION ALL
SELECT 'C', TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL CONNECT BY LEVEL <=  1 UNION ALL
SELECT 'D', TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL CONNECT BY LEVEL <=  2 UNION ALL
SELECT 'E', TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL CONNECT BY LEVEL <=  5 UNION ALL
SELECT 'F', TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL CONNECT BY LEVEL <=  7 UNION ALL
SELECT 'G', TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL CONNECT BY LEVEL <=  6 UNION ALL
SELECT 'H', TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL CONNECT BY LEVEL <=  4 UNION ALL
SELECT 'I', TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL CONNECT BY LEVEL <=  5 UNION ALL
SELECT 'J', TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL CONNECT BY LEVEL <=  3 UNION ALL
SELECT 'K', TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL CONNECT BY LEVEL <=  2
-- Etc.

(注意：这只有最近两周的数据，所以所有其他排名都是平局。(

输出：

<1>td style="text-align:left；">I<1>>td style="ext-align:right<1><1>

PRODUCT	RANK_THIS_WEEK	RANK_LAST_WEEK	RANK_LASS_MONTH	RANK_TWO_MONTH秩_三个月
K	1	9		J	2	6	3	3		H	4	5		G	5	2
F	6	1		E	7		D	8		C	9	1
B	10