SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Feb 2010
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How many values are present in two intervals across several tables?

    Hello there,

    I am writing again, because last time I wrote on this forum, I had great support. Thank you.

    I am trying to make a query where I count the number of id that are present in an unixTimestamp interval and at the same time present in another unixTimestamp interval from a series of tables

    I.e How many id that are present where unixTimestamp > 1266428321 in "table 1" and "table 2" that is also present where unixTimestamp < 1266428321 in any of the tables.

    Not how many rows, but how many unique ids? The answer is three (id: 1 & id: 2 & id 3)

    The question:

    How do I write the query?

    Code:
    Table 1
    +-------+---------------+
    |  id  	| unixTimestamp |
    +-------+---------------+
    |       |               |
    |     1 |   1266416813  |
    |     1 |   1266416921  |
    |     3 |   1266418721  |
    |     2 |   1266420471  |
    |     4 |   1266428321  |
    |     1 |   1266429921  |
    |     3 |   1266430821  |
    |       |               |
    +-------+---------------+
    
    Table 2
    +-------+---------------+
    |  id  	| unixTimestamp |
    +-------+---------------+
    |       |               |
    |     3 |   1266416814  |
    |     1 |   1266416921  |
    |     3 |   1266418721  |
    |     5 |   1266420471  |
    |     4 |   1266428321  |
    |     1 |   1266429921  |
    |     2 |   1266430821  |
    |       |               |
    +-------+---------------+

    Thank you for your time!

    Kind regards,
    Marius

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Supposing "table 1" has the name ts1 and "table 2" has the name ts2, this query works for me:

    Code MySQL:
    SELECT DISTINCT
      tmp.id
    FROM 
      (
         SELECT id
         FROM ts1
         WHERE unixTimestamp < 1266428321
       UNION
         SELECT id
         FROM ts2
         WHERE unixTimestamp < 1266428321
      )
      AS tmp
    WHERE
       id IN
       (
         SELECT id
         FROM ts1
         WHERE unixTimestamp > 1266428321
       UNION
         SELECT id
         FROM ts2
         WHERE unixTimestamp > 1266428321
       )

    I'm pretty sure there are better (shorter, more efficient) queries possible though ...
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SitePoint Member
    Join Date
    Feb 2010
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    Supposing "table 1" has the name ts1 and "table 2" has the name ts2, this query works for me:

    Code MySQL:
    SELECT DISTINCT
      tmp.id
    FROM 
      (
         SELECT id
         FROM ts1
         WHERE unixTimestamp < 1266428321
       UNION
         SELECT id
         FROM ts2
         WHERE unixTimestamp < 1266428321
      )
      AS tmp
    WHERE
       id IN
       (
         SELECT id
         FROM ts1
         WHERE unixTimestamp > 1266428321
       UNION
         SELECT id
         FROM ts2
         WHERE unixTimestamp > 1266428321
       )

    I'm pretty sure there are better (shorter, more efficient) queries possible though ...
    Yes, that query seems to preform what I need.

    I have two questions:

    1. Can I make it count the num of id's instead of returning a list?
    2. Does somebody know a shorter, more efficient way? :P

    Thanks,
    Marius

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    Untested

    Code SQL:
    SELECT
         t.id
         ,SUM(t.total) total
         ,t.select_num
      FROM
         (SELECT
               id
               ,COUNT(*) total
               ,1 select_num
            FROM
               table1
           WHERE
               unixTimestamp > 1266428321
           GROUP
              BY
               id
       UNION ALL
          SELECT
    	       id
    	       ,COUNT(*)
    	       ,4
            FROM
               table2
           WHERE
               unixTimestamp > 1266428321
           GROUP
              BY
               id
       UNION ALL
          SELECT
    	       id
    	       ,COUNT(*)
    	       ,2
            FROM
               table1
           WHERE
               unixTimestamp < 1266428321
           GROUP
              BY
               id
       UNION ALL
          SELECT
    	       id
    	       ,COUNT(*)
    	       ,3
            FROM
               table2
           WHERE
               unixTimestamp < 1266428321
           GROUP
              BY
               id) t
     GROUP
        BY
         t.id
    HAVING
         MIN(t.select_num) = 1
       AND
         MAX(t.select_num) = 4
       AND
         AVG(t.select_num) BETWEEN 2 AND 3


Bookmarks

Posting Permissions

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