CTE到Pivot函数的语法问题

Question

我在从变量到CTE再到Pivot的语法正确性方面遇到问题。我正在将sp_whoisative捕获到一个表中，现在正在按区域聚合CPU和Duration。然后我想调整这些结果，以便在Excel中轻松绘制图表。我的解决方法是输出到#Temp表，这会在尝试输出到Excel表时导致SSIS出现问题。

为了简洁起见，我精简了CASE语句。大约有30个区域。

如何将其封装在CTE中，以便使用开始和结束变量日期进行透视并消除#Temp表？

IF OBJECT_ID('tempdb..#MySP') IS NOT NULL
DROP TABLE #MySP

DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = DATEADD(hh,23,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-1),0))
SET @EndDate =  DATEADD(hh,9,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-0),0))
;WITH CTE AS
(
select --login_name, login_time, session_id,
(Substring([dd hh:mm:ss.mss],1,2) * 86400) + (Substring([dd hh:mm:ss.mss],4,2) * 3600) + (Substring([dd hh:mm:ss.mss],7,2) * 60) + (Substring([dd hh:mm:ss.mss],10,2))  as TotalSec
,t.*
from (
select w.*,
row_number() over(partition by session_id, login_time, login_name order by collection_time desc) rn
from Regional.DBA.WhoIsActive w
) t 
where rn = 1 and login_time between  @StartDate and @EndDate 
), RESULT as
(
select  CTE.login_name  ,   SUM(CTE.TotalSec ) as UserSecTot, SUM(CONVERT(int,REPLACE(cte.CPU,',',''))) as UserCPUTot from CTE 
Group by CTE.login_name 
) 
select  @StartDate as StartDate, @EndDate as EndDate,
CASE
WHEN login_name = 'sa' then 'sa'
WHEN ParseName(Replace(login_name,'','.'),2) like '%vhamaster%' and login_name like '%Region01%' or login_name like '%_V1%' THEN 'Region01'
WHEN ParseName(Replace(login_name,'','.'),2) like '%vhamaster%' and login_name like '%Region02%' or login_name like '%_V2%' THEN 'Region02'
WHEN ParseName(Replace(login_name,'','.'),2) like '%vhamaster%' then 'z' + ParseName(Replace(login_name,'','.'),1)
WHEN ParseName(Replace(login_name,'','.'),2) like '%Reg01%' then 'Region01'
WHEN ParseName(Replace(login_name,'','.'),2) like '%Reg02%' then 'Region02'
ELSE ParseName(Replace(login_name,'','.'),2) 
END as Domain , login_name,  DBA.udf_SecToHHMMSS(UserSecTot) as Total_Time, UserSecTot, UserCPUTot 
into #MySP
from RESULT
select * from #MySP 
select * from
(select Domain, UserCPUTot from #MySP) as sourcetable
PIVOT(
sum(UserCPUTot) 
FOR Domain IN (
[Region01], 
[Region02] 
)
) AS pivot_table;
select * from
(select Domain, UserSecTot from #MySP) as sourcetable
PIVOT(
sum(UserSecTot) 
FOR Domain IN (
[Region01], 
[Region02]

)
) AS pivot_table;

Answer 1

DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = DATEADD(hh,23,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-1),0))
SET @EndDate =  DATEADD(hh,9,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-0),0))
;WITH CTE AS
(
select --login_name, login_time, session_id,
(Substring([dd hh:mm:ss.mss],1,2) * 86400) + (Substring([dd hh:mm:ss.mss],4,2) * 3600) + (Substring([dd hh:mm:ss.mss],7,2) * 60) + (Substring([dd hh:mm:ss.mss],10,2))  as TotalSec
,t.*
from (
select w.*,
row_number() over(partition by session_id, login_time, login_name order by collection_time desc) rn
from Regional.DBA.WhoIsActive w
) t 
where rn = 1 and login_time between  @StartDate and @EndDate 
), RESULT as
(
select  CTE.login_name  ,   SUM(CTE.TotalSec ) as UserSecTot, SUM(CONVERT(int,REPLACE(cte.CPU,',',''))) as UserCPUTot from CTE 
Group by CTE.login_name 
) 
, cteoutput as (
select  @StartDate as StartDate, @EndDate as EndDate,
CASE
WHEN login_name = 'sa' then 'sa'
WHEN ParseName(Replace(login_name,'','.'),2) like '%vhamaster%' and login_name like '%Region01%' or login_name like '%_V1%' THEN 'Region01'
WHEN ParseName(Replace(login_name,'','.'),2) like '%vhamaster%' and login_name like '%Region02%' or login_name like '%_V2%' THEN 'Region02'
WHEN ParseName(Replace(login_name,'','.'),2) like '%vhamaster%' then 'z' + ParseName(Replace(login_name,'','.'),1)
WHEN ParseName(Replace(login_name,'','.'),2) like '%Reg01%' then 'Region01'
WHEN ParseName(Replace(login_name,'','.'),2) like '%Reg02%' then 'Region02'
ELSE ParseName(Replace(login_name,'','.'),2) 
END as Domain , login_name,  DBA.udf_SecToHHMMSS(UserSecTot) as Total_Time, UserSecTot, UserCPUTot 
from RESULT
)

select * from
(select Domain, UserCPUTot from cteoutput) as sourcetable
PIVOT(
sum(UserCPUTot) 
FOR Domain IN (
[Region01], 
[Region02] 
)
) AS pivot_table;

Answer 2

如果临时表导致问题，则一种解决方案是创建物理表。这可以防止不必要地重复每个数据透视的代码。有很多方法可以实现这一点，其中一种方法是创建一个"report_datetimes"表来存储唯一的日期时间范围，以及一个"report_whoisactivity"表来保存域数据。然后，SQL脚本可以在必要时删除现有行，插入新行，并创建数据透视表。像这样的东西。

DDL-

drop table if exists report_datetimes;
go
create table report_datetimes(
r_id                      int identity(1,1) primary key not null,
start_dtm                 datetime not null,
end_dtm                   datetime not null);
go
create unique index ndx_unq_start_end_dtm on report_datetimes(start_dtm, end_dtm);
go
drop table if exists report_whoisactive;
go
create table report_whoisactive(
w_id                      int identity(1,1) primary key not null,
r_id                      int not null references report_datetimes(r_id),
domain                    varchar(10) not null,
login_name                varchar(256) not null,
Total_Time                time,
UserSecTot                time,
UserCPUTot                time);
go
create unique index ndx_unq_r_domain_login on report_whoisactive(r_id, domain, login_name);
go

脚本第1部分：填充表

declare
@start_dtm    datetime=dateadd(hh,23,dateadd(dd,datediff(dd,0,getdate()-1),0)),
@end_dtm      datetime=dateadd(hh,9,dateadd(dd,datediff(dd,0,getdate()-0),0)),
@r_id         int;
/* get r_id if exists (and delete existing data), create if it doesn't */
select @r_id=r_id
from report_datetimes
where start_dtm=@start_dtm
and end_dtm=@end_dtm;
if @@rowcount=0
begin
insert report_datetimes(start_dtm, end_dtm) values (@start_dtm, @end_dtm);
select @r_id=cast(scope_identity() as int);
end
else
delete report_whoisactive where r_id=@r_id;
;WITH 
CTE AS (
select (Substring([dd hh:mm:ss.mss],1,2) * 86400) + 
(Substring([dd hh:mm:ss.mss],4,2) * 3600) + 
(Substring([dd hh:mm:ss.mss],7,2) * 60) + 
(Substring([dd hh:mm:ss.mss],10,2))  as TotalSec, t.*
from (select w.*,
row_number() over(partition by session_id, login_time, login_name 
order by collection_time desc) rn
from Regional.DBA.WhoIsActive w) t 
where rn = 1
and login_time between @StartDate and @EndDate), 
RESULT as (
select  CTE.login_name, SUM(CTE.TotalSec ) as UserSecTot,
SUM(CONVERT(int,REPLACE(cte.CPU,',',''))) as UserCPUTot
from CTE 
Group by CTE.login_name) 
insert report_whoisactive(r_id, domain, login_name, Total_Time,UserSecTot, UserCPUTot)
select @r_id,
CASE
WHEN login_name = 'sa' then 'sa'
WHEN ParseName(Replace(login_name,'','.'),2) like '%vhamaster%' and login_name like '%Region01%' or login_name like '%_V1%' THEN 'Region01'
WHEN ParseName(Replace(login_name,'','.'),2) like '%vhamaster%' and login_name like '%Region02%' or login_name like '%_V2%' THEN 'Region02'
WHEN ParseName(Replace(login_name,'','.'),2) like '%vhamaster%' then 'z' + ParseName(Replace(login_name,'','.'),1)
WHEN ParseName(Replace(login_name,'','.'),2) like '%Reg01%' then 'Region01'
WHEN ParseName(Replace(login_name,'','.'),2) like '%Reg02%' then 'Region02'
ELSE ParseName(Replace(login_name,'','.'),2) 
END as Domain , login_name,  DBA.udf_SecToHHMMSS(UserSecTot) as Total_Time, UserSecTot, UserCPUTot 
from RESULT;

脚本第2部分：引用r_id 的数据透视表

select * from
(select Domain, UserCPUTot from report_whoisactive where r_id=@r_id) as sourcetable
PIVOT(
sum(UserCPUTot) 
FOR Domain IN (
[Region01], 
[Region02] 
)
) AS pivot_table;
select * from
(select Domain, UserSecTot from report_whoisactive where r_id=@r_id) as sourcetable
PIVOT(
sum(UserSecTot) 
FOR Domain IN (
[Region01], 
[Region02]

)
) AS pivot_table;