1 2 Previous Next 21 Replies Latest reply: Feb 6, 2013 2:40 AM by chris227 Go to original post RSS
      • 15. Re: Can it be done through SQL only? I am able to do it thorugh PL-SQL
        chris227
        Mr Lonely wrote:
        Hi ,

        One little problem. I forgot that while asking for help.
        ;-)

        I hope you are on 11g (otherwise we could generate the dates with connect by, if partitionend join was there in 10g, now i know, it is)
        with dates(a, mn, mx) as (
         select
         approval_id
        ,min(fixed_ddate) mn
        ,max(fixed_ddate) mx
        from tmp_viewing_data
        group by approval_id
        union all
         select
         a
        ,mn + 1
        ,mx
        from dates
        where
        mn+1<=mx
        )
        , viewing_data as (
        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
        ))
        
        select 
         A
        ,MN 
        ,last_value(TOTAL_MEMBER_COUNT ignore nulls) over (partition by a order by mn) TOTAL_MEMBER_COUNT
        ,last_value(UNIQUE_MEMBER_COUNT ignore nulls) over (partition by a order by mn) UNIQUE_MEMBER_COUNT
        from
        viewing_data
        right outer join
        dates
        -- no need to: partition by (a)
        on (
        viewing_data.approval_id = dates.a
        and
        viewing_data.fixed_ddate=dates.mn)
        order by 
          a
        , mn
        Edited by: chris227 on 05.02.2013 06:30

        Edited by: chris227 on 05.02.2013 06:50

        Edited by: chris227 on 06.02.2013 01:10
        commented out partition by join
        • 16. Re: Can it be done through SQL only? I am able to do it thorugh PL-SQL
          Frank Kulash
          Hi,

          If you want to include all dates, even if they are not present in your table, then outer-join your table to some other table (or result set, as shown below) that does have all the dates.
          I modified Jeneesh's solution to do that:
          WITH     got_rk         AS
          (
              select t.*,row_number() 
                          over(partition by house_id,member_id 
                                  order by fixed_ddate) rk 
              from tmp_viewing_data t
          )
          ,     all_dates     AS
          (
               SELECT     first_date + LEVEL - 1     AS a_date
               FROM     (
                         SELECT  MIN (fixed_ddate)     AS first_date
                         ,     MAX (fixed_ddate)     AS last_date
                         FROM     got_rk
                    )
               CONNECT BY     LEVEL <= 1 + last_date
                                       - first_date
          )
          SELECT    a.a_date,
                        COUNT (*) OVER (ORDER BY a.a_date)     AS cnt,
                        SUM ( CASE
                           WHEN  r.rk = 1 
                        THEN  1 
                        ELSE  0 
                    END
                     ) OVER (ORDER BY  a.a_date)     AS dist_cnt
          FROM             all_dates  a
          LEFT OUTER JOIN  got_rk         r  ON  r.fixed_ddate  = a.a_date
          ORDER BY  a.a_date
          ;
          Output:
          A_DATE           CNT   DIST_CNT
          --------- ---------- ----------
          03-DEC-12          1          1
          04-DEC-12          2          2
          05-DEC-12          3          2
          06-DEC-12          4          2
          07-DEC-12          5          2
          08-DEC-12          6          2
          09-DEC-12          7          3
          10-DEC-12          8          3
          11-DEC-12          9          3
          12-DEC-12         10          3
          13-DEC-12         11          3
          14-DEC-12         12          3
          15-DEC-12         13          4
          16-DEC-12         14          4
          17-DEC-12         15          4
          18-DEC-12         16          4
          19-DEC-12         17          4
          20-DEC-12         18          4
          21-DEC-12         19          4
          22-DEC-12         20          5
          23-DEC-12         21          5
          24-DEC-12         22          6
          • 17. Re: Can it be done through SQL only? I am able to do it thorugh PL-SQL
            Mr Lonely
            Yes. I am on 11g.
            SELECT *
              FROM v$version
             WHERE banner LIKE 'Oracle%';
            
            Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
            I am trying to understand it :)

            Seems like I need some time for this and fresh mind too.

            Edited by: Mr Lonely on Feb 5, 2013 9:53 PM
            • 18. Re: Can it be done through SQL only? I am able to do it thorugh PL-SQL
              Mr Lonely
              Hi Frank ,

              the CNT is increasing with date...

              For missing dates count and distinct count will be same as last date.
              • 19. Re: Can it be done through SQL only? I am able to do it thorugh PL-SQL
                Frank Kulash
                Hi,
                Mr Lonely wrote:
                Hi Frank ,

                the CNT is increasing with date...

                For missing dates count and distinct count will be same as last date.
                Sorry, my mistake.
                This is what I should have posted:
                WITH     got_rk         AS
                (
                    select t.*,row_number() 
                                over(partition by house_id,member_id 
                                        order by fixed_ddate) rk 
                    from tmp_viewing_data t
                )
                ,     all_dates     AS
                (
                     SELECT     first_date + LEVEL - 1     AS a_date
                     FROM     (
                               SELECT  MIN (fixed_ddate)     AS first_date
                               ,     MAX (fixed_ddate)     AS last_date
                               FROM     got_rk
                          )
                     CONNECT BY     LEVEL <= 1 + last_date
                                             - first_date
                )
                SELECT    a.a_date,
                              COUNT (r.fixed_ddate)          -- ***** Not COUNT (*) *****
                            OVER (ORDER BY a.a_date)     AS cnt,
                              SUM ( CASE
                                 WHEN  r.rk = 1 
                              THEN  1 
                              ELSE  0 
                          END
                           ) OVER (ORDER BY  a.a_date)     AS dist_cnt
                FROM             all_dates  a
                LEFT OUTER JOIN  got_rk         r  ON  r.fixed_ddate  = a.a_date
                ORDER BY  a.a_date
                ;
                It helps if you post the results you want. That way, people can compare their results to yours before they post them.
                • 20. Re: Can it be done through SQL only? I am able to do it thorugh PL-SQL
                  chris227
                  Mr Lonely wrote:
                  Hi Frank ,

                  the CNT is increasing with date...

                  For missing dates count and distinct count will be same as last date.
                  It also doesnt work on approval_id as partitions.

                  In my solution you may use a connect by instead of the recursive subquery, if this is more familiar to you.
                  Aside from this it differs from franks solution by taking care of approval_id as partitions,
                  calculting only the dates of the range of each partition. It was unclear if you want an overall range instead.
                  • 21. Re: Can it be done through SQL only? I am able to do it thorugh PL-SQL
                    chris227
                    Ok, i will add some explanation:
                    the subquery viewing_data is the same query as before. Now we want to fill the date gaps in it.
                    We regard each approval_id as a separate group (partition).

                    In the subquery dates we generate the whole range of dates for each aproval_id, by first calculating min- and max-date with a group by query and then applying a recursive subqery.

                    After that we outer join the origin result set to the generated dates, but with regard of the approval_id partitions, which means we join only the rows of each approval_id-group from the two subquery together.
                    Finally the gaps in the count-columns are fiilled by applying the last_value analytical function.
                    1 2 Previous Next