SQL-виклик для повернення відліку по днях місяця з багатоденними елементами

Я намагаюся конденсувати декілька викликів SQL в один і хотів би знати, чи можливі наступні.

Ось мої таблиці.

Organization
    id

Event
    id,
    startDate
    endDate

FavoriteOrgs
    userId
    orgId

Для кожного дня місяця я хочу повернути кількість подій, що відбуваються в цей день. Не надто складно, поки ви не додасте того, що події можуть тривати 2 або 3 дні .

Ось що я маю до сих пір, який точно показує кількість подій у день, але він включає лише подію в рахунку за день, коли вона починається.

SELECT DAYOFMONTH( CAST( o.start_date AS DATE ) ) AS dayNum, COUNT( * ) AS count
    FROM favoriteOrgs f, event e, organization o
    WHERE f.user_id =200372
    AND e.profile_id = o.id AND e.profile_id = f.profile_id AND o.id = f.profile_id
    AND e.last_date >=  '$startDate'
    AND e.start_date <=  '$lastDate'
    GROUP BY e.start_date
2
Чи можете ви розмістити свою схему
додано Автор hafichuk, джерело
Це не схема. Я не знаю, що це таке. У вашому клієнті MySQL запустіть "організацію DESC" тощо. Див. .com/doc/refman/5.0/en/description.html .
додано Автор hafichuk, джерело
Я розмістив його в питанні, але тут це знову: організація (id); event (id, startDate, endDate); Улюблені повідомлення (userId, orgId)
додано Автор Jeremy Penrod, джерело
Ах. ID є ints. startDate і endDate є мітками часу.
додано Автор Jeremy Penrod, джерело

1 Відповіді

Ваша модель даних насправді не моделює всі об'єкти у вашій системі. Однією суттю є дати, на які ви зацікавлені у повідомленні про речі.

Вам слід додати таблицю до бази даних (або скористатися тимчасовою таблицею, вбудованою таблицею або будь-якою іншою доступною з MySQL таблицею). Таблиця просто всі відповідні дати. Навіть таблиця, яка повертається до 1900 року і переходить до 2100, буде досить маленькою.

Після цього запит стає тривіальним:

CREATE TABLE Calendar (
    calendar_date DATE NOT NULL,
    CONSTRAINT PK_Calendar PRIMARY KEY CLUSTERED
)

SELECT
    C.calendar_date,
    COUNT(*) AS count
FROM
    favoriteOrgs F
INNER JOIN Event E ON
    E.profile_id = F.profile_id AND
    E.last_date >= '$startDate' AND
    E.start_date <= '$lastDate'
INNER JOIN Organization O ON
    O.id = F.profile_id AND
    O.id = E.profile_id
WHERE
    F.user_id = 200372
GROUP BY
    C.calendar_date

Тепер у вас також є перевага, що до календарних дат можна додати додаткову інформацію про компанію, наприклад стовпець "is_holiday", "economic_quarter" або інше. Просто не забудьте заповнити таблицю, яка є простим циклом, який ви повинні запустити один раз.

3
додано
Якщо ви тільки хочете, щоб вона була розбита на день, то я не думаю, що код відрізняється. Я людина MS SQL, хоча і вони мають гнусний тип даних TIMESTAMP, тому я не впевнений, як MySQL обробляє його. Якщо ви бажаєте, щоб вона була розбита на годину або на щось, то ви можете просто JOIN у створеному наборі чисел від 1 до 24 і відрахувати на основі цих даних разом з датами. Ключовим є мислення вашого набору відра як самого набору даних.
додано Автор Tom H, джерело
Ого. Відмінна відповідь, спасибі. Пробачте мою недосвідченість тут, але в даний час 'столом' 'стовпчика' 'початок' 'і' 'endDate' 'стовпці є часовими мітками - як можливості мають початковий і кінцевий час, а також дати. Чи є спосіб використовувати це рішення з мітками часу?
додано Автор Jeremy Penrod, джерело