Подсчет количества текущей записи в окне с рамкой по времени

Question

Нужно посчитать, сколько раз встречается текущее id за последние 30 дней

WITH a AS (
            SELECT   timestamp / 24 / 3600   AS day,
                     client_id               AS id
            FROM dataset)
SELECT  day,
        id,
        COUNT(id) OVER w                     AS cnt
FROM a
window w AS (ORDER BY day
             RANGE BETWEEN 30 PRECEDING AND CURRENT ROW)

Answer 1

Я правильно понимаю, что надо для каждой строки показать, сколько раз id встретился до этого в течение прошедших 30 дней?

Тогда так:

SELECT 
  client_id, timestamp
  /* если надо дату, можно так DATE(FROM_UNIXTIME(timestamp)) day */
  ,(SELECT COUNT(*) 
    FROM dataset d2 
    WHERE d2.client_id=d1.client_id 
    AND d2.timestamp BETWEEN d1.timestamp-30*86400 AND d1.timestamp
   ) cnt
FROM dataset d1

Это при условии, что timestamp у вас просто число. Но если это настоящий тип timestamp, то второе условие обязательно заменить на

AND d2.timestamp BETWEEN d1.timestamp-INTERVAL '30' day AND d1.timestamp

Для шустрой работы нужен комбинированный индекс по client_id, timestamp именно в таком порядке.

И ещё важный момент. BETWEEN берет интервал включая концы [a,b]. Если преобразовать в даты, получим 31 день. Так что если нужны интервалы (...),[...),(...] надо использовать <, >, >=, <=

Answer 2

Вот, скажем, решение для MySQL (таблица и поля переименованы, показываю принцип)

SELECT id, created_at, 
       COUNT(created_at) OVER w AS amount_previous_30_day
FROM test
WINDOW w AS (
  PARTITION BY id
  ORDER BY created_at
  RANGE BETWEEN INTERVAL 30 DAY PRECEDING AND CURRENT ROW)
ORDER BY id, created_at;

fiddle

БЛОГ НА HUSL

Подсчет количества текущей записи в окне с рамкой по времени

Ответы (2 шт):