本文介紹了如何每周獲取前十大數(shù)據(jù)(本周、前一周、上個月、2個月前、3個月前)的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學習吧！
問題描述
我想檢索排名前十的產(chǎn)品-每周
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查詢中獲取此類數(shù)據(jù)。謝謝
推薦答案
假設您有一個表：
CREATE TABLE your_table (
product VARCHAR2(20),
some_date_column DATE
)
然后您可以在RANK分析函數(shù)中使用條件聚合：
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;
對于樣本數(shù)據(jù)：
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.
(注意：這只是最近兩周的數(shù)據(jù)，因此所有其他排名將并列。)
輸出：
這篇關于如何每周獲取前十大數(shù)據(jù)(本周、前一周、上個月、2個月前、3個月前)的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，
