Помогите модифицировать select

eg · Сообщение eg » 13 дек 2005, 14:19

stix-s писал(а):данные и структура базы не мои, все взято с форума

ой, извините, промашечка получилась.
Ребята, я запутался, уж и не знаю какие запросы в этом топике сравнивать.
Остановлюсь на

Селект без погрешности:
Код:

Код: Выделить всё

SELECT COALESCE( SUM( 
    (SELECT COUNT(DISTINCT(CS.CLIENTFK)) FROM CLIENTS2SUBJECTS CS WHERE 
                                         CS.SUBJECTFK IN ( 
                                                            SELECT SUBJECTID FROM SUBJECTS WHERE COURSEFK=C.COURSEID 
                                                         ) 
                                                          )), 0) 
FROM COURSES C WHERE 
    (SELECT COUNT(*) FROM SUBJECTS WHERE COURSEFK=C.COURSEID)>0 AND 
    (( 1133384400 BETWEEN C.COURSESTARTDATE AND C.COURSEENDDATE ) OR ( 1136062799 BETWEEN C.COURSESTARTDATE AND C.COURSEENDDATE )) 
AND DIRDEPARTMENTFK=2

возврашает число 293, а твой
Код:

Код: Выделить всё

select COUNT(DISTINCT(CS.CLIENTFK)) 
from  courses c,  Subjects s, Clients2Subjects cs 
where s.coursefk = c.courseid 
  and s.subjectid = cs.subjectfk and 
    (( 1133384400 BETWEEN C.COURSESTARTDATE AND C.COURSEENDDATE ) OR ( 1136062799 BETWEEN C.COURSESTARTDATE AND C.COURSEENDDATE )) 
  and DIRDEPARTMENTFK=2

возрашает 292.
Только чего-то не соображу, почему разница все-таки есть...

зная только два слова из sql-науки (селект и фром), доложу Вам, что это разные запросы, как по эстетическим соображениям, так и по функциональным. Читаю вслух как могу:
в первом случае для _каждой_ строки в COURSES подсчитаем количество уникальных CS.CLIENTFK и просуммируем эти количества вообще для всего запроса;
во втором случае подсчитывается только количество уникальных CS.CLIENTFK для всей выборки.
Они, эти запросы, оба правильные, отличаются только степенью эффективности и самими задачами, которые они решают.
А какую задачу решает зачинатель обсуждения?
Хотя, особо не вникаю, может опять не тому и не на тот вопрос отвечаю. Уж не ругайте.

Ivan_Pisarevsky

Честно говоря в этом трехэтажном "правильном" запросе я не разбирался, слишком хитроумен, посему гарантировать повторяемость его результатов как минимум трудно... Свой запрос я стряпал от словесной постановки задачи и исходных таблиц. С прицелом на максимальную простоту и скорость исполнения.

eg · Сообщение eg » 13 дек 2005, 14:31

avenger писал(а):Всем спасибо за помощь!

Ну вот, я опять не в теме

, пока писал предыдущий пост, все уже наладилось

Сообщение **kdv** » 13 дек 2005, 14:31

разница в том, что первое решение "прямолинейное", а второе - сделано исходя из связей между таблицами. Считают они одно и то же, кстати. Я почему и спросил автора топика, почему такая нелюбовь к джойнам. Ведь они именно делаются по смысловым связям между таблицами.

avenger · Сообщение **avenger** » 13 дек 2005, 14:32

Селект без погрешности:
Код:

Код: Выделить всё

SELECT COALESCE( SUM( 
    (SELECT COUNT(DISTINCT(CS.CLIENTFK)) FROM CLIENTS2SUBJECTS CS WHERE 
                                         CS.SUBJECTFK IN ( 
                                                            SELECT SUBJECTID FROM SUBJECTS WHERE COURSEFK=C.COURSEID 
                                                         ) 
                                                          )), 0) 
FROM COURSES C WHERE 
    (SELECT COUNT(*) FROM SUBJECTS WHERE COURSEFK=C.COURSEID)>0 AND 
    (( 1133384400 BETWEEN C.COURSESTARTDATE AND C.COURSEENDDATE ) OR ( 1136062799 BETWEEN C.COURSESTARTDATE AND C.COURSEENDDATE )) 
AND DIRDEPARTMENTFK=2

возврашает число 293, а твой
Код:

Код: Выделить всё

select COUNT(DISTINCT(CS.CLIENTFK)) 
from  courses c,  Subjects s, Clients2Subjects cs 
where s.coursefk = c.courseid 
  and s.subjectid = cs.subjectfk and 
    (( 1133384400 BETWEEN C.COURSESTARTDATE AND C.COURSEENDDATE ) OR ( 1136062799 BETWEEN C.COURSESTARTDATE AND C.COURSEENDDATE )) 
  and DIRDEPARTMENTFK=2

