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

Откуда:
Сообщений: 17
 		Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Express Edition (64-bit) on Windows 10 Home Single Language 10.0 <X64> (Build 16299: )

Уважаемые знатоки!

Довольно нетривиальная (или мне так кажется..) задача.

Есть данные посещения занятий в учебном заведении:

CREATE TABLE Visits
(VisitID [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED,
 LessonID [int] FOREIGN KEY (ClientID) REFERENCES Clients (ClientID)
 ClientID [int] FOREIGN KEY (LessonID) REFERENCES Lessons (LessonID)
 Status [bit] NOT NULL) -- 0 - не был, 1 - был

Таблица Client содержит данные об учениках.

Таблица Lessons имеет примерно такой вид:

CREATE TABLE Lessons
(LessonID [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED,
 UnitID [int] FOREIGN KEY (UnitID) REFERENCES Units (UnitID) -- Unit - есть учебная группа.
 LessonDate [date] NOT NULL),
 ....) --Есть ещё поля, но для задачи они не важны.


И из этих данных необходимо получить ведомость посещения с за определённый месяц и в определённой группе
примерно такого вида:

Client2018-06-012018-06-042018-06-12
Jhon Doe111
Jane Doe001
Alex Mills011
Andy Coin101

В общем, как в школьном журнале.

В связи с этим два вопроса:

1) Я вполне себе представляю, как я могу переработать информацию из БД, чтобы получить искомую таблицу на стороне клиента (с#). Но может быть можно каким-то образом делать это на стороне сервера? Средствами T-SQL?

2) Возможно, кто-то может посоветовать другую схему таблиц для хранения этих данных, чтобы получить искомый результат было легче?

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

Заранее благодарен!
С уважением, Сергей.
6 авг 18, 19:06    [21632015]     Ответить | Цитировать Сообщить модератору
 Re: Нетривиальный запрос. Хранение и выборка данных о посещениях в уч. заведении.  [new]
Remind
Member

Откуда: UK
Сообщений: 523
 		Однозначно делать на стороне клиента.

Можете и на стороне SQL Server'a, конечно, PIVOT вам в помощь. Просто вы наверняка захотите dynamic pivot, это тоже возможно, но нафиг такой гемор творить на стороне SQL - не совсем понятно.
6 авг 18, 19:55    [21632070]     Ответить | Цитировать Сообщить модератору
 Re: Нетривиальный запрос. Хранение и выборка данных о посещениях в уч. заведении.  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
eonae
ведомость посещения с за определённый месяц и в определённой группе
примерно такого вида:

Client2018-06-012018-06-042018-06-12
Jhon Doe111
Jane Doe001
Alex Mills011
Andy Coin101

В общем, как в школьном журнале.

Таки лучше подумать и написать кошерно.

