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

Откуда: EU
Сообщений: 2960
Помогите пожалуйста составить оптимальный запрос типа over .. Partition (хотя другая модель тоже подойдёт) или что там недорогое по нагрузке на сервер будет в реальном времени.

> Необходимо для каждого студента найти общую продолжительность занятий начиная (т.е. после) каждого из 3-х последних экзаменов.

Насколько я понимаю это через ..RowNumber() over (Partition by StudentID Order By Date Desc) as rw .. Where rw=.. и потом Join-ы .. ну или что-то в этом роде.

Исходная таблица (StudentID,Type,Date,Duration) в самом низу

>Например на выходе нужно увидеть такое:
---------
StudentID__DateEx1___Lessons1__DateEx2__Lessons2__DateEx3___Lessons3
1284______4/2/2013____4______1/22/2013___12____11/16/2012_____19
3079______2/15/2013___4______10/26/2012__11_______Null________Null
5045______2/18/2013__10______1/15/2013___17____11/10/2012_____20
---------

Выделить даты последних 3х экзаменов один за одним могу через RowNumber() over (partition.. , a вот как это в едином запросе соединить без временных таблиц и пользовательских функций, да с суммами за период - ну ни как не срастается :(

Заранее спасибо.
P.S. Система: SQLServer 2012

CREATE TABLE [dbo].[Book2](
	[StudentID] [int] NOT NULL,
	[Type] [varchar](8) NOT NULL,
	[Date] [date] NOT NULL,
	[Duration] [smallint] NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Book2] ([StudentID], [Type], [Date], [Duration]) VALUES (1284, N'Lesson', CAST(0x40360B00 AS Date), 4)
INSERT [dbo].[Book2] ([StudentID], [Type], [Date], [Duration]) VALUES (5045, N'Exam', CAST(0x7D360B00 AS Date), 1)
INSERT [dbo].[Book2] ([StudentID], [Type], [Date], [Duration]) VALUES (1284, N'Exam', CAST(0x2B360B00 AS Date), 2)
INSERT [dbo].[Book2] ([StudentID], [Type], [Date], [Duration]) VALUES (3079, N'Lesson', CAST(0x31360B00 AS Date), 2)
INSERT [dbo].[Book2] ([StudentID], [Type], [Date], [Duration]) VALUES (5045, N'Lesson', CAST(0x35360B00 AS Date), 3)
INSERT [dbo].[Book2] ([StudentID], [Type], [Date], [Duration]) VALUES (3079, N'Lesson', CAST(0x6B350B00 AS Date), 2)
INSERT [dbo].[Book2] ([StudentID], [Type], [Date], [Duration]) VALUES (5045, N'Lesson', CAST(0x45360B00 AS Date), 5)
INSERT [dbo].[Book2] ([StudentID], [Type], [Date], [Duration]) VALUES (1284, N'Lesson', CAST(0x47360B00 AS Date), 2)
INSERT [dbo].[Book2] ([StudentID], [Type], [Date], [Duration]) VALUES (3079, N'Lesson', CAST(0x4A360B00 AS Date), 2)
INSERT [dbo].[Book2] ([StudentID], [Type], [Date], [Duration]) VALUES (1284, N'Lesson', CAST(0x4D360B00 AS Date), 4)
INSERT [dbo].[Book2] ([StudentID], [Type], [Date], [Duration]) VALUES (3079, N'Exam', CAST(0x51360B00 AS Date), 1)
INSERT [dbo].[Book2] ([StudentID], [Type], [Date], [Duration]) VALUES (3079, N'Lesson', CAST(0x6C350B00 AS Date), 3)
INSERT [dbo].[Book2] ([StudentID], [Type], [Date], [Duration]) VALUES (1284, N'Lesson', CAST(0xE6350B00 AS Date), 5)
INSERT [dbo].[Book2] ([StudentID], [Type], [Date], [Duration]) VALUES (5045, N'Exam', CAST(0x42360B00 AS Date), 1)
INSERT [dbo].[Book2] ([StudentID], [Type], [Date], [Duration]) VALUES (1284, N'Exam', CAST(0x66360B00 AS Date), 2)
INSERT [dbo].[Book2] ([StudentID], [Type], [Date], [Duration]) VALUES (5045, N'Lesson', CAST(0x6A360B00 AS Date), 3)
INSERT [dbo].[Book2] ([StudentID], [Type], [Date], [Duration]) VALUES (1284, N'Lesson', CAST(0x43360B00 AS Date), 2)
INSERT [dbo].[Book2] ([StudentID], [Type], [Date], [Duration]) VALUES (1284, N'Lesson', CAST(0x84360B00 AS Date), 5)
INSERT [dbo].[Book2] ([StudentID], [Type], [Date], [Duration]) VALUES (5045, N'Exam', CAST(0xA2360B00 AS Date), 1)
INSERT [dbo].[Book2] ([StudentID], [Type], [Date], [Duration]) VALUES (5045, N'Lesson', CAST(0xA7360B00 AS Date), 4)
INSERT [dbo].[Book2] ([StudentID], [Type], [Date], [Duration]) VALUES (1284, N'Exam', CAST(0xA9360B00 AS Date), 1)
INSERT [dbo].[Book2] ([StudentID], [Type], [Date], [Duration]) VALUES (3079, N'Lesson', CAST(0xB0360B00 AS Date), 4)
INSERT [dbo].[Book2] ([StudentID], [Type], [Date], [Duration]) VALUES (1284, N'Lesson', CAST(0xD0360B00 AS Date), 2)
INSERT [dbo].[Book2] ([StudentID], [Type], [Date], [Duration]) VALUES (5045, N'Lesson', CAST(0x0D370B00 AS Date), 3)
INSERT [dbo].[Book2] ([StudentID], [Type], [Date], [Duration]) VALUES (3079, N'Exam', CAST(0xC1360B00 AS Date), 2)
INSERT [dbo].[Book2] ([StudentID], [Type], [Date], [Duration]) VALUES (1284, N'Lesson', CAST(0xC3360B00 AS Date), 1)
INSERT [dbo].[Book2] ([StudentID], [Type], [Date], [Duration]) VALUES (5045, N'Exam', CAST(0xC4360B00 AS Date), 1)
INSERT [dbo].[Book2] ([StudentID], [Type], [Date], [Duration]) VALUES (5045, N'Lesson', CAST(0xCA360B00 AS Date), 2)
INSERT [dbo].[Book2] ([StudentID], [Type], [Date], [Duration]) VALUES (3079, N'Lesson', CAST(0x4B370B00 AS Date), 4)
INSERT [dbo].[Book2] ([StudentID], [Type], [Date], [Duration]) VALUES (1284, N'Lesson', CAST(0x89370B00 AS Date), 3)
INSERT [dbo].[Book2] ([StudentID], [Type], [Date], [Duration]) VALUES (1284, N'Lesson', CAST(0xE8360B00 AS Date), 2)
INSERT [dbo].[Book2] ([StudentID], [Type], [Date], [Duration]) VALUES (5045, N'Lesson', CAST(0xEA360B00 AS Date), 3)
INSERT [dbo].[Book2] ([StudentID], [Type], [Date], [Duration]) VALUES (1284, N'Exam', CAST(0xB6360B00 AS Date), 1)
INSERT [dbo].[Book2] ([StudentID], [Type], [Date], [Duration]) VALUES (1284, N'Lesson', CAST(0x0F370B00 AS Date), 4)
INSERT [dbo].[Book2] ([StudentID], [Type], [Date], [Duration]) VALUES (5045, N'Lesson', CAST(0x6B370B00 AS Date), 5)

или
StudentID Type Date Duration
1284 Lesson 9/10/2012 4
5045 Exam 9/12/2012 1
1284 Exam 9/18/2012 2
3079 Lesson 9/24/2012 2
5045 Lesson 9/28/2012 3
3079 Lesson 10/3/2012 2
5045 Lesson 10/14/2012 5
1284 Lesson 10/16/2012 2
3079 Lesson 10/19/2012 2
1284 Lesson 10/22/2012 4
3079 Exam 10/26/2012 1
3079 Lesson 11/3/2012 3
1284 Lesson 11/7/2012 5
5045 Exam 11/10/2012 1
1284 Exam 11/16/2012 2
5045 Lesson 11/20/2012 3
1284 Lesson 12/10/2012 2
1284 Lesson 12/16/2012 5
5045 Exam 1/15/2013 1
5045 Lesson 1/20/2013 4
1284 Exam 1/22/2013 1
3079 Lesson 1/29/2013 4
1284 Lesson 2/3/2013 2
5045 Lesson 2/5/2013 3
3079 Exam 2/15/2013 2
1284 Lesson 2/17/2013 1
5045 Exam 2/18/2013 1
5045 Lesson 2/24/2013 2
3079 Lesson 3/7/2013 4
1284 Lesson 3/9/2013 3
1284 Lesson 3/26/2013 2
5045 Lesson 3/28/2013 3
1284 Exam 4/2/2013 1
1284 Lesson 4/5/2013 4
5045 Lesson 4/8/2013 5
16 дек 13, 04:53    [15296764]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста составить оптимальный запрос типа Over .. Partition  [new]
Добрый Э - Эх
Guest
vikkiv,

что-то твои тестовые данные, желаемый результат на них и словесное описание - не согласовываются друг с другом. Или ты результат от балды написал ?
16 дек 13, 05:58    [15296778]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста составить оптимальный запрос типа Over .. Partition  [new]
Добрый Э - Эх
Guest
как вариант:
select e.STUDENTID,
       max(case e.rn
             when 1 then e.DATE
           end) as date_ex_1,
       sum(case e.rn
              when 1 then l.Duration
           end) as less_1,
       max(case e.rn
              when 2 then e.DATE
           end) as date_ex_2,
       sum(case e.rn
              when 2 then l.Duration
           end) as less_2,
       max(case e.rn
              when 3 then e.DATE
           end) as date_ex_3,
       sum(case e.rn
              when 3 then l.Duration
           end) as less_3
  from (
         select STUDENTID, DATE,
                row_number() over(partition by STUDENTID order by DATE desc) as rn
           from book2
          where type = N'Exam'
       ) e
  join book2 l
    on e.STUDENTID = l.STUDENTID
   and e.DATE <= l.DATE
   and l.TYPE = N'Lesson'
   and e.rn <= 3
 group by e.STUDENTID
on-line проверка на sqlfiddle.com

Варианты "улучшения":
0) гирлянду из CASE можно переписать через оператор PIVOT.
1) гирлянду из CASE + JOIN переписать через CROSS APPLY
2) Увеличением глубины вложенности и расширением списка используемых window-функций можно избавиться от JOIN-a вообще.
16 дек 13, 07:15    [15296799]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста составить оптимальный запрос типа Over .. Partition  [new]
vikkiv
Member

Откуда: EU
Сообщений: 2960
Добрый Э - Эх,

Ой, да, SQL скрипт и текст из разных таблиц :( , даже в ручную после выходныхничего не считается нормально, "..общую продолжительность занятий начиная (т.е. после) каждого из 3-х последних экзаменов..." , не между экзаменами, а начиная с последнего и до конца таблицы данных, извиняюсь, после учёбы допишу, сейчас бежать надо..
16 дек 13, 11:31    [15297591]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста составить оптимальный запрос типа Over .. Partition  [new]
vikkiv
Member

Откуда: EU
Сообщений: 2960
Добрый Э - Эх,

Огромущее спасибо, буду проверять..
16 дек 13, 11:32    [15297594]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста составить оптимальный запрос типа Over .. Partition  [new]
BuKTaP
Member

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

то может так?
SELECT b.StudentID, SUM(b.Duration) FROM Book2 b
CROSS APPLY
(SELECT TOP 1 be.StudentID, be.Date FROM Book2 be
 WHERE be.StudentID = b.StudentID AND be.[Type] = 'Exam' AND b.Date >= be.Date
 ORDER BY be.Date DESC) rr
 WHERE b.[Type] = 'Lesson'
GROUP BY b.StudentID
17 дек 13, 11:58    [15303696]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста составить оптимальный запрос типа Over .. Partition  [new]
BuKTaP
Member

Откуда:
Сообщений: 132
а не. сорри. херню написал.
17 дек 13, 12:02    [15303733]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста составить оптимальный запрос типа Over .. Partition  [new]
BuKTaP
Member

Откуда:
Сообщений: 132
SELECT b.StudentID, SUM(b.Duration) FROM Book2 b
CROSS APPLY
(SELECT TOP 1 be.StudentID, be.Date, be.[Type] FROM Book2 be
 WHERE be.StudentID = b.StudentID AND be.[Type] = 'Exam'
 ORDER BY be.Date DESC) rr
 WHERE b.[Type] = 'Lesson' AND b.Date >= rr.date
GROUP BY b.StudentID
ORDER BY b.StudentID

вот так вот может как-то.
17 дек 13, 12:05    [15303753]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста составить оптимальный запрос типа Over .. Partition  [new]
vikkiv
Member

Откуда: EU
Сообщений: 2960
BuKTaP,
Если отдельно рассматривать то конечно верно, но если не вырывать из контекста изначального вопроса то не верно..

> Необходимо для каждого студента найти общую продолжительность занятий начиная (т.е. после) каждого из 3-х последних экзаменов.
..
не между экзаменами, а начиная с последнего и до конца таблицы данных..
.
.
т.е. напр:
давным-давно
1 exam1
3 lesson1a
6 lesson1b
3 exam2
2 lesson2a
4 lesson2b
2 lesson2c
Сегодня
.
Exam1 > L1a+L1b+L2a+L2b+L2c = 17
Exam2 > L2a+L2b+L2c = 8
.
.
Но собственно не важно, третий пост вопрос вполне решил.
17 дек 13, 16:14    [15305985]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить