Блог

Скажем, в MonetDB (в частности, встроенная версия из пакета R «MonetDBLite») У меня есть таблица «события», содержащая идентификационные коды объектов и даты начала и окончания события в формате:

 | id  | start_date  | end_date   |
| 1   | 2010-01-01  | 2010-03-30 |
| 1   | 2010-04-01  | 2010-06-30 |
| 2   | 2018-04-01  | 2018-06-30 |
| ... | ...         | ...        |
  

Таблица содержит примерно 80 миллионов строк событий, относящихся примерно к 2,5 миллионам уникальных объектов (значения идентификаторов). Даты, похоже, хорошо совпадают с календарными кварталами, но я не проверял их тщательно, поэтому предполагаю, что они могут быть произвольными. Однако я, по крайней мере, проверил их на предмет end_date> start_date .

Я хочу создать таблицу «nonevent_qtrs», в которой перечислены календарные кварталы, в которых идентификатор не записан, например:

 | id  | last_doq   |
| 1   | 2010-09-30 |
| 1   | 2010-12-31 |
| ... | ...        |
| 1   | 2018-06-30 |
| 2   | 2010-03-30 |
| ... | ...        |
  

(doq = день квартала)

Если масштаб события охватывает любые дни квартала (включая первую и последнюю даты), то я хочу, чтобы оно считалось произошедшим в этом квартале.

Чтобы помочь с этим, я создал «календарную таблицу»; таблица кварталов «qtrs», охватывающая весь диапазон дат, присутствующих в «событиях», и формата:

 | first_doq  | last_doq   |
| 2010-01-01 | 2010-03-30 |
| 2010-04-01 | 2010-06-30 |
| ...        | ...        |
  

И попытался использовать неравномерное слияние следующим образом:

 create table nonevents
as select
    id,
    last_doq
from
    events
    full outer join
    qtrs
on
    start_date > last_doq or
    end_date < first_doq
group by
    id,
    last_doq
  

Но это а) ужасно неэффективно и б) определенно неправильно, поскольку большинство идентификаторов указаны как не связанные с событиями для всех кварталов.

Как я могу создать таблицу «nonevent_qtrs», которую я описал, которая содержит список кварталов, для которых каждый идентификатор не имел событий?

Если это уместно, конечным вариантом использования является вычисление прогонов, не связанных с событиями, для анализа и прогнозирования времени до события. Похоже, потребуется кодирование длины выполнения. Если есть более прямой подход, чем тот, который я описал выше, то я весь внимание. Единственная причина, по которой я для начала фокусируюсь на запусках без событий, — это попытка ограничить размер перекрестного продукта. Я также рассматривал возможность создания чего-то вроде:

 | id  | last_doq   | event |
| 1   | 2010-01-31 | 1     |
| ... | ...        | ...   |
| 1   | 2018-06-30 | 0     |
| ... | ...        | ...   |
  

Но, хотя это более полезно, это может быть невозможно из-за размера используемых данных. Широкий формат:

 | id  | 2010-01-31 | ... | 2018-06-30 |
| 1   | 1          | ... | 0          |
| 2   | 0          | ... | 1          |
| ... | ...        | ... | ...        |
  

также было бы удобно, но поскольку MonetDB является хранилищем столбцов, я не уверен, является ли это более или менее эффективным.

Позвольте мне предположить, что у вас есть таблица кварталов с датой начала квартала и датой окончания. Вам действительно нужно это, если вы хотите, чтобы кварталы, которые не существуют. В конце концов, как далеко назад во времени или вперед во времени вы хотите вернуться?

Затем вы можете сгенерировать все комбинации id / quarter и отфильтровать те, которые существуют:

 select i.id, q.*
from (select distinct id from events) i cross join
     quarters q left join
     events e
     on e.id = i.id and
        e.start_date <= q.quarter_end and
        e.end_date >= q.quarter_start
where e.id is null;