用于计算每月记录的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.

-- 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,elapsed time = 5 ms.

两种解决方案都具有相同数量的读取,工作表等.但是,SUM()解决方案只需少一个运算符.

我要给两个回答竖起大拇指的人1 !!

原文链接：https://www.f2er.com/mssql/77083.html

用于计算每月记录的SQL查询

解决方法

猜你在找的MsSQL相关文章