SQL Group by с агрегацией и Distinct

Данный :

Страховые компании (cid, название, телефон, адрес)

Врачи (делали, имя, специальность, адрес, телефон, возраст, код)

Пациенты (pid, имя, адрес, телефон, возраст, пол, cid)

Посещения (видео, сделал, pid, дата, описание)

где

cid - Insurance Company code
did - doctor code
pid - patient code
vid - code of visit

и ЗАДАЧА: Для каждого врача верните количество (разных) пациентов в возрасте 20-25 лет:

is : 

SELECT  V.did, COUNT ( V.pid ) 
FROM    (   SELECT  DISTINCT V1.did, V1.pid
            FROM    Visits V1,Patient P
            WHERE   P.pid=V1.pid and P.age >= 20 and  P.age <=25 ) AS V 
GROUP BY    V.did

эквивалентно:

SELECT  V.did, COUNT (DISTINCT V.pid ) 
FROM    Visits V,Patient P
WHERE   P.pid=V.pid and P.age >= 20 and  P.age <=25
GROUP BY    V.did

и оба они являются хорошим решением задачи?


sql group-by distinct
person Ofek Ron    schedule 24.04.2012    source источник
comment
Должно ли V1.pid быть V1.id в первом примере?   -  person MatBailie    schedule 25.04.2012
comment
Избегайте подзапросов там, где это возможно - для этого ваш второй подход лучше (и быстрее, особенно с большими наборами данных)   -  person Madbreaks    schedule 25.04.2012
comment
Второй лучше, потому что он проще, делает его более читаемым и, возможно, оптимизатору запросов легче выбрать лучший план.   -  person JohnFx    schedule 25.04.2012
comment
@Madbreaks — неверно для этого запроса. Это предостережение действительно относится только к коррелированным подзапросам, а это не так. Тем не менее, первый запрос сложнее, чем нужно, что никогда не бывает хорошей идеей.   -  person JohnFx    schedule 25.04.2012

Ответы (5)


arrow_upward
1
arrow_downward

Второй пример меня вполне устраивает. Когда это скомпилировано в план, РСУБД решит, как лучше всего подойти к этому из ряда алгоритмов. Я не вижу необходимости добавлять промежуточный шаг, который вы представили в первой версии.

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

Как это сделать, зависит от конкретной СУБД, которую вы используете.

person MatBailie    schedule 24.04.2012

arrow_upward
3
arrow_downward

Ваш второй запрос больше подходит для задачи и должен лучше оптимизироваться. Кроме того, в каждом запросе вы ссылаетесь на Visit.age. Разве вы не должны присоединиться к патентам и ссылаться наpatient.age?

Кроме того, в каждом вы подсчитываете уникальные посещения врача, что не требует отдельного утверждения, поскольку посещения никогда не дублируются. Вместо этого вы должны подсчитывать отдельных пациентов (p.id).

person therealmitchconnors    schedule 24.04.2012
comment
правильно. я пропустил это, потому что это было менее актуально для того, о чем я пытался говорить... - person Ofek Ron; 25.04.2012
comment
Второй запрос обрабатывает не отдельных пациентов, а только отдельные визиты. - person dfb; 25.04.2012
comment
spinning_plate: вы правы, должно быть COUNT(DISTINCT P.id). Я обновлю свой ответ, чтобы отразить это. - person therealmitchconnors; 25.04.2012
comment
Ключ на Vist позволяет иметь более 1 визита пациента к врачу, поэтому РАЗЛИЧНЫЕ - person Ofek Ron; 25.04.2012
comment
Офек: Я понимаю цель таблицы посещений, но в своем вопросе вы заявили, что хотите узнать количество пациентов на одного врача, а ваши запросы не делают этого. Вместо этого они находят количество посещений врача, для чего не требуется отдельного ключевого слова. Если вы хотите узнать количество посещений на одного врача, попробуйте SELECT did, count(*) from visits group by did. - person therealmitchconnors; 25.04.2012
comment
обратите внимание на (разные) обозначения пациентов, это означает, согласно моему пониманию (поправьте меня, если не так), что мы не заинтересованы в подсчете двойного посещения конкретного врача конкретным пациентом дважды... поэтому DISTINCT - person Ofek Ron; 25.04.2012
comment
COUNT(DISTINCT V.id) каждое посещение подсчитывается один раз, поэтому ключевое слово DISTINCT не нужно, так как каждое посещение указывается только один раз. COUNT(DISTINCT V.pid) подсчитывает каждого пациента один раз, и DISTINCT необходим, так как каждый пациент указывается один раз за посещение. - person therealmitchconnors; 25.04.2012
comment
вы имеете в виду COUNT(DISTINCT V.vid), да, сначала это было неясно, потому что я опечатался в p... - person Ofek Ron; 25.04.2012

arrow_upward
0
arrow_downward

Первый запрос работает, но не является обычной практикой.

Второй запрос - это путь, гораздо яснее, что происходит.

person verhage    schedule 24.04.2012

arrow_upward
0
arrow_downward

Это два разных запроса, возможно

1) Вы считаете отдельные визиты между пациентом и врачом, это означает, что если пациент посещает врача дважды, вы не будете это учитывать.

2) Вы просто подсчитываете посещения врача, если есть несколько посещений одного и того же пациента, они будут засчитаны дважды.

Итак, для задачи 1) правильный ответ, но 2) выглядит лучше

SELECT  V.did, COUNT ( V.id ) 
FROM    (   SELECT  DISTINCT V1.did, V1.pid
            FROM    Visits V1
            WHERE V1.age >= 20 and  V1.age <=25 ) AS V  
GROUP BY    V.did

можно было бы лучше написать как

SELECT  V.did, COUNT ( V.pid ) ### change here
FROM    Visits, ### your joins
            WHERE V1.age >= 20 and  V1.age <=25 
GROUP BY    V.did
person dfb    schedule 24.04.2012
comment
1 - Я не хочу считать это, мне нужно знать, сколько разных пациентов посещал врач, поэтому я не считаю визит одного и того же пациента к одному и тому же врачу дважды... - person Ofek Ron; 25.04.2012
comment
Я говорю о ваших запросах - ответом на вашу проблему является (ваш) запрос 1), который вы написали. Способ украсить это - сделать это в запросе, который я предложил - person dfb; 25.04.2012
comment
Другими словами, ваш запрос (2) НЕ достигает вашей цели, но небольшое его изменение приведет к - person dfb; 25.04.2012
comment
Напишите свою модификацию так, как считаете правильным... и мы оценим - person Ofek Ron; 25.04.2012
comment
Хм? это второй запрос в моем ответе, похоже, вы уже изменили его в своем. Ваш первоначальный запрос вызывал DISTINCT для V.id, а не для пациента. - person dfb; 25.04.2012

arrow_upward
0
arrow_downward

Вам придется использовать левое соединение, потому что оно говорит for each doctor, а вы не показываете врачей без посещений с 0:

select d.did, count(distinct p.pid) from doctor d
left join visits v on d.did = v.did
join patients p on v.pid = p.pid
where p.age between 20 and 25
group by d.did
person Mosty Mostacho    schedule 24.04.2012