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

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

Здравствуйте уважаемые жители. Есть селект (FB1.5):

SELECT SUBJECTID, (select count(*) from clients2subjects c2s where c2s.subjectfk=SUBJECTS.subjectid) as tt
FROM SUBJECTS WHERE COURSEFK=150

Он возвращает результат, например:
SUBJECTID|TT
225 |12
226 |13

Как я должен модифицировать селект, что бы выбрать одну запись с максимальным TT

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

У меня есть одна идея, но она немного извращенная:

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

SELECT FIRST 1 SUBJECTID, (select count(*) from clients2subjects c2s where c2s.subjectfk=SUBJECTS.subjectid) AS TT
FROM SUBJECTS WHERE COURSEFK=150 ORDER BY (select count(*) from clients2subjects c2s where c2s.subjectfk=SUBJECTS.subjectid) DESC

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

Жду советов. А так вообще это надо для такого селекта, считающего статистику:

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

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

Ivan_Pisarevsky

Хм... да... уж...

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

Какой тайный смысл подсчитаь каунт а потом его еще раз пересчитать в ордер бай?

Merlin · Сообщение **Merlin** » 08 дек 2005, 14:04

Бывает такая необходимость. Аналогично бывает нужно с максимальной суммой. В двойке можно через select from select, сейчас же либо так как он делает, либо создавая вьюху с агрегатами типа каунт или сум и выбирая из неё макс.

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

не. я когда вижу такие запросы, чую что или в таблицах что-то не то, или с составлением запроса проблемы (то есть, его надо писать ВООБЩЕ не так).

Ivan_Pisarevsky

Бывает такая необходимость.

просто я про запрос

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

SELECT FIRST 1 SUBJECTID, (select count(*) from clients2subjects c2s where c2s.subjectfk=SUBJECTS.subjectid) AS TT 
FROM SUBJECTS WHERE COURSEFK=150 ORDER BY (select count(*) from clients2subjects c2s where c2s.subjectfk=SUBJECTS.subjectid) DESC

or

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

SELECT FIRST 1 SUBJECTID, 
           (select count(*) from clients2subjects c2s 
            where c2s.subjectfk=SUBJECTS.subjectid) AS TT 
FROM SUBJECTS 
WHERE COURSEFK=150 
ORDER BY 2 DESC

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

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

Структура таблиц такая:

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

CREATE TABLE Courses (
    CourseID NUMERIC180 NOT NULL,
    CourseStartDate INTEGER NOT NULL, // Дата начала курса
    CourseEndDate INTEGER NOT NULL, // Дата окончания
    DirDepartmentFK NUMERIC180, // Подразделение, которому пренадлежит курс.
    PRIMARY KEY (CourseID)
);

CREATE TABLE Subjects (
    SubjectID NUMERIC180 NOT NULL,
    CourseFK NUMERIC180 NOT NULL,
    PRIMARY KEY (SubjectID)
);

CREATE TABLE Clients2Subjects (
    ClientFK NUMERIC180 NOT NULL,
    SubjectFK NUMERIC180 NOT NULL,
    PRIMARY KEY (ClientFK, SubjectFK)
);

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

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

Правильный select, следующий:

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

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
     (( ДатаНачалаИзФильтра BETWEEN C.COURSESTARTDATE AND C.COURSEENDDATE ) OR
      ( ДатаОкончанияИзФильтра   BETWEEN C.COURSESTARTDATE AND C.COURSEENDDATE )) AND
     DIRDEPARTMENTFK=IDПодразделения

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

Представим себе такую ситуацию. Есть определенный курс. В этом курсе 10 предметов. На первый, третий предметы ходят 10 человек с ID (ClientID: 1,2,3,4,5,6,7,8,9,10). На второй предмет ходят 2-а человека (ID: 1,2) и т.д. Соответственно, раньше у меня выбиралось и суммировалось с основной статистикой COUNT(DISTINCT(CS.CLIENTFK)) - где были выбраны все клиенты, ходящие на данный курс. Еще раз повторюсь - этот селект - долгий. Сейчас я решил выбрать один ID предмета, но с максимальным количеством человек, ходящем на этот предмет из этого курса, т.е. изменить селект на такое:

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

SELECT
      COALESCE( SUM( (SELECT COUNT(DISTINCT(CS.CLIENTFK)) FROM CLIENTS2SUBJECTS CS
             WHERE
                  CS.SUBJECTFK = (
ЗДЕСЬ Я ВЫБИРАЮ ОДИН SubjectID, но с максимальным количеством человек, ходящем на данный курс.

SELECT FIRST 1 SUBJECTID 
FROM SUBJECTS 
WHERE COURSEFK=C.COURSEID 
ORDER BY (select count(*) from clients2subjects c2s where c2s.subjectfk=SUBJECTS.subjectid) DESC


)              ) ), 0)
FROM
    COURSES C
WHERE
     (SELECT COUNT(*) FROM SUBJECTS WHERE COURSEFK=C.COURSEID)>0 AND
     (( ДатаНачалаИзФильтра BETWEEN C.COURSESTARTDATE AND C.COURSEENDDATE ) OR
      ( ДатаОкончанияИзФильтра   BETWEEN C.COURSESTARTDATE AND C.COURSEENDDATE )) AND
     DIRDEPARTMENTFK=IDПодразделения

Последний селект - селект с погрешностью (зато быстро). Например Курс с 2-мя предметами. На первый предмет ходят люди с ID:1,2,3. На второй предмет с ID: 2,3,4,5. Второй селект сосчитает 4-е человека с ID(2,3,4,5). А должен сосчитать с ID:1,2,3,4,5. T.е. правильная статистика - 5 человек.

Какие есть предложения еще?

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

мусью с джойнами знаком?

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

kdv писал(а):мусью с джойнами знаком?

Слышал об этом, но не разу не использовал. В чем состоит идея?

Ivan_Pisarevsky

1. зачем для ключевых полей нумерик, а не интегер?
2. зачам для дат интегер, когда надо дате или таймстамп?
3. где генераторы для ключевых поей?
4. где внешние ключи?
5. где индекс по датам начала-окончания?

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

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

Ivan_Pisarevsky писал(а):1. зачем для ключевых полей нумерик, а не интегер?
2. зачам для дат интегер, когда надо дате или таймстамп?
3. где генераторы для ключевых поей?
4. где внешние ключи?
5. где индекс по датам начала-окончания?

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

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


CREATE DOMAIN NUMERIC180 AS NUMERIC(18);

CREATE DOMAIN VARCHAR1000 AS VARCHAR(1000);

CREATE DOMAIN VARCHAR100 AS VARCHAR(100);

CREATE DOMAIN CHAR1 AS CHAR(1);

CREATE DOMAIN VARCHAR20 AS VARCHAR(20);

CREATE DOMAIN VARCHAR50 AS VARCHAR(50);

/*==========================================================================*/
/*  Tables                                                                  */
/*==========================================================================*/


CREATE TABLE Courses (
    CourseID NUMERIC180 NOT NULL,
    DirDepartmentFK NUMERIC180 NOT NULL,
    DirCourseFK NUMERIC180 NOT NULL,
    CourseStartDate INTEGER NOT NULL,
    CourseEndDate INTEGER NOT NULL,
    GroupNumber VARCHAR20 NOT NULL,
    Cost INTEGER NOT NULL,
    IsMonthlyPayment SMALLINT NOT NULL CHECK (IsMonthlyPayment IN (0,1)),
    Description VARCHAR1000,
    UserFK NUMERIC180,
    PRIMARY KEY (CourseID)
);

