1 2 Previous Next 21 Replies Latest reply: Feb 6, 2013 2:40 AM by chris227 RSS

    Can it be done through SQL only? I am able to do it thorugh PL-SQL

    Mr Lonely
      CREATE TABLE tmp_viewing_data
        (
          approval_id           NUMBER(10,0),
          fixed_ddate            date,
          house_id              NUMBER,
          member_id             NUMBER
        );
      
      insert into tmp_viewing_data values(321981,to_date('15-Dec-12','DD-Mon-YY'),33623,2);
      insert into tmp_viewing_data values(321981,to_date('22-Dec-12','DD-Mon-YY'),13272,1);
      insert into tmp_viewing_data values(321981,to_date('24-Dec-12','DD-Mon-YY'),26018,2);
      insert into tmp_viewing_data values(321981,to_date('3-Dec-12','DD-Mon-YY'),12942,2);
      insert into tmp_viewing_data values(321981,to_date('4-Dec-12','DD-Mon-YY'),12942,1);
      insert into tmp_viewing_data values(321981,to_date('9-Dec-12','DD-Mon-YY'),18055,2);
      Expected Result.

      For any date there will be three column.

      1. Date, Unique_Member_Count, Total_Member_Count.

      Date will be the date in the table.
      Unique_Member_Count = distinct count ( distinct house_id || member_id ) between the minimum date and that date.
      Total_member_Count = count(*) between the minimum date and that date.

      ( for this set of date I think both will be same though.)

      Edited by: Mr Lonely on Feb 5, 2013 2:36 PM

      Edited by: Mr Lonely on Feb 5, 2013 2:39 PM
        • 1. Re: Can it be done through SQL only? I am able to do it thorugh PL-SQL
          Purvesh K
          select fixed_ddate, count(distinct member_id) Unique_Member_count, count(member_id) Total_Member_count --Assuming Member_ID will never be NULL.
            from tmp_viewing_data
          group by fixed_ddate;
          • 2. Re: Can it be done through SQL only? I am able to do it thorugh PL-SQL
            Mr Lonely
            No no ..

            It's giving count for that date only.

            But the count should be for the data between minimum date and that date..
            select count ( distinct house_id || member_id) , count(*) from tmp_viewing_data where fixed_ddate <= '09-DEC-2012'
            but I need like

            Date, Unique_Member_Count, Total_Member_Count so that I can directly store them into one table.

            I have done it thorugh PL-SQL.. like taking all the dates in a loop and then using the above mentioned query and inserted them in the table.

            Edited by: Mr Lonely on Feb 5, 2013 2:41 PM
            • 3. Re: Can it be done through SQL only? I am able to do it thorugh PL-SQL
              jeneesh
              select *
              from tmp_viewing_data;
              
              APPROVAL_ID FIXED_DDATE HOUSE_ID MEMBER_ID
              ----------- ----------- -------- ---------
                   321981 15-DEC-12      33623         2 
                   321981 22-DEC-12      13272         1 
                   321981 24-DEC-12      26018         2 
                   321981 03-DEC-12      12942         2 
                   321981 04-DEC-12      12942         1 
                   321981 09-DEC-12      18055         2 
                   321981 05-DEC-12      33623         2 ---"Added to test"
              
              select fixed_ddate,
                     count(*) over(order by fixed_ddate) cnt,
                     sum(case when rk=1 then 1 else 0 end) 
                             over(order by fixed_ddate) dist_cnt
              from (
                  select t.*,row_number() 
                              over(partition by house_id,member_id 
                                      order by fixed_ddate) rk 
                  from tmp_viewing_data t
                  );
              
              FIXED_DDATE CNT DIST_CNT
              ----------- --- --------
              03-DEC-12     1        1 
              04-DEC-12     2        2 
              05-DEC-12     3        3 
              09-DEC-12     4        4 
              15-DEC-12     5        4 
              22-DEC-12     6        5 
              24-DEC-12     7        6
              • 4. Re: Can it be done through SQL only? I am able to do it thorugh PL-SQL
                jeneesh
                And if you expect multiple entries for a day, you could add DISTINCT
                select distinct fixed_ddate,
                       count(*) over(order by fixed_ddate) cnt,
                       sum(case when rk=1 then 1 else 0 end) 
                              over(order by fixed_ddate) dist_cnt
                ...
                • 5. Re: Can it be done through SQL only? I am able to do it thorugh PL-SQL
                  chris227
                  select
                    approval_id 
                  , fixed_ddate
                  , Total_Member_Count
                  , count(case rn when 1 then 1 end)
                               over (partition by approval_id
                               order by fixed_ddate
                               range between unbounded preceding and current row
                    ) Unique_Member_Count
                  from
                  (select
                    approval_id 
                  , fixed_ddate
                  , count(*) over (partition by approval_id order by fixed_ddate
                                  range between unbounded preceding and current row
                    ) Total_Member_Count
                  , row_number() over (partition by approval_id, house_id, member_id
                                      order by fixed_ddate
                    ) rn
                  from tmp_viewing_data
                  )
                  order by 
                    approval_id 
                  , fixed_ddate
                  Edited by: chris227 on 05.02.2013 01:56
                  changed rows to range
                  • 6. Re: Can it be done through SQL only? I am able to do it thorugh PL-SQL
                    Nicosa-Oracle
                    Hi,

                    I'm not sure it can be done using straight analytics because you need a distinct with a windowing_clause and hence an order by which seems actually not supported :
                    [11.2] Scott @ My11g > !cat t.sql
                    with tmp_viewing_data ( approval_id, fixed_ddate, house_id, member_id) as (
                         select 321981,to_date('15-Dec-12','DD-Mon-YY'),33623,2 from dual union all
                         select 321981,to_date('22-Dec-12','DD-Mon-YY'),13272,1 from dual union all
                         select 321981,to_date('24-Dec-12','DD-Mon-YY'),26018,2 from dual union all
                         select 321981,to_date('3-Dec-12','DD-Mon-YY'),12942,2 from dual union all
                         select 321981,to_date('4-Dec-12','DD-Mon-YY'),12942,1 from dual union all
                         select 321981,to_date('9-Dec-12','DD-Mon-YY'),18055,2 from dual
                    )
                    select fixed_ddate
                    ,count(distinct house_id||'/'|| member_id) over (order by fixed_ddate) Unique_Member_Count
                    ,count(*) over (order by fixed_ddate) Total_Member_Count
                    from tmp_viewing_data;
                    
                    [11.2] Scott @ My11g > @t
                    ,count(distinct house_id||'/'|| member_id) over (order by fixed_ddate) Unique_Member_Count
                                                                     *
                    ERROR at line 10:
                    ORA-30487: ORDER BY not allowed here
                    My guess is that it can be done using model_clause (which I know nothing about, but others here are masters on the topic)
                    • 7. Re: Can it be done through SQL only? I am able to do it thorugh PL-SQL
                      jeneesh
                      It can be done using Analytic itself I feel (As already shown..)
                      • 8. Re: Can it be done through SQL only? I am able to do it thorugh PL-SQL
                        chris227
                        select
                         approval_id
                        ,fd
                        ,Unique_Member_Count
                        ,Total_Member_Count
                        from tmp_viewing_data
                        model
                        partition by (approval_id )
                        dimension by (row_number() over
                                       (partition by approval_id order by fixed_ddate) rn
                                     , fixed_ddate fd)
                        measures(0  Total_Member_Count, 0 Unique_Member_Count, house_id, member_id)
                        rules(
                         Unique_Member_Count[any,any] order by rn =count(distinct house_id||member_id) [any, fd<=cv(fd)]
                        ,Total_Member_Count[any,any] order by rn =count(*) [any, fd<=cv(fd)]
                        )
                        • 9. Re: Can it be done through SQL only? I am able to do it thorugh PL-SQL
                          jeneesh
                          I was not saying, it cant be done using MODEL.

                          Just was mentioning Analytics will be enough..
                          • 10. Re: Can it be done through SQL only? I am able to do it thorugh PL-SQL
                            chris227
                            keep cool this one was for Nicosa as you could have easily figured out by yourself.
                            • 11. Re: Can it be done through SQL only? I am able to do it thorugh PL-SQL
                              Mr Lonely
                              thank you .. this is exactly what I was looking for.
                              • 12. Re: Can it be done through SQL only? I am able to do it thorugh PL-SQL
                                Nicosa-Oracle
                                jeneesh wrote:
                                It can be done using Analytic itself I feel (As already shown..)
                                Hi Jeneesh,

                                Sorry, I missed your post +(I guess I didn't refresh the page before posting)+.

                                Nice way of dealing the "distinct over a period" !
                                • 13. Re: Can it be done through SQL only? I am able to do it thorugh PL-SQL
                                  jeneesh
                                  chris227 wrote:
                                  keep cool this one was for Nicosa as you could have easily figured out by yourself.
                                  Ah..ok..

                                  By the way, your post was addressed to me..

                                  No worries.. :)
                                  • 14. Re: Can it be done through SQL only? I am able to do it thorugh PL-SQL
                                    Mr Lonely
                                    Hi ,

                                    One little problem. I forgot that while asking for help.

                                    In case some dates are missing in between I need to display the count for that day as previous day's count.

                                    For example we don't have any record for 6th, 7th, 8th.

                                    So it for 6th I need to display 5th's count. For 7th I need to display 6th's count and so on.
                                    1 2 Previous Next