Диапазон SQL между прошедшей датой и неограниченным предшествующим

Структура таблицы следующая

country_id | ID | date | same_week_count

Меня интересует номер недели, поэтому я извлекаю его из окончательного вывода.

Я хотел бы подсчитать, сколько раз идентификатор появлялся до этой недели в каждой стране, поэтому я использую

count(ID) OVER (PARTITION BY country_id, ID ORDER BY to_number(date, 'IW') RANGE UNBOUNDED PRECEDING) as ttl_count

но получаю следующее:

 country_id  |  ID   |  date  | same_week_count | ttl_count
------------------------------------------------------------
   1         |  123  |   35   |   2             |   1
   1         |  123  |   35   |   2             |   3

Откуда взялась тройка? Включает ли неограниченное количество подписок текущую неделю или нет? Должен ли я иметь выражение, подобное RANGE BETWEEN to_number(date, 'IW')-1 PRECEDING AND UNBOUNDED PRECEDING?


sql oracle range window-functions
person katz    schedule 01.10.2014    source источник

Ответы (1)


arrow_upward
2
arrow_downward

Когда вы пишете RANGE UNBOUNDED PRECEDING, это сокращенный синтаксис для RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Таким образом, ваш RANGE UNBOUNDED PRECEDING действительно включает номер текущей недели. Счетчик в данной строке будет включать все строки (внутри раздела), имеющие тот же номер недели или меньше, что и сама строка, даже если эти строки на самом деле идут после строки. сам. Это определение RANGE BETWEEN - иначе ROWS BETWEEN может быть альтернативой.

Но поскольку вы хотите, чтобы количество этих строк в каждом разделе, который имеет номер недели меньше, чем тот же, вы захотите использовать что-то вроде RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING, которое будет включать номера недель до включительно, на один меньше, чем текущая строка .

Еще одна проблема, которая у вас есть, - что происходит, когда вы пересекаете год? Предполагается ли, что это будет использоваться только для данных в течение одного календарного года? На 3-й неделе 2015 г., хотите ли вы включить данные с 51-й недели 2014 г.? Если да, то будьте осторожны со своим TO_NUMBER выражением - может быть, лучше сделать TRUNC(date,'IW'), как в:

count(ID) OVER (
  PARTITION BY country_id, ID
  ORDER BY trunc(date, 'IW')
  RANGE BETWEEN UNBOUNDED PRECEDING AND 7 PRECEDING
) as ttl_count

TRUNC делает дату понедельником каждой недели. 7 PRECEDING, то раньше было 7 дней.

person Kim Berg Hansen    schedule 01.10.2014
comment
Спасибо за развернутый ответ! Это было именно то, что я искал! - person katz; 01.10.2014