问题描述
我有一个数据集,需要为特定用户的每月访问次数构建.我有一个包含以下字段的 SQL 表:
I have a dataset I need to build for number of visits per month for particular user. I have a SQL table which contains these fields:
- 用户 nvarchar(30)
- 日期访问日期时间
- User nvarchar(30)
- DateVisit datetime
我现在想要实现的是将每个用户的所有访问按月分组,如图所示:
What I want to achieve now is to get all the visits grouped by month for each user, something like at the picture:
我开始查询,我可以通过这个查询获得月份和该月的总访问量(不按用户拆分);
I started the query, I am able to get the months and the total sum of visits for that month (not split by user) with this query;
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
通过上面的查询,我得到了这个结果:
With the query above I am getting this result:
现在我想知道如何为用户再添加一列并按用户拆分值.
Now I want to know how I can add one more column for user and split the values by user.
推荐答案
好的,两种解决方案看起来都不错.Ali 的答案有效,但我会改用 SUM() 函数,我讨厌 NULLS.让我们同时尝试一下,看看查询计划与执行时间的对比.
Okay, both solutions look good. The answer by Ali works but I would use a SUM() function instead, I hate NULLS. Let's try both and see the query plans versus execution times.
我总是用数据创建一个测试表,这样我就不会给用户 Aziale 错误的答案.
I always create a test table with data so that I do not give the user, Aziale, bad answers.
下面的代码不是最漂亮的,但它确实设置了一个测试用例.我在 tempdb 中创建了一个名为 user_visits 的数据库.对于每个月,我使用 for 循环添加用户并为他们提供该月的创建开始日期.
The code below is not the prettiest but it does set up a test case. I made a database in tempdb called user_visits. For each month, I used a for loop to add the users and give them the create start date for the month.
现在我们有了数据,我们可以玩了.
Now that we have data, we can play.
-- 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, uv_user_name) values ('20130101', 'Barbara'); if (@cnt <= 65) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130101', 'Danielle'); if (@cnt <= 103) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130101', '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, uv_user_name) values ('20130201', 'Barbara'); if (@cnt <= 55) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130201', 'Danielle'); if (@cnt <= 99) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130201', '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, uv_user_name) values ('20130301', 'Barbara'); if (@cnt <= 75) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130301', 'Danielle'); if (@cnt <= 98) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130301', '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, uv_user_name) values ('20130401', 'Barbara'); if (@cnt <= 60) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130401', 'Danielle'); if (@cnt <= 91) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130401', '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, uv_user_name) values ('20130501', 'Barbara'); if (@cnt <= 70) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130501', 'Danielle'); if (@cnt <= 120) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130501', '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, uv_user_name) values ('20130601', 'Barbara'); if (@cnt <= 62) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130601', 'Danielle'); if (@cnt <= 103) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130601', '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, uv_user_name) values ('20130701', 'Barbara'); if (@cnt <= 66) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130701', 'Danielle'); if (@cnt <= 99) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130701', '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, uv_user_name) values ('20130801', 'Barbara'); if (@cnt <= 71) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130801', 'Danielle'); if (@cnt <= 98) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130801', '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, uv_user_name) values ('20130901', 'Barbara'); if (@cnt <= 59) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130901', 'Danielle'); if (@cnt <= 91) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130901', '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, uv_user_name) values ('20131001', 'Barbara'); if (@cnt <= 73) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20131001', 'Danielle'); if (@cnt <= 120) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20131001', '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, uv_user_name) values ('20131101', 'Barbara'); if (@cnt <= 65) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20131101', 'Danielle'); if (@cnt <= 101) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20131101', '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, uv_user_name) values ('20131201', 'Barbara'); if (@cnt <= 61) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20131201', 'Danielle'); if (@cnt <= 90) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20131201', 'John'); set @cnt = @cnt + 1 end go
请不要在编码中使用保留字作为列名 - IE - 月份是保留字.
Please do not use reserve words in coding as column names - IE - month is a reserve word.
下面的代码给你正确的答案.
The code below gives you the correct answer.
-- Grab the data (1) select my_user, [1] AS January, [2] AS Febrary, [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(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], [2], [3], [4], [5],[6],[7],[8],[9],[10],[11],[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.
When doing this type of analysis, always clear the cache/buffers and get the 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, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 16 ms, elapsed time = 5 ms.
-- Solution 2 SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 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, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 16 ms, elapsed time = 5 ms.
两种解决方案具有相同的读取次数、工作表等.但是,SUM() 解决方案少了一个运算符.
Both solutions have the same number of reads, work table, etc. However, the SUM() solution has one less operator.
我要给两个回答赞的人+1!!
I am going to give both people who answered a thumbs up +1!!