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

How to get top 10 data weekly (This week, Previous week, Last month, 2 months ago, 3 month ago)(如何每周获取前十大数据(本周、前一周、上个月、2个月前、3个月前))
本文介绍了如何每周获取前十大数据(本周、前一周、上个月、2个月前、3个月前)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我想检索排名前十的产品-每周

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查询中获取此类数据。谢谢

推荐答案

假设您有一个表:

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.

(注意:这只是最近两周的数据,因此所有其他排名将并列。)

输出:

这篇关于如何每周获取前十大数据(本周、前一周、上个月、2个月前、3个月前)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!

相关文档推荐

Execute complex raw SQL query in EF6(在EF6中执行复杂的原始SQL查询)
Hibernate reactive No Vert.x context active in aws rds(AWS RDS中的休眠反应性非Vert.x上下文处于活动状态)
Bulk insert with mysql2 and NodeJs throws 500(使用mysql2和NodeJS的大容量插入抛出500)
Flask + PyMySQL giving error no attribute #39;settimeout#39;(FlASK+PyMySQL给出错误,没有属性#39;setTimeout#39;)
auto_increment column for a group of rows?(一组行的AUTO_INCREMENT列?)
Sort by ID DESC(按ID代码排序)