Intereting Posts
Как написать sql-запрос, условия которого являются необязательными? Как расшифровать хранимую процедуру в SQL Server 2008 Ошибка запроса SQL Server: «Отсутствующий или неполный оператор выбора» как обновить все таблицы с определенным именем столбца LINQ to Entities не распознает метод «System.String ToString ()», и этот метод не может быть переведен в выражение хранилища Как добавить условие в 'order by'? Использование SSIS для извлечения XML-представления данных таблицы в файл Обновить другую таблицу после вставки с помощью триггера? Как включить службы интеграции (SSIS) в SQL Server 2008? Создайте SQL INSERT Script со значениями, собранными из таблицы Ссылка на таблицы внешнего запроса в подзапросе Как получить эксклюзивную блокировку базы данных firebird для выполнения изменений схемы? Проверка достоверности данных SSIS для передачи данных объединить две таблицы вместе со счетчиками записей из второй таблицы на основе условия MySQL: разница между `… ADD INDEX (a); … ADD INDEX (b); `и` … ADD INDEX (a, b); `?

Роллинг Daily Distinct Counts

У нас есть таблица со следующими столбцами:

SESSION_ID USER_ID CONNECT_TS -------------- --------------- --------------- 1 99 2013-01-01 2:23:33 2 101 2013-01-01 2:23:55 3 104 2013-01-01 2:24:41 4 101 2013-01-01 2:24:43 5 233 2013-01-01 2:25:01 

Нам нужно получать определенное количество пользователей за каждый день и количество активных пользователей, которые определяются как пользователи, которые использовали приложение за последние 45 дней. Вот что мы придумали, но я чувствую, что должен быть лучший способ:

 select trunc(a.connect_ts) , count(distinct a.user_id) daily_users , count(distinct b.user_id) active_users from sessions a join sessions b on (b.connect_ts between trunc(a.connect_ts) - 45 and trunc(a.connect_ts)) where a.connect_ts between '01-jan-13' and '12-jun-13' and b.connect_ts between '01-nov-12' and '12-jun-13' group by trunc(a.connect_ts); 

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

Первое, что нужно сделать, – создать список дней, в которые вы заинтересованы:

 select (trunc(sysdate, 'yyyy') -1) + level as ts_day from dual connect by level <= to_number( to_char(sysdate, 'DDD' ) ) 

Это приведет к созданию таблицы дат от 01-JAN в этом году до сегодняшнего дня. Присоедините свою таблицу к этому подзапросу. Использование перекрестного соединения может быть не особенно эффективным, в зависимости от того, сколько данных у вас в диапазоне. Поэтому, пожалуйста, рассматривайте это как доказательство концепции и настройте по мере необходимости.

 with days as ( select (trunc(sysdate, 'yyyy') -1) + level as ts_day from dual connect by level <= to_number( to_char(sysdate, 'DDD' ) ) ) select days.ts_day , sum ( case when trunc(connect_ts) = ts_day then 1 else 0 end ) as daily_users , sum ( case when trunc(connect_ts) between ts_day - 45 and ts_day then 1 else 0 end ) as active_users from days cross join sessions where connect_ts between trunc(sysdate, 'yyyy') - 45 and sysdate group by ts_day order by ts_day / 

Если ваша версия Oracle поддерживает WITH-заявления, это может помочь вам:

 with sel as ( select trunc(a.connect_ts) as logon_day , count(distinct user_id) as logon_count from sessions group by trunc(connect_ts) ) select s1.logon_day , s1.logon_count as daily_users , (select sum(logon_count) from sel where logon_day between s1.logon_day - 45 and s1.logon_day) as active_users from sel s1 

в противном случае вам придется написать его таким образом (который выполняется намного медленнее …):

 select sel.logon_day , sel.logon_count as daily_users , (select count(distinct user_id) as logon_count from t_ad_session where trunc(connect_ts) between sel.logon_day - 45 and sel.logon_day) as active_users from (select trunc(connect_ts) as logon_day, count(distinct user_id) as logon_count from t_ad_session group by trunc(connect_ts)) sel