возрашает 292.

А эти два селекта дают разные результаты - 292 и 293 соответственно!

avenger · Сообщение **avenger** » 13 дек 2005, 14:38

Мои страшные селекты дают и суммарную и подробную статистику правильно(2-е число: сумма по полю COUNT c помощью калькулятора), но выполняются долговато. Так как лучше пусть пользователи мучаются, ожидая статистику или мы найдем где собака зарылась? Надо-то всего, чтобы и подробная и суммарная статистика давали одинаковые результаты....

Сообщение **kdv** » 13 дек 2005, 14:44

есть подозрение, что в данных есть null или что-то в этом роде. Потому что неявный join такую запись в расчет не примет, в то время как навороченные друг в друга вложенные суб-селекты - запросто. Отсюда и разница в результате.

Ivan_Pisarevsky

Так как лучше пусть пользователи мучаются, ожидая статистику или мы найдем где собака зарылась? Надо-то всего, чтобы и подробная и суммарная статистика давали одинаковые результаты....

проверяй ссылочную целостность... (об этом тебе уж, наверно, в пятый раз говорят)

Попробуй заменить неявные джойны на лефт джойны и столкнуть результаты. (соответственно прочти хотя бы статью про джойны на www.ibase.ru) По идее твой запрос с подзапросами должен соответствовать лефт джойн, а не тому, как я написал запрос выше.

eg · Сообщение eg » 13 дек 2005, 14:55

kdv писал(а):разница в том, что первое решение "прямолинейное", а второе - сделано исходя из связей между таблицами.

они РАЗНЫЕ!

kdv писал(а):Считают они одно и то же, кстати.

Дмитрий, если я и Вы, оба, имеем в виду одни и теже запросы (из моего поста), готов поспорить с вами на какое-нибудь ощутимое материальное благо. Мои предложения: хороший (по всем понятиям) коньяк или книга с личной подписью Хелен (взамен могу выставить коньяк или книгу Хелен без ее подписи).

А эти два селекта дают разные результаты - 292 и 293 соответственно!

Похоже я и вправду хреновенько объяснил. Эти запросы разные!!!! Запрос с джойном будет возвращать или такое же количество записей или меньшее, это зависит от данных.

Ivan_Pisarevsky

eg, хорош разводить на "слабО"

Хотел бы бы я увидеть картину, как Вы объясняете КДВ разницу между джойнами

По словесной постановке односторонние джойны здесь нафиг не нужны, но в силу того, что где-то посыпалась целостность имеем разные результаты.
А мне просто хотелось помочь автору понять КАК надо идти дальше, а не дать просто готовый результат.

eg · Сообщение eg » 13 дек 2005, 15:20

Ivan_Pisarevsky писал(а):eg, хорош разводить на "слабО"

это не развод.

Ivan_Pisarevsky писал(а):Хотел бы бы я увидеть картину, как Вы объясняете КДВ разницу между джойнами

при большом желании эту картину можно узрить в старых постах другого форума.

Ivan_Pisarevsky писал(а):По словесной постановке односторонние джойны здесь нафиг не нужны, но в силу того, что где-то посыпалась целостность имеем разные результаты.

Целостность тут абсолютно не в кассу. При визуальном осмотре самих запросов ответственно заявляю: запросы отличаются по смыслу.

Ivan_Pisarevsky писал(а):А мне просто хотелось помочь автору понять КАК надо идти дальше, а не дать просто готовый результат.

для этого нужно точное знасть, что нужно автору. Он уверен в правильности результата своего многоэтажного запроса, однако на словах просит другого.

Сообщение **kdv** » 13 дек 2005, 15:20

они РАЗНЫЕ!

есть модель данных, спроектированная на реляционной модели. Лично мне непонятно, как в одной модели данных можно "выполнять разное" пытаясь получить одну и ту же информацию. Либо выберется эта информация, одна и та же, независимо от запроса, либо будет выдана бурда. Либо, в конце-концов, из-за глюков сервера мы получим третий результат.

готов поспорить

не люблю спорить (за редким исключением). Кроме того
1. у меня нет книги Хелен с ее подписью.
2. к исходному запросу в этом топике я испытываю отвращение, извините.

Запрос с джойном будет возвращать или такое же количество записей или меньшее, это зависит от данных.

дык. а запрос с outer join может вернуть или большее, или такое же, или меньшее количество данных, по сравнению с inner join.

Вот допустим, вот этот кусок

FROM COURSES C WHERE
(SELECT COUNT(*) FROM SUBJECTS WHERE COURSEFK=C.COURSEID)>0

