本文介紹了用于計算每月記錄的 SQL 查詢的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學習吧！
問題描述
我有一個數據集，需要為特定用戶的每月訪問次數構建.我有一個包含以下字段的 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!!
這篇關于用于計算每月記錄的 SQL 查詢的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！
【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題，如果有圖片或者內容侵犯了您的權益，請聯系我們刪除處理，感謝您的支持！