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

Откуда: Москва
Сообщений: 4804
Мне потребовалось посчитать оконный DISTINCT COUNT, который не поддерживается даже в SQL 2014.

Прошу высказаться по поводу оптимальности данного метода. Может у кого есть идеи получше.

И поправьте меня, если я не прав, но это должно работать начиная с SQL 2005.

CREATE TABLE #T(ID INT, A INT, B INT)
GO
-- тестовые данные 
INSERT INTO #T(ID, A, B)
VALUES
(1,1,1),(2,1,2),(1,1,3),(3,2,1),(3,2,2),(1,2,3),(6,3,1),(6,3,2),
(1,3,3),(4,1,1),(4,1,2),(1,1,3),(3,2,1),(3,2,2),(1,2,3),(3,3,1),
(6,3,2),(1,3,3),(6,1,1),(5,1,2),(1,1,3),(7,2,1),(8,2,2),(9,2,3),
(0,3,1),(8,3,2),(1,3,3)
GO


-- Distinсt_COUNT с группировкой по двум полям 
SELECT Distinсt_COUNT = COUNT(DISTINCT ID), CNT = COUNT(*), A, B FROM #T
GROUP BY A, B
ORDER BY A, B
GO 
-- эмуляция оконного Distinсt_COUNT с группировкой по двум полям 

SELECT -- COUNT(DISTINCT ID) OVER (PARTITION BY A, B), --> Use of DISTINCT is not allowed with the OVER clause.
	Distinсt_COUNT = MAX(DR) OVER(PARTITION BY A, B), 
	CNT = COUNT(*) OVER(PARTITION BY A, B), 
	A, B 
FROM  
(
	SELECT DR = DENSE_RANK() OVER(PARTITION BY A, B ORDER BY ID), A, B FROM #T
) F
ORDER BY A, B
GO 

-- Distinсt_COUNT с группировкой по одному полю 
SELECT Distinсt_COUNT = COUNT(DISTINCT ID), CNT = COUNT(*), A FROM #T
GROUP BY A
ORDER BY A
GO 
-- эмуляция оконного Distinсt_COUNT с группировкой по одному полю 
SELECT -- COUNT(DISTINCT ID) OVER (PARTITION BY A), --> Use of DISTINCT is not allowed with the OVER clause.
	Distinсt_COUNT = MAX(DR) OVER (PARTITION BY A), A, B,
	CNT = COUNT(*) OVER (PARTITION BY A)
FROM  
(
	SELECT DR = DENSE_RANK() OVER(PARTITION BY A ORDER BY ID), A, B FROM #T
) F
ORDER BY A, B

GO 

DROP TABLE #T
GO
11 мар 15, 14:13    [17370059]     Ответить | Цитировать Сообщить модератору
 Re: Оконный DISTINCT COUNT  [new]
denvio
Member

Откуда:
Сообщений: 132
-- эмуляция оконного Distinсt_COUNT с группировкой по одному полю 
WITH C AS
(
SELECT A, B, 
	CASE
		WHEN ROW_NUMBER() OVER (PARTITION BY A, ID ORDER BY ID) = 1
		THEN ID
	END AS DC
FROM #T
)
SELECT Distinсt_COUNT = COUNT(DC) OVER (PARTITION BY A/* ORDER BY ID*/), A, B, CNT = COUNT(*) OVER (PARTITION BY A)
FROM C
ORDER BY A, B;


"Высокопроизводительный код T-SQL. Оконные функции" Ицик Бен-Ган
12 мар 15, 09:47    [17373471]     Ответить | Цитировать Сообщить модератору
 Re: Оконный DISTINCT COUNT  [new]
a_voronin
Member

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

Планы почти одинаковые, но с Row_Number есть один лишний шаг.

+
CREATE TABLE #T(ID INT, A INT, B INT)
GO
-- тестовые данные 
INSERT INTO #T(ID, A, B)
VALUES
(1,1,1),(2,1,2),(1,1,3),(3,2,1),(3,2,2),(1,2,3),(6,3,1),(6,3,2),
(1,3,3),(4,1,1),(4,1,2),(1,1,3),(3,2,1),(3,2,2),(1,2,3),(3,3,1),
(6,3,2),(1,3,3),(6,1,1),(5,1,2),(1,1,3),(7,2,1),(8,2,2),(9,2,3),
(0,3,1),(8,3,2),(1,3,3)
GO
DECLARE @I INT = 14
WHILE @I > 0 
BEGIN 
	SET @I -= 1; 
	INSERT INTO #T(ID, A, B)
	SELECT ID, A, B FROM #T
END 
GO

SET STATISTICS IO  ON

-- Distinсt_COUNT с группировкой по двум полям 
SELECT Distinсt_COUNT = COUNT(DISTINCT ID), CNT = COUNT(*), A, B FROM #T
GROUP BY A, B
ORDER BY A, B
GO 
-- эмуляция оконного Distinсt_COUNT с группировкой по двум полям 

SELECT -- COUNT(DISTINCT ID) OVER (PARTITION BY A, B), --> Use of DISTINCT is not allowed with the OVER clause.
	Distinсt_COUNT = MAX(DR) OVER(PARTITION BY A, B), 
	CNT = COUNT(*) OVER(PARTITION BY A, B), 
	A, B 
FROM  
(
	SELECT DR = DENSE_RANK() OVER(PARTITION BY A, B ORDER BY ID), A, B FROM #T
) F
ORDER BY A, B
GO 

-- Distinсt_COUNT с группировкой по одному полю 
SELECT Distinсt_COUNT = COUNT(DISTINCT ID), CNT = COUNT(*), A FROM #T
GROUP BY A
ORDER BY A
GO 
-- эмуляция оконного Distinсt_COUNT с группировкой по одному полю 
SELECT -- COUNT(DISTINCT ID) OVER (PARTITION BY A), --> Use of DISTINCT is not allowed with the OVER clause.
	Distinсt_COUNT = MAX(DR) OVER (PARTITION BY A), A, B,
	CNT = COUNT(*) OVER (PARTITION BY A)
FROM  
(
	SELECT DR = DENSE_RANK() OVER(PARTITION BY A ORDER BY ID), A, B FROM #T
) F
ORDER BY A, B
GO 

WITH C AS
(
SELECT A, B, 
	CASE
		WHEN ROW_NUMBER() OVER (PARTITION BY A, ID ORDER BY ID) = 1
		THEN ID
	END AS DC
FROM #T
)
SELECT Distinсt_COUNT = COUNT(DC) OVER (PARTITION BY A/* ORDER BY ID*/), A, B, CNT = COUNT(*) OVER (PARTITION BY A)
FROM C
ORDER BY A, B;
GO

DROP TABLE #T
GO


К сообщению приложен файл. Размер - 91Kb
12 мар 15, 13:54    [17375155]     Ответить | Цитировать Сообщить модератору
 Re: Оконный DISTINCT COUNT  [new]
denvio
Member

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

К сообщению приложен файл. Размер - 34Kb
13 мар 15, 17:51    [17382322]     Ответить | Цитировать Сообщить модератору
 Re: Оконный DISTINCT COUNT  [new]
0-0
Guest
denvio, a_voronin

посмотрите что будет по чтениям
13 мар 15, 18:08    [17382413]     Ответить | Цитировать Сообщить модератору
 Re: Оконный DISTINCT COUNT  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
0-0,

На моём 2014-ом CASE WHEN ROW_NUMBER() проигрывает DENSE_RANK на несколько процентов по всем параметрам

К сообщению приложен файл. Размер - 10Kb
13 мар 15, 20:25    [17382911]     Ответить | Цитировать Сообщить модератору
 Re: Оконный DISTINCT COUNT  [new]
denvio
Member

Откуда:
Сообщений: 132
5 разных серверов, 3 снимка в разное время.
Чтение все время больше у ROW_NUMBER(), только вот Duration в большинстве случаев не в пользу DENSE_RANK

К сообщению приложен файл. Размер - 39Kb
15 мар 15, 12:45    [17385594]     Ответить | Цитировать Сообщить модератору
 Re: Оконный DISTINCT COUNT  [new]
denvio
Member

Откуда:
Сообщений: 132


К сообщению приложен файл. Размер - 34Kb
15 мар 15, 12:46    [17385596]     Ответить | Цитировать Сообщить модератору
 Re: Оконный DISTINCT COUNT  [new]
denvio
Member

Откуда:
Сообщений: 132


К сообщению приложен файл. Размер - 35Kb
15 мар 15, 12:46    [17385597]     Ответить | Цитировать Сообщить модератору
 Re: Оконный DISTINCT COUNT  [new]
invm
Member

Откуда: Москва
Сообщений: 9396
+ Как нужно тестировать и почему оконные функции не панацея
use tempdb;
set nocount on;
go

create table dbo.t (id int identity primary key, g int, v int);
go

insert into dbo.t
 (g, v)
 select top (1000000)
  rand(checksum(newid())) * 1000,
  rand(checksum(newid())) * 100
 from
  master.dbo.spt_values a cross apply
  master.dbo.spt_values b;
go

--create index IX_t__g__v on dbo.t (g, v);
go

declare @r table (d varchar(30), m varchar(50));
declare @m varchar(50), @s1 varchar(max), @s2 varchar(max), @s3 varchar(max);

select
 @m = newid(),
 @s1 = '/*' + @m + '*/declare @c int;
select
 @c = max(x.dr) over (partition by x.g)
from  
(
 select g, dr = dense_rank() over(partition by g order by v) from dbo.t
) x
option (maxdop 1);'

insert into @r
values
 ('dense_rank() + max()', @m);

select
 @m = newid(),
 @s2 = '/*' + @m + '*/declare @c int;
select
 @c = count(x.f) over (partition by x.g)
from  
(
 select g, case when row_number() over(partition by g, v order by (select 1)) = 1 then 1 end as f from dbo.t
) x
option (maxdop 1);'

insert into @r
values
 ('row_number() + count()', @m);

select
 @m = newid(),
 @s3 = '/*' + @m + '*/declare @c int;
select
 @c = t.id + x.g
from
 dbo.t t join
 (
  select g, count(distinct v) as c from dbo.t group by g
 ) x on x.g = t.g
option (maxdop 1);'

insert into @r
values
 ('group by + count(distinct)', @m);

declare @c int = 0;
while @c < 5
 begin
  exec(@s1);
  exec(@s2);
  exec(@s3);

  select
   @c += 1;
 end;

select
 r.d as [description],
 e.[elapsed time, ms],
 e.[logical reads],
 qp.query_plan
from
 sys.dm_exec_query_stats s cross apply
 sys.dm_exec_sql_text(s.sql_handle) t cross apply
 sys.dm_exec_query_plan(s.plan_handle) qp join
 @r r on t.text like '%/*' + r.m + '*/%' cross apply
 (select s.total_elapsed_time / cast(s.execution_count as money) / 1000, s.total_logical_reads / execution_count) as e([elapsed time, ms], [logical reads])
order by
 e.[elapsed time, ms];
go

drop table dbo.t;
go

Без индекса:
descriptionelapsed time, mslogical reads
group by + count(distinct)480,5788694
dense_rank() + max()3563,02262054797
row_number() + count()3581,25242054926

С индексом:
descriptionelapsed time, mslogical reads
group by + count(distinct)310,23984474
row_number() + count()2131,19342051463
dense_rank() + max()2195,32342051484
15 мар 15, 17:23    [17386247]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить