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

Откуда:
Сообщений: 16
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64)
Mar 19 2015 12:32:14
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Есть таблица, требуется собрать сумму одного поля (inv_amt) среди записей, удовлетворяющих одному критерию, а другого поля (amt_paid) - среди записей, удовлетворяющих другому критерию. Это элементарно сделано запросом ниже:

select	p1.voucher
,	sum(p1.inv_amt) as invoiced_amount
,	sum(p2.amt) as amount_paid
from	aptrxp p1
outer apply 
	(select amt_paid as amt from aptrxp px
	where	px.voucher = p1.voucher
	and	px.[type] like 'P'
	and	px.due_date <= '2015-12-31') p2
where	p1.[type] in ('V','A')
group by	p1.voucher


Однако запрос ходит по таблице aptrx ДВА раза:

|--Compute Scalar(DEFINE:([Expr1005]=CASE WHEN [Expr1017]=(0) THEN NULL ELSE [Expr1018] END, [Expr1006]=CASE WHEN [Expr1019]=(0) THEN NULL ELSE [Expr1020] END))
|--Stream Aggregate(GROUP BY:([p1].[voucher]) DEFINE:([Expr1017]=COUNT_BIG([NLSTEST_App].[dbo].[aptrxp].[inv_amt] as [p1].[inv_amt]), [Expr1018]=SUM([NLSTEST_App].[dbo].[aptrxp].[inv_amt] as [p1].[inv_amt]), [Expr1019]=COUNT_BIG([Expr1004]), [Expr1020]=SUM([Expr1004])))
|--Sort(ORDER BY:([p1].[voucher] ASC))
|--Hash Match(Right Outer Join, HASH:([px].[voucher])=([p1].[voucher]))
|--Compute Scalar(DEFINE:([Expr1004]=[NLSTEST_App].[dbo].[aptrxp].[amt_paid] as [px].[amt_paid]))
| |--Clustered Index Scan(OBJECT:([NLSTEST_App].[dbo].[aptrxp].[IX_aptrxp] AS [px]), WHERE:([NLSTEST_App].[dbo].[aptrxp].[due_date] as [px].[due_date]<='2015-12-31 00:00:00.000' AND [NLSTEST_App].[dbo].[aptrxp].[type] as [px].[type] like N'P'))
|--Clustered Index Scan(OBJECT:([NLSTEST_App].[dbo].[aptrxp].[IX_aptrxp] AS [p1]), WHERE:([NLSTEST_App].[dbo].[aptrxp].[type] as [p1].[type]=N'A' OR [NLSTEST_App].[dbo].[aptrxp].[type] as [p1].[type]=N'V'))


Есть ли способ написать этот запрос так, чтобы был один, а не два скана?


Для сравнения, если бы агрегация нужна была бы по одному и тому же набору записей, иными словами:
select	p1.voucher
,	sum(p1.inv_amt) as invoiced_amount
,	sum(p1.amt_paid) as amount_paid
from		aptrxp p1
where	p1.[type] in ('V','A')
and	p1.due_date < '2015-12-31'
group by	p1.voucher

то план запроса такой:
|--Compute Scalar(DEFINE:([Expr1002]=CASE WHEN [Expr1014]=(0) THEN NULL ELSE [Expr1015] END, [Expr1003]=CASE WHEN [Expr1016]=(0) THEN NULL ELSE [Expr1017] END))
|--Stream Aggregate(GROUP BY:([p1].[voucher]) DEFINE:([Expr1014]=COUNT_BIG([NLSTEST_App].[dbo].[aptrxp].[inv_amt] as [p1].[inv_amt]), [Expr1015]=SUM([NLSTEST_App].[dbo].[aptrxp].[inv_amt] as [p1].[inv_amt]), [Expr1016]=COUNT_BIG([NLSTEST_App].[dbo].[aptrxp].[amt_paid] as [p1].[amt_paid]), [Expr1017]=SUM([NLSTEST_App].[dbo].[aptrxp].[amt_paid] as [p1].[amt_paid])))
|--Sort(ORDER BY:([p1].[voucher] ASC))
|--Clustered Index Scan(OBJECT:([NLSTEST_App].[dbo].[aptrxp].[IX_aptrxp] AS [p1]), WHERE:([NLSTEST_App].[dbo].[aptrxp].[due_date] as [p1].[due_date]<'2015-12-31 00:00:00.000' AND ([NLSTEST_App].[dbo].[aptrxp].[type] as [p1].[type]=N'A' OR [NLSTEST_App].[dbo].[aptrxp].[type] as [p1].[type]=N'V')))
7 авг 16, 22:23    [19513428]     Ответить | Цитировать Сообщить модератору
 Re: агрегация по разным критериям отбора  [new]
invm
Member

Откуда: Москва
Сообщений: 9838
select
 p1.voucher
,sum(case when p1.[type] in ('V', 'A') then p1.inv_amt else 0 end) as invoiced_amount
,sum(case when p1.[type] = 'P' and p1.due_date <= '20151231' then p1.amt else 0 end) as amount_paid
from
 aptrxp p1
where
 p1.[type] in ('V', 'A', 'P')
group by
 p1.voucher
7 авг 16, 22:43    [19513537]     Ответить | Цитировать Сообщить модератору
 Re: агрегация по разным критериям отбора  [new]
SL8Behemoth
Member

Откуда:
Сообщений: 16
Да, проще некуда...
Спасибо. Ступил, искал что-то более сложное.
7 авг 16, 23:01    [19513622]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить