Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Правильная группировка результата запроса  [new]
YoHnA
Member

Откуда:
Сообщений: 43
Доброго времени суток.

Вопрос будет длинным.

Есть две таблицы, Справочник и Основа. Связаны они по ключу из трех полей - Профиль, Группа и Год.
Таблица Справочник имеет структуру: ID записи, Группа, Имя профиля, Год, Профиль.
Статистика собирается по полю Имя профиля.
Соответственно, происходит обращение к таблице Основа, где подсчитывается количество записей, удовлетворяющих некоторым условиям, и группируется по Имени профиля.
В чем затык.
В Справочнике могут быть несколько записей формата
ID записи Группа Имя профиля Год Профиль
1 1 Лечение 2016 01.00
2 2 Лечение 2016 01.00
3 3 Лечение1 2016 02.00
4 4 Лечение2 2016 02.00

Т.е. различающихся только полем Группа. Соответственно, подсчет количества записей и дальнейшую группировку удобнее всего осуществлять именно по группе.
Пример запроса:
select * from dbo.Основа inner join dbo.Справочник on (dbo.Основа.[Group]=dbo.Справочник.[Group]) where <...>

Но.
В Основе поле Группа может запросто быть пустым. И в таком случае в считалочку запись не попадает.
Я решила высчитывать запросами отдельно количество с проставленной Группой и отдельно без, а потом сложить. Со второй частью возникла проблема - поскольку связь между таблицами остается по трем полям, то и выкидывает мне все записи с пустой Группой, НО при этом в двух или трех экземплярах - сколько раз в Справочнике встречается совпадение этой записи по полям Профиль и Год.
Демонстрация примерной схемы запроса:
select * from dbo.Основа inner join dbo.Справочник on (dbo.Основа.[Profile]=dbo.Справочник.[Profile]) 
					   inner join dbo.MO on (dbo.Основа.MO_Code=dbo.MO.IDmor) 
where (dbo.Справочник.[Имя Профиля]='Лечение' and dbo.Основа.Y=2016 and <...> and dbo.Основа.[Group] is null and dbo.Основа.Y=dbo.Справочник.[Year]) and <...>

Результат в приложенной картинке.

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

Помогите, пожалуйста.
17 окт 16, 14:15    [19790359]     Ответить | Цитировать Сообщить модератору
 Re: Правильная группировка результата запроса  [new]
YoHnA
Member

Откуда:
Сообщений: 43
Файл с картинкой почему-то не прикрепился, прошу прощения.

К сообщению приложен файл. Размер - 7Kb
17 окт 16, 14:18    [19790372]     Ответить | Цитировать Сообщить модератору
 Re: Правильная группировка результата запроса  [new]
invm
Member

Откуда: Москва
Сообщений: 9408
YoHnA
Есть две таблицы, Справочник и Основа. Связаны они по ключу из трех полей - Профиль, Группа и Год.
YoHnA
В Основе поле Группа может запросто быть пустым.
Связаны - это foreign key?
17 окт 16, 14:40    [19790531]     Ответить | Цитировать Сообщить модератору
 Re: Правильная группировка результата запроса  [new]
YoHnA
Member

Откуда:
Сообщений: 43
invm, да, конечно. Внешний составной ключ.
17 окт 16, 14:44    [19790558]     Ответить | Цитировать Сообщить модератору
 Re: Правильная группировка результата запроса  [new]
invm
Member

Откуда: Москва
Сообщений: 9408
YoHnA
Внешний составной ключ.
Не делайте так - FK не проверяется, если в дочерней таблице в какой-либо столбец, в него входящий, заносится null.
17 окт 16, 14:56    [19790629]     Ответить | Цитировать Сообщить модератору
 Re: Правильная группировка результата запроса  [new]
YoHnA
Member

Откуда:
Сообщений: 43
invm, я знаю, но у меня выбора нет. Группа может в разные годы повторяться, у разных групп может быть разный профиль. Я больше не знаю, как можно выкрутиться из этой структуры. Суть я описала - статистика нужна по Имени профиля, которое не фиксируется в Основе.
17 окт 16, 15:00    [19790650]     Ответить | Цитировать Сообщить модератору
 Re: Правильная группировка результата запроса  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
YoHnA,

почему Основа не может ссылаться на ID записи справочника?
17 окт 16, 15:09    [19790712]     Ответить | Цитировать Сообщить модератору
 Re: Правильная группировка результата запроса  [new]
invm
Member

Откуда: Москва
Сообщений: 9408
YoHnA
Я больше не знаю, как можно выкрутиться из этой структуры
Например, так:
use tempdb;
go

create table dbo.t1 (a int not null, b int null, b1 as isnull(b, -1) persisted not null, constraint UQ_t1 unique (a, b1));
create table dbo.t2 (a int not null, b int null, c int, b1 as isnull(b, -1) persisted not null, constraint FK_t2_t1 foreign key (a, b1) references dbo.t1(a, b1));
go

insert into dbo.t1 values (1, 1), (2, null);
go

insert into dbo.t2 values (1, 2, 0);
insert into dbo.t2 values (1, null, 0);

select * from dbo.t1;
select * from dbo.t2;
go

drop table dbo.t2, dbo.t1;
go
Плюс автоматически решает ваши трудности с запросом.
17 окт 16, 15:13    [19790737]     Ответить | Цитировать Сообщить модератору
 Re: Правильная группировка результата запроса  [new]
YoHnA
Member

Откуда:
Сообщений: 43
iap, потому что Основа - это выгрузка из некоторой системы, она имеет такой вот вид, и переводить его в свой мне, мягко скажем, несподручно. И если даже попытаться себе это сделать, то это придется в справочник добавить кучу строк с вариациями типа пустых групп, но с указанием профиля. Каждый год комбинации профилей и групп меняются, каждый год мне и так придется руками Справочник править, а если потом еще и прогонять массивы по 100к записей через процедуру вставки в отдельное поле айдишника из справочника - я бы хотела что-то попроще, честно сказать.
17 окт 16, 15:13    [19790738]     Ответить | Цитировать Сообщить модератору
 Re: Правильная группировка результата запроса  [new]
YoHnA
Member

Откуда:
Сообщений: 43
invm,
так и задумано? Простите, я только начинающая.

К сообщению приложен файл. Размер - 28Kb
17 окт 16, 15:20    [19790776]     Ответить | Цитировать Сообщить модератору
 Re: Правильная группировка результата запроса  [new]
invm
Member

Откуда: Москва
Сообщений: 9408
YoHnA
так и задумано?
Да.
Показано, что в t2 нельзя вставить строки, не удовлетворяющие FK.
17 окт 16, 15:29    [19790859]     Ответить | Цитировать Сообщить модератору
 Re: Правильная группировка результата запроса  [new]
YoHnA
Member

Откуда:
Сообщений: 43
invm, а что мне делать, если Основа- это готовая выгрузка из системы? Вот в таком вот имеющемся виде. Что все комбинации ПрофильГруппаГод я заранее знать не могу.
И, если честно, смысл вычисляемого поля от меня ускользнул. В смысле, не понимаю, как его можно прилепить к моей базе.
17 окт 16, 15:38    [19790939]     Ответить | Цитировать Сообщить модератору
 Re: Правильная группировка результата запроса  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
YoHnA
iap, потому что Основа - это выгрузка из некоторой системы, она имеет такой вот вид, и переводить его в свой мне, мягко скажем, несподручно. И если даже попытаться себе это сделать, то это придется в справочник добавить кучу строк с вариациями типа пустых групп, но с указанием профиля. Каждый год комбинации профилей и групп меняются, каждый год мне и так придется руками Справочник править, а если потом еще и прогонять массивы по 100к записей через процедуру вставки в отдельное поле айдишника из справочника - я бы хотела что-то попроще, честно сказать.
Это, вообще, учебный пример или рабочий?
Боюсь опять начинать дискуссию суррогатный PK - естественный PK,
но если нравится, тащите всюду три поля, во все ссылающиеся таблицы.
И в запросах будут по три условия вместо одного.