CREATE TABLE Visits
(VisitID [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED,
LessonID [int] FOREIGN KEY (ClientID) REFERENCES Lessons (LessonID)
ClientID [int] FOREIGN KEY (LessonID) REFERENCES Clients (ClientID)
 Status [bit] NOT NULL) -- 0 - не был, 1 - был


Начните со списка Clients, присоединив к нему через cross join выборку ВСЕХ дат из таблицы дат. Ну или до максимальной даты LessonDate для выбранного месяца из таблицы Lessons.

А уже потом на пересечении ученика и даты выбирайте нужные значения из Visits. Хотя бы потом, что пересечение ученика и даты может дать несколько разных записей в Lessons - и я никак не пойму, почему Вы в ведомости рисуете 0 и 1.
А если уроков должно было быть 5, а ученик посетил только 4?
Или Вам нужно, чтобы он посетил хотя бы один урок из выбранной даты?
6 авг 18, 20:15    [21632107]     Ответить | Цитировать Сообщить модератору
 Re: Нетривиальный запрос. Хранение и выборка данных о посещениях в уч. заведении.  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
 		Andy_OLAP,

Поправлю таки сам себя - присоединив через cross join выборку всех даты выбранного для ведомости месяца.
6 авг 18, 20:16    [21632109]     Ответить | Цитировать Сообщить модератору
 Re: Нетривиальный запрос. Хранение и выборка данных о посещениях в уч. заведении.  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
 		+1
За то чтобы делать на клиенте.
По описанию это вообще обычный сводный отчет, возможно вам нужно просто выбрать "правильный" тип отчета в вашем reporting framework в c#.
6 авг 18, 21:03    [21632160]     Ответить | Цитировать Сообщить модератору
 Re: Нетривиальный запрос. Хранение и выборка данных о посещениях в уч. заведении.  [new]
eonae
Member

Откуда:
Сообщений: 17
 		Большое спасибо за ответы!

Remind
но нафиг такой гемор творить на стороне SQL - не совсем понятно

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

Andy_OLAP
Хотя бы потом, что пересечение ученика и даты может дать несколько разных записей в Lessons - и я никак не пойму, почему Вы в ведомости рисуете 0 и 1.
А если уроков должно было быть 5, а ученик посетил только 4?
Или Вам нужно, чтобы он посетил хотя бы один урок из выбранной даты?


На самом деле, может быть название Visits не совсем корректно по смыслу. Имеется ввиду статус занятия для данного ученика: Null - не заполнено, 0 - отсутствовал, 1 - присутствовал.. там ещё будет статусы: оплачиваемый пропуск, неоплачиваемый и много чего ещё.
Записи в Visits генерируются автоматически при появлении записи в таблице Lessons, для каждого клиента, привязанного к данной группе. И дальше неважно, пока его "не отвязать", записи для него генерятся, хоть даже его ни разу не было на занятии. Наверное немного путанно объяснил.. С cross join поэкспериментирую обязательно.

Владимир_Затуливетер
По описанию это вообще обычный сводный отчет, возможно вам нужно просто выбрать "правильный" тип отчета в вашем reporting framework в c#.

Большое спасибо за наводку. В с# я пока тоже не гуру далеко )). Думал "ручками" делать. Буду смотреть!
6 авг 18, 21:47    [21632204]     Ответить | Цитировать Сообщить модератору
 Re: Нетривиальный запрос. Хранение и выборка данных о посещениях в уч. заведении.  [new]
Wlr-l
Member

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

Пусть в группе 30 человек, в месяце 30 дней, т.е. из таблицы Visits нужно взять 30*30=900 строк и развернуть. Пусть еще и 40 групп. Если это гемор, то что тогда не гемор?

С одной строны "Status [bit] NOT NULL) -- 0 - не был, 1 - был", с другой стороны "Null - не заполнено, 0 - отсутствовал, 1 – присутствовал"

"Записи в Visits генерируются автоматически при появлении записи в таблице Lessons, для каждого клиента, привязанного к данной группе".
Действительно, "немного путанно", Lessons – это расписание занятий, формируется один раз в начале семестра, с возможной коррекцией в течение семестра, а Visits заполняется после каждого занятия в соответствии с реальной посещаемостью. Не важно каким способом.
7 авг 18, 14:30    [21632952]     Ответить | Цитировать Сообщить модератору
 Re: Нетривиальный запрос. Хранение и выборка данных о посещениях в уч. заведении.  [new]
982183
Member

Откуда: VL
Сообщений: 3374
eonae
И из этих данных необходимо получить ведомость посещения с за определённый месяц и в определённой группе
примерно такого вида:

Отчет всегда будет выдаваться за календарный месяц?
Или возможна выборка за несколько месяцев?
7 авг 18, 14:46    [21632977]     Ответить | Цитировать Сообщить модератору
 Re: Нетривиальный запрос. Хранение и выборка данных о посещениях в уч. заведении.  [new]
Remind
Member

Откуда: UK
Сообщений: 523
Wlr-l
Пусть в группе 30 человек, в месяце 30 дней, т.е. из таблицы Visits нужно взять 30*30=900 строк и развернуть. Пусть еще и 40 групп. Если это гемор, то что тогда не гемор?

Для меня гемор - это решать задачи тулзами, которые для этого не предназначены. Да пусть там хоть 1 строка, писать динамический pivot ради этого считаю нецелесообразным. Это как решать data science задачи на чистом T-SQL без использования python и т.п.
7 авг 18, 17:37    [21633215]     Ответить | Цитировать Сообщить модератору
 Re: Нетривиальный запрос. Хранение и выборка данных о посещениях в уч. заведении.  [new]
komrad
Member

Откуда:
Сообщений: 5498
Remind
Однозначно делать на стороне клиента.

Можете и на стороне SQL Server'a, конечно, PIVOT вам в помощь. Просто вы наверняка захотите dynamic pivot, это тоже возможно, но нафиг такой гемор творить на стороне SQL - не совсем понятно.

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

пример с тестовыми данными внизу
идея в том, чтобы выдавать пивотом посещения за следующие 31 день, начиная с интересующей даты

+ функция

create function fn_show_pivot (@dt datetime)
returns table 
as
return
with data([client],[dt],[presence]) as (
select 'Alex','20180101',1 union all select 'Alex','20180102',1 union all select 'Alex','20180103',0 union all select 'Alex','20180104',1 union all select 'Alex','20180105',1 union all 
select 'Alex','20180106',0 union all select 'Alex','20180107',1 union all select 'Alex','20180108',1 union all select 'Alex','20180109',1 union all select 'Alex','20180110',0 union all 
select 'Alex','20180111',0 union all select 'Alex','20180112',0 union all select 'Alex','20180113',0 union all select 'Alex','20180114',1 union all select 'Alex','20180115',1 union all 
select 'Alex','20180116',0 union all select 'Alex','20180117',1 union all select 'Alex','20180118',1 union all select 'Alex','20180119',1 union all select 'Alex','20180121',0 union all 
select 'Alex','20180122',1 union all select 'Alex','20180123',1 union all select 'Alex','20180124',1 union all select 'Alex','20180125',0 union all select 'Alex','20180126',1 union all 
select 'Alex','20180127',0 union all select 'Alex','20180128',1 union all select 'Alex','20180129',1 union all select 'Alex','20180130',1 union all select 'Alex','20180131',1 union all 
select 'Alex','20180201',0 union all select 'Alex','20180202',1 union all select 'Alex','20180203',1 union all select 'Alex','20180204',0 union all select 'Alex','20180205',1 union all 
select 'Alex','20180206',1 union all select 'Alex','20180207',1 union all select 'Alex','20180208',0 union all select 'Alex','20180209',1 union all select 'Alex','20180210',1  union all select 'Alex','20180120',0
union all 
select 'Piter','20180101',1 union all select 'Piter','20180102',0 union all select 'Piter','20180103',1 union all select 'Piter','20180104',1 union all select 'Piter','20180105',0 union all 
select 'Piter','20180106',0 union all select 'Piter','20180107',1 union all select 'Piter','20180108',1 union all select 'Piter','20180109',1 union all select 'Piter','20180110',0 union all 
select 'Piter','20180111',1 union all select 'Piter','20180112',0 union all select 'Piter','20180113',1 union all select 'Piter','20180114',1 union all select 'Piter','20180115',0 union all 
select 'Piter','20180116',1 union all select 'Piter','20180117',1 union all select 'Piter','20180118',1 union all select 'Piter','20180119',1 union all select 'Piter','20180121',0 union all 
select 'Piter','20180122',0 union all select 'Piter','20180123',0 union all select 'Piter','20180124',1 union all select 'Piter','20180125',1 union all select 'Piter','20180126',0 union all 
select 'Piter','20180127',1 union all select 'Piter','20180128',1 union all select 'Piter','20180129',1 union all select 'Piter','20180130',1 union all select 'Piter','20180131',0 union all 
select 'Piter','20180201',1 union all select 'Piter','20180202',0 union all select 'Piter','20180203',1 union all select 'Piter','20180204',1 union all select 'Piter','20180205',0 union all 
select 'Piter','20180206',1 union all select 'Piter','20180207',1 union all select 'Piter','20180208',1 union all select 'Piter','20180209',1 union all select 'Piter','20180210',0  union all select 'Piter','20180120',0
union all
select 'Mary','20180101',1 union all select 'Mary','20180102',1 union all select 'Mary','20180103',1 union all select 'Mary','20180104',1 union all select 'Mary','20180105',1 union all 
select 'Mary','20180106',0 union all select 'Mary','20180107',0 union all select 'Mary','20180108',1 union all select 'Mary','20180109',0 union all select 'Mary','20180110',1 union all 
select 'Mary','20180111',1 union all select 'Mary','20180112',0 union all select 'Mary','20180113',1 union all select 'Mary','20180114',0 union all select 'Mary','20180115',1 union all 
select 'Mary','20180116',1 union all select 'Mary','20180117',1 union all select 'Mary','20180118',0 union all select 'Mary','20180119',1 union all select 'Mary','20180121',1 union all 
select 'Mary','20180122',0 union all select 'Mary','20180123',1 union all select 'Mary','20180124',1 union all select 'Mary','20180125',1 union all select 'Mary','20180126',1 union all 
select 'Mary','20180127',1 union all select 'Mary','20180128',0 union all select 'Mary','20180129',0 union all select 'Mary','20180130',0 union all select 'Mary','20180131',1 union all 
select 'Mary','20180201',0 union all select 'Mary','20180202',0 union all select 'Mary','20180203',1 union all select 'Mary','20180204',1 union all select 'Mary','20180205',1 union all 
select 'Mary','20180206',1 union all select 'Mary','20180207',0 union all select 'Mary','20180208',1 union all select 'Mary','20180209',1 union all select 'Mary','20180210',1  union all select 'Mary','20180120',0
union all
select 'John','20180101',1 union all select 'John','20180102',0 union all select 'John','20180103',1 union all select 'John','20180104',1 union all select 'John','20180105',1 union all 
select 'John','20180106',1 union all select 'John','20180107',1 union all select 'John','20180108',1 union all select 'John','20180109',0 union all select 'John','20180110',0 union all 
select 'John','20180111',0 union all select 'John','20180112',0 union all select 'John','20180113',1 union all select 'John','20180114',1 union all select 'John','20180115',0 union all 
select 'John','20180116',0 union all select 'John','20180117',1 union all select 'John','20180118',1 union all select 'John','20180119',0 union all select 'John','20180121',0 union all 
select 'John','20180122',1 union all select 'John','20180123',1 union all select 'John','20180124',1 union all select 'John','20180125',0 union all select 'John','20180126',1 union all 
select 'John','20180127',1 union all select 'John','20180128',1 union all select 'John','20180129',0 union all select 'John','20180130',0 union all select 'John','20180131',0 union all 
select 'John','20180201',0 union all select 'John','20180202',1 union all select 'John','20180203',0 union all select 'John','20180204',0 union all select 'John','20180205',1 union all 
select 'John','20180206',0 union all select 'John','20180207',0 union all select 'John','20180208',0 union all select 'John','20180209',1 union all select 'John','20180210',0  union all select 'John','20180120',0
union all
select 'Irene','20180101',1 union all select 'Irene','20180102',1 union all select 'Irene','20180103',1 union all select 'Irene','20180104',0 union all select 'Irene','20180105',0 union all 
select 'Irene','20180106',1 union all select 'Irene','20180107',1 union all select 'Irene','20180108',0 union all select 'Irene','20180109',0 union all select 'Irene','20180110',0 union all 
select 'Irene','20180111',1 union all select 'Irene','20180112',0 union all select 'Irene','20180113',1 union all select 'Irene','20180114',0 union all select 'Irene','20180115',1 union all 
select 'Irene','20180116',1 union all select 'Irene','20180117',0 union all select 'Irene','20180118',1 union all select 'Irene','20180119',1 union all select 'Irene','20180121',1 union all 
select 'Irene','20180122',1 union all select 'Irene','20180123',0 union all select 'Irene','20180124',1 union all select 'Irene','20180125',0 union all select 'Irene','20180126',1 union all 
select 'Irene','20180127',1 union all select 'Irene','20180128',0 union all select 'Irene','20180129',0 union all select 'Irene','20180130',1 union all select 'Irene','20180131',1 union all 
select 'Irene','20180201',1 union all select 'Irene','20180202',0 union all select 'Irene','20180203',1 union all select 'Irene','20180204',0 union all select 'Irene','20180205',1 union all 
select 'Irene','20180206',1 union all select 'Irene','20180207',0 union all select 'Irene','20180208',0 union all select 'Irene','20180209',1 union all select 'Irene','20180210',1 union all select 'Irene','20180120',0
)
,data_prepared as ( 
select 
	client
	,datediff(dd,@dt,dt) [diff_dd]
	,presence 
from data 
)

select 
	@dt [month]
	,client
	,[0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10]
	,[11],[12],[13],[14],[15],[16],[17],[18],[19],[20]
	,[21],[22],[23],[24],[25],[26],[27],[28],[29],[30]
from data_prepared 
pivot 
(max(presence) for [diff_dd] in ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10]
	,[11],[12],[13],[14],[15],[16],[17],[18],[19],[20]
	,[21],[22],[23],[24],[25],[26],[27],[28],[29],[30])
	) p



+ пример вызова и результат

select * from fn_show_pivot ('20180101')

monthclient0123456789101112131415161718192021222324252627282930
2018-01-01 00:00:00Alex1101101110000110111001110101111
2018-01-01 00:00:00Irene1110011000101011011011010110011
2018-01-01 00:00:00John1011111100001100110001110111000
2018-01-01 00:00:00Mary1111100101101011101010111110001
2018-01-01 00:00:00Piter1011001110101101111000011011110


select * from fn_show_pivot ('20180201')

monthclient0123456789101112131415161718192021222324252627282930
2018-02-01 00:00:00Alex0110111011NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
2018-02-01 00:00:00Irene1010110011NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
2018-02-01 00:00:00John0100100010NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
2018-02-01 00:00:00Mary0011110111NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
2018-02-01 00:00:00Piter1011011110NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL


* столбцы 0..30 - это кол-во дней вперед от переданной даты, т.е. 0 - переданная дата, 1 - следующий день и т.д.
7 авг 18, 18:42    [21633343]     Ответить | Цитировать Сообщить модератору
 Re: Нетривиальный запрос. Хранение и выборка данных о посещениях в уч. заведении.  [new]
eonae
Member

Откуда:
Сообщений: 17
 		В итоге сделал всё-таки на c#. Работает хорошо, вполне быстро даже при выгрузке всей информации за год.

