Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2010
    Posts
    15

    Unanswered: Help with a query

    I'm trying to query a table with over 2,000,000 rows, and the query I wrote is taking about 30 seconds to complete. I'm wondering if there's any way to speed it up?

    Code:
    select current timestamp, count(a.actionuserid) 
    from actions a 
    join actions b 
    on a.actionuserid = b.actionuserid
    where a.actionstatus='Login' and b.actionstatus='Registered' and a.actiontime between current timestamp - 15 days and current timestamp
    I have a table with all the actions logged. I'm looking for the number of people who logged in and registered within the last 15 days.

    TableActions
    ActionID = INT PK
    ActionUserID = INT FK
    ActionStatus = Varchar
    ActionTime = DateTime

  2. #2
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    Try this query:
    Code:
    select actionuserid, actionstatus, count(*)
     from  actions
    where  actionstatus in( 'Login', 'Registered' )
     and   actiontime between current timestamp - 15 days and current timestamp
    group by actionuserid, actionstatus

  3. #3
    Join Date
    Jun 2010
    Posts
    15
    That doesn't seem to work because it returns rows where a user Logged in but didn't register and vice versa. I need a list (or a count) of users who did both.
    Last edited by Pryach; 01-23-13 at 18:18.

  4. #4
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Please post real DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Narrative is nice but not as much help.

    >> I'm trying to query a table with over 2,000,000 rows, and the query I wrote is taking about 30 seconds to complete. I'm wondering if there's any way to speed it up? <<

    SELECT X.report_date, COUNT(DISTINCT X.action_user_id) AS login_and_register_cnt
    FROM (SELECT CAST(CURRENT_TIMESTAMP AS DATE) AS report_date, action_status,
    action_user_id
    FROM Something_Actions
    WHERE action_status IN ('Login', 'Registered')
    AND action_timestamp
    BETWEEN CURRENT_TIMESTAMP – INTERVAL '15' DAYS
    AND CURRENT_TIMESTAMP)
    AS X(report_date, action_status, action_user_id)
    GROUP BY X.report_date
    HAVING COUNT(DISTINCT action_user_id);

    Untested.

  5. #5
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    Quote Originally Posted by Pryach View Post
    That doesn't seem to work because it returns rows where a user Logged in but didn't register and vice versa. I need a list (or a count) of users who did both.
    Ok, This will only count the ones with both a 'Login' and 'Registered' within specified time range:

    Code:
    select actionuserid, count(*)
     from  actions a
    where  actionstatus  =  'Login'
     and   actiontime between current timestamp - 15 days and current timestamp
     and   exists( select *
                    from  actions
                   where  actionuserid  =  a.actionuserid
                    and   actionstatus  =  'Registered'
                    and   actiontime between current timestamp - 15 days and current timestamp )
    group by actionuserid

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I'm looking for the number of people who logged in and registered within the last 15 days.
    I think that a predicate for b.actiontime may also be necessary.
    Example 1:
    Code:
    SELECT current date
         , COUNT(DISTINCT a.actionuserid) AS count_of_users
    /* If you want list of users
    SELECT DISTINCT
           current date
         , a.actionuserid
    */
     FROM  actions a
     INNER JOIN
           actions b
      ON   b.actionuserid = a.actionuserid
       AND b.actionstatus = 'Registered'
       AND b.actiontime
           BETWEEN current timestamp - 15 days
               AND current timestamp
     WHERE a.actionstatus = 'Login'
       AND a.actiontime
           BETWEEN current timestamp - 15 days
               AND current timestamp
    ;
    Some indexes might be worth to try.
    for a:
    ( actiontime , actionstatus , actionuserid )

    for b:
    ( actionuserid , actionstatus , actiontime )
    or
    ( actionuserid , actiontime , actionstatus )




    Example 2:
    Code:
    SELECT current date
         , COUNT(*) AS count_of_users
     FROM  (SELECT actionuserid
             FROM  actions
             WHERE actionstatus IN ('Login' , 'Registered')
               AND actiontime
                   BETWEEN current timestamp - 15 days
                       AND current timestamp
             GROUP BY
                   actionuserid
             HAVING
                   COUNT(DISTINCT actionstatus) = 2
           )
    ;

    Example 3:
    Code:
    SELECT current timestamp
         , COUNT(DISTINCT actionuserid) AS count_of_users
     FROM  (SELECT actionuserid
                 , actionstatus
                 , MIN(actionstatus)
                      OVER(PARTITION BY actionuserid) AS min_actionstatus
             FROM  actions
             WHERE actionstatus IN ('Login' , 'Registered')
               AND actiontime
                   BETWEEN current timestamp - 15 days
                       AND current timestamp
           )
     WHERE min_actionstatus < actionstatus
    ;


    Example 4:
    Code:
    SELECT current date
         , COUNT(DISTINCT actionuserid) AS count_of_users
     FROM  (SELECT actionuserid
                 , DENSE_RANK()
                      OVER( PARTITION BY actionuserid
                                ORDER BY actionstatus ) AS d_rank_status
             FROM  actions
             WHERE actionstatus IN ('Login' , 'Registered')
               AND actiontime
                   BETWEEN current timestamp - 15 days
                       AND current timestamp
           )
     WHERE d_rank_status = 2
    ;
    Last edited by tonkuma; 01-23-13 at 19:18. Reason: Remove max_actionstatus from Example 3.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Example 5:
    Code:
    SELECT current date
         , COUNT(DISTINCT actionuserid) AS count_of_users
     FROM  actions a
     WHERE actionstatus = 'Registered'
       AND actiontime
           BETWEEN current timestamp - 15 days
               AND current timestamp
       AND EXISTS (
           SELECT 0
            FROM  actions b
            WHERE b.actionuserid = a.actionuserid
              AND b.actionstatus = 'Login'
              AND b.actiontime
                  BETWEEN current timestamp - 15 days
                      AND current timestamp
    
           )
    ;

Posting Permissions

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