Logo MurCode
Microsoft SQL Server Новое сообщение

Вопрос по group by

Kudep
Дата: 10.06.2011 10:57:12
Есть group by с приджойненной табличкой
SELECT AuthorID
FROM [Message]
JOIN Author on Message.AuthorID = Author.ID
GROUP BY [Message].[AuthorID]
Почему я не могу взять в select поле Author.Name? Ведь одной группе будет соответствовать только одно Author.Name, потому что AuthorID - primary key.
Делаю так:
SELECT AuthorID, MIN(Author.Name)
Есть более красивое решение?
SomewhereSomehow
Дата: 10.06.2011 11:05:42
Kudep,

ну так и добавьте поле Author.Name в список полей для группировки GROUP BY [Message].[AuthorID], Author.Name
Mnior
Дата: 10.06.2011 11:08:24
Не слушайте SomewhereSomehow. Перенесите JOIN за пределы группировки.

Kudep
Ведь одной группе будет соответствовать только одно Author.Name
Сначало всё собирается во FROM в одну виртуальную таблицу, а потом группируется. И уже не важно из чего там оно собиралось до этого, данные (Author) вы уже бесполезно размножили для каждой строки.
SomewhereSomehow
Дата: 10.06.2011 11:13:09
Mnior,

что-то мне подсказывает, что оптимизатор не дурней велосипеда, для приведенного запроса, планы будут одинаковые, проверьте =)
bibiskula
Дата: 10.06.2011 11:18:58
Kudep
Есть group by с приджойненной табличкой
SELECT AuthorID
FROM [Message]
JOIN Author on Message.AuthorID = Author.ID
GROUP BY [Message].[AuthorID]
Почему я не могу взять в select поле Author.Name? Ведь одной группе будет соответствовать только одно Author.Name, потому что AuthorID - primary key.
Делаю так:
SELECT AuthorID, MIN(Author.Name)
Есть более красивое решение?


SELECT AuthorID, Author.Name
FROM [Message]
JOIN Author on Message.AuthorID = Author.ID
Вот! Красивее не бывает
Mnior
Дата: 10.06.2011 20:34:00
SomewhereSomehow
что-то мне подсказывает, что оптимизатор не дурней велосипеда
Надо не чувствовать, надо понимать.
SomewhereSomehow
проверьте =)
Тролите. Ладно, вот вам:

  |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1007],0)))
       |--Stream Aggregate(GROUP BY:([M].[Author]) DEFINE:([Expr1007]=Count(*), [A].[Name]=ANY([tempdb].[dbo].[Author].[Name] as [A].[Name])))
            |--Nested Loops(Inner Join, OUTER REFERENCES:([M].[Author]))
                 |--Sort(ORDER BY:([M].[Author] ASC))
                 |    |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[Message].[PK_Message] AS [M]))
                 |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[Author].[PK_Author] AS [A]), SEEK:([A].[ID]=[tempdb].[dbo].[Message].[Author] as [M].[Author]) ORDERED FORWARD)

  |--Nested Loops(Inner Join, OUTER REFERENCES:([tempdb].[dbo].[Message].[Author]))
       |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1008],0)))
       |    |--Stream Aggregate(GROUP BY:([tempdb].[dbo].[Message].[Author]) DEFINE:([Expr1008]=Count(*)))
       |         |--Sort(ORDER BY:([tempdb].[dbo].[Message].[Author] ASC))
       |              |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[Message].[PK_Message]))
       |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[Author].[PK_Author] AS [A]), SEEK:([A].[ID]=[tempdb].[dbo].[Message].[Author]) ORDERED FORWARD)
+ SQL
USE tempdb
GO
CREATE TABLE [dbo].[Author] (
	 [ID]	Int	IDENTITY
	 CONSTRAINT [PK_Author]	PRIMARY KEY
	,[Name]	Int	NOT NULL
)
CREATE TABLE [dbo].[Message] (
	 [ID]		Int	IDENTITY
	 CONSTRAINT [PK_Message]	PRIMARY KEY
	,[Author]	Int	NOT NULL
	 CONSTRAINT [FK_Message_Author]	REFERENCES [dbo].[Author]([ID])
)
GO
SET SHOWPLAN_TEXT ON
GO
SELECT	 M.Author
	,A.Name
	,Count(*)
FROM	     dbo.Message	M
	JOIN dbo.Author		A ON A.ID = M.Author
GROUP BY M.Author
	,A.Name
GO
SELECT	 M.Author
	,A.Name
	,M.[Count]
FROM (	SELECT	 Author
		,Count(*)	AS [Count]
	FROM	dbo.Message
	GROUP BY Author)	M
	JOIN dbo.Author		A ON A.ID = M.Author
