This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Feb 6, 2013 12: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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points