CREATE TABLE Clients2Subjects (
    ClientFK NUMERIC180 NOT NULL,
    SubjectFK NUMERIC180 NOT NULL,
    OrganizationFK NUMERIC180,
    DateReceipt INTEGER NOT NULL,
    PRIMARY KEY (ClientFK, SubjectFK)
);

CREATE TABLE Subjects (
    SubjectID NUMERIC180 NOT NULL,
    CourseFK NUMERIC180 NOT NULL,
    DirSubjectFK NUMERIC180 NOT NULL,
    TeacherFK NUMERIC180 NOT NULL,
    Description VARCHAR1000,
    PRIMARY KEY (SubjectID)
);

/*==========================================================================*/
/*  Foreign Keys                                                            */
/*==========================================================================*/

ALTER TABLE Clients2Subjects
    ADD FOREIGN KEY (SubjectFK) REFERENCES Subjects (SubjectID);

ALTER TABLE Subjects
    ADD FOREIGN KEY (CourseFK) REFERENCES Courses (CourseID) ON DELETE CASCADE ON UPDATE CASCADE;

CREATE UNIQUE INDEX IDX_Courses3 ON Courses (CourseID);

CREATE INDEX IDX_Courses4 ON Courses (UserFK);

CREATE INDEX IDX_Clients2Subjects1 ON Clients2Subjects (ClientFK);

CREATE INDEX IDX_Clients2Subjects2 ON Clients2Subjects (OrganizationFK);

CREATE INDEX IDX_Clients2Subjects3 ON Clients2Subjects (SubjectFK);

CREATE UNIQUE INDEX IDX_Clients2Subjects4 ON Clients2Subjects (ClientFK, SubjectFK);

CREATE INDEX IDX_Subjects1 ON Subjects (CourseFK);

CREATE INDEX IDX_Subjects2 ON Subjects (TeacherFK);

CREATE INDEX IDX_Subjects3 ON Subjects (DirSubjectFK);

CREATE UNIQUE INDEX IDX_Subjects4 ON Subjects (SubjectID);

/*==========================================================================*/
/*  Generators                                                              */
/*==========================================================================*/

CREATE GENERATOR CourseID;
SET GENERATOR CourseID TO 0;

CREATE GENERATOR SubjectID;
SET GENERATOR SubjectID TO 0;

/*==========================================================================*/
/*  Triggers                                                                */
/*==========================================================================*/

SET TERM !! ;
CREATE TRIGGER BI_Courses
FOR Courses
BEFORE INSERT AS
BEGIN
  IF (NEW.CourseID IS NULL) THEN NEW.CourseID = GEN_ID(CourseID, 1);
END !!
SET TERM ; !!

SET TERM !! ;
CREATE TRIGGER BI_Subjects
FOR Subjects
BEFORE INSERT AS
BEGIN
  IF (NEW.SubjectID IS NULL) THEN NEW.SubjectID = GEN_ID(SubjectID, 1);
END !!
SET TERM ; !!

1. зачем для ключевых полей нумерик, а не интегер? - Если надо изменить - изменим
2. зачам для дат интегер, когда надо дате или таймстамп? - для кроссплатформенности. Так из php проше с датами работать

5. где индекс по датам начала-окончания? - их нету.

А скрипт нежелательно изменять. Логика уже продумана. Система работает достаточно быстро. Эта статистика - пока единственный камень.

Ivan_Pisarevsky

вот такой примитив не подойдет?

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

select count(*)

from  courses c,  Subjects s, Clients2Subjects cs

where s.coursefk = c.courseid
  and s.subjectid = cs.subjectfk
  and c.coursestartdate <= :my_date
  and c.courseenddate > :my_date

должен сосчитать кол-во народу по откытым на дату my_date курсам.

Для особо ленивых может набросаешь скриптец который нальет по несколько записей в каждую из трех таблиц.

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

Ivan_Pisarevsky писал(а):вот такой примитив не подойдет?
Код: Выделить всё
select count(*)

from  courses c,  Subjects s, Clients2Subjects cs

where s.coursefk = c.courseid
  and s.subjectid = cs.subjectfk
  and c.coursestartdate <= :my_date
  and c.courseenddate > :my_date
должен сосчитать кол-во народу по откытым на дату my_date курсам.

Для особо ленивых может набросаешь скриптец который нальет по несколько записей в каждую из трех таблиц.

Не совсем то. По сути дела мне нужна сумма: Количество уникальных учашихся на каждом курсе. Твой селект тоже с погрешностью.

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

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

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.

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

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

Ivan_Pisarevsky писал(а):несколько записей в каждую из трех таблиц.

Ловите данные: http://www.ucpskov.ru/Temp/sql.rar (20 кбайт)

Ivan_Pisarevsky

1133384400
1136062799

по какому принципу это сделано? т.е. какую чиселку я должен скормить в параметр, чтоб получить повторяемый с тобой результат? а то ни 292 ни 293 и близко не наблюдаю...

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

Ivan_Pisarevsky писал(а):1133384400
1136062799

по какому принципу это сделано? т.е. какую чиселку я должен скормить в параметр, чтоб получить повторяемый с тобой результат? а то ни 292 ни 293 и близко не наблюдаю...

php:

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

  echo strftime("%d-%m-%Y %H:%M:%S", 1133384400)."<br>";
  echo strftime("%d-%m-%Y %H:%M:%S", 1136062799)."<br>";

Result: 01-12-2005 00:00:00/31-12-2005 23:59:59

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

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

COURSES.DIRDEPARTMENTFK=2

(например, 2-е подразделение) и
промежуток времени определяется именно так

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

(( 1133384400 BETWEEN C.COURSESTARTDATE AND C.COURSEENDDATE ) OR ( 1136062799 BETWEEN C.COURSESTARTDATE AND C.COURSEENDDATE ))

Ivan_Pisarevsky

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

т.е то, что началось до второй даты и закончилось после первой?
1.
c.coursestartdate <= 1136062799 and c.courseenddate >= 1133384400
2.
(( 1133384400 BETWEEN C.COURSESTARTDATE AND C.COURSEENDDATE ) OR ( 1136062799 BETWEEN C.COURSESTARTDATE AND C.COURSEENDDATE ))

1 и 2 есть одно и то же. Ась?

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

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

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

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

Ivan_Pisarevsky писал(а):
Прошу прошения, я неправильно выразился, мне нужна сумма: Количество уникальных учашихся на каждом курсе в рамках одного подразделения в течении определенного прмежутка времени.
т.е то, что началось до второй даты и закончилось после первой?
1.
c.coursestartdate <= 1136062799 and c.courseenddate >= 1133384400
2.
(( 1133384400 BETWEEN C.COURSESTARTDATE AND C.COURSEENDDATE ) OR ( 1136062799 BETWEEN C.COURSESTARTDATE AND C.COURSEENDDATE ))

1 и 2 есть одно и то же. Ась?

Нет. Один курс имеет дату начала ДатаНач и дату окончания ДатаОкон. Есть фильтр, в котором указывается 2-е даты Дата1, Дата2.

Т.е. выбираются те курсы в промежуток проведения которых (такой промежуток [ДатаНач, ДатаОкон]) попадает дата Дата1 или Дата2.
Возможны 4-и ситуации:

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

__ДатаН_Дата1_Дата2_ ДатаО_
__Дата1_ДатаН_ДатаО_ Дата2_
__Дата1_ДатаН_Дата2_ДатаО_
__ДатаН_Дата1_ДатаО_Дата2

Вот эти четыре случая учитываются в статистике.
А вот это не учитывается:

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

__Дата1_Дата2_ДатаН_ДатаО
__ДатаН_ДатаО_Дата1_Дата2

Да ты прав, так проще. Да одно и тоже. А почему разные результаты получаются?

iBase.ru Forum

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

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

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

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