GO
SET SHOWPLAN_TEXT OFF
GO
DROP TABLE [dbo].[Message]
DROP TABLE [dbo].[Author]
GO
Kudep, для удобства юзайте WITH:
;WITH GroupMessage AS (
	SELECT	 Author
		,Count(*)	AS [Count]
	FROM	dbo.Message
	GROUP BY Author
)SELECT	 M.Author
	,A.Name
	,M.[Count]
FROM	     GroupMessage	M
	JOIN dbo.Author		A ON A.ID = M.Author
SomewhereSomehow
Дата: 10.06.2011 23:19:32
Mnior,

т.е. вот так да? исключив полностью данные, вы решили не давать шансов оптимизатору? =)
внимание, коронный номер, перестаем дурить оптимизатор, и добавляем хоть какие-то строки (остальное, заметьте - не трогаем)
+

USE tempdb
GO
CREATE TABLE [dbo].[Author] (
	 [ID]	Int	IDENTITY
	 CONSTRAINT [PK_Author]	PRIMARY KEY
	,[Name]	Int	NOT NULL
)
CREATE TABLE [dbo].[Message] (
	 [ID]		Int	IDENTITY
	 CONSTRAINT [PK_Message]	PRIMARY KEY
	,[Author]	Int	NOT NULL
	 CONSTRAINT [FK_Message_Author]	REFERENCES [dbo].[Author]([ID])
)
GO
insert into [Author](Name) values (1),(2),(3),(4),(5)
insert into [Message]([Author]) values (1),(2),(3),(4),(5),(1),(2),(3),(4),(5)
go
SET SHOWPLAN_TEXT ON
GO
SELECT	 M.Author
	,A.Name
	,Count(*)
FROM	     dbo.Message	M
	JOIN dbo.Author		A ON A.ID = M.Author
GROUP BY M.Author
	,A.Name
GO
SELECT	 M.Author
	,A.Name
	,M.[Count]
FROM (	SELECT	 Author
		,Count(*)	AS [Count]
	FROM	dbo.Message
	GROUP BY Author)	M
	JOIN dbo.Author		A ON A.ID = M.Author
GO
SET SHOWPLAN_TEXT OFF
GO
DROP TABLE [dbo].[Message]
DROP TABLE [dbo].[Author]
GO

+

StmtText
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Nested Loops(Inner Join, OUTER REFERENCES:([M].[Author]))
|--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1007],0)))
| |--Stream Aggregate(GROUP BY:([M].[Author]) DEFINE:([Expr1007]=Count(*)))
| |--Sort(ORDER BY:([M].[Author] ASC))
| |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[Message].[PK_Message] AS [M]))
|--Clustered Index Seek(OBJECT:([tempdb].[dbo].[Author].[PK_Author] AS [A]), SEEK:([A].[ID]=[tempdb].[dbo].[Message].[Author] as [M].[Author]) ORDERED FORWARD)

(6 row(s) affected)

StmtText
-------------------------------------------------------------------------------------------------------------------------------------------------------
|--Nested Loops(Inner Join, OUTER REFERENCES:([tempdb].[dbo].[Message].[Author]))
|--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1008],0)))
| |--Stream Aggregate(GROUP BY:([tempdb].[dbo].[Message].[Author]) DEFINE:([Expr1008]=Count(*)))
| |--Sort(ORDER BY:([tempdb].[dbo].[Message].[Author] ASC))
| |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[Message].[PK_Message]))
|--Clustered Index Seek(OBJECT:([tempdb].[dbo].[Author].[PK_Author] AS [A]), SEEK:([A].[ID]=[tempdb].[dbo].[Message].[Author]) ORDERED FORWARD)

(6 row(s) affected)

Mnior,
когда-то давно я переписывал запросы, в доставшейся мне базе, так, как вы сейчас посовтовали, т.к. это мне казалось логичнее. Пока не понял, что этих запросов много, а меня обуяла лень - тогда я решил проверить, насколько овчинка стоит выделки и сравнить производительность, после чего увидел, что везде в простых случаях оптимизатор и сам справляется, после чего вздохнул с облегчением и забил на это дело.
В данном случае, я вообще пока склонен присоединиться к мнению bibiskula, т.к. никаких резонов для группировки ТС не представил. А меряться производительностью на чисто академических задачах - может привести к конфузу =)
Mind
Дата: 11.06.2011 03:19:05
No GROUP BY:

SELECT Author.ID, Author.Name
FROM Author
WHERE EXISTS 
  (SELECT * FROM Message where Message.AuthorID = Author.ID)
Mnior
Дата: 11.06.2011 14:26:00
SomewhereSomehow
т.е. вот так да? исключив полностью данные, вы решили не давать шансов оптимизатору? =)
Баг на баге. Доколе железная логика будет стоять ниже статистики, а это один из 100500 случаев. Будем регить.
SomewhereSomehow
перестаем дурить оптимизатор
Ну он сам виноват, что тупой.

SomewhereSomehow, вы правы. Удивительно что в реальности в основном совпадает как у меня в примере. Ибо переписывал кучу запросов, т.к. план был мерзопакостный.

SomewhereSomehow
когда-то давно я переписывал запросы
Это большая ошибка, планы надо смотреть всегда. Вы забыли основную проблему? - время оптимизации ограниченно Поэтому на сложных запросах (не этих детских шалостей) такое не канает. Это первое.
SomewhereSomehow
что везде в простых случаях
Во во. Это вы понимаете. Не лениво вносить "разнообразие"?

Второе, дописывать руками в GROUP BY ради лени - вброс не засчитан (ну разве что на мелких запросах) ибо WITH короче, и самое главное понятнее. Мухи, котлеты и всё такое.

Скажу одно, выражение "тяжело лопатить много текста" не верно. Нет оно верно для вас например, но не верно для меня. Какгрится "Что не убивает - делает нас сильней". Лопатив гигабайты кода скилл увеличивается на пару порядков, поэтому выровнять кривые руки во всём коде не тяжелее, чем написать маленький костыль.
Не лень двигатель, а прямые мозги. Уберёте лень - получите гениальность. Так что не ленитесь. ;)

SomewhereSomehow, спасибо. Ещё одна тема! Жаль что пора отпусков, iljy и других нет на месте (форум мёртвый).
Оптимизатор часто тупит, и рассчитывать на повышение на более высокий уровень логического вывода даже не приходится.
Повторюсь: Почему железная логика стоит ниже статистики?

Давайте опять "обдурим" оптимизатор и поставим везде LEFT:
	LEFT JOIN dbo.Author	A ON A.ID = M.Author
Всё, план опять съехал.
И не лень учитывать столько факторов? Может проще писать одинаково, просто и надёжно.

И меня это напрягло, а вот почему:
Частенько во VIEW пишу LEFT из-за одной очень удобной особенности (но в целом тупости) оптимизатора - только так он исключает таблицу из запроса, если не были выбраны по ней колонки.
По идее он должен был убрать и при простом JOIN, зачем же бесполезно проверять повторно существование строки при CHECK-нутом FOREIGN KEY?
ViPRos
Дата: 11.06.2011 14:58:09
Mnior,

убыстрите эту процедуру , пиво поставлю с шашлычком

ALTER PROCEDURE [dbo].[GetFreeTimeBlockForPowerDirect] 
 @proc uniqueidentifier,	
 @dn datetime,
 @dk datetime,
 @power numeric(5,2)
AS
BEGIN
	 SET NOCOUNT ON;
	
	 with t0 ([Дата начала],
	 		  [Дата окончания],
	 		  [Потребляемая мощность, процент])
	 as
	 (
	 	select [dbo].[Процессор процесса расчетного].[Дата начала],
	 		   [dbo].[Процессор процесса расчетного].[Дата окончания],
	 		   [dbo].[Процессор процесса расчетного].[Потребляемая мощность, процент]
	 	from [dbo].[Процессор процесса расчетного]
	 	where [dbo].[Процессор процесса расчетного].[Процессоры] = @proc and [dbo].[Процессор процесса расчетного].[Потребляемая мощность, процент] > 0
	 )
     select min(t4.Дата) Дата from
	 (select t3.Дата,
	  max(case when t2.Мощность + @power > 100 OR t2.Дата < @dn then 1 else 0 end) НеУдовлетворяетОграничениюПоМощьности
	  from 
	  (select t1.Дата, sum(ISNULL(case when t1.Дата <> t0.[Дата окончания] then t0.[Потребляемая мощность, процент] else 0 end,0)) Мощность 
       from 
      (select [Дата начала] Дата from t0
 	   union 
	   select [Дата окончания] Дата from t0
	   union 
	   select @dn)
	  t1 left outer join t0
	  on t1.Дата >= t0.[Дата начала] and t1.Дата <= t0.[Дата окончания]
      group by t1.Дата)
	  t2,
      (select [Дата начала] Дата from t0
 	   union 
	   select [Дата окончания] Дата from t0
	   union 
	   select @dn)
	  t3
	  where t3.Дата <= t2.Дата and t2.Дата <= DATEADD("ss", DATEDIFF("ss" ,@dn, @dk), t3.Дата)
      group by t3.Дата)
    t4
    where
    t4.НеУдовлетворяетОграничениюПоМощьности = 0

END