ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Проблема в составлении запроса, которая, очевидно, больше моего мозга
10 сообщений из 10, страница 1 из 1
Проблема в составлении запроса, которая, очевидно, больше моего мозга
    #39087461
neigenoir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
neigenoir
Гость
Доброго времени суток. Я далеко не мастер мускула в сложных запросах или даже просто когда нужны джоины - стопорюсь, посему нужна ваша помощь.

Есть таблица в БД, записи в которой имеют столбец даты в формате unix. Нужно получить список, скажем, 30 последних дней с количеством записей привязанных к этому дню тем самым столбцом даты. Причем если в какой то день записей не было, нужно получить таки строчку с 0. 30 дней - это должен быть параметр. вместо дня тоже хотелось бы иметь возможность подставить любой интервал.

Сам я дошел пока только до такого:

SELECT COUNT(id) FROM table WHERE 1 GROUP BY date/(60*60*24) ORDER BY date DESC LIMIT 30

это выдает количество записей в дни, с которыми есть хоть бы одна запись, пропуская дни с 0 записей. Плюс в выдачу пролезают лишние строчки - дни более месячной давности.

размышления привели меня к такому возможному решению: если добавить условие WHERE date>UNIX_TIMESTAMP()-(30)*(60*60*24) - то не будут возвращаться лишние строчки и мускул не работает лишнего. Теперь уже я гипотетически могу силами пхп добавить сколько надо нулевых дней и отсортировать их в нужной последовательности.

Вот только это дико некрасивое решение. Подскажите как грамотнее решить эту проблему?
...
Рейтинг: 0 / 0
26.10.2015, 23:52:14
| Ответить | Цитировать | Написать  
Проблема в составлении запроса, которая, очевидно, больше моего мозга
    #39087464
anvano
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
anvano
Участник
Ну, т.к. вам требуются даты, которые явно отсутствуют в исходных данных, то скорее всего вам не поможет ничего, кроме внешнего джойна с заранее сгенерированной таблицей с датами.

Тут уже два варианта:
1) Генерить таблицу с датами за указанный период на лету (можно поискать решения в интернете)
2) Иметь в БД заранее сгенеренную таблицу с датами на десяток лет назад/вперед, в зависимости от того, какие периоды предполагаются в исходных данных.

Второй вариант при наличии индекса и заранее наложенных ограничениях на период будет скорее всего быстрее.
...
Рейтинг: 0 / 0
27.10.2015, 00:00:22
| Ответить | Цитировать | Написать  
Проблема в составлении запроса, которая, очевидно, больше моего мозга
    #39087467
neigenoir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
neigenoir
Гость
anvano,

Прошу прощения, но это выглядит не менее кривым решением, чем добавление отсутствующих дат средствами пхп. Значит, вариантов нет?
...
Рейтинг: 0 / 0
27.10.2015, 00:08:05
| Ответить | Цитировать | Написать  
Проблема в составлении запроса, которая, очевидно, больше моего мозга
    #39087470
anvano
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
anvano
Участник
Ну, наличие в БД заранее заполненной таблицы с днями на несколько лет - вполне себе стандартное решение для такого рода задач.
Ничего в этом особо кривого нет. Более того, в эту табличку можно всякие доп. данные по дням внести, заранее предрасчитанные, типа является ли день праздником или выходным днем и т.п. что тоже может использоваться в отчетах/расчетах
...
Рейтинг: 0 / 0
27.10.2015, 00:20:59
| Ответить | Цитировать | Написать  
Проблема в составлении запроса, которая, очевидно, больше моего мозга
    #39087475
neigenoir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
neigenoir
Гость
anvano,

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

А как должен выглядет запрос при наличии такой таблицы? - в джойнах я не силен
...
Рейтинг: 0 / 0
27.10.2015, 00:30:42
| Ответить | Цитировать | Написать  
Проблема в составлении запроса, которая, очевидно, больше моего мозга
    #39087478
neigenoir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
neigenoir
Гость
а еще, неужели в мускуле нет подобия функции range из php?

range(5,9); // ~~ array(5,6,7,8,9);
...
Рейтинг: 0 / 0
27.10.2015, 00:34:04
| Ответить | Цитировать | Написать  
Проблема в составлении запроса, которая, очевидно, больше моего мозга
    #39087485
anvano
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
anvano
Участник
Готовый код для создания и заполнения таблицы с датами за любой период
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
DROP PROCEDURE IF EXISTS FillCalendar;
DROP TABLE IF EXISTS calendar;
CREATE TABLE IF NOT EXISTS calendar(calendar_date DATE NOT NULL PRIMARY KEY);

DELIMITER $$
    CREATE PROCEDURE FillCalendar(start_date DATE, end_date DATE)
    BEGIN
    DECLARE crt_date DATE;
    SET crt_date = start_date;
    WHILE crt_date <= end_date DO
        INSERT IGNORE INTO calendar VALUES(crt_date);
        SET crt_date = ADDDATE(crt_date, INTERVAL 1 DAY);
    END WHILE;
    END$$
DELIMITER ;

CALL FillCalendar('2013-01-01', '2013-01-03');
CALL FillCalendar('2013-01-01', '2013-01-07');




При наличии таблицы внешнее соединение типа такого (если в t.date у вас правда линуксовый timestamp)

Код: plsql
1.
2.
3.
4.
5.
SELECT c.calendar_date, COUNT(t.id) cnt
FROM calendar c
LEFT JOIN table  t ON (DATE(FROM_UNIXTIME(t.date)) = c.calendar_date) 
GROUP BY c.calendar_date
ORDER BY 1 DESC LIMIT 30
...
Рейтинг: 0 / 0
27.10.2015, 00:51:55
| Ответить | Цитировать | Написать  
Проблема в составлении запроса, которая, очевидно, больше моего мозга
    #39087489
Фотография Lumix
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lumix
Участник
лимит уже выполняет функцию ограничения:
Код: sql
1.
select round(date / 86400) d, count(*) c from t group by 1 desc limit 30;



если мы сами фильтруем, то лимит лишний
Код: sql
1.
2.
3.
select round(date / 86400) d, count(*) c 
    from (select date from t where date > unix_timestamp() - 2592000) x 
group by 1 desc;



neigenoirВот только это дико некрасивое решение. Подскажите как грамотнее решить эту проблему?
Это наоборот дико красивое решение и так делают все трудяги.
Любые танцы доп.таблицами - это наоборот снижает красоту.

Код: php
1.
2.
3.
4.
$init = round((mktime() - 86400 * 30) / 86400);
foreach(get() as $v) if ((($i = ($v["d"] - $init)) >= 0) && $i < 30) $list[$i] = $v["c"]; 
for($i = 0; $i < 30; $i++) $list[$i] = isset($list[$i]) ? $list[$i] : 0;
ksort($list);
...
Рейтинг: 0 / 0
27.10.2015, 01:01:33
| Ответить | Цитировать | Написать  
Проблема в составлении запроса, которая, очевидно, больше моего мозга
    #39087498
neigenoir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
neigenoir
Гость
anvano,

огромное спасибо. Вроде все понял и осознал. Благо, времени на проект еще прорва - попробую.


Lumix,

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

Еще раз всем спасибо!
...
Рейтинг: 0 / 0
27.10.2015, 02:13:21
| Ответить | Цитировать | Написать  
Проблема в составлении запроса, которая, очевидно, больше моего мозга
    #39087576
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
neigenoirэто выглядит не менее кривым решением, чем добавление отсутствующих дат средствами пхп.
Перекладывание на клиента работы по обработке данных, которые ДОЛЖНЫ обрабатываться на сервере - именно это и есть КРИВОЕ решение.

anvanoГотовый код для создания и заполнения таблицы с датами за любой период
Перед циклом заполнения таблицы calendar надо сделать её TRUNCATE.
...
Рейтинг: 0 / 0
27.10.2015, 09:05:06
| Ответить | Цитировать | Написать  
10 сообщений из 10, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Проблема в составлении запроса, которая, очевидно, больше моего мозга
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&msg=39087467&tid=1832573]:
 0ms
get settings:
 8ms
get forum list:
 14ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 62ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 58ms
get tp. blocked users:
 2ms
others: 238ms
total:  400ms
созд. / загр.: 400ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]