3 Replies Latest reply: Apr 1, 2013 9:34 AM by user12028775 RSS

    how to get top 11 values per date range

    user12028775
      I want to get the top 11 values by date range.

      Sample Data
      CREATE TABLE SAMPLE_DATA
        (
          DOMAIN_NAME VARCHAR2(100),
          QTD         NUMBER,
          LOAD_DATE   DATE
        );
      
      -- Insert
      BEGIN
        FOR lc IN 1..20
        LOOP
          FOR ld IN 1..30
          LOOP
            INSERT
            INTO SAMPLE_DATA VALUES
              (
                'DM_'
                ||lc,
                round(dbms_random.value(0,1000)),
                SYSDATE-ld
              );
          END LOOP;
        END LOOP;
      COMMIT;
      END;
      /
      SELECT *
      FROM
        (SELECT DOMAIN_NAME,
          QTD,
          LOAD_DATE
        FROM
          (SELECT DOMAIN_NAME,
            QTD,
            LOAD_DATE
          FROM SAMPLE_DATA
          WHERE LOAD_DATE = TRUNC(SYSDATE-3)
          ORDER BY QTD DESC
          )
        WHERE ROWNUM <=10
        UNION ALL
        SELECT 'Others' DOMAIN_NAME,
          SUM(QTD) QTD,
          LOAD_DATE
        FROM
          (SELECT DOMAIN_NAME,
            QTD,
            LOAD_DATE
          FROM
            (SELECT rownum rn,
              DOMAIN_NAME,
              QTD,
              LOAD_DATE
            FROM
              (SELECT DOMAIN_NAME,
                QTD,
                LOAD_DATE
              FROM SAMPLE_DATA
              WHERE LOAD_DATE = TRUNC(SYSDATE-3)
              ORDER BY QTD DESC
              )
            )
          WHERE rn > 10
          )
        GROUP BY LOAD_DATE
        )
      ORDER BY QTD DESC
      
      -- Result
      
      DOMAIN_NAME                 QTD                         LOAD_DATE                   
      Others                      2888                        24/03/13                    
      DM_1                        1000                        24/03/13                    
      DM_20                       933                         24/03/13                    
      DM_11                       913                         24/03/13                    
      DM_3                        743                         24/03/13                    
      DM_13                       572                         24/03/13                    
      DM_12                       568                         24/03/13                    
      DM_9                        564                         24/03/13                    
      DM_6                        505                         24/03/13                    
      DM_5                        504                         24/03/13                    
      DM_2                        480                         24/03/13     
      Please, Help me get in one query this result using a range of date.

      e.g
      using LOAD_DATE BETWEEN '24/03/13' AND '25/03/13'
      DOMAIN_NAME                 QTD                         LOAD_DATE                   
      Others                      2888                        24/03/13                    
      DM_1                        1000                        24/03/13                    
      DM_20                       933                         24/03/13                    
      DM_11                       913                         24/03/13                    
      DM_3                        743                         24/03/13                    
      DM_13                       572                         24/03/13                    
      DM_12                       568                         24/03/13                    
      DM_9                        564                         24/03/13                    
      DM_6                        505                         24/03/13                    
      DM_5                        504                         24/03/13                    
      DM_2                        480                         24/03/13                      
      Others                      1948                        25/03/13                    
      DM_1                        807                         25/03/13                    
      DM_8                        764                         25/03/13                    
      DM_7                        761                         25/03/13                    
      DM_11                       656                         25/03/13                    
      DM_18                       611                         25/03/13                    
      DM_17                       523                         25/03/13                    
      DM_14                       467                         25/03/13                    
      DM_19                       447                         25/03/13                    
      DM_15                       437                         25/03/13                    
      DM_6                        380                         25/03/13             
      Thank you in advance.
        • 1. Re: how to get top 11 values per date range
          Frank Kulash
          Hi,

          Sorry, I can't figure out what you want.

          Please don't use random data. You can't be sure it will test all the special cases you need to test, and it makes explanations very difficult. Post INSERT statements for a little fixed data., and I do mean "little". Do you really need to have 20 domains? Can't you show the problem with, say, 5? In your real problem, you may need to get the top 11 values, but wouldn't a problem where you got the top 3 show you how to do it?

          Post a little sample data, and the exact results you want from that sample data. Explain how you get those results from that data. Define any special terms you use, such as "top" and "date range". What if there are ties, that is, if 2 (or more) rows have an equal claim to being the "top" row?
          Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
          See the forum FAQ {message:id=9360002}
          • 2. Re: how to get top 11 values per date range
            user12028775
            Hi Frank,

            RDBMS:11.2.0.3

            The reason behind this is get this result as showed previouly and load into a bar chart. (BIP Oracle)

            X = QTD
            Y = LOAD_DATE
            SERIES = DOMAIN_NAME

            So, I need a query which get two dates (range) and return:
            TOP 10 QTD of DOMAIN_NAME by DAY
            The others DOMAIN_NAME not in TOP 10...grouped all by DAY into a single row SUM(QTD).


            The domain_name can be 5 or 1000, the qtd can be any value. There is no fixed value.
            This sample data is same result which I get from my complex query result. It's because the result depend on that variance.

            My query return the data from a single date. ( which is not enough to load my graph)

            There is no problem if exist only 3 or 1 DOMAIN_NAME ( always at least one will exists)



            Sorry for my bad eng.

            Regards
            • 3. Re: how to get top 11 values per date range
              user12028775
              I got the solution. Just sharing.

              I used analytic functions that make my job easy.

              Sample Data
              DOMAIN_NAME                 QTD                         LOAD_DATE                   
              DM_1                        807                         25/03/2013                  
              DM_1                        1000                        24/03/2013                  
              DM_2                        226                         25/03/2013                  
              DM_2                        480                         24/03/2013                  
              DM_3                        244                         25/03/2013                  
              DM_3                        743                         24/03/2013                  
              DM_4                        48                          25/03/2013                  
              DM_4                        413                         24/03/2013                  
              DM_5                        164                         25/03/2013                  
              DM_5                        504                         24/03/2013                  
              DM_6                        380                         25/03/2013                  
              DM_6                        505                         24/03/2013                  
              DM_7                        761                         25/03/2013                  
              DM_7                        212                         24/03/2013                  
              DM_8                        764                         25/03/2013                  
              DM_8                        308                         24/03/2013                  
              DM_9                        354                         25/03/2013                  
              DM_9                        564                         24/03/2013                  
              DM_10                       214                         25/03/2013                  
              DM_10                       367                         24/03/2013                  
              DM_11                       656                         25/03/2013                  
              DM_11                       913                         24/03/2013                  
              DM_12                       37                          25/03/2013                  
              DM_12                       568                         24/03/2013                  
              DM_13                       332                         25/03/2013                  
              DM_13                       572                         24/03/2013                  
              DM_14                       467                         25/03/2013                  
              DM_14                       87                          24/03/2013                  
              DM_15                       437                         25/03/2013                  
              DM_15                       450                         24/03/2013                  
              DM_16                       238                         25/03/2013                  
              DM_16                       299                         24/03/2013                  
              DM_17                       523                         25/03/2013                  
              DM_17                       143                         24/03/2013                  
              DM_18                       611                         25/03/2013                  
              DM_18                       145                         24/03/2013                  
              DM_19                       447                         25/03/2013                  
              DM_19                       464                         24/03/2013                  
              DM_20                       91                          25/03/2013                  
              DM_20                       933                         24/03/2013                  
              Top 11 QTD of DOMAIN_NAME per Data Range.
              SELECT *
              FROM
                (SELECT DOMAIN_NAME,
                  QTD,
                  LOAD_DATE
                FROM
                  (SELECT LOAD_DATE,
                    DOMAIN_NAME ,
                    QTD,
                    (DENSE_RANK() OVER (PARTITION BY LOAD_DATE ORDER BY QTD DESC )) AS RANK_QTD
                  FROM SAMPLE_DATA
                  WHERE trunc(load_date) BETWEEN '24/03/2013' AND '25/03/2013'
                  )
                WHERE RANK_QTD <= 10
                UNION ALL
                SELECT 'Others',
                  SUM(QTD) AS QTD,
                  LOAD_DATE
                FROM
                  (SELECT LOAD_DATE,
                    DOMAIN_NAME ,
                    QTD,
                    (DENSE_RANK() OVER (PARTITION BY LOAD_DATE ORDER BY QTD DESC )) AS RANK_QTD
                  FROM SAMPLE_DATA
                  WHERE trunc(load_date) BETWEEN '24/03/2013' AND '25/03/2013'
                  )
                WHERE RANK_QTD > 10
                GROUP BY LOAD_DATE
                )
              ORDER BY LOAD_DATE ASC,
                QTD DESC
              DOMAIN_NAME                 QTD                         LOAD_DATE                   
              Others                      2888                        24/03/2013                  
              DM_1                        1000                        24/03/2013                  
              DM_20                       933                         24/03/2013                  
              DM_11                       913                         24/03/2013                  
              DM_3                        743                         24/03/2013                  
              DM_13                       572                         24/03/2013                  
              DM_12                       568                         24/03/2013                  
              DM_9                        564                         24/03/2013                  
              DM_6                        505                         24/03/2013                  
              DM_5                        504                         24/03/2013                  
              DM_2                        480                         24/03/2013                  
              Others                      1948                        25/03/2013                  
              DM_1                        807                         25/03/2013                  
              DM_8                        764                         25/03/2013                  
              DM_7                        761                         25/03/2013                  
              DM_11                       656                         25/03/2013                  
              DM_18                       611                         25/03/2013                  
              DM_17                       523                         25/03/2013                  
              DM_14                       467                         25/03/2013                  
              DM_19                       447                         25/03/2013                  
              DM_15                       437                         25/03/2013                  
              DM_6                        380                         25/03/2013