Но за новый импульс в изучении sql всем большое спасибо!
8 авг 18, 10:46    [21633984]     Ответить | Цитировать Сообщить модератору
 Re: Нетривиальный запрос. Хранение и выборка данных о посещениях в уч. заведении.  [new]
Wlr-l
Member

Откуда:
Сообщений: 523
Remind
Wlr-l
Пусть в группе 30 человек, в месяце 30 дней, т.е. из таблицы Visits нужно взять 30*30=900 строк и развернуть. Пусть еще и 40 групп. Если это гемор, то что тогда не гемор?

Для меня гемор - это решать задачи тулзами, которые для этого не предназначены. Да пусть там хоть 1 строка, писать динамический pivot ради этого считаю нецелесообразным. Это как решать data science задачи на чистом T-SQL без использования python и т.п.


Пример статического запроса уже привели. Приведу пример динамического запроса:

+
if object_id(N'tempdb..#k') is not null  drop table #k;
create table #k (dt date, y int, m int, d int); --фрагмент календаря
insert #k (       dt,    y, m, d)
  values ('20180701', 2018, 7, 1),
         ('20180702', 2018, 7, 2),
         ('20180703', 2018, 7, 3),
         ('20180704', 2018, 7, 4),
         ('20180705', 2018, 7, 5);

if object_id(N'tempdb..#v') is not null  drop table #v; --таблица посещений
create table #v (n nvarchar(10), dt date, s smallint);
insert #V (    n,         dt, s)
  values ('Alex', '20180701', 1),
         ('Alex', '20180702', 1),
         ('Alex', '20180703', 0),
         ('Mary', '20180701', 1),
         ('Mary', '20180702', 0),
         ('Mary', '20180703', 1),
         ('Mary', '20180704', 1);
--select * from #k;
--select * from #v;

declare @sql varchar(1000) = '';
declare @cc  varchar( 400) = '';

--с помощью календаря и pivot
select @cc=stuff ( (select distinct concat(',[',d,']') from #k for xml path('') ), 1, 1,'');
--select @cc;
select @sql=concat('select * from (select n,d,s from #k k left join #v v on v.dt=k.dt) as t pivot (sum(s) for d in (',@cc,')) as pt');
--print @sql
exec(@sql);

--можно и без календаря, но с pivot
--в этом случае в колонках будут только те даты, которые есть в таблице посещений
select @cc=stuff ( (select distinct concat(',[',day(dt),']') from #v for xml path('') ), 1, 1,'');
--select @cc;
select @sql=concat('select * from (select n,d,s from #k k left join #v v on v.dt=k.dt) as t pivot (sum(s) for d in (',@cc,')) as pt');
--print @sql
exec(@sql);

--так же легко можно сформировать и стандартный запрос с "case when d=1 then sum(s) end as [1]",
--как с использованием календаря, так и без него
--я уже приводил пример формирования такого запроса, поэтому здесь приводить его не буду.


Для мышки(Wlr-l) кошка (python) страшнее зверя нет!
Проблема использования тех или иных языков для решения тех или иных задач сводится к известной проблеме Мастера и подмастерья. Мастер с помощью молотка и зубила (чистый T-SQL) может сделать то, что подмастерье не сможет сделать даже с помощью станков (python).
8 авг 18, 14:36    [21634444]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить