PIVOT SQL Server 协助

Question

给定以下表结构：

CrimeID | No_Of_Crimes  |   CrimeDate    | Violence | Robbery | ASB

1            1             22/02/2011         Y          Y      N

2            3             18/02/2011         Y          N      N

3            3             23/02/2011         N          N      Y

4            2             16/02/2011         N          N      Y

5            1             17/02/2011         N          N      Y

是否有可能使用 T-SQL 生成如下所示的结果集？

Category   |  This Week |  Last Week
Violence          1           3
Robbery           1           0
ASB               3           1

上周的数据应该小于“20/02/2011”，而本周的数据应该大于或等于“20/02/2011”，

我不是在寻找有人为我编码，尽管有一个代码代码片段会很方便:)，只是一些关于这是否可能的建议，以及我应该如何使用 SQL Server 来实现它。

有关信息，我目前正在 Web 服务器上使用 LINQ 执行所有这些聚合，但这需要每次发出此请求时通过网络发送 19MB。 （该表有很多类别，并且 > 150,000 行）。我想让数据库完成所有工作，只通过网络发送少量数据

非常感谢

Answer 1

为了清楚起见，编辑删除了不正确的sql
编辑忘记上面的尝试下面的

select * 
from (
select wk, crime, SUM(number) number
from (
    select case when datepart(week, crimedate) = datepart(week, GETDATE()) then 'This Week'
            when datepart(week, crimedate) = datepart(week, GETDATE())-1 then 'Last Week'
            else 'OLDER' end as wk, 
            crimedate, 
            case when violence ='Y' then no_of_crimes else 0 end as violence, 
            case when robbery ='Y' then no_of_crimes else 0 end as robbery, 
            case when asb ='Y' then no_of_crimes else 0 end as asb 
    from crimetable) as src
UNPIVOT
    (number for crime in 
    (violence, robbery, asb)) as pivtab
group by wk, crime
) z
PIVOT
( sum(number)
for wk in ([This Week], [Last Week])
) as pivtab

Answer 2

虽然迟到了，但有一个具有最佳查询计划的解决方案：

示例数据

create table crimes(
    CrimeID int, No_Of_Crimes int, CrimeDate datetime,
    Violence char(1), Robbery char(1), ASB char(1));
insert crimes
select 1,1,'20110221','Y','Y','N' union all
select 2,3,'20110218','Y','N','N' union all
select 3,3,'20110223','N','N','Y' union all
select 4,2,'20110216','N','N','Y' union all
select 5,1,'20110217','N','N','Y';

制作更多数据 - 除了上述 5 行之外，总共约 10240 行，每 5 行比前 5 行早 2 周。同时创建一个索引，这将有助于在犯罪日期。

insert crimes
select crimeId+number*5, no_of_Crimes, DATEADD(wk,-number*2,crimedate),
violence, robbery, asb
from crimes, master..spt_values
where type='P'

create index ix_crimedate on crimes(crimedate)

从这里开始，检查每个的输出以了解其进展情况。还要检查执行计划。

标准 Unpivot 打破类别。

select CrimeID, No_Of_Crimes, CrimeDate, Category, YesNo
from crimes
unpivot (YesNo for Category in (Violence,Robbery,ASB)) upv
where YesNo='Y'

注意：

1. YesNo 上的过滤器实际上是在逆透视之后应用的。你可以评论出来看看。

再次逆透视，但这次仅选择上周和本周的数据。

select CrimeID, No_Of_Crimes, Category,
    Week = sign(datediff(d,CrimeDate,w.firstDayThisWeek)+0.1)
from crimes
unpivot (YesNo for Category in (Violence,Robbery,ASB)) upv
cross join (select DATEADD(wk, DateDiff(wk, 0, getdate()), 0)) w(firstDayThisWeek)
where YesNo='Y'
  and CrimeDate >= w.firstDayThisWeek -7
  and CrimeDate <  w.firstDayThisWeek +7

注意：

1. (select DATEADD(wk, DateDiff(wk, 0, getdate()), 0)) w(firstDayThisWeek) 创建一个单列表，其中列包含此查询的关键日期，是本周的第一天（使用 DATEFIRST 设置）
2. CrimeDate 上的过滤器实际上在取消透视之前应用于基表。检查计划
3. Sign() 只是将数据分成 3 个桶 (-1/0/+1)。添加+0.1确保只有两个桶-1和+1。

最终查询，按本周/上周

select Category, isnull([1],0) ThisWeek, isnull([-1],0) LastWeek
from
(
    select Category, No_Of_Crimes,
        Week = sign(datediff(d,w.firstDayThisWeek,CrimeDate)+0.1)
    from crimes
    unpivot (YesNo for Category in (Violence,Robbery,ASB)) upv
    cross join (select DATEADD(wk, DateDiff(wk, 0, getdate()), -1)) w(firstDayThisWeek)
    where YesNo='Y'
      and CrimeDate >= w.firstDayThisWeek -7
      and CrimeDate <  w.firstDayThisWeek +7
) p
pivot (sum(No_Of_Crimes) for Week in ([-1],[1])) pv
order by Category Desc

输出旋转

Category  ThisWeek    LastWeek
--------- ----------- -----------
Violence  1           3
Robbery   1           0
ASB       3           3

Answer 3

我会尝试这个：

declare @FirstDayOfThisWeek date = '20110220';
select cat.category, 
       ThisWeek = sum(case when cat.CrimeDate >= @FirstDayOfThisWeek
                     then crt.No_of_crimes else 0 end), 
       LastWeek = sum(case when cat.CrimeDate >= @FirstDayOfThisWeek
                     then 0 else crt.No_of_crimes end)
from crimetable crt
cross apply (values 
    ('Violence', crt.Violence),
    ('Robbery', crt.Robbery),
    ('ASB', crt.ASB))
    cat (category, incategory)
where cat.incategory = 'Y'
  and crt.CrimeDate >= @FirstDayOfThisWeek-7
group by cat.category;