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

前端之家收集整理的这篇文章主要介绍了用于计算每月记录的SQL查询前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我有这个数据集,我需要为特定用户每月的访问次数建立.
我有一个包含以下字段的sql表:

>用户nvarchar(30)
> DateVisit日期时间

我现在想要实现的是按月为每个用户分组所有访问,如图所示:

我启动了查询,我能够通过此查询获取该月的月份和访问总数(不是由用户拆分);

  1. 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
  2. from
  3. (
  4. SELECT MONTH(DateVisit) AS month,[User] FROM UserVisit
  5. ) AS t
  6. PIVOT (
  7. COUNT([User])
  8. FOR month IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
  9. ) p

通过上面的查询,我得到了这个结果:

现在我想知道如何为用户添加一个列并按用户分割值.
我知道它的星期五,但如果有人可以帮助我,我将不胜感激.
提前谢谢,Laziale

解决方法

好的,两种解决方案都很好看. Ali的答案有效,但我会使用SUM()函数,我讨厌NULLS.让我们尝试两者,看看查询计划与执行时间.

我总是用数据创建一个测试表,这样我就不会给用户Aziale一个错误的答案.

下面的代码不是最漂亮的,但确实设置了一个测试用例.我在tempdb中创建了一个名为user_visits的数据库.对于每个月,我使用for循环来添加用户并为他们提供月份的创建开始日期.

现在我们有数据,我们可以玩.

  1. -- Drop the table
  2. drop table tempdb.dbo.user_visits
  3. go
  4.  
  5. -- Create the table
  6. create table tempdb.dbo.user_visits
  7. (
  8. uv_id int identity(1,1),uv_visit_date smalldatetime,uv_user_name varchar(30)
  9. );
  10. go
  11.  
  12. -- January data
  13. declare @cnt int = 1;
  14. while @cnt <= 103
  15. begin
  16. if (@cnt <= 21)
  17. insert into tempdb.dbo.user_visits
  18. (uv_visit_date,uv_user_name)
  19. values ('20130101','Patrick');
  20.  
  21. if (@cnt <= 44)
  22. insert into tempdb.dbo.user_visits
  23. (uv_visit_date,'Barbara');
  24.  
  25. if (@cnt <= 65)
  26. insert into tempdb.dbo.user_visits
  27. (uv_visit_date,'Danielle');
  28.  
  29. if (@cnt <= 103)
  30. insert into tempdb.dbo.user_visits
  31. (uv_visit_date,'John');
  32.  
  33. set @cnt = @cnt + 1
  34. end
  35. go
  36.  
  37. -- February data
  38. declare @cnt int = 1;
  39. while @cnt <= 99
  40. begin
  41. if (@cnt <= 29)
  42. insert into tempdb.dbo.user_visits
  43. (uv_visit_date,uv_user_name)
  44. values ('20130201','Patrick');
  45.  
  46. if (@cnt <= 42)
  47. insert into tempdb.dbo.user_visits
  48. (uv_visit_date,'Barbara');
  49.  
  50. if (@cnt <= 55)
  51. insert into tempdb.dbo.user_visits
  52. (uv_visit_date,'Danielle');
  53.  
  54. if (@cnt <= 99)
  55. insert into tempdb.dbo.user_visits
  56. (uv_visit_date,'John');
  57.  
  58. set @cnt = @cnt + 1
  59. end
  60. go
  61.  
  62. -- March data
  63. declare @cnt int = 1;
  64. while @cnt <= 98
  65. begin
  66. if (@cnt <= 25)
  67. insert into tempdb.dbo.user_visits
  68. (uv_visit_date,uv_user_name)
  69. values ('20130301','Patrick');
  70.  
  71. if (@cnt <= 46)
  72. insert into tempdb.dbo.user_visits
  73. (uv_visit_date,'Barbara');
  74.  
  75. if (@cnt <= 75)
  76. insert into tempdb.dbo.user_visits
  77. (uv_visit_date,'Danielle');
  78.  
  79. if (@cnt <= 98)
  80. insert into tempdb.dbo.user_visits
  81. (uv_visit_date,'John');
  82.  
  83. set @cnt = @cnt + 1
  84. end
  85. go
  86.  
  87. -- April data
  88. declare @cnt int = 1;
  89. while @cnt <= 91
  90. begin
  91. if (@cnt <= 32)
  92. insert into tempdb.dbo.user_visits
  93. (uv_visit_date,uv_user_name)
  94. values ('20130401','Patrick');
  95.  
  96. if (@cnt <= 48)
  97. insert into tempdb.dbo.user_visits
  98. (uv_visit_date,'Barbara');
  99.  
  100. if (@cnt <= 60)
  101. insert into tempdb.dbo.user_visits
  102. (uv_visit_date,'Danielle');
  103.  
  104. if (@cnt <= 91)
  105. insert into tempdb.dbo.user_visits
  106. (uv_visit_date,'John');
  107.  
  108. set @cnt = @cnt + 1
  109. end
  110. go
  111.  
  112. -- May data
  113. declare @cnt int = 1;
  114. while @cnt <= 120
  115. begin
  116. if (@cnt <= 40)
  117. insert into tempdb.dbo.user_visits
  118. (uv_visit_date,uv_user_name)
  119. values ('20130501','Patrick');
  120.  
  121. if (@cnt <= 41)
  122. insert into tempdb.dbo.user_visits
  123. (uv_visit_date,'Barbara');
  124.  
  125. if (@cnt <= 70)
  126. insert into tempdb.dbo.user_visits
  127. (uv_visit_date,'Danielle');
  128.  
  129. if (@cnt <= 120)
  130. insert into tempdb.dbo.user_visits
  131. (uv_visit_date,'John');
  132.  
  133. set @cnt = @cnt + 1
  134. end
  135. go
  136.  
  137. -- June data
  138. declare @cnt int = 1;
  139. while @cnt <= 103
  140. begin
  141. if (@cnt <= 17)
  142. insert into tempdb.dbo.user_visits
  143. (uv_visit_date,uv_user_name)
  144. values ('20130601','Patrick');
  145.  
  146. if (@cnt <= 45)
  147. insert into tempdb.dbo.user_visits
  148. (uv_visit_date,'Barbara');
  149.  
  150. if (@cnt <= 62)
  151. insert into tempdb.dbo.user_visits
  152. (uv_visit_date,'John');
  153.  
  154. set @cnt = @cnt + 1
  155. end
  156. go
  157.  
  158. -- July data
  159. declare @cnt int = 1;
  160. while @cnt <= 99
  161. begin
  162. if (@cnt <= 20)
  163. insert into tempdb.dbo.user_visits
  164. (uv_visit_date,uv_user_name)
  165. values ('20130701','Patrick');
  166.  
  167. if (@cnt <= 43)
  168. insert into tempdb.dbo.user_visits
  169. (uv_visit_date,'Barbara');
  170.  
  171. if (@cnt <= 66)
  172. insert into tempdb.dbo.user_visits
  173. (uv_visit_date,'John');
  174.  
  175. set @cnt = @cnt + 1
  176. end
  177. go
  178.  
  179. -- August data
  180. declare @cnt int = 1;
  181. while @cnt <= 98
  182. begin
  183. if (@cnt <= 26)
  184. insert into tempdb.dbo.user_visits
  185. (uv_visit_date,uv_user_name)
  186. values ('20130801','Patrick');
  187.  
  188. if (@cnt <= 47)
  189. insert into tempdb.dbo.user_visits
  190. (uv_visit_date,'Barbara');
  191.  
  192. if (@cnt <= 71)
  193. insert into tempdb.dbo.user_visits
  194. (uv_visit_date,'John');
  195.  
  196. set @cnt = @cnt + 1
  197. end
  198. go
  199.  
  200. -- September data
  201. declare @cnt int = 1;
  202. while @cnt <= 91
  203. begin
  204. if (@cnt <= 25)
  205. insert into tempdb.dbo.user_visits
  206. (uv_visit_date,uv_user_name)
  207. values ('20130901','Patrick');
  208.  
  209. if (@cnt <= 49)
  210. insert into tempdb.dbo.user_visits
  211. (uv_visit_date,'Barbara');
  212.  
  213. if (@cnt <= 59)
  214. insert into tempdb.dbo.user_visits
  215. (uv_visit_date,'John');
  216.  
  217. set @cnt = @cnt + 1
  218. end
  219. go
  220.  
  221. -- October data
  222. declare @cnt int = 1;
  223. while @cnt <= 120
  224. begin
  225. if (@cnt <= 25)
  226. insert into tempdb.dbo.user_visits
  227. (uv_visit_date,uv_user_name)
  228. values ('20131001','Patrick');
  229.  
  230. if (@cnt <= 40)
  231. insert into tempdb.dbo.user_visits
  232. (uv_visit_date,'Barbara');
  233.  
  234. if (@cnt <= 73)
  235. insert into tempdb.dbo.user_visits
  236. (uv_visit_date,'John');
  237.  
  238. set @cnt = @cnt + 1
  239. end
  240. go
  241.  
  242. -- November data
  243. declare @cnt int = 1;
  244. while @cnt <= 101
  245. begin
  246. if (@cnt <= 32)
  247. insert into tempdb.dbo.user_visits
  248. (uv_visit_date,uv_user_name)
  249. values ('20131101','Patrick');
  250.  
  251. if (@cnt <= 50)
  252. insert into tempdb.dbo.user_visits
  253. (uv_visit_date,'Danielle');
  254.  
  255. if (@cnt <= 101)
  256. insert into tempdb.dbo.user_visits
  257. (uv_visit_date,'John');
  258.  
  259. set @cnt = @cnt + 1
  260. end
  261. go
  262.  
  263. -- December data
  264. declare @cnt int = 1;
  265. while @cnt <= 90
  266. begin
  267. if (@cnt <= 40)
  268. insert into tempdb.dbo.user_visits
  269. (uv_visit_date,uv_user_name)
  270. values ('20131201','Patrick');
  271.  
  272. if (@cnt <= 52)
  273. insert into tempdb.dbo.user_visits
  274. (uv_visit_date,'Barbara');
  275.  
  276. if (@cnt <= 61)
  277. insert into tempdb.dbo.user_visits
  278. (uv_visit_date,'Danielle');
  279.  
  280. if (@cnt <= 90)
  281. insert into tempdb.dbo.user_visits
  282. (uv_visit_date,'John');
  283.  
  284. set @cnt = @cnt + 1
  285. end
  286. go

请不要在编码中使用保留字作为列名 – IE – 月是保留字.

下面的代码为您提供了正确的答案.

  1. -- Grab the data (1)
  2. select
  3. my_user,[1] AS January,[2] AS Febrary,[12] AS December
  4. from
  5. (
  6. SELECT MONTH(uv_visit_date) AS my_month,uv_user_name as my_user FROM tempdb.dbo.user_visits
  7. ) AS t
  8. PIVOT (
  9. COUNT(my_month)
  10. FOR my_month IN([1],[12])
  11. ) as p
  1. -- Grab the data (2)
  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
  3. FROM tempdb.dbo.user_visits
  4. GROUP BY uv_user_name

进行此类分析时,请始终清除缓存/缓冲区并获取I / O.

  1. -- Show time & i/o
  2. SET STATISTICS TIME ON
  3. SET STATISTICS IO ON
  4. GO
  5.  
  6. -- Remove clean buffers & clear plan cache
  7. CHECKPOINT
  8. DBCC DROPCLEANBUFFERS
  9. DBCC FREEPROCCACHE
  10. GO
  11.  
  12.  
  13. -- Solution 1
  14. sql Server parse and compile time:
  15. cpu time = 0 ms,elapsed time = 42 ms.
  16.  
  17. (4 row(s) affected)
  18. 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.
  19. Table 'user_visits'. Scan count 1,logical reads 11,lob read-ahead reads 0.
  20.  
  21. sql Server Execution Times:
  22. cpu time = 16 ms,elapsed time = 5 ms.
  1. -- Solution 2
  2. sql Server parse and compile time:
  3. cpu time = 0 ms,elapsed time = 0 ms.
  4.  
  5. (4 row(s) affected)
  6. Table 'Worktable'. Scan count 0,elapsed time = 5 ms.

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

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

猜你在找的MsSQL相关文章