Выгрузку можно нормально устроить и с суррогатным ключом.
17 окт 16, 15:42    [19790988]     Ответить | Цитировать Сообщить модератору
 Re: Правильная группировка результата запроса  [new]
YoHnA
Member

Откуда:
Сообщений: 43
iap, рабочая база, не учебная.
Пожалуйста, я упомянула, что начинающая, можно чуть более понятным языком? Суррогатный ключ - этот тот что с вычисляемым полем? А естественный какой?
Что значит, устроить выгрузку? Перелопатить ее нутро в смысле? Убрать эти три поля и заменить каким-то одним? А как, если комбинаций слишком много, чтобы прописывать их руками в isnull?
17 окт 16, 15:48    [19791043]     Ответить | Цитировать Сообщить модератору
 Re: Правильная группировка результата запроса  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
YoHnA,

суррогатный - который не имеет прикладного смысла, создан искусственно для того, чтобы различать записи.
17 окт 16, 15:59    [19791117]     Ответить | Цитировать Сообщить модератору
 Re: Правильная группировка результата запроса  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Суррогат - это заменитель функциональной потребности.
17 окт 16, 16:01    [19791137]     Ответить | Цитировать Сообщить модератору
 Re: Правильная группировка результата запроса  [new]
invm
Member

Откуда: Москва
Сообщений: 9408
YoHnA
а что мне делать, если Основа- это готовая выгрузка из системы?
Это не мешает добавить вычисляемый столбец.
YoHnA
В смысле, не понимаю, как его можно прилепить к моей базе.
Использовать в соединениях таблиц вместо "группа" - не нужно будет разделять в предикатах null и not null.

По-хорошему, нужно вашу "готовую выгрузку" переделать - FK должен быть по "ID записи" справочника, а не по его содержимому.
17 окт 16, 16:01    [19791143]     Ответить | Цитировать Сообщить модератору
 Re: Правильная группировка результата запроса  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
YoHnA,

у вас [ID записи] - суррогатный. На него достаточно ссылаться.
И не повторять по сто раз одну и ту же информацию ([Профиль], [Группа], [Год]) в разных таблицах.
Ведь её просто получить из Справочника по [ID записи].
17 окт 16, 16:02    [19791148]     Ответить | Цитировать Сообщить модератору
 Re: Правильная группировка результата запроса  [new]
YoHnA
Member

Откуда:
Сообщений: 43
Кажется, я плохо объяснила.
Сейчас попробую предположить.
Создать вычисляемое поле в таблице Основа, которое, если Группа пуста, будет принимать какое-то определенное значение, если не пуста, оставаться пустым, например. Окей. Тогда ключом что будет в таблице Справочник, просто ID каждой записи?
Хорошо. Тогда, с учетом того, что в Справочнике у меня сейчас более сотни записей, в которых указаны ГруппаПрофильГод, мне нужно будет добавить еще столько же с пустым полем Группа? Потому что у одного Профиля могут быть разные Имена профилей, разные Группы и разные Годы. А статистика подводится по Именам профилей. Следовательно, мне придется ввести кучу строк в Справочник, чтобы учесть все варианты, чтобы при вставке ключа ID из справочника в таблицу Основа можно было найти именно нужный вариант по комбинации ГруппаПрофильГод.
Правильно понимаю?
17 окт 16, 16:13    [19791227]     Ответить | Цитировать Сообщить модератору
 Re: Правильная группировка результата запроса  [new]
YoHnA
Member

