Results 1 to 7 of 7
  1. #1
    Join Date
    May 2004
    Posts
    95

    Unanswered: RANGE on Analytical Function

    Hi all,

    I've got a table with several records of registrations.

    Table REG: ID NUMBER, brand_id NUMBER, registration_type_id NUMBER, date_registration DATE

    Since 2011 I've been putting data on that table, and now I need to make some reports, and analytical functions are the best, but I can't quite get it working.

    What I need specifically, is a count of registrations by month, and an aggregated value from the beginning of the year of that registration.

    My select worked, if I only had one year:
    Code:
    select COUNT(1) qt, SUM(COUNT(1)) OVER (PARTITION BY brand_id, registration_type_id ORDER BY TO_NUMBER(TO_CHAR(date_registration ,'YYYYMM')) ROWS BETWEEN unbounded preceding AND CURRENT ROW) qt_agg, brand_id, registration_type_id, TO_NUMBER(TO_CHAR(date_registration ,'YYYYMM')) year_month
    FROM reg
    GROUP BY brand_id, registration_type_id, TO_NUMBER(TO_CHAR(date_registration ,'YYYYMM'))
    But now with 2012 data, the unbounded preceding starts collecting data from 2011.

    Can anyone give me some pointers for the construction of the interval on the analytical function?

    Many thanks

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Hi,
    Quote Originally Posted by xixo View Post
    Can anyone give me some pointers for the construction of the interval on the analytical function?
    Is there anything special that you insist on including that filter into the interval clause?
    What is wrong with adding "the year of that registration" (whichever expression represents it) to the PARTITION BY clause?
    Or entirely filter out "data from 2011"/include only "the year of that registration" from/into the WHERE clause?

    Of course, without a test case - CREATE TABLE statements for table structure, INSERT statements for sample data, expected result set and exact rules for achieving it - it is imposible for me to propose/check anything.

  3. #3
    Join Date
    May 2004
    Posts
    95
    Of course, without a test case - CREATE TABLE statements for table structure, INSERT statements for sample data, expected result set and exact rules for achieving it - it is imposible for me to propose/check anything.
    You are right, sorry.

    TABLE:
    Code:
    CREATE TABLE reg
    (id NUMBER(10,0) NOT NULL,
    brand_id NUMBER(10,0) NOT NULL,
    registration_type_id NUMBER(10,0) NOT NULL,
    date_registration DATE NOT NULL
    )
    DATA:
    Code:
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (1,58,1,TO_DATE('20110201','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (2,91,1,TO_DATE('20110201','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (3,313,1,TO_DATE('20110201','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (4,91,2,TO_DATE('20110201','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (5,313,2,TO_DATE('20110201','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (6,58,1,TO_DATE('20110301','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (7,91,1,TO_DATE('20110301','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (8,313,1,TO_DATE('20110301','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (9,58,2,TO_DATE('20110301','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (10,91,2,TO_DATE('20110301','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (11,313,2,TO_DATE('20110301','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (12,58,1,TO_DATE('20110401','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (13,91,1,TO_DATE('20110401','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (14,313,1,TO_DATE('20110401','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (15,58,2,TO_DATE('20110401','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (16,91,2,TO_DATE('20110401','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (17,313,2,TO_DATE('20110401','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (18,58,1,TO_DATE('20110601','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (19,91,1,TO_DATE('20110601','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (20,313,1,TO_DATE('20110601','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (21,58,2,TO_DATE('20110601','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (22,91,2,TO_DATE('20110601','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (23,313,2,TO_DATE('20110601','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (24,58,1,TO_DATE('20110701','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (25,91,1,TO_DATE('20110701','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (26,313,1,TO_DATE('20110701','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (27,58,2,TO_DATE('20110701','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (28,91,2,TO_DATE('20110701','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (29,313,2,TO_DATE('20110701','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (30,58,1,TO_DATE('20110801','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (31,58,2,TO_DATE('20110801','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (32,91,2,TO_DATE('20110801','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (33,313,2,TO_DATE('20110801','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (34,58,1,TO_DATE('20110901','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (35,313,1,TO_DATE('20110901','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (36,58,2,TO_DATE('20110901','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (37,91,2,TO_DATE('20110901','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (38,313,2,TO_DATE('20110901','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (39,58,1,TO_DATE('20111001','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (40,91,1,TO_DATE('20120201','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (41,313,1,TO_DATE('20120201','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (42,58,2,TO_DATE('20120201','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (43,91,2,TO_DATE('20120201','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (44,313,2,TO_DATE('20120201','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (45,58,2,TO_DATE('20120301','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (46,91,2,TO_DATE('20120301','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (47,313,2,TO_DATE('20120301','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (48,58,1,TO_DATE('20120601','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (49,91,2,TO_DATE('20120601','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (50,58,2,TO_DATE('20120801','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (51,91,2,TO_DATE('20120801','YYYYMMDD'));
    INSERT INTO reg (id, brand_id, registration_type_id, date_registration) VALUES (52,313,2,TO_DATE('20120801','YYYYMMDD'));
    Quote Originally Posted by flyboy View Post
    Hi,
    Is there anything special that you insist on including that filter into the interval clause?
    What is wrong with adding "the year of that registration" (whichever expression represents it) to the PARTITION BY clause?
    Or entirely filter out "data from 2011"/include only "the year of that registration" from/into the WHERE clause?
    The situation is that this select is to put on a view, and I cannot put the conditions on the where condition or in the interval. It has to be a dynamic.

    At any given time, I need to be able to ask the view for the values of a specific brand and registration type at any month, with the respective value for the month and the aggregate of the same year.

    Hope it would help you to help me.
    Thanks,

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Thank you for scripts; however I am missing one important part - expected result set. I also doubt whether you could not demonstrate your problem (make representative data sample) with fewer rows.

    Anyway, I still wonder, whether you followed my first proposal:
    What is wrong with adding "the year of that registration" (whichever expression represents it) to the PARTITION BY clause?
    Code:
    select COUNT(1) qt,
       SUM(COUNT(1)) OVER (PARTITION BY brand_id, registration_type_id, TO_NUMBER(TO_CHAR(date_registration ,'YYYY'))
                           ORDER BY TO_NUMBER(TO_CHAR(date_registration ,'YYYYMM'))
                           ROWS BETWEEN unbounded preceding AND CURRENT ROW) qt_agg,
       brand_id, registration_type_id, TO_NUMBER(TO_CHAR(date_registration ,'YYYYMM')) year_month
    FROM reg
    GROUP BY brand_id, registration_type_id, TO_NUMBER(TO_CHAR(date_registration ,'YYYYMM'));
    (as it returns all 52 rows - the same count as your initial query - I will not paste its result here)

  5. #5
    Join Date
    May 2004
    Posts
    95
    ok...
    bottom line what I need is a result like this:

    Year-Month Brand qt qt_agg
    201101 1 1 1
    201102 1 1 2
    201103 1 1 3
    ...
    201112 1 1 4
    201201 1 1 1 <---I need this aggregate to start yearly
    201201 1 1 2
    ....

  6. #6
    Join Date
    Mar 2007
    Posts
    623
    Almost perfect, although I see no relation between that result set and sample data.

    Anyway, if you overlooked it, I will ask you for the third (and last) time:
    What is wrong with adding "the year of that registration" (whichever expression represents it) to the PARTITION BY clause?
    Regarding the query I posted: just add that new expression in PARTION BY clause to the GROUP BY clause (I forgot to put it when comparing with your initial query). Then it seems to give the figures based on logic you described.
    Additionally you may want to introduce ORDER BY clause for getting rows in required order.

  7. #7
    Join Date
    May 2004
    Posts
    95
    ok...

    just added the year on the partition clause, and now it's all correct (at a first glance).

    It breaks the aggregate where I need.

    Many Many Many Thanks (Commandant Lassard)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •