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

Откуда: Санкт-Петербург
Сообщений: 308
Приветствую!
Есть две таблицы:

CREATE TABLE [dbo].[tblClub]
(
    [Id] BIGINT NOT NULL PRIMARY KEY, 
    [Name] NVARCHAR(50) NULL
);

CREATE TABLE [dbo].[tblUser]
(
	[Id] BIGINT NOT NULL PRIMARY KEY,
	[FullName] NVARCHAR(50) NULL,
	[Birthday] DATE,
	[ClubId] BIGINT NULL,
	CONSTRAINT [FK_User_Club] FOREIGN KEY([ClubId]) 
		REFERENCES [dbo].[tblClub] ([Id])
);


В результате селекта хочется получить набор данных вида:
([ClubName], [Кол-во людей в клубе от 16 до 20], [Кол-во людей в клубе от 20 до 35], [Кол-во людей в клубе старше 35])

На первый взгляд достаточно простой запрос, но чего-то застрял. Заранее спасибо!
15 июн 15, 00:18    [17770326]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с несколькими группировками  [new]
Anton.
Member

Откуда: Санкт-Петербург
Сообщений: 308
Сходу написал так, но работает неверно :(
select c.Id, c.Name, 
	SUM(CASE
		   WHEN (u35.Id IS NOT NULL) THEN 1
		   ELSE 0
		END) AS U35,
	SUM(CASE
		   WHEN (u20.Id IS NOT NULL) THEN 1
		   ELSE 0
		END) AS U20
FROM tblClub c
LEFT JOIN tblUser u35 ON u35.ClubId = c.Id AND (YEAR(getdate()) - YEAR(u35.Birthday)) >= 35
LEFT JOIN tblUser u20 ON u20.ClubId = c.Id AND (YEAR(getdate()) - YEAR(u20.Birthday)) BETWEEN 20 AND 35
GROUP BY c.Id, c.Name
ORDER BY c.Name;
15 июн 15, 00:43    [17770350]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с несколькими группировками  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2680
Anton.,

between 20 and 34???
15 июн 15, 00:59    [17770355]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с несколькими группировками  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21242
Anton.
Сходу написал так
А нахрена две копии таблицы? А если очень хочется две - то нахрена кейсы?
15 июн 15, 01:02    [17770358]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с несколькими группировками  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2680
И да, пара left join ничуть не дешевле, чем пара вложенных селектов.

select c.Id, c.Name, 
        (select count(1) from tblUser where ClubId = c.Id AND (YEAR(getdate()) - YEAR(u35.Birthday)) >= 35) AS U35,
        (select count(1) from tblUser where ClubId = c.Id AND (YEAR(getdate()) - YEAR(u35.Birthday)) BETWEEN 20 AND 34) AS U20
FROM tblClub c
ORDER BY c.Name;
15 июн 15, 01:04    [17770361]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с несколькими группировками  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2680
...Можно ещё так:

select c.Id, c.Name, 
	SUM(u.u35) AS U35,
	SUM(u.u20) AS U20
FROM tblClub c
LEFT JOIN
(select ClubId,
  case 
    when (YEAR(getdate()) - YEAR(Birthday)) >= 35 then 1
    else 0
  end as u35,
  case 
    when (YEAR(getdate()) - YEAR(Birthday)) BETWEEN 20 AND 34 then 1
    else 0
  end as u20
  from tblUser 
) u on u.ClubId  = c.Id
GROUP BY c.Id, c.Name
ORDER BY c.Name;


...но это всё фигня.
Лучше:
select count(*), ClubId, 
  case....
 from tblUser
group by
ClubId, case....
15 июн 15, 01:18    [17770369]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с несколькими группировками  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2680
...только вот что-то мне подсказывает, что YEAR(getdate()) - YEAR(u35.Birthday) - не говорит о паспортном возрасте.
Тут нужен datediff
15 июн 15, 01:23    [17770372]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с несколькими группировками  [new]
Okmor
Member

Откуда:
Сообщений: 132
Так будет производительнее.
Функция outer apply присутствует начиная с 2008 R2

if object_id('tempdb..#tblClub') is not null drop table #tblClub
if object_id('tempdb..#tblUser') is not null drop table #tblUser


CREATE TABLE [#tblClub]
(
    [ClubId] BIGINT NOT NULL PRIMARY KEY, 
    [Name] NVARCHAR(50) NULL
);
INSERT INTO [#tblClub]
           ([ClubId],[Name])
VALUES
(1, 'Зелена гичка'),(2, 'Під плотом'),(3, 'Жіночі сльози'),(4, 'Остання зустріч')

CREATE TABLE [#tblUser]
(
	[Id] BIGINT NOT NULL PRIMARY KEY,
	[FullName] NVARCHAR(50) NULL,
	[Birthday] DATE,
	[ClubId] BIGINT NULL,
	
);
INSERT INTO [#tblUser]
           ([Id],[FullName],[Birthday],[ClubId])
 VALUES
 (1,'Вася',		'1995.01.01',1),
 (2,'Петя',		'1980.01.01',2),
 (3,'Славко',	'1980.01.01',3), 
 (4,'Вася',		'1990.01.01',4),
 (5,'Петя',		'1990.01.01',1),
 (6,'Славко',	'1990.01.01',2),    
 (7,'Вася',		'1980.01.01',3),
 (8,'Петя',		'1980.01.01',4),
 (9,'Славко',	'1975.01.01',1), 
 (10,'Вася',	'1965.01.01',2),
 (11,'Петя',	'1980.01.01',3),
 (12,'Славко',	'1935.01.01',4), 
 (13,'Вася',	'1980.01.01',1),
 (14,'Петя',	'1980.01.01',2),
 (15,'Славко',	'1980.01.01',3)  
 
 select * 
 from [#tblClub]
 outer apply
	(select 
	 count(case when datediff(year, [#tblUser].Birthday,GETDATE()) between 16 and 20 then [#tblUser].Id else null end) as U_16_20
	,count(case when datediff(year, [#tblUser].Birthday,GETDATE()) between 20 and 35 then [#tblUser].Id else null end) as U_20_35
	,count(case when datediff(year, [#tblUser].Birthday,GETDATE()) >35 then [#tblUser].Id else null end) as U_35_100 
	 from [#tblUser]
	 where [#tblUser].ClubId=[#tblClub].ClubId
	) as Client
     
15 июн 15, 09:53    [17770749]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с несколькими группировками  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4893
Anton.
Приветствую!
Есть две таблицы:


В результате селекта хочется получить набор данных вида:
([ClubName], [Кол-во людей в клубе от 16 до 20], [Кол-во людей в клубе от 20 до 35], [Кол-во людей в клубе старше 35])

На первый взгляд достаточно простой запрос, но чего-то застрял. Заранее спасибо!


Судя по запросу, вам надо начинать думать про OLAP-кубы
15 июн 15, 10:19    [17770877]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с несколькими группировками  [new]
Anton.
Member

Откуда: Санкт-Петербург
Сообщений: 308
Спасибо огромное за ответы!

Makar4ik, ты крут! :)

Окончательная версия запроса получилась такой:

select c.Id, c.Name, 
	SUM(u.u35) AS U35,
	SUM(u.u20) AS U20
FROM tblClub c
JOIN
(
select ClubId,
  case 
    when FLOOR(DATEDIFF(DAY, Birthday, getdate())/365.25) >= 35 then 1
    else 0
  end as u35,
  case 
    when FLOOR(DATEDIFF(DAY, Birthday, getdate())/365.25) BETWEEN 20 AND 34 then 1
    else 0
  end as u20
  from tblUser
) u on u.ClubId  = c.Id
GROUP BY c.Id, c.Name
HAVING SUM(u.u35)>0 OR SUM(u.u20)>0
ORDER BY c.Name;


Okmor, Спасибо!
Только по скорости получилось не быстрее, а наоборот - на моей выборке примерно в 2 раза медленнее.
15 июн 15, 10:27    [17770921]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с несколькими группировками  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21242
SELECT c.Id, c.Name, 
SUM(CASE WHEN DATEDIFF(YEAR, u.Birthday, GETDATE()) >= 35 then 1 ELSE 0 END) U35,
SUM(CASE WHEN DATEDIFF(YEAR, u.Birthday, GETDATE()) BETWEEN 20 AND 34 THEN 1 ELSE 0 END) U20
FROM tblClub c
INNER JOIN tblUser u ON u.ClubId = c.Id AND DATEDIFF(YEAR, u.Birthday, GETDATE()) >= 20
GROUP BY c.Id, c.Name
ORDER BY c.Name;

Почему используется левое связывание? явно хватит внутреннего - HAVING в последнем запросе на него намекает более чем явно.
15 июн 15, 10:44    [17770990]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с несколькими группировками  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2680
Akina
SELECT c.Id, c.Name, 
SUM(CASE WHEN DATEDIFF(YEAR, u.Birthday, GETDATE()) >= 35 then 1 ELSE 0 END) U35,
SUM(CASE WHEN DATEDIFF(YEAR, u.Birthday, GETDATE()) BETWEEN 20 AND 34 THEN 1 ELSE 0 END) U20
FROM tblClub c
INNER JOIN tblUser u ON u.ClubId = c.Id AND DATEDIFF(YEAR, u.Birthday, GETDATE()) >= 20
GROUP BY c.Id, c.Name
ORDER BY c.Name;

Почему используется левое связывание? явно хватит внутреннего - HAVING в последнем запросе на него намекает более чем явно.
вообще-то, я left использовал в надежде, что топикстартер сознательно хочет в запросе получать клубы, в которых нет юзеров...
Inner join это исключает какбэ... :)
15 июн 15, 21:37    [17773724]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить