ReSQL.ru
     
powered by simpleCommunicator - 2.0.59     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Выбор нескольких подмножеств из подмножества
18 сообщений из 18, страница 1 из 1
Выбор нескольких подмножеств из подмножества
    #39707449
Tachyon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Tachyon
Гость
Здравствуйте. Возник вопрос, буду благодарен за помощь.
Есть две таблицы: список платежных терминалов (devices) и список платежей (paylog). Необходима выборка по каждому терминалу сумма платежей за день, неделю, месяц, год и произвольный период с возможностью сортировки по любому полю.

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
CREATE TABLE IF NOT EXISTS `devices` (
	`uid` MEDIUMINT(6) UNSIGNED NOT NULL AUTO_INCREMENT,
	`mac` VARBINARY(6) NOT NULL,
	`city` TINYINT UNSIGNED DEFAULT NULL,
	`area` SMALLINT UNSIGNED DEFAULT NULL,
	`party` SMALLINT UNSIGNED DEFAULT NULL,
	`adress` VARCHAR(256) DEFAULT NULL,
	PRIMARY KEY (`uid`),
	UNIQUE KEY (`mac`),
	UNIQUE KEY (`uid`, `mac`),
	FOREIGN KEY (`city`) REFERENCES `cities` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
	FOREIGN KEY (`area`) REFERENCES `cityareas` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
	FOREIGN KEY (`party`) REFERENCES `party` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `paylog` (
	`uid` MEDIUMINT(6) UNSIGNED NOT NULL,
	`pid` BIGINT UNSIGNED NOT NULL,
	`paydat` DATETIME DEFAULT NULL,
	`paysum` INTEGER UNSIGNED NOT NULL DEFAULT '0',
	`code` TINYINT UNSIGNED DEFAULT NULL,
	PRIMARY KEY (`uid`, `pid`),
	FOREIGN KEY (`uid`) REFERENCES `devices` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



Я составил вот такой запрос, и он вроде как работает. Но меня мучают сомнения, как оно поведет себя, когда в базе будет 10000 терминалов и 100000 платежей в каждом.

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
SELECT 
	dev.uid,
	dev.adress,
	(SELECT IFNULL(SUM(paysum),'0') FROM paylog WHERE DATE(paydat) = CURRENT_DATE() AND uid = dev.uid AND code = '1') AS sum1,
	(SELECT IFNULL(SUM(paysum),'0') FROM paylog WHERE YEAR(paydat) = YEAR(NOW()) AND WEEK(paydat, 1) = WEEK(NOW(), 1) AND uid = dev.uid AND code = '1') AS sum2,
	(SELECT IFNULL(SUM(paysum),'0') FROM paylog WHERE YEAR(paydat) = YEAR(NOW()) AND MONTH(paydat) = MONTH(NOW()) AND uid = dev.uid AND code = '1') AS sum3,
	(SELECT IFNULL(SUM(paysum),'0') FROM paylog WHERE YEAR(paydat) = YEAR(NOW()) AND uid = dev.uid AND code = '1') AS sum4,
	(SELECT IFNULL(SUM(paysum),'0') FROM paylog WHERE DATE(paydat) BETWEEN STR_TO_DATE('$date1', '%d-%m-%Y') AND STR_TO_DATE('$date2', '%d-%m-%Y') AND uid = dev.uid AND code = '1') AS sum5
FROM
	(SELECT uid, adress FROM devices WHERE city IS NULL AND uid = '1' ORDER BY uid) AS dev
GROUP BY dev.uid ORDER BY sum1;
...
Рейтинг: 0 / 0
25.09.2018, 09:25
| Ответить | Цитировать | Написать  
Выбор нескольких подмножеств из подмножества
    #39707450
Tachyon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Tachyon
Гость
Пожалуйста, расскажите, если есть какой-то способ сделать это грамотнее и оптимальнее.
...
Рейтинг: 0 / 0
25.09.2018, 09:26
| Ответить | Цитировать | Написать  
Выбор нескольких подмножеств из подмножества
    #39707474
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Нафига тут подзапросы? просто

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
SELECT devices.*,
-- суммы в нужном количестве
       COALESCE(SUM (CASE WHEN paylog.paydat BETWEEN @period_start AND @period_end 
                          THEN paylog.paysum 
                     END), 0)
FROM devices, paylog 
WHERE paylog.uid = devices.uid
AND devices.uid = 1



Условия в CASE формируются как граничные - от и до. Никаких функций от paylog.paydat.
...
Рейтинг: 0 / 0
25.09.2018, 09:52
| Ответить | Цитировать | Написать  
Выбор нескольких подмножеств из подмножества
    #39707477
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Поскольку берутся данные по одному devices.uid - GROUP BY нафиг не нужен. А если надо получить по нескольким/всем - то добавить GROUP BY devices.uid (надеюсь, это поле - уникальное?).
...
Рейтинг: 0 / 0
25.09.2018, 09:53
| Ответить | Цитировать | Написать  
Выбор нескольких подмножеств из подмножества
    #39707523
Tachyon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Tachyon
Гость
Это в примере один uid. В норме uid будет BETWEEN x AND y.
...
Рейтинг: 0 / 0
25.09.2018, 11:17
| Ответить | Цитировать | Написать  
Выбор нескольких подмножеств из подмножества
    #39707529
Tachyon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Tachyon
Гость
PS: uid - разумеется, уникальное поле. В таблице devices это primary key.

А куда приспособить условие paylog.code = 1? В конец?
WHERE paylog.uid = devices.uid
AND devices.uid = 1
AND paylog.code = 1

Так? И в таком случае выведет ли с нулями те девайсы, для которых нет подходящих по условиям записей в paylog? (должно выводить все девайсы, чей uid попадает в заданный диапазон).
...
Рейтинг: 0 / 0
25.09.2018, 11:25
| Ответить | Цитировать | Написать  
Выбор нескольких подмножеств из подмножества
    #39707549
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Вы слишком упростили задачу, получив неадекватную модель и, соответственно, неадекватное начальной задаче решение. Старайтесь всегда сперва думать, сможете ли применить ответ...

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
SELECT devices.*,
-- суммы в нужном количестве
       COALESCE(SUM (CASE WHEN paylog.paydat BETWEEN @period_start AND @period_end 
                          THEN paylog.paysum 
                     END), 0)
FROM devices
LEFT JOIN paylog ON paylog.uid = devices.uid -- AND paylog.code IN (список отбора)
WHERE devices.uid IN (/* список отбора */)
GROUP BY devices.uid



Список отбора может задаваться как фиксированным набором IN (), так и диапазоном BETWEEN.
...
Рейтинг: 0 / 0
25.09.2018, 11:45
| Ответить | Цитировать | Написать  
Выбор нескольких подмножеств из подмножества
    #39707564
Tachyon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Tachyon
Гость
Я же вроде указал, что для выборки из devices может быть (а может не быть) несколько условий, по city, area, диапазону uid. Вот про диапазон uid да, не сказал.

Тогда еще пара вопросов: почему COALESCE? Вроде же IFNULL должно быть достаточно.

И еще, если сделать так:
Код: sql
1.
2.
3.
4.
SELECT devices.*, STR_TO_DATE('CURRENT_DATE() 23:59:59', '%Y-%m-%d %H:%i:%s) AS end
       COALESCE(SUM (CASE WHEN paylog.paydat BETWEEN @period_start AND end 
                          THEN paylog.paysum 
                     END), 0)



не будет ли лучше, чем в четырех выборках (текущие день, неделя, месяц и год) указывать функцию каждый раз?
...
Рейтинг: 0 / 0
25.09.2018, 12:02
| Ответить | Цитировать | Написать  
Выбор нескольких подмножеств из подмножества
    #39707580
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Tachyonдля выборки из devices может быть (а может не быть) несколько условий, по city, area, диапазону uid.Это всё запихивается во WHERE.

Tachyonесли сделать такНе страдайте ерундой. Это же для каждого отдельно взятого запроса константа, которая вычисляется один раз на запрос. А если притянуть переменные, мало того, что прозрачность запроса пострадает, так ещё ведь неизвестно, что взбредёт в голову планировщику - а ну как он начнёт всю эту ерунду вычислять для каждой записи?
...
Рейтинг: 0 / 0
25.09.2018, 12:21
| Ответить | Цитировать | Написать  
Выбор нескольких подмножеств из подмножества
    #39707603
Tachyon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Tachyon
Гость
Ок, понял, большое спасибо. Может быть, еще подскажете, как грамотно сформировать диапазоны

(сегодня 00:00:00) - (сегодня 23:59:59);
(последний понедельник 00:00:00) - (сегодня 23:59:59);
(1 число последнего месяца 00:00:00) - (сегодня 23:59:59);
(1 января последнего года 00:00:00) - (сегодня 23:59:59);
...
Рейтинг: 0 / 0
25.09.2018, 12:37
| Ответить | Цитировать | Написать  
Выбор нескольких подмножеств из подмножества
    #39707656
Tachyon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Tachyon
Гость
У меня получается как-то так, но, может быть, есть варианты оптимальнее?

Код: sql
1.
2.
3.
4.
BETWEEN STR_TO_DATE(CONCAT(CURRENT_DATE(), ' 00:00:00'), '%Y-%m-%d %H:%i:%s') AND STR_TO_DATE(CONCAT(CURRENT_DATE(), ' 23:59:59'), '%Y-%m-%d %H:%i:%s')
BETWEEN (CURDATE() - INTERVAL WEEKDAY(CURDATE()) DAY) AND STR_TO_DATE(CONCAT(CURRENT_DATE(), ' 23:59:59'), '%Y-%m-%d %H:%i:%s')
BETWEEN (CURDATE() - INTERVAL (DAYOFMONTH(CURDATE()) -1) DAY) AND STR_TO_DATE(CONCAT(CURRENT_DATE(), ' 23:59:59'), '%Y-%m-%d %H:%i:%s')
BETWEEN STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-01-01 00:00:00'), '%Y-%m-%d %H:%i:%s') AND STR_TO_DATE(CONCAT(CURRENT_DATE(), ' 23:59:59'), '%Y-%m-%d %H:%i:%s')
...
Рейтинг: 0 / 0
25.09.2018, 13:28
| Ответить | Цитировать | Написать  
Выбор нескольких подмножеств из подмножества
    #39707666
Tachyon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Tachyon
Гость
PS: Точнее, я читал, что в любом случае лучше приводить к DATETIME, тогда получается вот так:

Код: sql
1.
2.
3.
4.
BETWEEN STR_TO_DATE(CONCAT(CURRENT_DATE(), ' 00:00:00'), '%Y-%m-%d %H:%i:%s') AND STR_TO_DATE(CONCAT(CURRENT_DATE(), ' 23:59:59'), '%Y-%m-%d %H:%i:%s')
BETWEEN STR_TO_DATE(CONCAT((CURDATE() - INTERVAL WEEKDAY(CURDATE()) DAY), ' 00:00:00'), '%Y-%m-%d %H:%i:%s') AND STR_TO_DATE(CONCAT(CURRENT_DATE(), ' 23:59:59'), '%Y-%m-%d %H:%i:%s')
BETWEEN STR_TO_DATE(CONCAT((CURDATE() - INTERVAL (DAYOFMONTH(CURDATE()) -1) DAY), ' 00:00:00'), '%Y-%m-%d %H:%i:%s') AND STR_TO_DATE(CONCAT(CURRENT_DATE(), ' 23:59:59'), '%Y-%m-%d %H:%i:%s')
BETWEEN STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-01-01 00:00:00'), '%Y-%m-%d %H:%i:%s') AND STR_TO_DATE(CONCAT(CURRENT_DATE(), ' 23:59:59'), '%Y-%m-%d %H:%i:%s')
...
Рейтинг: 0 / 0
25.09.2018, 13:37
| Ответить | Цитировать | Написать  
Выбор нескольких подмножеств из подмножества
    #39707680
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Tachyonсегодня 00:00:00CURRENT_DATE
Tachyonсегодня 23:59:59CURRENT_DATE + INTERVAL 1 DAY - INTERVAL 1 SECOND
Tachyonпоследний понедельник 00:00:00CURRENT_DATE - INTERVAL DAYOFWEEK(CURRENT_DATE) - 2 DAY
Tachyon1 число последнего месяца 00:00:00CURRENT_DATE - INTERVAL DAY(CURRENT_DATE) - 1 DAY
Tachyon1 января последнего года 00:00:00CURRENT_DATE - INTERVAL DAYOFYEAR(CURRENT_DATE) - 1 DAY
...
Рейтинг: 0 / 0
25.09.2018, 13:52
| Ответить | Цитировать | Написать  
Выбор нескольких подмножеств из подмножества
    #39707705
Tachyon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Tachyon
Гость
Спасибо. Понятия не имею насчет производительности, но выглядит намного красивше.

Тогда последний вопрос: следует ли подобные выражения заключать в скобки в выражении between?
BETWEEN (выражение1) AND (выражение2)
или для интерпретатора MySQL разницы нет?
...
Рейтинг: 0 / 0
25.09.2018, 14:12
| Ответить | Цитировать | Написать  
Выбор нескольких подмножеств из подмножества
    #39707722
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Хочется - заключай, не хочется - не заключай. Парсеру пофиг. Можно вообще изобрести нечто вроде
Код: sql
1.
2.
BETWEEN выражение1
    AND выражение2
...
Рейтинг: 0 / 0
25.09.2018, 14:36
| Ответить | Цитировать | Написать  
Выбор нескольких подмножеств из подмножества
    #39707731
Tachyon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Tachyon
Гость
Теперь все ясно. Спасибо, попробую применить.
...
Рейтинг: 0 / 0
25.09.2018, 14:50
| Ответить | Цитировать | Написать  
Выбор нескольких подмножеств из подмножества
    #39710963
Tachyon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Tachyon
Гость
Здравствуйте еще раз. С вашего позволения, задам еще один вопрос на эту же тему.

Идея следующая: в таблице devices, упоминавшейся выше, есть поле status TINYINT(1), принимающее значение NULL или 1 в зависимости от того, онлайн или оффлайн данный терминал в настоящее время.

Для проверки и обновления статуса мое начальство задвинуло следующую идею: все терминалы (10000 или сколько их там планируется) раз в 30 секунд шлют на сервер NOW() в поле типа TIMESTAMP, находящейся в таблице типа MEMORY.
Код: sql
1.
2.
3.
4.
5.
CREATE TABLE IF NOT EXISTS `ping` (
	`uid` MEDIUMINT(6) UNSIGNED NOT NULL,
	`ping` TIMESTAMP NOT NULL,
	PRIMARY KEY (`uid`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8;



А на сервере крутится в цикле скрипт, который раз в 30 секунд проверяет таблицу ping и сравнивает ее с devices, и только в случае изменений обновляет поле status в таблице devices. Идея всей этой запутки в том, что начальство беспокоится за твердотельный накопитель на сервере, который может помереть от частых (20000 в минуту) перезаписываний в одно и то же место. У меня есть крепкое подозрение, что это ерунда, и что в результате выйдет то на то, но аргументированно доказать это не могу, так что приходится делать, как велено.

В результате, каждый терминал будет слать на сервер запросы вида
Код: sql
1.
INSERT IGNORE ping (uid, ping) VALUES ('$uid', NOW()) ON DUPLICATE KEY UPDATE ping = NOW()



А теперь самое главное, в чем заключается вопрос: запрос, выполняющийся на сервере, должен делать следующее:
(devices.uid = ping.uid, уникальное значение)
1) если devices.status IS NULL, а ping.ping не старее 90 секунд, то devices.status меняется на 1 (устройство онлайн).
2) если devices.status = 1, а ping.ping старее 90 секунд или отсутствует для данного uid, то devices.status меняется на NULL.

Я экспериментирую с запросами типа
Код: sql
1.
2.
3.
4.
UPDATE IGNORE ping
   RIGHT JOIN devices AS dev0 ON dev0.uid = ping.uid AND dev0.status IS NULL AND ping.ping > UNIX_TIMESTAMP() - 90
   RIGHT JOIN devices AS dev1 ON dev1.uid = ping.uid AND dev1.status = 1 AND (ping.ping IS NULL OR ping.ping <= UNIX_TIMESTAMP() - 90)
SET dev0.status = 1, dev1.status = NULL;


Но с двумя JOIN выходит ерунда. Два отдельных запроса использовать не хотелось бы, все-таки этот скрипт должен выполняться раз в 30 секунд на довольно толстой (теоретически) базе. Может быть, вы подскажете мне какую-нибудь идею?
...
Рейтинг: 0 / 0
01.10.2018, 15:02
| Ответить | Цитировать | Написать  
Выбор нескольких подмножеств из подмножества
    #39710966
Tachyon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Tachyon
Гость
PS: Основная идея, на всякий случай еще раз поясню, в том, что если devices.status = 1, то он должен перезаписываться только на NULL и только тогда, когда связь с девайсом пропала. И наоборот. Во всех остальных случаях запись должна игнорироваться, 1 на 1 и NULL на NULL не перезаписываться.
...
Рейтинг: 0 / 0
01.10.2018, 15:07
| Ответить | Цитировать | Написать  
18 сообщений из 18, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Выбор нескольких подмножеств из подмножества
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&msg=39707549&tid=1829570]:
 0ms
get settings:
 8ms
get forum list:
 12ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 30ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 47ms
get tp. blocked users:
 1ms
others: 13ms
total:  129ms
созд. / загр.: 129ms
x
x
Закрыть


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