Лучший способ запустить SQL-запрос для разных дат
У меня есть приложение PHP, которое записывает сеансы различных устройств, подключенных к серверу. В базе есть таблица session со столбцами device_id, start_date, end_date. Чтобы узнать количество устройств, подключенных в определенную дату, я могу использовать запрос:
SELECT COUNT(DISTINCT device_id)
FROM session
WHERE :date >= start_date AND (:date <= end_date OR end_date IS NULL)
где :date передается в качестве параметра подготовленному оператору.
Это прекрасно работает, но если я хочу узнать количество устройств на каждый день в году, это требует выполнения 365 запросов, и я боюсь, что все может стать очень медленным. Кажется неправильным повторять дату в PHP, мне кажется, что должен быть более оптимальный способ сделать это с помощью одного запроса к базе данных.
Можно ли это сделать одним запросом?
Будет ли это на самом деле быстрее, чем повторять дату в PHP при выполнении нескольких запросов?
РЕДАКТИРОВАТЬ, чтобы ответить на комментарии:
Мне нужно число для каждого отдельного дня (например, чтобы нарисовать график), а не просто сумма
тип данных ДАТА
Конечно, вам может понадобиться немного изменить запрос, чтобы он проверял, является ли start_date больше, чем первый параметр даты, а также меньше, чем второй. Или вам нужно увидеть, соответствует ли сеанс перекрывается диапазону дат? например если дата начала предшествует первой дате, а дата окончания — после нее, хотите ли вы, чтобы она была включена в результаты?
Вы хотите результаты за каждый день в этом диапазоне? И каков тип данных дат начала и окончания?
Ответы 1
Если я правильно понимаю, то сначала нужна таблица дат, что-то вроде:
create table dates(dt date);
insert into dates(dt) values
('2001-01-01'),
('2001-01-02'),
...
('2100-12-31')
И используйте такой запрос:
select dates.dt, count(session.device_id)
from dates
join session on start_date <= dates.dt and (dates.dt <= end_date or end_date is null)
-- change to left join to include zero counts
where dates.dt >= :date1 and dates.dt <= :date2
group by dates.dt
PS: поскольку вы упомянули диаграммы, я мог бы добавить, что можно избежать таблицы дат. Однако результат будет содержать только даты изменения количества устройств. API-интерфейсы диаграмм обычно принимают такие данные, но по-прежнему создают точки данных для всех промежуточных дат.
Это сработало, спасибо, я бы не подумал добавить еще одну таблицу. Я должен не забыть обновить его до 2100 года!
Если устройство может отображаться дважды на одном dt, вам может понадобиться DISTINCT внутри COUNT().
Вы пытались просто установить дату начала на 2021-01-01 и дату окончания на 2021-12-31 (например)? Это даст вам все сеансы в течение года. Вы наложили на себя ограничение, используя одно значение
:dateдважды для двух разных параметров в вашем SQL, а это означает, что вы никогда не сможете просмотреть более одной даты.