Forum Stats

  • 3,767,857 Users
  • 2,252,726 Discussions
  • 7,874,365 Comments

Discussions

Reg: Weekly count from query data

chandra_1986
chandra_1986 Member Posts: 285 Blue Ribbon

HI Experts,


We are using database : - 12.1.0.2.0

below query is used for session count for one day.

 select count (unique(session_id)) from user.report_data partition

 for (to_date('28Jan2021','ddmonyyyy')) where session_id in (select unique(session_id)

 from user.report_data partition for (to_date('28Jan2021','ddmonyyyy')) 

 where logdata like '%Exposed Outside|Dispatched%' 

 and RECORD_TYPE ='SESSION_DATA') and low_name = 'Update_Put' 

 and tep_name = 'finaltype' and logdata like '%WreIn%'


How can we use above query to get 1 week session count data.


Thanks

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,900 Red Diamond

    And how is table partitioned? What is partitioning column name? I'll assume table is partitioned by day:

    select  count(unique(session_id))
      from  user.report_data partition
      where <partitioning-column> >= <week-start-date>
        and <partitioning-column>  < <week-start-date> + 7
        and session_id in (
                           select  unique(session_id)
                             from  user.report_data partition
                             where <partitioning-column> >= <week-start-date>
                               and <partitioning-column>  < <week-start-date> + 7
                               and logdata like '%Exposed Outside|Dispatched%' 
                               and RECORD_TYPE ='SESSION_DATA'
                          )
        and low_name = 'Update_Put' 
        and tep_name = 'finaltype'
        and logdata like '%WreIn%'
    /
    
    
    

    SY.

  • chandra_1986
    chandra_1986 Member Posts: 285 Blue Ribbon

    Thanks expert,


    We have daily partitioned like with partitioned type: RANGE partition.


    report_data P_FIRST   TIMESTAMP' 2017-11-02 00:00:00'

    report_data SYS_P72623 TIMESTAMP' 2020-10-09 00:00:00'

    report_data SYS_P72724 TIMESTAMP' 2020-10-10 00:00:00'

    report_data SYS_P72725 TIMESTAMP' 2020-10-11 00:00:00'

    report_data SYS_P72826 TIMESTAMP' 2020-10-12 00:00:00'

    report_data SYS_P72927 TIMESTAMP' 2020-10-13 00:00:00'



    how can we use above query to get weekly data.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,900 Red Diamond

    I already gave you a query. Oracle optimizer is smart enough to prune partitions based on

      where report_data >= <week-start-date>
        and report_data  < <week-start-date> + 7
    

    SY.

  • chandra_1986
    chandra_1986 Member Posts: 285 Blue Ribbon

    Thanks experts,


    I have run the below query, so is i need to select  CREATE_TIME as partitioning-column> in above query.
    


     SELECT OWNER, NAME, OBJECT_TYPE, COLUMN_NAME, COLUMN_POSITION FROM ALL_PART_KEY_COLUMNS WHERE NAME = 'REPORT_DATA';

     Output is below

     REPORT_USER REPORT_DATA  TABLE   CREATE_TIME   1

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,900 Red Diamond

    We have no idea how is your table partitioned. So use:

      where create_time >= <week-start-date>
        and create_time  < <week-start-date> + 7
    

    For example, week starting Sunday, Feb 21:

      where create_time >= date '2020-02-21'
        and create_time  < date '2020-02-21' + 7
    

    SY.

  • chandra_1986
    chandra_1986 Member Posts: 285 Blue Ribbon

    Thanks experts, query is running from last 6 hours butt not getting any result.


    select count(unique(session_id))

     from user.report_data partition 

     where CREATE_TIME >= TIMESTAMP' 2021-02-18 00:00:00'

      and CREATE_TIME < TIMESTAMP' 2021-02-18 00:00:00' + 7

      and session_id in (

                select unique(session_id)

                 from user.report_data partition

                 where CREATE_TIME >= TIMESTAMP' 2021-02-18 00:00:00'

                  and CREATE_TIME < TIMESTAMP' 2021-02-18 00:00:00' + 7

                  and logdata like '%Exposed Outside|Dispatched%' 

                  and RECORD_TYPE ='SESSION_DATA'

               )

      and low_name = 'Update_Put' 

      and tep_name = 'finaltype'

      and logdata like '%WreIn%'