Aggregate over a sliding window with only equi-joins

Question

I would like to calculate the total unique accounts in a 30 day range. The engine I am using on MySQL (InfiniDB) seems to only support joining tables on equality conditions.

My table looks like this:

sessions (date_id, account_id) =
{ '2013-07-07', 5
  '2013-07-07', 5
  '2013-07-07', 1
  '2013-07-08', 3
  '2013-07-09', 3
}

My current query looks like this:

SELECT
  date_id, (
    SELECT COUNT(DISTINCT account_id)
    FROM sessions t2
    WHERE t2.date_id BETWEEN DATE_SUB(t1.date_id, INTERVAL 30 DAY) AND t1.date_id
  ) cnt
FROM sessions t1
GROUP BY date_id

Only equi-joins are supported so the BETWEEN operator does not work to join the tables.

Any help is greatly appreciated :)

score 0 · Answer 1 · answered Nov 14 '13 at 20:15

I'm not particularly familiar with InfiniDB, but what i think would be cleaner is:

SELECT
  t1.date_id,
  COUNT(DISTINCT t2.account_id) AS Uniques
FROM sessions t1
INNER JOIN sessions t2 
  ON t2.date_id BETWEEN DATE_SUB(t1.date_id, INTERVAL 30 DAY) AND t1.date_id
GROUP BY t1.date_id;

Result:

DATE_ID                   UNIQUES
July, 07 2013 00:00:00+0000   2
July, 08 2013 00:00:00+0000   3
July, 09 2013 00:00:00+0000   3

sqlfiddle demo

Is this what you said it wasn't supported?

Thanks for the help. Unfortunately, its not supported. That query has the same problem of joining using inequality. — beefyhalo, Nov 14 '13 at 21:52

score 0 · Answer 2 · edited Apr 15 '14 at 02:02

0

Starting InfiniDB 4.0 you can do such calculation with Windowing function: You query look like following

SELECT date_id, COUNT(act_id)  AS Uniques
FROM ( SELECT date_id, 
              DISTINCT(account_id) OVER (PARTITION BY date_id RANGE INTERVAL 30 DAY PRECEDING) act_id
       FROM sessions ) t1
GROUP BY date_id

This would give you the expected results

edited Apr 15 '14 at 02:02

mrjimoy_05

3,452
9
58
95

answered Apr 15 '14 at 01:43

Dipti Joshi

1
1

Thanks for spending the time to submit an answer. InfiniDB's windowing functions would certainly be useful here, but unfortunately the query doesn't work: "error in your SQL syntax ... near 'DISTINCT(account_id) ..." – beefyhalo Apr 17 '14 at 15:29
Hello: Please try this query instead: SELECT date_id, uniques FROM ( SELECT date_id, COUNT(DISTINCT(account_id)) OVER (PARTITION BY date_id RANGE INTERVAL 30 DAY PRECEDING) as Uniques FROM sessions) t1 GROUP by date_id, uniques – Dipti Joshi Apr 24 '14 at 21:43

Aggregate over a sliding window with only equi-joins

2 Answers2