問(wèn)題描述

我有一個(gè)數(shù)據(jù)集，需要為特定用戶的每月訪問(wèn)次數(shù)構(gòu)建.我有一個(gè)包含以下字段的 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)
• 日期訪問(wèn)日期時(shí)間

• User nvarchar(30)
• DateVisit datetime

我現(xiàn)在想要實(shí)現(xiàn)的是將每個(gè)用戶的所有訪問(wèn)按月分組，如圖所示:

What I want to achieve now is to get all the visits grouped by month for each user, something like at the picture:

我開(kāi)始查詢，我可以通過(guò)這個(gè)查詢獲得月份和該月的總訪問(wèn)量(不按用戶拆分)；

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

通過(guò)上面的查詢，我得到了這個(gè)結(jié)果:

With the query above I am getting this result:

現(xiàn)在我想知道如何為用戶再添加一列并按用戶拆分值.

Now I want to know how I can add one more column for user and split the values by user.

推薦答案

好的，兩種解決方案看起來(lái)都不錯(cuò).Ali 的答案有效，但我會(huì)改用 SUM() 函數(shù)，我討厭 NULLS.讓我們同時(shí)嘗試一下，看看查詢計(jì)劃與執(zhí)行時(shí)間的對(duì)比.

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.

我總是用數(shù)據(jù)創(chuàng)建一個(gè)測(cè)試表，這樣我就不會(huì)給用戶 Aziale 錯(cuò)誤的答案.

I always create a test table with data so that I do not give the user, Aziale, bad answers.

下面的代碼不是最漂亮的，但它確實(shí)設(shè)置了一個(gè)測(cè)試用例.我在 tempdb 中創(chuàng)建了一個(gè)名為 user_visits 的數(shù)據(jù)庫(kù).對(duì)于每個(gè)月，我使用 for 循環(huán)添加用戶并為他們提供該月的創(chuàng)建開(kāi)始日期.

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.

現(xiàn)在我們有了數(shù)據(jù)，我們可以玩了.

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

請(qǐng)不要在編碼中使用保留字作為列名 - 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

進(jìn)行此類分析時(shí)，請(qǐng)始終清除緩存/緩沖區(qū)并獲取 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.

兩種解決方案具有相同的讀取次數(shù)、工作表等.但是，SUM() 解決方案少了一個(gè)運(yùn)算符.

Both solutions have the same number of reads, work table, etc. However, the SUM() solution has one less operator.

我要給兩個(gè)回答贊的人+1??！

I am going to give both people who answered a thumbs up +1!!

這篇關(guān)于用于計(jì)算每月記錄的 SQL 查詢的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！