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

Databases
1

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?


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

Supposing “table 1” has the name ts1 and “table 2” has the name ts2, this query works for me:


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 …

3

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? :stuck_out_tongue:

Thanks,
Marius

4

Untested


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