用户行为统计
按照日 月 用户同类行为计数
sqlserver
SELECT COUNT(DISTINCT(uid)) ,convert(varchar, dateandtime, 112) FROM user_action GROUP BY convert(varchar, dateandtime, 112) ORDER BY convert(varchar, dateandtime, 112) DESC;
SELECT COUNT(DISTINCT(uid)) ,LEFT(convert(varchar, dateandtime, 112),6) FROM user_action GROUP BY LEFT(convert(varchar, dateandtime, 112),6) ORDER BY LEFT(convert(varchar, dateandtime, 112),6) DESC;
4621462.1 20180508
4166416.6 20180507
1978197.8 20180506
3056305.6 20180505
4474447.4 20180504
4309430.9 20180503
3720372.0 20180502
1304130.4 20180501
1572157.2 20180430
2110211.0 20180429
4045404.5 20180428
字符串截取
SELECT COUNT(DISTINCT(uid)) ,convert(varchar, dateandtime, 112) FROM user_action GROUP BY convert(varchar, dateandtime, 112) ORDER BY convert(varchar, dateandtime, 112) DESC;
SELECT COUNT(DISTINCT(uid)) ,LEFT(convert(varchar, dateandtime, 112),6) FROM user_action GROUP BY LEFT(convert(varchar, dateandtime, 112),6) ORDER BY LEFT(convert(varchar, dateandtime, 112),6) DESC;
6.9762 201805 8.7307 201804 8.4821 201803 6.6759 201802 8.4029 201801 8.4546 201712 8.6647 201711 8.5712 201710 8.9540 201709 9.4039 201708 9.8076 201707 9.8978 201706 9.8670 201705 9.4897 201704 9.7966 201703 8.9595 201702 8.5976 201701 9.4413 201612 9.3456 201611 9.0409 201610 9.3159 201609 9.2169 201608 9.0145 201607 8.8957 201606 8.7417 201605 8.3050 201604 8.2214 201603 6.4372 201602 7.2193 201601 7.7011 201512 7.6978 201511 7.2886 201510 7.4470 201509 7.4580 201508 7.3986 201507 7.0840 201506 6.8706 201505 6.5945 201504 6.2040 201503 4.4495 201502 5.9895 201501 5.8762 201412 5.6034 201411 5.4263 201410 5.3284 201409 5.2272 201408 5.2173 201407 4.9588 201406 4.8983 201405 4.7498 201404 4.4759 201403 3.7609 201402 3.7554 201401 4.3142 201312 4.2526 201311 3.9743 201310 4.0942 201309 4.1833 201308 4.1635 201307 4.0865 201306 4.0337 201305 3.6872 201304 3.4705 201303 2.3408 201302 3.0327 201301 2.9821 201212 2.9557 201211 2.7522 201210 2.7599 201209 2.7423 201208 2.5168 201207 2.1868 201206 2.1208 201205 2.0845 201204 1.9723 201203 1.6775 201202 1.2419 201201 1.7754 201112 1.9008 201111 1.6093 201110 1.5510 201109 1.5763 201108 1.4509 201107 1.4245 201106 1.3409 201105 1.2430 201104 1.1990 201103 .8789 201102 .9317 201101 1.1352 201012 1.0274 201011 .9317 201010 .9295 201009 .9108 201008 .8085 201007 .6919 201006 .6149 201005 .5654 201004 .5181 201003 .2772 201002 .5082 201001 .4763 200912 .5313 200911 .4279 200910 .5005 200909 .4169 200908 .0022 200808 .0011 200301 .0022 200201 .0011
