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

Ivan_Pisarevsky

Убери нафиг левые индексы (см. пост КДВ) добавь индексы по датам и сделай ключи интегер, нутром чую еще и в нумерик ключах собака порылась.

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

kdv писал(а):мрак полный. на таблице ПК, и тут же по нему сделан уникальный индекс. FK - и опять вручную создан индекс. Зачем? Про джойны не знаем, и т.п.

Купи себе книжку по
1. Firebird (Хелен Борри, свежак).
2. по проектированию баз, что-нибудь.

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

Про join - не было задачь, что бы их применять. Конечно если бы было несколько подряд идуших объединений WHERE AND ... AND ... и был бы смысл то нужно применять join. А в таком select очень тяжело увидеть join

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

SELECT COALESCE( SUM( 
    (SELECT COUNT(DISTINCT(CS.CLIENTFK)) FROM CLIENTS2SUBJECTS CS WHERE 
                                         CS.SUBJECTFK = ( 
                                          SELECT FIRST 1 SUBJECTID 
                                            FROM SUBJECTS WHERE COURSEFK=C.COURSEID ORDER BY (select count(*) from clients2subjects c2s where c2s.subjectfk=SUBJECTS.subjectid) DESC 
                                                         ) AND ( 
                                                            SELECT COUNT(*) FROM DEDUCTIONS D WHERE D.CLIENTFK=CS.CLIENTFK AND D.COURSEFK=C.COURSEID AND D."DATE"<=1136062799)=0 
                                                         ) ), 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

Не стоит упрекать нас в наших ошибках. Я видел только такой селект. Ну и что с этого, у всех бывает....

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

Про join - не было задачь, что бы их применять.

да ты шо!

один мой знакомый говорил - если ты не знаешь join, то ты фактически не знаешь sql. Вот у тебя в запросе какие то подозрительные in (select), субселекты, субселекты с first, субселекты в where, и т.п. То есть, мешанина такая, что возникают сомнения в наличии смысла даже не вникая. Уж извини.

Ivan_Pisarevsky

То есть, мешанина такая, что возникают сомнения в наличии смысла даже не вникая.

А оказалось 3 таблицы 2 попарных джойна и пара фильтрующих условий, а шоб быстро еще 6 индексов нуно

В понедельник мож еще репу почешу и склюем твово червяка на закусь, а на сегодня у меня рабочий день закончился.

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

kdv писал(а):
Про join - не было задачь, что бы их применять.
да ты шо! один мой знакомый говорил - если ты не знаешь join, то ты фактически не знаешь sql. Вот у тебя в запросе какие то подозрительные in (select), субселекты, субселекты с first, субселекты в where, и т.п. То есть, мешанина такая, что возникают сомнения в наличии смысла даже не вникая. Уж извини.

Так статистика-то в вашем итоге неправильная. В моем правильная. Следовательно в вашем подходе ошибка.

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

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

SELECT
      C.COURSEID, C.GROUPNUMBER,
      (SELECT COUNT(DISTINCT(CS.CLIENTFK)) FROM CLIENTS2SUBJECTS CS
      WHERE
           CS.SUBJECTFK IN (SELECT SUBJECTID FROM SUBJECTS WHERE COURSEFK=C.COURSEID)
    ) AS "COUNT"
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
     C.DIRDEPARTMENTFK=2
ORDER BY C.GROUPNUMBER

Если выполнить опять же очень долгий селект (1.32sec) то он в итоге выведет

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

COURSEID	GROUPNUMBER	COUNT
80	40	29
111	40/с1	2
85	45	30
88	46В	26
108	47	25
159	47с	2
89	48	28
90	49Д	19
119	51	27
123	52Д	21
129	53	28
128	54В	24
151	55	6
154	56Д	10
158	57	13
175	58В	2

В сумме по полю COUNT=292 ну ни как 293 (дает ваш select)

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

Следовательно в вашем подходе ошибка.

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

Ivan_Pisarevsky

Так статистика-то в вашем итоге неправильная. В моем правильная. Следовательно в вашем подходе ошибка.

Вот упертый, беда прям... Тебе сказано убери нафиг нумерики из ключей... я накатил твой скрипт на одну из своих игровых БД, она в первом диалекте и твои нумерики стали лихо дабл пресижнами, теперь я начинаю джойнить это дело... дальше амбиснять?

Хотя я не знаю где ты 293 откопал...
Например запрос

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

select cs.clientfk, count(*)

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 cs.clientfk

Plan
PLAN SORT (JOIN (C NATURAL,S INDEX (IDX_SUBJECTS1),CS INDEX (IDX_CLIENTS2SUBJECTS3)))

Adapted Plan
PLAN SORT (JOIN (C NATURAL,S INDEX (IDX_SUBJECTS1),CS INDEX (IDX_CLIENTS2SUBJECTS3)))

------ Performance info ------
Prepare time = 10ms
Execute time = 80ms
Avg fetch time = 3,48 ms
Current memory = 895 688
Max memory = 954 320
Memory buffers = 2 048
Reads from disk to cache = 39
Writes from cache to disk = 0
Fetches from cache = 1 462

возвращает 292 записи, по уникальному клиенту в каждой записи и кол-во его участий в занаятиях, причем всего за 80 милисекунд

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

Сделал ключевые поля типа Integer. Backup/Restore и все равно мой селект возврашает 292, а ваш возврашает 293.

select cs.clientfk, count(*)

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 cs.clientfk

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

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

SELECT 
      C.COURSEID, C.GROUPNUMBER, 
      (SELECT COUNT(DISTINCT(CS.CLIENTFK)) FROM CLIENTS2SUBJECTS CS 
      WHERE 
           CS.SUBJECTFK IN (SELECT SUBJECTID FROM SUBJECTS WHERE COURSEFK=C.COURSEID) 
    ) AS "COUNT" 
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 
     C.DIRDEPARTMENTFK=2 
ORDER BY C.GROUPNUMBER

Если проссумировать результат по полю COUNT, то получится 292.

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

avenger писал(а):все равно мой селект возврашает 292, а ваш возврашает 293.
select cs.clientfk, count(*)
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 cs.clientfk
возврашает не то что мне нужно.

да наплюйте Вы на эти join-ы! Это все чтоб мозги запудрить придумано.

avenger писал(а):Попробуйте написать селект, который возвратит сколько уникальных человек учится на каждом курсе.

пробовать и думать не охота. Чуть модифицирую запрос Ivan_Pisarevsky

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

select C.COURSEID, C.GROUPNUMBER, count(distinct CS.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 1, 2

это может и вернет 292, а может и нет.

Ivan_Pisarevsky

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

select c.courseid, count(distinct cs.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

возвращает 293... хз у тебя где-то ссылочная целостность плывет...

Ivan_Pisarevsky

да наплюйте Вы на эти join-ы! Это все чтоб мозги запудрить придумано.

Еще, блин, один ламер объявился, который сольет все в универсальное отношение, чтоб без джойнов и будет везде потом вопить какой файрберд тормознутый и глючный не может мой запрос обсчитать...

А это "s.coursefk = c.courseid and s.subjectid = cs.subjectfk" по вашему мозгопудрилка? А я, видимо по своей природной глупости, считаю это неявными джойнами...

stix-s · Сообщение **stix-s** » 13 дек 2005, 11:56

сделал таблицы, загнал данные
выполнил запрос
select cs.clientfk, count(*)
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 cs.clientfk

запрос вернул 292 строки - в чем проблемы-то?
первичные ключи делал INTEGER, внешние вообще не создавал, ибо не суть пока данные не меняются

stix-s · Сообщение **stix-s** » 13 дек 2005, 12:04

А это "s.coursefk = c.courseid and s.subjectid = cs.subjectfk" по вашему мозгопудрилка? А я, видимо по своей природной глупости, считаю это неявными джойнами...

в данном случае они и есть неявные JOIN
и чего ругаться? видимо результаты разные, поскольку данные разные или проблема все же в объявлении полей

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

Ivan_Pisarevsky писал(а):
да наплюйте Вы на эти join-ы! Это все чтоб мозги запудрить придумано.
Еще, блин, один ламер объявился

преклоняюсь перед Вашими глубокими познаниями в сиквел-таинствах, ув.Гуру. И пардон за стыренные строки из Вашего запроса в моем предыдущем посте, больше не буду.

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

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

2stix-s, Вам на ваших данных должно быть гораздо виднее, почему запросы возвращают разные наборы. Пронализируйте результаты своего запроса и запроса без подзапросов с каунт(дистинкт по фк) по конкретному фк, где эти данные разнятся. Может чего и прояснится.

Ivan_Pisarevsky

И пардон за стыренные строки из Вашего запроса в моем предыдущем посте, больше не буду.

В силу того, что я их выложил в публичном форуме и на всеобщее обозрение можете пользоваться сколько душе угодно компилировать-декомпилировать, извлекать коммерческую выгоду и тд и тп.

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

Месье знает толк в извращениях. С одинэсом знакомы не понаслышке

ОК, признаю, был груб, сорри.

stix-s · Сообщение **stix-s** » 13 дек 2005, 12:54

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

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

SELECT
C.COURSEID, C.GROUPNUMBER,
(SELECT COUNT(DISTINCT(CS.CLIENTFK)) FROM CLIENTS2SUBJECTS CS
WHERE
CS.SUBJECTFK IN (SELECT SUBJECTID FROM SUBJECTS WHERE COURSEFK=C.COURSEID)
) AS "COUNT"
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
C.DIRDEPARTMENTFK=2

ORDER BY C.GROUPNUMBER у меня в сумме по COUNT возвращает как раз 293

то есть с точностью до наоборот, чем у автора
разница тут
COURSEID GROUPNUMBER COUNT
108 47 26(25)

select c.courseid,C.GROUPNUMBER, 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
C.DIRDEPARTMENTFK=2

group by c.courseid,C.GROUPNUMBER
order by 3

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

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

Ivan_Pisarevsky писал(а):
Вот.
Месье знает толк в извращениях.

О, да! Только вот никак для себя не решу, кто я в этом виртуальном явлении: мусью или мэдам...

Ivan_Pisarevsky писал(а):С одинэсом знакомы не понаслышке

йес, в самую точку! Но как Вы догадались?!!!

Ivan_Pisarevsky писал(а):ОК, признаю, был груб, сорри.

Иван, Вы слишком серьезны!

Ivan_Pisarevsky

Но как Вы догадались?!!!

Мусье телепат?

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

Ivan_Pisarevsky писал(а):возвращает 293... хз у тебя где-то ссылочная целостность плывет...

Еще один бэкап-ресторе и два абсолютно разных запроса стали сходится....

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