Откуда:
Сообщений: 43
Грубо говоря, если Группа=1, Профиль='01.01', Год=2016, то ID_справочника=1, Если Группа is null (или вычисляемое поле=1, например), Профиль='01.01', Год=2016, то ID_справочника=2 и так далее. И таких комбинаций вагон и все они должны быть учтены в Справочнике. Так же?
17 окт 16, 16:18    [19791253]     Ответить | Цитировать Сообщить модератору
 Re: Правильная группировка результата запроса  [new]
YoHnA
Member

Откуда:
Сообщений: 43
Это, в общем-то, было очевидное решение, но мне ну очень не хотелось расширять Справочник, да и переборка большого количества ID вместо отсеивания в where мне казалась менее рациональной идеей, но если по-другому с этим null в Группе мне никак не избежать, то придется.
Спасибо большое :)
17 окт 16, 16:36    [19791362]     Ответить | Цитировать Сообщить модератору
 Re: Правильная группировка результата запроса  [new]
invm
Member

Откуда: Москва
Сообщений: 9408
Понятно. Для вас, применительно к "Основы", возможность нарушить FK - фича, а не ошибка.

Согласно вашему примеру, строка из "Основы" с "Профиль" = 02.00, "Год" = 2016 и "Группа" = null какому имени профиля должна соответствовать?
17 окт 16, 17:06    [19791514]     Ответить | Цитировать Сообщить модератору
 Re: Правильная группировка результата запроса  [new]
YoHnA
Member

Откуда:
Сообщений: 43
invm, это не моя фича, это я так получаю данные) мне их вот так дают, мне остается только работать с таким видом, либо переделывать этот исходник вверх дном, чего мне делать не хотелось, т.к. происходит адаптация всей этой каши и ее статистики под сервер из экселя, и хотелось бы иметь возможность сравнивать, правильно ли у меня все высчитывается с тем же экселем, а там, естественно, все работает и вот с таким видом.
Я прекрасно понимаю, что так быть не должно, но ввод дополнительного ключа и расширение Справочника тоже не несут больших плюсов для всего этого массива. Плюс каждый раз я понятия не имею, что опять изменится в этой выгрузке, какие там новые значения появятся и какие ошибки, что опять же заводит вариант ID из Справочника в тупик. Оно просто откажется загружаться в таблицу, пока я не внесу в Справочник все новые варианты, а их может быть много. Возможность пустоты Группы - это косяк программы, через которую эти данные заносятся, а не моя персональная фича)
"Профиль" = 02.00, "Год" = 2016 и "Группа" = null - предположим, Онкология. Вообще, подразумевается, что Имя профиля на каждый Профиль одно, но есть и одно исключение, где к одному Профилю относятся два Имени профиля, из-за которого я не могу просто сделать ключом Профиль. Поэтому ориентировка была на Группу, поскольку она не повторяется нигде.
18 окт 16, 09:23    [19793101]     Ответить | Цитировать Сообщить модератору
 Re: Правильная группировка результата запроса  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
YoHnA,

1. Вносить "руками" записи в справочник совсем не обязательно
2.
автор
"Профиль" = 02.00, "Год" = 2016 и "Группа" = null - предположим, Онкология

а вариант нахождения ещё одной записи
автор
Профиль" = 02.00, "Год" = 2016 и "Группа" = null - предположим, Лечение
присутствует?
данные надо нормализовать
18 окт 16, 09:31    [19793130]     Ответить | Цитировать Сообщить модератору
 Re: Правильная группировка результата запроса  [new]
YoHnA
Member

Откуда:
Сообщений: 43
TaPaK,
присутствует. И, более того, поскольку даны только эти три данных, я не смогу их отличить. Поэтому это в любом случае вылетает в статистическую ошибку, что по ID, что по трем полям. Когда я попыталась узнать, как мне решать эту проблему у руководства, мне сказали, ну запиши к тому, который первым попадется. Это момент нерешабельный, так сказать, это проблема на первичном уровне внесения этих данных, а я уже вторичный.
18 окт 16, 09:45    [19793169]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить