Оптимизация поиска по БД: надо ли делать и как

Нужно искать в БД следующим образом: - по строке объекта из справочника найти запись, которая ссылается на этот объект (проект данного менеджера) - по интервалу дат с точностью до дня или месяца (проекты за последний месяц) - по их объединению Я придумал следующие гениальные оптимизации: Не делаем нормализации, пишем строку из справочника сразу в нашу таблицу. тогда при получении результатов поиска нам не надо делать join, однако придется искать по строке, а не по id, вероятно такой индекс дороже стоит, хотя можно наверное записать и то и то, и искать по id Запоминаем дату как число дней с какой-то даты. Тогда можно будет искать по int, а не по дате, что (возможно) быстрее. Есть ли смысл заниматься такими делами? Понимаю, что все зависит от нагрузки и не надо ничего преждевременно оптимизировать, тут спрашиваю просто про то возможен ли именно такой путь оптимизации, будет ли выигрыш, если заменить поиск до строке или дате на поиск по int

Ответ

Нормализация придумана не просто так. Ответьте себе на 1 простой вопрос: что будет с вашим поиском если значение в справочнике поменяется? P.S. Если значение в справочнике никогда не меняется - то справочник ли это?

Как создать запрос к MSSQL, возвращающий фиксированное число строк, даже если данных нет?

Имеется таблица. Необходимо сделать к ней запрос и вернуть не менее 5 записей по определенным условиям. Если в таблице нет 5 записей, удовлетворяющих заданным условиям, необходимо, чтобы вернулись те записи, которые удовлетворяют условию (например, 3 записи), и еще 2 записи с какими-то фиксированными данными (числовые поля 0, строковые - пустые строки).
Пример. Таблица table
| INT id | INT data | VARCHAR str_data | INT status | ----------------------------------------------------- | 1 | 123 | "qwert" | 1 | ----------------------------------------------------- | 2 | 343 | "zzzzz" | 1 | ----------------------------------------------------- | 3 | 923 | "qweq" | 2 | ----------------------------------------------------- | 4 | 843 | "qdfgrt" | 2 | ----------------------------------------------------- | 5 | 763 | "qddftp" | 1 | -----------------------------------------------------
Необходим запрос (SELECT data, str_data, status FROM table WHERE status = 1), который вернет следующее:
| INT data | VARCHAR str_data | INT status | -------------------------------------------- | 123 | "qwert" | 1 | -------------------------------------------- | 343 | "zzzzz" | 1 | -------------------------------------------- | 763 | "qddftp" | 1 | -------------------------------------------- | 0 | "" | 0 | -------------------------------------------- | 0 | "" | 0 | --------------------------------------------
Единственное, что приходит в голову, это держать в таблице 5 "нулевых" записей (последние 2 строки), но этот вариант не нравится.

Ответ

Например, так: select top 5 * from ( SELECT data, str_data, status FROM table WHERE status = 1 union all select 0, '', 0 union all select 0, '', 0 union all select 0, '', 0 union all select 0, '', 0 union all select 0, '', 0 ) X order by status desc

Правильный SQL запрос UPDATE на PHP

Как написать правильный запрос на обновление определенных слов в таблице wp_post?
Код, который обновляет слова:
UPDATE wp_posts SET post_content = REPLACE(post_content, 'слово1', 'замена1'); UPDATE wp_posts SET post_content = REPLACE(post_content, 'слово2', 'замена2'); UPDATE wp_posts SET post_content = REPLACE(post_content, 'слово3', 'замена3'); UPDATE wp_posts SET post_content = REPLACE(post_content, 'слово4', 'замена4'); UPDATE wp_posts SET post_content = REPLACE(post_content, 'слово5', 'замена5'); UPDATE wp_posts SET post_content = REPLACE(post_content, 'слово6', 'замена6');
Этот код работает в PHPmyAdmin но когда я делаю php файл который подключается к базе и отправляет SQL запрос, то тут уже мой код не работает я обращался к своему ХОСТЕРУ они говорят что нужно использовать "mysql_multi_query" что бы отправлять такой запрос пожалуйста кто нибудь напишите код как отправить такой запрос через этот multi_query я пробовал по мануалу php.net не получилось! У меня получился такой код
header('Content-Type: text/html; charset=utf-8'); $mysqli = new mysqli(тут мои данные);
/* проверка соединения */ if (mysqli_connect_errno()) { printf("Не удалось подключиться: %s
", mysqli_connect_error()); exit(); }
$query = "UPDATE wp_posts SET post_content = REPLACE(post_content, 'слово1', 'замена1');"; $query .= "UPDATE wp_posts SET post_content = REPLACE(post_content, 'слово2', 'замена2');"; $query .= "UPDATE wp_posts SET post_content = REPLACE(post_content, 'слово3', 'замена3');";
/* запускаем мультизапрос */ if ($mysqli->multi_query($query)) { do { /* получаем первый результирующий набор */ if ($result = $mysqli->store_result()) { while ($row = $result->fetch_row()) { printf("%s
", $row[0]); } $result->free(); } /* печатаем разделитель */ if ($mysqli->more_results()) { printf("Все ок.
"); } } while ($mysqli->next_result()); }
/* закрываем соединение */ $mysqli->close();

Ответ

А я предлагаю все запросы скомпоновать в один. То есть вместо
UPDATE wp_posts SET post_content = REPLACE(post_content, 'слово1', 'замена1'); UPDATE wp_posts SET post_content = REPLACE(post_content, 'слово2', 'замена2'); UPDATE wp_posts SET post_content = REPLACE(post_content, 'слово3', 'замена3');
пишем один запрос
UPDATE wp_posts SET post_content = REPLACE(post_content, 'слово1', 'замена1'), post_content = REPLACE(post_content, 'слово2', 'замена2'), post_content = REPLACE(post_content, 'слово3', 'замена3');
Все REPLACE выполняются за один раз.
Пример http://sqlfiddle.com/#!9/e9442/1
P.S. А по вашему коду скажу (в дополнении к комментарию): В цикле вам там ни чего не вернет, так как вы выполняете UPDATE запрос, а не выборку данных через SELECT
UPD
@msi в комментарии написал, что один столбец изменять несколько раз в UPDATE нельзя. Правда доказательств не предоставил. Тогда перепишу запрос по другому с вложенными REPLACE
UPDATE wp_posts SET post_content = REPLACE( REPLACE( REPLACE(post_content, 'слово1', 'замена1'), 'слово2', 'замена2'), 'слово3', 'замена3');
Пример http://sqlfiddle.com/#!9/e15f9b/1
UPD2
Протестировал утверждение @msi о том, что нельзя в UPDATE несколько раз менять поле на реальной базе mysql через phpmyadmin.
Первый вариант из моего ответа прекрасно работает!

Слияние таблиц в T-SQL

Есть запрос, который объединяет две таблицы: продавцов и покупателей. На выходе получается имя продавца или покупателя, тип (продавец или покупатель) и страна. Как теперь можно оставить только те строчки, где какая-либо страна имеетcя и в продавце, и в покупателе? Т.е. если есть покупатели из России, а продавцов из России нет, то эти строчки нужно исключить.
Можно полностью изменить запрос, но не использовать JOIN
SELECT CompanyName AS Person, 'Customer' AS Type, Country FROM Customers UNION SELECT CompanyName AS Person, 'Seller' AS Type, Country FROM Suppliers ORDER BY Country, Person

Ответ

SELECT CompanyName AS Person, 'Customer' AS Type, Country FROM Customers where Country in (select distinct Country from Suppliers) UNION SELECT CompanyName AS Person, 'Seller' AS Type, Country FROM Suppliers where Country in (select distinct Country from Customers) ORDER BY Country, Person

Запрос данных 4-х таблиц

Задача сделать запрос который получает данные из таблицы, которая не связана напрямую с таблицей условия. В общем до сих пор слишком сложно, попробую нарисовать.
4 таблицы:
+cases-------------+client-----------+punishment---------+default_values+ |case_name |client_id(связан)|punishment_id |max_case_value| |client_id(связан>)| |client_id (<связан)|min_case_value| |case_value | |punishment_value | |
В конце концов эти данные мне нужно будет отобразить вот так: Вывести название дела и расчет максимальное значение(оно всегда одинаковое) - case_value(свое для каждого дела) - punishment_value(свое для каждого клиента)
case_name1 max_case_value - case_value1 - punishment_value
Отнимать буду в PHP, но я запутался настолько что не понимаю как получить эти данные в свои переменные для удобного циклического прохода по ним.
Если непонятно - задайте вопрос, я отлично понимаю что я обьясняю очень плохо, но не знаю как описать лучше.

Ответ

Ну например как нибудь так:
SELECT CL.client_full_name,C.client_id,C.case_name,C.case_value, (SELECT IFNULL(sum(punishment_value),0) FROM punishment P WHERE P.client_id=C.client_id) punishment_val, D.max_case_value FROM cases C, default_values D, client CL WHERE CL.client_id=C.client_id
Так как default_values не связана с остальными таблицами никакими условиями в ней обязана быть одна и только одна запись. Либо, если записей там несколько надо условиями отбора добиться что бы выбиралась требуемая. В случае если вопреки всем ожиданиям в ней окажется более 1 записи то результирующих строк под одному делу окажется несколько.
Я намеренно не использовал JOIN т.к. в простых случаях в большинстве СУБД они не требуются, в них появляется необходимость только в случае LEFT JOIN
Вариант 2 (с LEFT JOIN):
SELECT CL.client_full_name,C.client_id,C.case_name,C.case_value, D.max_case_value, IFNULL(sum(punishment_value),0) punishment_val FROM default_values D,client CL, cases C LEFT JOIN punishment P ON P.client_id=C.client_id WHERE CL.client_id=C.client_id GROUP BY CL.client_full_name,C.client_id,C.case_name,C.case_value
Оба варианта в принципе равнозначны. Я привел их что бы показать степени свободы при написании запросов.

Elasticsearch получить ранжирование значение поля по уникальным записям

В Elasticsearch получить ранжирование значение поля по уникальным записям.
Индекс содержит записи о прыжках спортсменов. Попыток у одного спортсмена может быть множество.
Структура документа:
{ 'event_at' : '2015-01-01T12:12:10', - дата прыжка 'user_id' : 2142, - id спортсмена 'distance' : 4 - результат }
Необходимо получить выборку:
{ 'distance_range' : { '*-5' : 12, - кол-во уникальных спортсменов у которых максимальный прыжок от 0 до 5. '6-10' : 14, - кол-во уникальных спортсменов у которых максимальный прыжок от 6 до 10. '11-15' : 5 - кол-во уникальных спортсменов у которых максимальный прыжок от 10 до 15. } }
Пока у меня получалось только получить максимальный результат для каждого спортсмена, но я не могу понять как его можно ранжировать уровнем выше.
Для примера на SQL это могло бы выглядеть так:
SELECT `distace_range`, count(*) FROM ( SELECT `user_id`, IF(MAX(`distace`) <=5, '*-5', IF(MAX(`distace`) >= 6 AND MAX(`distace`) >= 10, '6-10', '11-15' ) ) `distace_range` FROM `events` GROUP BY `user_id` ) t GROUP BY `distace_range;

Ответ

Опубликовал вопрос на официальном форуме elasticsearch. На данный момент задачу такой выборки не решить штатными средствами для версии elasticsearch 2.1.х т.к. для запроса:
'aggregations' => [ 'distance_range' => [ 'terms' => [ 'field' => 'doc.user_id',
], 'aggregations' => [ 'max_distance' => [ 'max' => [ 'field' => 'doc.distance' ] ] ] ] ]
то не хватает Pipeline агрегатора по range или term
Есть несколько подходов для решения в текущей ситуации:
создание дополнительного индекса содержащего максимальный результат использование скриптов просуммировать результат на клиенте
В данный момент я воспользовался 3 вариантом.
Вариант 1 меня не устроил тем, что дополнительный индекс необходимо контролировать, что-бы он был актуален.
Вариант 2 сложность вычисления или влияние на выборку очень сильно влияет на время выборки и дополнительно придется поддерживать код в нескольких системах.

Рекурсивные запросы с использованием Postgres

В таблице: flights (id,origin_apt, destination_apt, departure_time, arrival_time)
Мне нужно, построить маршруты для перелета с условием что может быть как прямой рейс из точки A в точку Z, так и с пересадками из точки A в точку B, затем из точки B в точку Z и так далее Максимальное число пересадок 5.
Я начал делать это с помощью запросов в Postgres, но мне удалось сделать только прямой рейс и с одной пересадкой
А вот осилить маршруты с 3-5 пересадками, мне не хватает знаний:
A -> Z
A -> B -> Z
A -> B -> C -> Z
A -> B -> C -> D -> Z
A -> B -> C -> D -> E -> Z
WITH RECURSIVE segs AS ( SELECT f0.id::text as flights , origin_apt, destination_apt , departure_time AS departure , arrival_time AS arrival , 1 as hops , (arrival_time - departure_time)::interval AS total_time , '00:00'::interval as waiting_time FROM flights f0 WHERE origin_apt = 'SVX' -- AND departure_time >= '2016-01-19' UNION ALL SELECT s.flights || '-->' || f1.id::text as flights , s.origin_apt, f1.destination_apt , s.departure AS departure , f1.arrival_time AS arrival , s.hops + 1 AS hops , s.total_time + (f1.arrival_time - f1.departure_time)::interval AS total_time , s.waiting_time + (f1.departure_time - s.arrival)::interval AS waiting_time FROM segs s JOIN flights f1 ON f1.origin_apt = s.destination_apt AND f1.departure_time >= s.arrival + '8 hour' -- ) SELECT * FROM segs WHERE destination_apt = 'PEK' -- AND waiting_time < '25 day'-- ORDER BY departure, waiting_time asc;
Пример данных:
CREATE TABLE flights ( id serial NOT NULL, origin_apt character varying, destination_apt character varying, departure_time timestamp without time zone, arrival_time timestamp without time zone, CONSTRAINT flights_pkey PRIMARY KEY (id) );
insert into flights VALUES (1,'GOJ','TAS','2016-01-04 06:00','2016-01-04 11:30'), (2,'GOJ','TAS','2016-01-11 06:00','2016-01-11 11:30'), ('3','GOJ','TAS','2016-01-18 06:00','2016-01-18 11:30'), ('4','GOJ','TAS','2016-01-25 06:00','2016-01-25 11:30'), ('5','GOJ','TAS','2016-02-01 06:00','2016-02-01 11:30'), ('6','GOJ','TAS','2016-02-08 06:00','2016-02-08 11:30'), ('7','GOJ','TAS','2016-02-15 06:00','2016-02-15 11:30'), ('8','GOJ','TAS','2016-02-22 06:00','2016-02-22 11:30'), ('9','GOJ','TAS','2016-02-29 06:00','2016-02-29 11:30'), ('10','GOJ','TAS','2016-03-07 06:00','2016-03-07 11:30'), ('11','GOJ','TAS','2016-03-14 06:00','2016-03-14 11:30'), ('12','GOJ','TAS','2016-03-21 06:00','2016-03-21 11:30'), ('13','GOJ','DYU','2016-01-02 03:40','2016-01-02 09:30'), ('14','GOJ','DYU','2016-01-09 03:40','2016-01-09 09:30'), ('15','GOJ','DYU','2016-01-16 03:40','2016-01-16 09:30'), ('16','GOJ','DYU','2016-01-23 03:40','2016-01-23 09:30'), ('17','GOJ','DYU','2016-01-30 03:40','2016-01-30 09:30'), ('18','GOJ','DYU','2016-02-06 03:40','2016-02-06 09:30'), ('19','GOJ','DYU','2016-02-13 03:40','2016-02-13 09:30'), ('20','GOJ','DYU','2016-02-20 03:40','2016-02-20 09:30'), ('21','GOJ','DYU','2016-02-27 03:40','2016-02-27 09:30'), ('22','GOJ','DYU','2016-03-05 03:40','2016-03-05 09:30'), ('23','GOJ','DYU','2016-03-12 03:40','2016-03-12 09:30'), ('24','GOJ','DYU','2016-03-19 03:40','2016-03-19 09:30'), ('25','GOJ','DYU','2016-03-26 03:40','2016-03-26 09:30'), ('26','TAS','PEK','2016-01-06 13:40','2016-01-06 22:00'), ('27','TAS','PEK','2016-01-13 13:40','2016-01-13 22:00'), ('28','TAS','PEK','2016-01-20 13:40','2016-01-20 22:00'), ('29','TAS','PEK','2016-01-27 13:40','2016-01-27 22:00'), ('30','TAS','PEK','2016-02-03 13:40','2016-02-03 22:00'), ('31','TAS','PEK','2016-02-10 13:40','2016-02-10 22:00'), ('32','TAS','PEK','2016-02-17 13:40','2016-02-17 22:00'), ('33','TAS','PEK','2016-02-24 13:40','2016-02-24 22:00'), ('34','TAS','PEK','2016-03-02 13:40','2016-03-02 22:00'), ('35','TAS','PEK','2016-03-09 13:40','2016-03-09 22:00'), ('36','TAS','PEK','2016-03-16 13:40','2016-03-16 22:00'), ('37','TAS','PEK','2016-03-23 13:40','2016-03-23 22:00'), ('38','PRG','PEK','2016-01-08 13:00','2016-01-09 05:55'), ('39','PRG','PEK','2016-01-15 13:00','2016-01-16 05:55'), ('40','PRG','PEK','2016-01-22 13:00','2016-01-23 05:55'), ('41','PRG','PEK','2016-01-29 13:00','2016-01-30 05:55'), ('42','PRG','PEK','2016-02-05 13:00','2016-02-06 05:55'), ('43','PRG','PEK','2016-02-12 13:00','2016-02-13 05:55'), ('44','PRG','PEK','2016-02-19 13:00','2016-02-20 05:55'), ('45','PRG','PEK','2016-02-26 13:00','2016-02-27 05:55'), ('46','PRG','PEK','2016-03-04 13:00','2016-03-05 05:55'), ('47','PRG','PEK','2016-03-11 13:00','2016-03-12 05:55'), ('48','PRG','PEK','2016-03-18 13:00','2016-03-19 05:55'), ('49','PRG','PEK','2016-03-25 13:00','2016-03-26 05:55'), ('50','PRG','PEK','2016-01-04 13:00','2016-01-05 06:00'), ('51','PRG','PEK','2016-01-11 13:00','2016-01-12 06:00'), ('52','PRG','PEK','2016-01-18 13:00','2016-01-19 06:00'), ('53','PRG','PEK','2016-01-25 13:00','2016-01-26 06:00'), ('54','PRG','PEK','2016-02-01 13:00','2016-02-02 06:00'), ('55','PRG','PEK','2016-02-08 13:00','2016-02-09 06:00'), ('56','PRG','PEK','2016-02-15 13:00','2016-02-16 06:00'), ('57','PRG','PEK','2016-02-22 13:00','2016-02-23 06:00'), ('58','PRG','PEK','2016-02-29 13:00','2016-03-01 06:00'), ('59','PRG','PEK','2016-03-07 13:00','2016-03-08 06:00'), ('60','PRG','PEK','2016-03-14 13:00','2016-03-15 06:00'), ('61','PRG','PEK','2016-03-21 13:00','2016-03-22 06:00'), ('62','GYD','PEK','2016-01-03 19:20','2016-01-04 06:20'), ('63','GYD','PEK','2016-01-10 19:20','2016-01-11 06:20'), ('64','GYD','PEK','2016-01-17 19:20','2016-01-18 06:20'), ('65','GYD','PEK','2016-01-24 19:20','2016-01-25 06:20'), ('66','GYD','PEK','2016-01-31 19:20','2016-02-01 06:20'), ('67','GYD','PEK','2016-02-07 19:20','2016-02-08 06:20'), ('68','GYD','PEK','2016-02-14 19:20','2016-02-15 06:20'), ('69','GYD','PEK','2016-02-21 19:20','2016-02-22 06:20'), ('70','GYD','PEK','2016-02-28 19:20','2016-02-29 06:20'), ('71','GYD','PEK','2016-03-06 19:20','2016-03-07 06:20'), ('72','GYD','PEK','2016-03-13 19:20','2016-03-14 06:20'), ('73','GYD','PEK','2016-03-20 19:20','2016-03-21 06:20'), ('74','GYD','PEK','2016-03-27 19:20','2016-03-28 06:20'), ('75','MUC','SVX','2016-01-16 10:40','2016-01-16 19:20'), ('76','MUC','SVX','2016-01-23 10:40','2016-01-23 19:20'), ('77','MUC','SVX','2016-01-30 10:40','2016-01-30 19:20'), ('78','MUC','SVX','2016-02-06 10:40','2016-02-06 19:20'), ('79','MUC','SVX','2016-02-13 10:40','2016-02-13 19:20'), ('80','MUC','SVX','2016-02-20 10:40','2016-02-20 19:20'), ('81','MUC','SVX','2016-02-27 10:40','2016-02-27 19:20'), ('82','MUC','SVX','2016-03-05 10:40','2016-03-05 19:20'), ('83','MUC','SVX','2016-03-12 10:40','2016-03-12 19:20'), ('84','MUC','SVX','2016-03-19 10:40','2016-03-19 19:20'), ('85','MUC','SVX','2016-03-26 10:40','2016-03-26 19:20'), ('86','PRG','SVX','2016-01-07 16:25','2016-01-08 00:40'), ('87','PRG','SVX','2016-01-14 16:25','2016-01-15 00:40'), ('88','PRG','SVX','2016-01-21 16:25','2016-01-22 00:40'), ('89','PRG','SVX','2016-01-28 16:25','2016-01-29 00:40'), ('90','PRG','SVX','2016-02-04 16:25','2016-02-05 00:40'), ('91','PRG','SVX','2016-02-11 16:25','2016-02-12 00:40'), ('92','PRG','SVX','2016-02-18 16:25','2016-02-19 00:40'), ('93','PRG','SVX','2016-02-25 16:25','2016-02-26 00:40'), ('94','PRG','SVX','2016-03-03 16:25','2016-03-04 00:40'), ('95','PRG','SVX','2016-03-10 16:25','2016-03-11 00:40'), ('96','PRG','SVX','2016-03-17 16:25','2016-03-18 00:40'), ('97','PRG','SVX','2016-03-24 16:25','2016-03-25 00:40'), ('98','PRG','SVX','2016-01-03 10:10','2016-01-03 18:15'), ('99','PRG','SVX','2016-01-10 10:10','2016-01-10 18:15'), ('100','PRG','SVX','2016-01-17 10:10','2016-01-17 18:15'), ('101','PRG','SVX','2016-01-24 10:10','2016-01-24 18:15'), ('102','PRG','SVX','2016-01-31 10:10','2016-01-31 18:15'), ('103','PRG','SVX','2016-02-07 10:10','2016-02-07 18:15'), ('104','PRG','SVX','2016-02-14 10:10','2016-02-14 18:15'), ('105','PRG','SVX','2016-02-21 10:10','2016-02-21 18:15'), ('106','PRG','SVX','2016-02-28 10:10','2016-02-28 18:15'), ('107','PRG','SVX','2016-03-06 10:10','2016-03-06 18:15'), ('108','PRG','SVX','2016-03-13 10:10','2016-03-13 18:15'), ('109','PRG','SVX','2016-03-20 10:10','2016-03-20 18:15'), ('110','PRG','SVX','2016-03-27 10:10','2016-03-27 18:15'), ('111','SVX','GYD','2016-01-07 17:00','2016-01-07 19:00'), ('112','SVX','GYD','2016-01-14 17:00','2016-01-14 19:00'), ('113','SVX','GYD','2016-01-21 17:00','2016-01-21 19:00'), ('114','SVX','GYD','2016-01-28 17:00','2016-01-28 19:00'), ('115','SVX','GYD','2016-02-04 17:00','2016-02-04 19:00'), ('116','SVX','GYD','2016-02-11 17:00','2016-02-11 19:00'), ('117','SVX','GYD','2016-02-18 17:00','2016-02-18 19:00'), ('118','SVX','GYD','2016-02-25 17:00','2016-02-25 19:00'), ('119','SVX','GYD','2016-03-03 17:00','2016-03-03 19:00'), ('120','SVX','GYD','2016-03-10 17:00','2016-03-10 19:00'), ('121','SVX','GYD','2016-03-17 17:00','2016-03-17 19:00'), ('122','SVX','GYD','2016-03-24 17:00','2016-03-24 19:00'), ('123','SVX','GYD','2016-01-02 17:00','2016-01-02 19:00'), ('124','SVX','GYD','2016-01-09 17:00','2016-01-09 19:00'), ('125','SVX','GYD','2016-01-16 17:00','2016-01-16 19:00'), ('126','SVX','GYD','2016-01-23 17:00','2016-01-23 19:00'), ('127','SVX','GYD','2016-01-30 17:00','2016-01-30 19:00'), ('128','SVX','GYD','2016-02-06 17:00','2016-02-06 19:00'), ('129','SVX','GYD','2016-02-13 17:00','2016-02-13 19:00'), ('130','SVX','GYD','2016-02-20 17:00','2016-02-20 19:00'), ('131','SVX','GYD','2016-02-27 17:00','2016-02-27 19:00'), ('132','SVX','GYD','2016-03-05 17:00','2016-03-05 19:00'), ('133','SVX','GYD','2016-03-12 17:00','2016-03-12 19:00'), ('134','SVX','GYD','2016-03-19 17:00','2016-03-19 19:00'), ('135','SVX','GYD','2016-03-26 17:00','2016-03-26 19:00'), ('136','SVX','DYU','2016-01-07 06:45','2016-01-07 09:55'), ('137','SVX','DYU','2016-01-14 06:45','2016-01-14 09:55'), ('138','SVX','DYU','2016-01-21 06:45','2016-01-21 09:55'), ('139','SVX','DYU','2016-01-28 06:45','2016-01-28 09:55'), ('140','SVX','DYU','2016-02-04 06:45','2016-02-04 09:55'), ('141','SVX','DYU','2016-02-11 06:45','2016-02-11 09:55'), ('142','SVX','DYU','2016-02-18 06:45','2016-02-18 09:55'), ('143','SVX','DYU','2016-02-25 06:45','2016-02-25 09:55'), ('144','SVX','DYU','2016-03-03 06:45','2016-03-03 09:55'), ('145','SVX','DYU','2016-03-10 06:45','2016-03-10 09:55'), ('146','SVX','DYU','2016-03-17 06:45','2016-03-17 09:55'), ('147','SVX','DYU','2016-03-24 06:45','2016-03-24 09:55'), ('148','SVX','DYU','2016-01-05 06:45','2016-01-05 09:55'), ('149','SVX','DYU','2016-01-12 06:45','2016-01-12 09:55'), ('150','SVX','DYU','2016-01-19 06:45','2016-01-19 09:55'), ('151','SVX','DYU','2016-01-26 06:45','2016-01-26 09:55'), ('152','SVX','DYU','2016-02-02 06:45','2016-02-02 09:55'), ('153','SVX','DYU','2016-02-09 06:45','2016-02-09 09:55'), ('154','SVX','DYU','2016-02-16 06:45','2016-02-16 09:55'), ('155','SVX','DYU','2016-02-23 06:45','2016-02-23 09:55'), ('156','SVX','DYU','2016-03-01 06:45','2016-03-01 09:55'), ('157','SVX','DYU','2016-03-08 06:45','2016-03-08 09:55'), ('158','SVX','DYU','2016-03-15 06:45','2016-03-15 09:55'), ('159','SVX','DYU','2016-03-22 06:45','2016-03-22 09:55'), ('160','SVX','DYU','2016-01-02 06:45','2016-01-02 09:55'), ('161','SVX','DYU','2016-01-09 06:45','2016-01-09 09:55'), ('162','SVX','DYU','2016-01-16 06:45','2016-01-16 09:55'), ('163','SVX','DYU','2016-01-23 06:45','2016-01-23 09:55'), ('164','SVX','DYU','2016-01-30 06:45','2016-01-30 09:55'), ('165','SVX','DYU','2016-02-06 06:45','2016-02-06 09:55'), ('166','SVX','DYU','2016-02-13 06:45','2016-02-13 09:55'), ('167','SVX','DYU','2016-02-20 06:45','2016-02-20 09:55'), ('168','SVX','DYU','2016-02-27 06:45','2016-02-27 09:55'), ('169','SVX','DYU','2016-03-05 06:45','2016-03-05 09:55'), ('170','SVX','DYU','2016-03-12 06:45','2016-03-12 09:55'), ('171','SVX','DYU','2016-03-19 06:45','2016-03-19 09:55'), ('172','SVX','DYU','2016-03-26 06:45','2016-03-26 09:55'), ('173','SVX','TAS','2016-01-04 07:40','2016-01-04 10:50'), ('174','SVX','TAS','2016-01-11 07:40','2016-01-11 10:50'), ('175','SVX','TAS','2016-01-18 07:40','2016-01-18 10:50'), ('176','SVX','TAS','2016-01-25 07:40','2016-01-25 10:50'), ('177','SVX','TAS','2016-02-01 07:40','2016-02-01 10:50'), ('178','SVX','TAS','2016-02-08 07:40','2016-02-08 10:50'), ('179','SVX','TAS','2016-02-15 07:40','2016-02-15 10:50'), ('180','SVX','TAS','2016-02-22 07:40','2016-02-22 10:50'), ('181','SVX','TAS','2016-02-29 07:40','2016-02-29 10:50'), ('182','SVX','TAS','2016-03-07 07:40','2016-03-07 10:50'), ('183','SVX','TAS','2016-03-14 07:40','2016-03-14 10:50'), ('184','SVX','TAS','2016-03-21 07:40','2016-03-21 10:50'), ('185','SVX','PEK','2016-01-07 19:45','2016-01-08 04:00'), ('186','SVX','PEK','2016-01-14 19:45','2016-01-15 04:00'), ('187','SVX','PEK','2016-01-21 19:45','2016-01-22 04:00'), ('188','SVX','PEK','2016-01-28 19:45','2016-01-29 04:00'), ('189','SVX','PEK','2016-02-04 19:45','2016-02-05 04:00'), ('190','SVX','PEK','2016-02-11 19:45','2016-02-12 04:00'), ('191','SVX','PEK','2016-02-18 19:45','2016-02-19 04:00'), ('192','SVX','PEK','2016-02-25 19:45','2016-02-26 04:00'), ('193','SVX','PEK','2016-03-03 19:45','2016-03-04 04:00'), ('194','SVX','PEK','2016-03-10 19:45','2016-03-11 04:00'), ('195','SVX','PEK','2016-03-17 19:45','2016-03-18 04:00'), ('196','SVX','PEK','2016-03-24 19:45','2016-03-25 04:00'), ('197','SVX','PEK','2016-01-05 19:45','2016-01-06 04:00'), ('198','SVX','PEK','2016-01-12 19:45','2016-01-13 04:00'), ('199','SVX','PEK','2016-01-19 19:45','2016-01-20 04:00'), ('200','SVX','PEK','2016-01-26 19:45','2016-01-27 04:00'), ('201','SVX','PEK','2016-02-02 19:45','2016-02-03 04:00'), ('202','SVX','PEK','2016-02-09 19:45','2016-02-10 04:00'), ('203','SVX','PEK','2016-02-16 19:45','2016-02-17 04:00'), ('204','SVX','PEK','2016-02-23 19:45','2016-02-24 04:00'), ('205','SVX','PEK','2016-03-01 19:45','2016-03-02 04:00'), ('206','SVX','PEK','2016-03-08 19:45','2016-03-09 04:00'), ('207','SVX','PEK','2016-03-15 19:45','2016-03-16 04:00'), ('208','SVX','PEK','2016-03-22 19:45','2016-03-23 04:00'), ('209','SVX','PEK','2016-01-08 19:45','2016-01-09 04:00'), ('210','SVX','PEK','2016-01-15 19:45','2016-01-16 04:00'), ('211','SVX','PEK','2016-01-22 19:45','2016-01-23 04:00'), ('212','SVX','PEK','2016-01-29 19:45','2016-01-30 04:00'), ('213','SVX','PEK','2016-02-05 19:45','2016-02-06 04:00'), ('214','SVX','PEK','2016-02-12 19:45','2016-02-13 04:00'), ('215','SVX','PEK','2016-02-19 19:45','2016-02-20 04:00'), ('216','SVX','PEK','2016-02-26 19:45','2016-02-27 04:00'), ('217','SVX','PEK','2016-03-04 19:45','2016-03-05 04:00'), ('218','SVX','PEK','2016-03-11 19:45','2016-03-12 04:00'), ('219','SVX','PEK','2016-03-18 19:45','2016-03-19 04:00'), ('220','SVX','PEK','2016-03-25 19:45','2016-03-26 04:00'), ('221','SVX','PEK','2016-01-03 19:45','2016-01-04 04:00'), ('222','SVX','PEK','2016-01-10 19:45','2016-01-11 04:00'), ('223','SVX','PEK','2016-01-17 19:45','2016-01-18 04:00'), ('224','SVX','PEK','2016-01-24 19:45','2016-01-25 04:00'), ('225','SVX','PEK','2016-01-31 19:45','2016-02-01 04:00'), ('226','SVX','PEK','2016-02-07 19:45','2016-02-08 04:00'), ('227','SVX','PEK','2016-02-14 19:45','2016-02-15 04:00'), ('228','SVX','PEK','2016-02-21 19:45','2016-02-22 04:00'), ('229','SVX','PEK','2016-02-28 19:45','2016-02-29 04:00'), ('230','SVX','PEK','2016-03-06 19:45','2016-03-07 04:00'), ('231','SVX','PEK','2016-03-13 19:45','2016-03-14 04:00'), ('232','SVX','PEK','2016-03-20 19:45','2016-03-21 04:00'), ('233','SVX','PEK','2016-03-27 19:45','2016-03-28 04:00')

Ответ

Ваш запрос уже может выбирать любые вложенности рекурсии. Ваши исходные данные для тестов не содержат подходящих маршрутов, что бы запрос мог выдать больше стыковок. При добавлении записи insert into flights VALUES(234,'DYU','PRG','2016-02-18 09:00','2016-02-18 15:30') ваш запрос сразу начинает находить еще кучу вариантов, к сожалению очень многие с повторным пролетом через Кольцово. Так что запрос надо ограничивать, не позволяя выбирать маршруты в которых один и тот же АП встречается более одного раза. Примерно так:
WITH RECURSIVE segs AS ( SELECT f0.id::text as flights ,'/'||origin_apt||'/'||destination_apt||'/' as route -- <-- Добавил полный маршрут , origin_apt, destination_apt , departure_time AS departure , arrival_time AS arrival , 1 as hops , (arrival_time - departure_time)::interval AS total_time , '00:00'::interval as waiting_time FROM flights f0 WHERE origin_apt = 'SVX' -- AND departure_time >= '2016-01-19' UNION ALL SELECT s.flights || '-->' || f1.id::text as flights ,s.route||f1.destination_apt||'/' -- <-- добавляем АП посадки в маршрут , s.origin_apt, f1.destination_apt , s.departure AS departure , f1.arrival_time AS arrival , s.hops + 1 AS hops , s.total_time + (f1.arrival_time - f1.departure_time)::interval AS total_time , s.waiting_time + (f1.departure_time - s.arrival)::interval AS waiting_time FROM segs s JOIN flights f1 ON f1.origin_apt = s.destination_apt AND f1.departure_time >= s.arrival + '8 hour' -- AND NOT s.route like '%/'||f1.destination_apt||'/%' -- <-- АП назначения не в полном маршруте AND s.hops < 5 ) SELECT * FROM segs WHERE destination_apt = 'PEK' -- AND waiting_time < '25 day'-- ORDER BY departure, waiting_time asc;

Помогите пожалуйста с запросом SQL (Oracle). Having, Group by, агрегатная функция от агрегатной функции

Задание - вывести фамилию-имя начальника отдела и название отдела, в котором сотрудники выполнили максимальное количество проектов. У меня получился такой запрос, но выводит он не совсем то, что нужно. Сделал его по примеру отсюда (второй пункт).

SELECT department_name, max_amount FROM ( SELECT dep_name AS department_name, COUNT(rel_prj_id) AS projects_amount FROM employees INNER JOIN departments ON emp_dep_id = dep_id INNER JOIN rel_prj_emp ON rel_emp_id = emp_id GROUP BY dep_name ) X INNER JOIN ( SELECT MAX(projects_amount) AS max_amount FROM ( SELECT dep_name AS department_name, COUNT(rel_prj_id) AS projects_amount FROM employees INNER JOIN departments ON emp_dep_id = dep_id INNER JOIN rel_prj_emp ON rel_emp_id = emp_id GROUP BY dep_name ) X ) Y ON projects_amount = max_amount
Вывод, при том, что проекта всего три:
В чем проблема - я думаю, вы поймёте, если посмотрете на схему БД. Каждому отделу должно соответствовать несколько проектов, но отделы и проекты связаны через таблицу сотрудников - и поэтому в моём запросе каждому отделу соответствует больше записей, чем нужно. Группирую по названию отдела, а получается, что в каждой группе - сотрудники этого отдела. Как связать таблицы иначе или сгруппировать иначе - я не придумал, в ступоре нахожусь. Если зайти с другой стороны, сгруппировать по id проекта - в каждой группе будет количество сотрудников, которое работало над проектом, тоже не то. Нужно решение задачи именно по этой схеме. Я знаю, что можно упростить, но задание учебное. Скрипт создания БД: http://pastebin.com/hnHnENpX

Заранее спасибо.

Ответ

SELECT emp_first_name,emp_last_name,department_name, max_amount FROM ( SELECT dep_name AS department_name,e2.emp_first_name,e2.emp_last_name, COUNT(distinct rel_prj_id) AS projects_amount FROM employees e1 INNER JOIN departments ON e1.emp_dep_id = dep_id INNER JOIN rel_prj_emp ON rel_emp_id = e1.emp_id INNER JOIN employees e2 ON e2.emp_id=dep_manager_id GROUP BY dep_name,e2.emp_first_name,e2.emp_last_name ) X INNER JOIN ( SELECT MAX(projects_amount) AS max_amount FROM ( SELECT dep_name AS department_name, COUNT(distinct rel_prj_id) AS projects_amount FROM employees INNER JOIN departments ON emp_dep_id = dep_id INNER JOIN rel_prj_emp ON rel_emp_id = emp_id GROUP BY dep_name ) X ) Y ON projects_amount = max_amount
Хотя верхний подзапрос немного странно смотрится, логичнее бы выглядело, если бы отделы были первой таблицей и к ней все клеилось. Хотя в данном случае порядок ни на что не влияет (т.к. в inner join таблицы слева и справа равнозначны).

Не срабатывает UPDATE

Здравствуйте. Делаю опросник на yii2. Отметил чекбокс - нажал голосовать - счетчики обновились. Это работает, но есть одно "но". UPDATE запрос не срабатывает на счетчиках у которых значение 0, ошибок нет, но и результата нет. Если же у поля будет какое-либо значение отличное от 0, то все хорошо срабатывает. При этом если выполнить нужный запрос в phpmyadmin, то даже с 0 значения обновляются.
Вот код с запросами из контроллера:
public function actionPoll($id) { $single = Poll::getOne($id);
if ($single->load(Yii::$app->request->post())) { if ($single->count1) { Yii::$app->db->createCommand("UPDATE poll SET count1 = count1 +1 WHERE id=$id")->execute(); } if ($single->count2) { Yii::$app->db->createCommand("UPDATE poll SET count2 = count2 +1 WHERE id=$id")->execute(); } if ($single->count3) { Yii::$app->db->createCommand("UPDATE poll SET count3 = count3 +1 WHERE id=$id")->execute(); } } return $this->render('poll', ['single' => $single]); }
А вот запрос который нормально выполняется в phpmyadmin:
UPDATE `poll` SET `count2` = `count2` +1 WHERE `id` = 2
Также прикрепляю представление:
field($single, 'count1')->checkbox(['label' => '', 'value' => $single->count1]) ?>
Подскажите пожалуйста в чем может быть проблема ?

Ответ

Проблема в том, что Вам не стоит указывать value в методе checkbox. Это потому, что метод checkbox автоматически считывает данные с модели. Вот как нужно вывести Вам checkbox:
field($single, 'count1')->checkbox(['label' => '']) ?>
Также хочу отметить, что Вы можете обновлять счетчики следующим способом:
$single->updateCounters(['count1' => 1]); //равносильно count1 = count1 + 1
В итоге следует также отметить, что если Вы не используете ActiveRecord, то Вам следует посмотреть в сторону PDO.

Как в PDO получить строку запроса prepare после обработки

Здравствуйте!
У меня есть строка запроса к базе данных:
SELECT * FROM `users` WHERE `id` = ?
Я использую PDO функцию prepare:
$sth = $dbh->prepare('SELECT * FROM `users` WHERE `id` = ?');
Нужно получить строку запроса после обработки функцией prepare, например:
SELECT * FROM `users` WHERE `id` = 294
Как решить эту проблему?

Ответ

Используйте библиотеку, которая позволяет это делать
$parameters = array( 'param1' => 'hello', 'param2' => 123, 'param3' => null );
$sql = "INSERT INTO test (col1, col2, col3) VALUES (:param1, :param2, :param3)"; echo PdoDebugger::show($sql, $parameters);
//shows: INSERT INTO test (col1, col2, col3) VALUES ('hello', 123, NULL)

Как объединить несколько выборок в одно представление SQL

Здравствуйте, у меня сложилась такая ситуация, нужно к основной выборке по таблице players добавить еще одну выборку с другой таблицы (statisticsTable). Суть в том, что у игрока много статистик за каждую игру, и мне нужно вывести дополнительные столбцы в суммами по нескольким полям. примерно так
select sum(StatisticsTable.Falls) as 'Фолов за всю жизнь' from StatisticsTable, Players where (Players.PlayerID = StatisticsTable.PlayerID)
сам запрос для первой таблицы выглядит вот так
select Players.FirstName as 'Имя', Countries.NationalityName as 'Национальность', Teams.TeamName as 'Команда', Positions.PositionName as 'Позиция', datepart(year,getdate())-datepart(year, Convert(Varchar, DateOfBurn, 104)) as 'Возраст' from Players, Countries, Positions, Teams where ((Players.CountryID = Countries.CountryID) and (Players.TeamID = Teams.TeamID) and (Players.PositionID = Positions.PositionID))
Но просто поставить UNION между этими запросами приводит к ошибке "Сообщение 205, уровень 16, Все запросы, объединенные с помощью операторов UNION, INTERSECT или EXCEPT, должны иметь одинаковое число выражений в целевых списках. " Помогите пожалуйста грамотно объединить эти 2 выборки

Ответ

union, который вы пытаетесь применить, используется для получения дополнительных строк в выборке, а не дополнительных столбцов. А все строки разумеется должны иметь одинаковое количество столбцов.
Если я правильно понял, вы хотите получить столбец где будет сумма некоей статистики по каждому игроку (почему в приведенном первым запросе нет group by не представляю, он вам давал сумму по всем игрокам, что при запросе в разрезе игроков как то странно).
select Players.FirstName as 'Имя', Countries.NationalityName as 'Национальность', Teams.TeamName as 'Команда', Positions.PositionName as 'Позиция', datepart(year,getdate())-datepart(year, Convert(Varchar, DateOfBurn, 104)) as 'Возраст', (select sum(StatisticsTable.Falls) from StatisticsTable where (Players.PlayerID = StatisticsTable.PlayerID) ) as 'Фолов за всю жизнь' from Players, Countries, Positions, Teams where ((Players.CountryID = Countries.CountryID) and (Players.TeamID = Teams.TeamID) and (Players.PositionID = Positions.PositionID))
Если надо выбрать много колонок из таблицы статистики, то лучше переписать запрос так:
select Players.FirstName as 'Имя', Countries.NationalityName as 'Национальность', Teams.TeamName as 'Команда', Positions.PositionName as 'Позиция', datepart(year,getdate())-datepart(year, Convert(Varchar, DateOfBurn, 104)) as 'Возраст', Stat.Falls as 'Фолов за всю жизнь', Stat.xyz as 'Еще какая то статистика' from Players, Countries, Positions, Teams, (select PlayerID, sum(StatisticsTable.Falls) as Falls, sum(xyz) as xyz from StatisticsTable group by PlayerID ) Stat where Players.CountryID = Countries.CountryID and Players.TeamID = Teams.TeamID and Players.PositionID = Positions.PositionID and Players.PlayerID = Stat.PlayerID

вывод последнего депозита для каждого аккаунта

дальше у нас есть такой запрос
SELECT distinct last_value(account) over(partition by account order by data desc) account, last_value(sum) over(partition by account order by data desc) sum FROM ( select account, data, sum, nth_value(data, 1) over(partition by account order by data desc) cr from accounts)x WHERE data = cr;
Выдает он последний депозит для каждого аккаунта. Как можно переписать программу, используя только одну оконную функцию?

Ответ

Можно попробовать вот так:
select account, data, sum from ( select a.*, row_number() over(partition by account order by data desc) as rn from accounts a ) where rn = 1;
Еще варианты:
RANK/DENSE_RANK
select account, sum from ( select a.*, rank() over(partition by account order by data desc, sum) as rn from accounts a ) where rn = 1; (использование dense_rank такое же),
NTH_VALUE
select account, sum from ( select a.*, nth_value(sum, 2) over(partition by account order by data desc, sum desc) as rn from accounts a ) where rn is null;
LAG
select account, sum from ( select a.*, lag(data) over(partition by account order by data desc, sum desc) as rn from accounts a ) where rn is null;
Ну и так далее...

Сравнение и подсчет процентного совпадения по интересам у пользователей

Столкнулся с серьёзной проблемой в разработке веб-сайта знакомств для курсовой. Создал всю внешнюю оболочку, обмен сообщениями и т.д., но остался сложный подбор людей по интересам.
Мне нужно, чтобы у пользователей хранились их процентные совпадения по интересам с другими пользователями, чтобы потом я мог находить им друзей. Но сейчас мне нужно конкретно находить процент совпадений.
Ниже прикладываю схему, логику действий, которую я не могу реализовать, и MySQL код таблиц.
Я уже посмотрел все схожие темы но не нашёл ничего столь запутанного, как моё задание. Прошу вашей помощи, вопрос жизни и смерти! Этот "цикл" будет отдельно для каждого Id_music, id_book и id_film, потому в описании они идут через /
При добавлении поля Id_music/id_book/id_film в таблицы Watched,Read,Listened создавать цикл, равный количеству пользователей минус 1. ID нашего пользователя заносится каждый раз на позицию user_one в таблицу Stats, а id того, с кем сравниваем - в user_two. Считать количество записей по id в таблице Listened/Watched/Read, чтобы узнать общее количество интересов в этой категории у нашего пользователя. Тоже самое делаем для пользователя в цикле
3.Поочередно сравнивать поле ID_Music/ID_Book/ID_Film в таблице Read/Listened/Watched нашего пользователя на совпадение с теми же полями у другого пользователя, что сейчас в цикле. В итоге мы получим число совпадений.
4.Сравниваем это число с количеством записей этих пользователей из пункта 2. К примеру, если у нашего пользователя всего 4 записи , а у второго 8, и совпадений 2, мы заносим в таблицу stats 50(%) в поле bks_prcnt/msc_prcnt/flm_prcnt и 25(%) в поле bks_prcnt_rev/msc_prcnt_rev/flm_prcnt_rev, потому что у второго пользователя больше интересов.
5.?цикл заканчивается. Если поля существовали ранее - обновляем, а не создаём их.
CREATE TABLE `users` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `username` text NOT NULL, `password` text NOT NULL, 'about' text(500), PRIMARY KEY (`id`) ) ENGINE=InnoDB CHARACTER SET=UTF8;
CREATE TABLE `stats` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `user_one` INT(11) NOT NULL, `user_two` INT(11) NOT NULL, `bks_prcnt` INT(4) NOT NULL , `bks_prcnt_rev` INT(4) NOT NULL, `flm_prcnt` INT(4) NOT NULL, `flm_prcnt_rev` INT(4) NOT NULL, `msc_prcnt` INT(4) NOT NULL, `msc_prcnt_rev` INT(4) NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (user_one) REFERENCES users(id), FOREIGN KEY (user_two) REFERENCES users(id) ) ENGINE=InnoDB CHARACTER SET=UTF8;
CREATE TABLE `genres` ( `genre_id` INT(11) NOT NULL AUTO_INCREMENT, `genre_name` text NOT NULL, PRIMARY KEY (`genre_id`) ) ENGINE=InnoDB CHARACTER SET=UTF8;
CREATE TABLE `music` ( `music_id` INT(11) NOT NULL AUTO_INCREMENT, `title` text NOT NULL, `id_genre` INT(11) NOT NULL, `Compositor` text NOT NULL, PRIMARY KEY (`music_id`) , FOREIGN KEY (id_genre) REFERENCES genres(genre_id) ) ENGINE=InnoDB CHARACTER SET=UTF8;
CREATE TABLE `book` ( `book_id` INT(11) NOT NULL AUTO_INCREMENT, `title` text NOT NULL, `id_genre` INT(11) NOT NULL, `Author` text NOT NULL, PRIMARY KEY (`book_id`) , FOREIGN KEY (id_genre) REFERENCES genres(genre_id) ) ENGINE=InnoDB CHARACTER SET=UTF8;
CREATE TABLE `film` ( `film_id` INT(11) NOT NULL AUTO_INCREMENT, `title` text NOT NULL, `id_genre` INT(11) NOT NULL, `Producer` text NOT NULL, PRIMARY KEY (`film_id`), FOREIGN KEY (id_genre) REFERENCES genres(genre_id) ) ENGINE=InnoDB CHARACTER SET=UTF8;
CREATE TABLE `Listened` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `id_user` INT(11) NOT NULL, `id_music` INT(11) NOT NULL, PRIMARY KEY (`id`) , FOREIGN KEY (id_user) REFERENCES users(id), FOREIGN KEY (id_music) REFERENCES music(id) ) ENGINE=InnoDB CHARACTER SET=UTF8;
CREATE TABLE `Watched` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `id_user` INT(11) NOT NULL, `id_film` INT(11) NOT NULL, PRIMARY KEY (`id`) , FOREIGN KEY (id_user) REFERENCES users(id), FOREIGN KEY (id_film) REFERENCES film(id) ) ENGINE=InnoDB CHARACTER SET=UTF8;
CREATE TABLE `Read` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `id_user` INT(11) NOT NULL, `id_book` INT(11) NOT NULL, PRIMARY KEY (`id`) , FOREIGN KEY (id_user) REFERENCES users(id), FOREIGN KEY (id_book) REFERENCES book(id) ) ENGINE=InnoDB CHARACTER SET=UTF8;
и вот несколько Insert-ов (не обращайте внимание на схожесть жанров музыки, книг и фильмов)
INSERT INTO `Users`(`id`, `username`, `password`, `about`) VALUES ('','User1','213','something') INSERT INTO `Users`(`id`, `username`, `password`, `about`) VALUES ('','User2','313','something') INSERT INTO `genres`(`genre_id`, `genre_name`) VALUES ('','Genre1') INSERT INTO `genres`(`genre_id`, `genre_name`) VALUES ('','Genre2') INSERT INTO `genres`(`genre_id`, `genre_name`) VALUES ('','Genre3') INSERT INTO `book`(`book_id`, `title`,'id_genre','Author') VALUES ('','Book1','1','Author1') INSERT INTO `book`(`book_id`, `title`,'id_genre','Author') VALUES ('','Book2','2','Author2') INSERT INTO `music`(`music_id`, `title`,'id_genre','Compositor') VALUES ('','Song1','1','Compositor1') INSERT INTO `music`(`music_id`, `title`,'id_genre','Compositor') VALUES ('','Song2','2','Compositor2') INSERT INTO `film`(`film_id`, `title`,'id_genre','Producer') VALUES ('','Film1','1','Producer1') INSERT INTO `film`(`film_id`, `title`,'id_genre','Producer') VALUES ('','Film2','2','Producer2') INSERT INTO `Read`(`id`, `id_user,'id_book') VALUES ('','1','1') INSERT INTO `Read`(`id`, `id_user,'id_book') VALUES ('','1','2') INSERT INTO `Read`(`id`, `id_user,'id_book') VALUES ('','2','1') INSERT INTO `Listened`(`id`, `id_user,'id_music') VALUES ('','1','1') INSERT INTO `Listened`(`id`, `id_user,'id_music') VALUES ('','1','2') INSERT INTO `Listened`(`id`, `id_user,'id_music') VALUES ('','2','1') INSERT INTO `Watched`(`id`, `id_user,'id_film') VALUES ('','1','1') INSERT INTO `Watched`(`id`, `id_user,'id_film') VALUES ('','1','2') INSERT INTO `Watched`(`id`, `id_user,'id_film') VALUES ('','2','1')

Ответ

Вы работаете с SQL базой данных. SQL позволяет получить любые данные в любом нужном виде и в 99% случаев это делается одним запросом. Если при работе с SQL вам приходится использовать "цикл" и этот цикл предназначен не для вывода готовых данных из БД на клиента - то скорее всего вы что то делаете не так. Полное содержимое для таблицы stats можно получить следующим запросом:
select u1, u2, max(prcnt*(source='Read')) as bks_prcnt, max(prcnt_rev*(source='Read')) as bks_prcnt_rev, max(prcnt*(source='List')) as flm_prcnt, max(prcnt_rev*(source='List')) as flm_prcnt_rev, max(prcnt*(source='Watch')) as msc_prcnt, max(prcnt_rev*(source='Watch')) as msc_prcnt_rev from ( select * from ( select least(u1,u2) u1, greatest(u1,u2) u2, 'Read' as source, max((u1u2)*prcnt) prcnt_rev from ( select R1.id_user u1, R2.id_user u2, round(count(1)*100/R.all_cnt) prcnt from `Read` R1, `Read` R2, ( select id_user u, count(1) all_cnt from `Read` group by id_user ) R where R1.id_book=R2.id_book and R1.id_user!=R2.id_user and R.u=R1.id_user group by R1.id_user, R2.id_user ) A group by least(u1,u2), greatest(u1,u2) ) A UNION ALL select * from ( select least(u1,u2) u1, greatest(u1,u2) u2, 'List' as source, max((u1u2)*prcnt) prcnt_rev from ( select R1.id_user u1, R2.id_user u2, round(count(1)*100/R.all_cnt) prcnt from `Listened` R1, `Listened` R2, ( select id_user u, count(1) all_cnt from `Listened` group by id_user ) R where R1.id_music=R2.id_music and R1.id_user!=R2.id_user and R.u=R1.id_user group by R1.id_user, R2.id_user ) A group by least(u1,u2), greatest(u1,u2) ) A UNION ALL select * from ( select least(u1,u2) u1, greatest(u1,u2) u2, 'Watch' as source, max((u1u2)*prcnt) prcnt_rev from ( select R1.id_user u1, R2.id_user u2, round(count(1)*100/R.all_cnt) prcnt from `Watched` R1, `Watched` R2, ( select id_user u, count(1) all_cnt from `Watched` group by id_user ) R where R1.id_film=R2.id_film and R1.id_user!=R2.id_user and R.u=R1.id_user group by R1.id_user, R2.id_user ) A group by least(u1,u2), greatest(u1,u2) ) B ) C
Если записей в БД не много - то можно всегда получать нужные данные на лету. Если тормозит - то можно их, конечно, кешировать в таблице stats. Запрос легко переделывается для получения похожих пользователей относительно конкретного. Для этого надо добавить условия с ID нужного пользователя во все 3 подзапроса для алиасов R1, а так же в самый глубокий подзапрос получающий all_cnt.
Вообще запрос мог бы быть гораздо проще и короче, если бы вы не захотели в одной записи видеть как прямой так и обратный процент. Из за него приходится сначала получать эти 2 процента отдельными строками, а потом затягивать в одну строку группировкой по least(), greatest() (которая оставляет только строки где первый ID меньше второго).
Так же данный запрос был бы в 3 раза короче (и заодно упростилась бы работа с БД практически везде) если бы вы свели таблицы Book/Music/Film в одну, просто добавив в запись поле "тип контента" и сведя равнозначные поля к одному. После этого таблицы Read/Listed/Watched так же сводятся в одну таблицу и мы можем посчитать как общий процент так и проценты в разрезе типов контента управляя фразой group by в одном коротком запросе.
А что до таблицы stats, то если она понадобится, из нее следует удалить поле id, первичный ключ сделать PRIMARY KEY (user_one, user_two). После этого писать/обновлять данные в ней можно будет как:
insert into stats select ВОТ_ТОТ_БОЛЬШОЙ_ЗАПРОС on duplicate key update bks_prcnt=values(bks_prcnt), bks_prcnt_rev=values(bks_prcnt_rev), ...
Итого: Подучите SQL, он на самом деле довольно простой, несколько базовых элементов можно вкладывать друг в друга сколько угодно глубоко и описывать любой разрез данных. Изучать можно как раз по запросу вверху, берите из него небольшие куски, отдельно их пробуйте, смотрите что они дают, экспериментируйте. И запомните, в SQL нет вопроса "можно ли это сделать", есть только вопрос "как это сделать".
P.S. Многих наверняка напряжет "странная" запись max((u1

Как вставить файл из папки в postgresql?

Как из папки на компьютере ( не из корневой папки бд ) вставить файл в одно из полей таблицы. Нужно что-то типа
insert into test(idserial, contenttext) values (4, нужнаяфункция('D:\html\ex02.xml'));
Знаю, что можно использовать copy, pg_read_file, но не понимаю как. P.S. Просто вставить текст из файла xml я могу, нужно именно вставлять файл из папки.

Ответ

insert into test(idserial, contenttext) values (4, pg_read_file('ex02.xml'));
Два момента: pg_read_file разрешено использовать только суперпользователю по соображениям безопасности. Если вы понимаете, что делаете, то обойти это ограничение возможно, например, созданием новой функции от имени суперпользователи и помеченной как security definer. От суперпользователя объявление функции:
CREATE OR REPLACE FUNCTION get_text_document(p_filename CHARACTER VARYING) RETURNS TEXT AS $$ SELECT pg_read_file($1); $$ LANGUAGE sql VOLATILE SECURITY DEFINER;
Затем можно её использовать от обычного пользователя
insert into test(idserial, contenttext) values (4, get_text_document('ex02.xml'));
Второй момент более проблемный: читать так можно только из директории, где лежит сама база или логи. Относительный путь выше по иерархии так же запрещён. Поэтому необходимо либо файл перебросить в нужное место, либо использовать симлинк на директорию (хотя надо бы проверить, вдруг и по симлинку ходить откажется) либо использовать другой способ.
Есть вариант через large object, опять же хранимка от суперпользователя:
create or replace function bytea_import(p_path text, p_result out bytea) language plpgsql as $$ declare l_oid oid; r record; begin p_result := ''; select lo_import(p_path) into l_oid; for r in ( select data from pg_largeobject where loid = l_oid order by pageno ) loop p_result = p_result || r.data; end loop; perform lo_unlink(l_oid); end;$$;
Оригинальная функция возвращает bytea, не стал это изменять в самой функции. Перекодировать можно вот так:
insert into test(idserial, contenttext) values (4, convert_from(bytea_import('/etc/fstab'), 'utf8'));
Можно использовать отдельное расширение с базовыми функциями ввода-вывода.
Или можно обратиться опять же к хранимым языкам, но помеченным как небезопасные: pl/perlu или pl/pythonu. Небезопасные они как раз потому, что могут обращаться в том числе к файловой системе беспрепятственно от имени пользователя базы данных. Например
CREATE FUNCTION gettext(url TEXT) RETURNS TEXT AS $$ import urllib2 try: f = urllib2.urlopen(url) return ''.join(f.readlines()) except Exception: return "" $$ LANGUAGE plpythonu;
insert into test(idserial, contenttext) values (4, gettext('file://D:\html\ex02.xml'));

Найти свободные строки в промежутках дат

Подскажите, как найти свободные classrooms.classroom_id для определенного services.service_id: такие, что не находятся в services_classrooms, или такие, что даты определенного services.service_id: services.service_start и services.service_end не пересекаются с датами других services.service_id связанных с classrooms.classroom_id находящимся в services_classrooms (Рис. 1)
Рис. 1

mysql> show columns from classrooms; +--------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+----------------+ | classroom_id | int(11) | NO | PRI | NULL | auto_increment | | classroom | varchar(10) | NO | UNI | NULL | | +--------------+-------------+------+-----+---------+----------------+ mysql> show columns from services_classrooms; +--------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+---------+------+-----+---------+-------+ | classroom_id | int(11) | NO | PRI | NULL | | | service_id | int(11) | NO | PRI | NULL | | +--------------+---------+------+-----+---------+-------+ mysql> show columns from services; +---------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+----------+------+-----+---------+----------------+ | service_id | int(11) | NO | PRI | NULL | auto_increment | | lesson_id | int(11) | NO | MUL | NULL | | | worth_id | int(11) | NO | MUL | NULL | | | service_end | datetime | NO | | NULL | | | service_start | datetime | NO | | NULL | | +---------------+----------+------+-----+---------+----------------+

Ответ

Вариант без UNION и вложенных запросов. Так же, дополнительно проверяет на пересечение аудитории текущего сервиса с другими сервисами в таблице.
SELECT c.classroom_id, c.classroom FROM services s -- перебираем все аудитории JOIN classrooms c ON (1 = 1) -- ищем все сервисы привязанные к аудиториям LEFT JOIN services s1 ON s1.classroom_id = c.classroom_id -- сервис для которого ищем свободные аудитории WHERE s.service_id = 1 -- группируем полученные записи по аудиториям GROUP BY c.classroom_id, c.classroom -- ищем минимальное значение в группировке по условиям -- если минимальное значение равно 1, то аудитория доступна HAVING MIN(CASE -- если у сервиса уже назначена эта аудитория WHEN s.classroom_id = c.classroom_id AND s.service_id = s1.service_id THEN 1 -- если аудитория заданная у сервиса s1 не пересекается по датам с сервисом s WHEN (s.service_start >= s1.service_end OR s.service_end <= s1.service_start) AND s.service_id <> s1.service_id THEN 1 -- если у аудитории нет ни одного назначенного сервиса WHEN (s1.service_id IS NULL) THEN 1 -- иначе будет 0 ELSE 0 END) = 1;

Заполнить базу данных данными

Есть три таблицы
CREATE TABLE students( stId SERIAL PRIMARY KEY, firstName VARCHAR(64), secondName VARCHAR(64), middleName VARCHAR(64));
CREATE TABLE subjects( sbId SERIAL PRIMARY KEY, title VARCHAR(64));
CREATE TABLE assessments( assId SERIAL PRIMARY KEY, valuation VARCHAR(64), stId INTEGER REFERENCES students, sbId INTEGER REFERENCES subjects);
Какие есть способы автоматического заполнения таблиц при помощи PL\pgSQL? Есть ли способ сгенерировать случайне данные(возможно "осмысленные") для таблицы students и subjects?

Ответ

Вот мое решение. может кому-то пригодится.
INSERT INTO subjects (title) VALUES ('Алгебра'), ('Геометрия'), ('Физика'), ('Экономика'), ('Английский'), ('Религия'), ('История'), ('ПТЦА'), ('Радиоматериалы'), ('Программирование'), ('Метрология'), ('Теория цепей'), ('Компьютерная графика'), ('Цифровые утсройства'), ('Философия'), ('Основы права'), ('Механика'), ('Радиоавтоматика'), ('Социология'), ('Политология');
CREATE OR REPLACE FUNCTION make_random_students() RETURNS int AS $$ DECLARE r record; studentsCount int; names VARCHAR[]; secondnames VARCHAR[]; middlenames VARCHAR[]; arr_names_length int; arr_secondnames_length int; arr_middlenames_length int; BEGIN studentsCount := 0; names := ARRAY['Сергей', 'Антон', 'Михаил', 'Степан', 'Семен', 'Николай', 'Василий', 'Виктор', 'Геннадий', 'Александр', 'Владимир', 'Денис', 'Дмитрий', 'Алексей', 'Константин', 'Евгений', 'Борис', 'Виталий', 'Станислав', 'Анатолий']; secondnames := ARRAY['Сергеев', 'Антонов', 'Михаилов', 'Степанов', 'Семенов', 'Николаевский', 'Васильев', 'Викторов', 'Геннадиев', 'Александров', 'Владимирский', 'Денисов', 'Дмитриев', 'Алексеев', 'Константинов', 'Евгениев', 'Борисов', 'Витальев', 'Станиславский', 'Анатольев']; middlenames := ARRAY['Сергевич', 'Антонович', 'Михаилович', 'Степанович', 'Семенович', 'Николаевич', 'Васильевич', 'Викторович', 'Геннадьевич', 'Александрович', 'Владимирович', 'Денисович', 'Дмитриевич', 'Алексеевич', 'Константинович', 'Евгениевич', 'Борисович', 'Витальевич', 'Станиславович', 'Анатольевич']; arr_names_length := array_length(names, 1); arr_secondnames_length := array_length(secondnames, 1); arr_middlenames_length := array_length(middlenames, 1); FOR i IN 1..50000 LOOP INSERT INTO students (firstName, secondName, middleName) VALUES (names[trunc(random()*arr_names_length)+1], secondnames[trunc(random()*arr_secondnames_length)+1], middlenames[trunc(random()*arr_middlenames_length)+1]); studentsCount := studentsCount+1; END LOOP; RETURN studentsCount; END; $$ LANGUAGE plpgsql;
SELECT make_random_students();
CREATE OR REPLACE FUNCTION make_random_assessments() RETURNS int AS $$ DECLARE student record; subject record; assessmentCount int; BEGIN assessmentCount := 0; FOR student IN SELECT * FROM students LOOP FOR subject IN SELECT * FROM subjects LOOP INSERT INTO assessments (valuation, stId, sbId) VALUES (trunc(random()*5)+1, student.stID, subject.sbID); assessmentCount := assessmentCount+1; END LOOP; END LOOP; RETURN assessmentCount; END; $$ LANGUAGE plpgsql;
SELECT make_random_assessments();

INSERT запрос без VALUES

Привет всем. Вот вопрос, который я имел в виду (не для SET):
Есть запрос такой:
mysqli_query($dataBase, "INSERT INTO `Users` VALUES ('','$email','$name','$pass','$gend','$startImg');");
Который хотелось бы записать примерно так:
INSERT INTO `Users` VALUES `id` = 2, `email` = '$email', `nickname` = '$name';
Как это сделать правильно. PDO желания использовать нет (имеенованые плейсхолдеры).

Ответ

INSERT INTO `Users` SET `id` = 2, `email` = '$email', `nickname` = '$name';
Ссылка на документацию, второй вариант синтаксиса.

SQL - Дубликаты при использовании UPDATE и REPLACE(UUID())

Имеется таблица, в которой необходимо создать UUID бинарного формата (binary(16)) для старых записей, которым он не задан. Выполняю запрос:
UPDATE `table` SET uuid=UNHEX(REPLACE(UUID(), '-', '')) WHERE uuid IS NULL;
И после обновления лишь одной записи получаю подобные ошибки:
Дублирующаяся запись '\x8B';\xA6T\xAE\x11\xE7\x9B\x0F\xF0yYry\xD5' по ключу 'uuid'
Подобное поведение наблюдается только при наличии REPLACE в запросе. Товарищи специалисты, подскажите решение этой проблемы, пожалуйста.

Ответ

Что делаете вы:
Находите все записи без uuid Генерируете один какой-то UUID Каждую запись пытаетесь этим UUID обновить.
Если представить что ваш UUID, как среднее случайно число, равен четырём, то вы пытаетесь сделать примерно следующее:
UPDATE `table` SET uuid=4 WHERE uuid IS NULL;
Естественно возникает ошибка.
У этой проблемы есть правильные решения, и есть быстрые.
Быстрое решение
Если быстро, то можно выдумать свой собственный UUID, который вычисляется как функция от уникальной части строки в БД. Например, от её ID:
UPDATE `table` SET `uuid` = UNHEX(MD5(CONCAT(UUID(), `id`))) WHERE `uuid` IS NULL;
Так, каждая запись будет обновляться своим собственным, в какой-то мере случайным, UUID.
Правильное решение
Если нужно решить правильно, то, надеюсь, местные эксперты по хранимым процедурам наведут на правильное направление в другом ответе.

Что за параметры _client_enable_auto_unregister и _undo_autotune?

В настройках бд увидел строчки:
_client_enable_auto_unregister = TRUE _undo_autotune = FALSE
Понятно, что знак подчеркивания перед параметром говорит о том, что эти параметры не документированы и не рекомендуются к использованию самостоятельно. Но что они делают? Про _undo_autotune нашел, что он отвечает за enable auto tuning of undo_retention, но не понял, что под этим подразумевается.

Ответ

Скрытый параметр _client_enable_auto_unregister используется для "лечения" Bug 9735536 в Oracle 11.2.0.4 и старше (см. SOLUTION ниже).
Описание "бага":
Event Monitor (EMON) slave process is consuming CPU. Multiple stacks from the process obtained via
connect / as sysdba
oradebug setospid 1379
or use the following to find EMON process
In 11g ps -ef | grep EMON
In 12c ps-ef |grep ennn
oradebug SHORT_STACK
have the form
Oracle pid: 43, Unix process pid: 1379, image: oracle@feltux3154 (E000)
_write()+10<-nttwr()+275<-nsntwrn()+111<-nspsend()+935<-nsdo()+4694<-nsfull_sd()+46<-kpcesend()+952<-kponsnd()+392<-kponepms()+1729<-kponprmsg()+405<-kponemn0()+597<-kponemn()+1152<-ksvrdp()+3653<-opirip()+901<-opidrv()+684<-sou2o()+87<-opimai_real()+280<-ssthrdmain()+295<-main()+203<-_start()+108
which indicates it is stuck in a network write.
CAUSE:
The cause of this issue has been identified in unpublished enhancement Bug 9735536. The emon process is stuck in a network write probably trying to communicate with a client that is not responding and this fix detects this and removes the unreachable client.
SOLUTION:
The workaround is to kill the emon slave process via
kill -9 ps_id
where ps_id is the process id of the emon slave.
The emon slave will automatically restart when it is next required to do so.
To permanently resolve the issue if the release is pre-11.2.0.3 apply Patch 9735536 (Just apply the fix, no need to set any underscore parameters to activate)
In 11.2.0.4 onwards do the following to enable the fix for unpublished Bug 9735536 :
connect / as sysdba
alter system set "_client_enable_auto_unregister"=true scope=spfile
shutdown immediate
startup

Как вывести значения для группы через запятую

есть такая таблица, idcode - это один товар(их много). Как из бд не выводить name похожего idcode но выводить все size.
id | name | idcode | size 1 | Hello | 1111 | 12 2 | Hello | 1111 | 13 2 | Hello | 1111 | 14
То есть желаемый результат:
Hello 1111 12,13,14

Ответ

Используем функцию GROUP_CONCAT вместе с группировкой
SELECT name, GROUP_CONCAT(idcode) FROM table_name GROUP BY name;