Sqlite3: нужно к Декартову В дату
Установите emacs + выбор gnuwin32 пакетов.
Также proexp для замены диспетчера задач.
4 ответа
Отличный вопрос!
Вот запрос, который, как я думаю, дает вам то, что вы хотите, без использования отдельной таблицы. Обратите внимание, что это не проверено (поэтому, вероятно, содержит ошибки), и я предположил, что datetime - это столбец типа int с # секунд, чтобы избежать тонны strftime.
select sum(concurrent_period) from (
select min(end_table.datetime - begin_table.begin_time) as concurrent_period
from (
select g1.datetime, g1.num_end, count(*) as concurrent
from (
select datetime, count(*) as num_end
from games group by datetime
) g1, games g2
where g2.datetime >= g1.datetime and
g2.datetime-g2.duration < g1.datetime and
g1.datetime >= strftime('%s','now') - 24*60*60 and
g1.datetime <= strftime('%s','now')+0
) end_table, (
select g3.begin_time, g1.num_begin, count(*) as concurrent
from (
select datetime-duration as begin_time,
count(*) as num_begin
from games group by datetime-duration
) g3, games g4
where g4.datetime >= g3.begin_time and
g4.datetime-g4.duration < g3.begin_time and
g3.begin_time >= strftime('%s','now') - 24*60*60 and
g3.begin_time >= strftime('%s','now')+0
) begin_table
where end_table.datetime > begin_table.begin_time
and begin_table.concurrent < 5
and begin_table.concurrent+begin_table.num_begin >= 5
and end_table.concurrent >= 5
and end_table.concurrent-end_table.num_end < 5
group by begin_table.begin_time
) aah
Основная идея состоит в том, чтобы создать две таблицы: одну с количеством одновременных игр в начале каждой игры, и один с количеством одновременных игр в конце. Затем объедините столы вместе и возьмите строки только в «критических точках», где количество одновременных игр пересекает 5. Для каждого критического времени начала возьмите критическое время окончания, которое произошло раньше всего и которое, надеюсь, дает все периоды, в которых выполнялось не менее 5 игр. одновременно.
Надеюсь, это не слишком запутанно, чтобы быть полезным!
Во-первых, я не могу придумать способ решить вашу проблему, создав таблицу на лету или без помощи дополнительной таблицы. Извините.
Я предлагаю вам положиться на статическую таблицу чисел.
Создайте фиксированную таблицу в формате:
CREATE TABLE Numbers (
number INTEGER PRIMARY KEY
);
Заполните ее количеством секунд в 24 часах (24 * 60 * 60 = 84600) . Я бы использовал любой язык сценариев, чтобы сделать это, используя оператор insert:
insert into numbers default values;
Теперь в таблице Numbers есть числа от 1 до 84600. Ваш запрос будет изменен следующим образом:
select count(*)
from (
select count(*) as concurrent, strftime('%s','now') - 84601 + n.number second
from games g, numbers n
where strftime('%s',datetime)+0 >= strftime('%s','now') - 84601 + n.number and
strftime('%s',datetime)-duration <= strftime('%s','now') - 84601 + n.number
group by second) x
where concurrent >=5
Без процедурного языка в смеси, то есть я думаю, лучшее, что ты сможешь сделать.
Кевин скорее опередил меня до кульминации (+1), но я опубликую этот вариант, поскольку он хоть немного отличается.
Ключевые идеи:
- Сопоставить данные в потоке событий с атрибутами «время» и «полярность» (= начало или конец игры)
- Сохраняйте текущую сумму того, сколько игр открыто во время каждого события (это делается путем формирования самосоединения в потоке событий)
- Найдите время события, когда количество игр (как говорит Кевин) увеличивается до 5 или до 4
- Небольшая хитрость: сложите все до 4 раз и убираем до 5 - порядок не важен
- Результат - количество секунд, проведенных с 5 или более открытыми играми
У меня нет sqllite, поэтому я тестировал MySQL и не удосужился ограничить временное окно, чтобы сохранить некоторое здравомыслие. Исправить должно быть несложно.
Кроме того, что более важно, я не думал, что делать, если игры открыты в начале или в конце периода!
Что-то подсказывает мне, что нужно большое упрощение. были здесь, но я еще не заметил этого.
SELECT SUM( event_time )
FROM (
SELECT -ga.event_type * ga.event_time AS event_time,
SUM( ga.event_type * gb.event_type ) event_type
FROM
( SELECT UNIX_TIMESTAMP( g1.endtime - g1.duration ) AS event_time
, 1 event_type
FROM games g1
UNION
SELECT UNIX_TIMESTAMP( g1.endtime )
, -1
FROM games g1 ) AS ga,
( SELECT UNIX_TIMESTAMP( g1.endtime - g1.duration ) AS event_time
, 1 event_type
FROM games g1
UNION
SELECT UNIX_TIMESTAMP( g1.endtime )
, -1
FROM games g1 ) AS gb
WHERE
ga.event_time >= gb.event_time
GROUP BY ga.event_time
HAVING SUM( ga.event_type * gb.event_type ) IN ( -4, 5 )
) AS gr
Почему бы вам не обрезать дату и не оставить только время, если вы фильтруете данные для любой заданной даты, каждый раз уникальной. Таким образом, вам понадобится только таблица с числами от 1 до 86400 (или меньше, если вы возьмете большие интервалы), вы можете создать два столбца «от» и «до» для определения интервалов. Я не знаком с функциями SQLite, но, согласно руководству, вы должны использовать функцию strftime в следующем формате: ЧЧ: ММ: СС.