我有这个数据集,我需要为特定用户每月的访问次数建立.
我有一个包含以下字段的sql表:
我有一个包含以下字段的sql表:
>用户nvarchar(30)
> DateVisit日期时间
我现在想要实现的是按月为每个用户分组所有访问,如图所示:
我启动了查询,我能够通过此查询获取该月的月份和访问总数(不是由用户拆分);
- select [1] AS January,[2] AS February,[3] AS March,[4] AS April,[5] AS May,[6] AS June,[7] AS July,[8] AS August,[9] AS September,[10] AS October,[11] AS November,[12] AS December
- from
- (
- SELECT MONTH(DateVisit) AS month,[User] FROM UserVisit
- ) AS t
- PIVOT (
- COUNT([User])
- FOR month IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
- ) p
通过上面的查询,我得到了这个结果:
现在我想知道如何为用户添加一个列并按用户分割值.
我知道它的星期五,但如果有人可以帮助我,我将不胜感激.
提前谢谢,Laziale
解决方法
好的,两种解决方案都很好看. Ali的答案有效,但我会使用SUM()函数,我讨厌NULLS.让我们尝试两者,看看查询计划与执行时间.
我总是用数据创建一个测试表,这样我就不会给用户Aziale一个错误的答案.
下面的代码不是最漂亮的,但确实设置了一个测试用例.我在tempdb中创建了一个名为user_visits的数据库.对于每个月,我使用for循环来添加用户并为他们提供月份的创建开始日期.
现在我们有数据,我们可以玩.
- -- Drop the table
- drop table tempdb.dbo.user_visits
- go
- -- Create the table
- create table tempdb.dbo.user_visits
- (
- uv_id int identity(1,1),uv_visit_date smalldatetime,uv_user_name varchar(30)
- );
- go
- -- January data
- declare @cnt int = 1;
- while @cnt <= 103
- begin
- if (@cnt <= 21)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,uv_user_name)
- values ('20130101','Patrick');
- if (@cnt <= 44)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,'Barbara');
- if (@cnt <= 65)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,'Danielle');
- if (@cnt <= 103)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,'John');
- set @cnt = @cnt + 1
- end
- go
- -- February data
- declare @cnt int = 1;
- while @cnt <= 99
- begin
- if (@cnt <= 29)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,uv_user_name)
- values ('20130201','Patrick');
- if (@cnt <= 42)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,'Barbara');
- if (@cnt <= 55)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,'Danielle');
- if (@cnt <= 99)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,'John');
- set @cnt = @cnt + 1
- end
- go
- -- March data
- declare @cnt int = 1;
- while @cnt <= 98
- begin
- if (@cnt <= 25)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,uv_user_name)
- values ('20130301','Patrick');
- if (@cnt <= 46)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,'Barbara');
- if (@cnt <= 75)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,'Danielle');
- if (@cnt <= 98)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,'John');
- set @cnt = @cnt + 1
- end
- go
- -- April data
- declare @cnt int = 1;
- while @cnt <= 91
- begin
- if (@cnt <= 32)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,uv_user_name)
- values ('20130401','Patrick');
- if (@cnt <= 48)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,'Barbara');
- if (@cnt <= 60)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,'Danielle');
- if (@cnt <= 91)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,'John');
- set @cnt = @cnt + 1
- end
- go
- -- May data
- declare @cnt int = 1;
- while @cnt <= 120
- begin
- if (@cnt <= 40)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,uv_user_name)
- values ('20130501','Patrick');
- if (@cnt <= 41)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,'Barbara');
- if (@cnt <= 70)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,'Danielle');
- if (@cnt <= 120)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,'John');
- set @cnt = @cnt + 1
- end
- go
- -- June data
- declare @cnt int = 1;
- while @cnt <= 103
- begin
- if (@cnt <= 17)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,uv_user_name)
- values ('20130601','Patrick');
- if (@cnt <= 45)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,'Barbara');
- if (@cnt <= 62)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,'John');
- set @cnt = @cnt + 1
- end
- go
- -- July data
- declare @cnt int = 1;
- while @cnt <= 99
- begin
- if (@cnt <= 20)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,uv_user_name)
- values ('20130701','Patrick');
- if (@cnt <= 43)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,'Barbara');
- if (@cnt <= 66)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,'John');
- set @cnt = @cnt + 1
- end
- go
- -- August data
- declare @cnt int = 1;
- while @cnt <= 98
- begin
- if (@cnt <= 26)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,uv_user_name)
- values ('20130801','Patrick');
- if (@cnt <= 47)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,'Barbara');
- if (@cnt <= 71)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,'John');
- set @cnt = @cnt + 1
- end
- go
- -- September data
- declare @cnt int = 1;
- while @cnt <= 91
- begin
- if (@cnt <= 25)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,uv_user_name)
- values ('20130901','Patrick');
- if (@cnt <= 49)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,'Barbara');
- if (@cnt <= 59)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,'John');
- set @cnt = @cnt + 1
- end
- go
- -- October data
- declare @cnt int = 1;
- while @cnt <= 120
- begin
- if (@cnt <= 25)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,uv_user_name)
- values ('20131001','Patrick');
- if (@cnt <= 40)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,'Barbara');
- if (@cnt <= 73)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,'John');
- set @cnt = @cnt + 1
- end
- go
- -- November data
- declare @cnt int = 1;
- while @cnt <= 101
- begin
- if (@cnt <= 32)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,uv_user_name)
- values ('20131101','Patrick');
- if (@cnt <= 50)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,'Danielle');
- if (@cnt <= 101)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,'John');
- set @cnt = @cnt + 1
- end
- go
- -- December data
- declare @cnt int = 1;
- while @cnt <= 90
- begin
- if (@cnt <= 40)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,uv_user_name)
- values ('20131201','Patrick');
- if (@cnt <= 52)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,'Barbara');
- if (@cnt <= 61)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,'Danielle');
- if (@cnt <= 90)
- insert into tempdb.dbo.user_visits
- (uv_visit_date,'John');
- set @cnt = @cnt + 1
- end
- go
请不要在编码中使用保留字作为列名 – IE – 月是保留字.
下面的代码为您提供了正确的答案.
- -- Grab the data (1)
- select
- my_user,[1] AS January,[2] AS Febrary,[12] AS December
- from
- (
- SELECT MONTH(uv_visit_date) AS my_month,uv_user_name as my_user FROM tempdb.dbo.user_visits
- ) AS t
- PIVOT (
- COUNT(my_month)
- FOR my_month IN([1],[12])
- ) as p
- -- Grab the data (2)
- SELECT uv_user_name,SUM(CASE WHEN MONTH(uv_visit_date) = 1 THEN 1 ELSE 0 END) January,SUM(CASE WHEN MONTH(uv_visit_date) = 2 THEN 1 ELSE 0 END) Feburary,SUM(CASE WHEN MONTH(uv_visit_date) = 3 THEN 1 ELSE 0 END) March,SUM(CASE WHEN MONTH(uv_visit_date) = 4 THEN 1 ELSE 0 END) April,SUM(CASE WHEN MONTH(uv_visit_date) = 5 THEN 1 ELSE 0 END) May,SUM(CASE WHEN MONTH(uv_visit_date) = 6 THEN 1 ELSE 0 END) June,SUM(CASE WHEN MONTH(uv_visit_date) = 7 THEN 1 ELSE 0 END) July,SUM(CASE WHEN MONTH(uv_visit_date) = 8 THEN 1 ELSE 0 END) August,SUM(CASE WHEN MONTH(uv_visit_date) = 9 THEN 1 ELSE 0 END) September,SUM(CASE WHEN MONTH(uv_visit_date) = 10 THEN 1 ELSE 0 END) October,SUM(CASE WHEN MONTH(uv_visit_date) = 11 THEN 1 ELSE 0 END) November,SUM(CASE WHEN MONTH(uv_visit_date) = 12 THEN 1 ELSE 0 END) December
- FROM tempdb.dbo.user_visits
- GROUP BY uv_user_name
进行此类分析时,请始终清除缓存/缓冲区并获取I / O.
- -- Show time & i/o
- SET STATISTICS TIME ON
- SET STATISTICS IO ON
- GO
- -- Remove clean buffers & clear plan cache
- CHECKPOINT
- DBCC DROPCLEANBUFFERS
- DBCC FREEPROCCACHE
- GO
- -- Solution 1
- sql Server parse and compile time:
- cpu time = 0 ms,elapsed time = 42 ms.
- (4 row(s) affected)
- Table 'Worktable'. Scan count 0,logical reads 0,physical reads 0,read-ahead reads 0,lob logical reads 0,lob physical reads 0,lob read-ahead reads 0.
- Table 'user_visits'. Scan count 1,logical reads 11,lob read-ahead reads 0.
- sql Server Execution Times:
- cpu time = 16 ms,elapsed time = 5 ms.
两种解决方案都具有相同数量的读取,工作表等.但是,SUM()解决方案只需少一个运算符.
我要给两个回答竖起大拇指的人1 !!