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

MsSQL 前端之家
前端之家收集整理的这篇文章主要介绍了用于计算每月记录的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 !!

猜你在找的MsSQL相关文章

SQL Server2012高可用之日志传送测试
（一）日志传送架构 （1.1）相关服务器 主服务器 ：用于生产的服务器，上面运行这生产SQL ...
SQL server事物复制报错：要复制的 LOB 数据的长度(xxxxx)超出了配置的最大值 65536
(一)事故背景 最近在SQL Server 2012生产数据库上配置完事物复制（发布订阅）后，生产数据...
SQL Server2012高可用之事物复制(发布订阅)测试
（一）测试目的 目前公司使用的SQL SERVER 2012高可用环境为主备模式，其中主库可执行读写...
使用zabbix监控sql server的发布订阅
（一）背景个人在使用sql server时，用到了sql server的发布订阅来做主从同步，类似MySQL的...
SQL学习（四）集合运算
UNION和OR谓词 找出 product 和 product2 中售价高于 500 的商品的基本信息. select * fro...
SQL学习（五） 高级处理
5.1 请说出针对本章中使用的 product（商品）表执行如下 SELECT 语句所能得到的结果。 SEL...
SQL学习(一）初识数据库
datawhale组队学习SQL task01
SQL学习(二）基础查询与排序
datawhale数据库组队学习第二天打卡
SQL Server 2005 返回修改后的数据
今天在公司看到同事写了个SQL2005的新特性的文章，觉得很实用，在这里和大家分享下。原著作...
SQL Server 数据库最小宕机迁移方案
一、目的 在做SQL Server数据库维护的时候，当上司要求我们把几十G的数据文件搬动到其它服...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问