что означает? Что COURSEFK может содержать null? Разве нет? А он должен, может? (по условиям задачи) То есть, для курса может не быть темы курса?
Посмотри на свое условие задачи:

Смысл такой: Есть организация занимающиеся обучением. Есть курсы(Courses) которые она проводит. В каждом курсе обязательно есть предметы (Subjects) (>=1). Клиенты, которые приходят на курс - соответственно записываются на предметы(Clients2Subjects).

А надо подсчитать Количество клиентов, которые ходят на курсы, в определенном подразделении в определенный промежуток времени.

все предельно ясно, четкие связи.
курс-предмет-клиент. Целиком число курсантов ходящих на предметы считается даже без таблицы курс. Но - по таблице курс мы вытаскиваем отдел и даты проведения курса. Где тут нет объединений? Где необходимость во всяких жутких вложениях?

Сообщение **kdv** » 13 дек 2005, 15:32

то есть, описываю нормальную логику при написании запроса.

1. клиенты записываются на темы курса (у курса есть хотя бы одна тема, всегда). значит можем объединить клиентов и темы.
это первый join
2. отдел (и даты) указаны для курсов, которые четко связаны с темами. Курсы БЕЗ тем нас не интересуют, потому что клиенты на них НЕ могут записаться (поскольку записываются на темы)
это второй join
3. добавляем условия отбора дат и отделов
4. в результате получается избыток клиентов, если один клиент записывался на несколько тем, или на разные темы разных курсов.
включаем distinct.
5. нужно общее число курсантов? добавляем count.

Что в результате получится идентично запросу, предложенному Иваном Писаревским. И выдаст именно те данные, которые ДОЛЖНЫ быть.

eg · Сообщение eg » 13 дек 2005, 15:35

kdv писал(а):2. к исходному запросу в этом топике я испытываю отвращение, извините.

думаешь он мне очень нравится? Pа забором из подзапросов ты смысловую разницу не ощутил. Имеем:

Код: Выделить всё

SELECT COALESCE( SUM( 
    (SELECT COUNT(DISTINCT(CS.CLIENTFK)) FROM CLIENTS2SUBJECTS CS WHERE 
                                         CS.SUBJECTFK IN ( 
                                                            SELECT SUBJECTID FROM SUBJECTS WHERE COURSEFK=C.COURSEID 
                                                         ) 
                                                          )), 0) 
FROM COURSES

если есть только два курса, в каждом по одному предмету, каждый предмет посещает только один и тот же студент сколько вернет запрос? ДВА. Запрос с join и count выдаст число уникальных идентификаторов студентов - ОДИН.

Сообщение **kdv** » 13 дек 2005, 15:38

eg писал(а):За забором из подзапросов ты смысловую разницу не ощутил

согласен, ты абсолютно прав, именно поэтому я не люблю когда запросы пишут именно так

eg · Сообщение eg » 13 дек 2005, 16:01

kdv писал(а):именно поэтому я не люблю когда запросы пишут именно так

да и я того же мнения, тем более даже этот замудренный запрос автора можно без подзапросов повторить

P.S. Дмитрий, приношу извинения за бардак, который я тут развел из шалости, больше не буду.

Ivan_Pisarevsky

Может и я был неправ, насчет посыпавшеся целостности, тк. в суть многоэтажного запроса не вникал...

avenger · Сообщение **avenger** » 15 дек 2005, 13:14

Большое спасибо ag, Ivan_Pisarevsky и всем кто принимал участие.

Проблема решена следующим образом:

Код: Выделить всё

SELECT C.COURSEID, COUNT(DISTINCT(CLIENTFK))
FROM COURSES C, SUBJECTS S, CLIENTS2SUBJECTS CS 
WHERE S.COURSEFK = C.COURSEID 
AND S.SUBJECTID = CS.SUBJECTFK 
AND C.COURSESTARTDATE <= 1136062799 
AND C.COURSEENDDATE >= 1133384400 
AND C.DIRDEPARTMENTFK=2 
GROUP BY C.COURSEID

Этот селект возврашает точную статистику(сколько уникальных человек учится на курсе). Для того что бы получить сколько человек учится всего мне осталось лишь просуммировать значения в поле COUNT(с помощью стороннего языка. Благо в php есть функция array_sum).

В итоге время генерации отчетов увеличилось на порядок (сейчас <1сек, а раньше >30сек). И статистика при этом осталось точной. А я уже думал что пидется переходить к статистике с погрешностью.

Всем еще раз спасибо!

Ivan_Pisarevsky

Тащщи пиво, ну хоть виртуальное, ща обмоем

А про джойны почитай, без них далеко не уедешь

Сообщение **kdv** » 15 дек 2005, 21:06

А про джойны почитай, без них далеко не уедешь

в данном случае, главное что он уже есть

а неявный или явный - дело десятое...