MySQL 获取今日每个时段的数量统计(24格式)

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/shenxiandashu/article/details/81102504

注意时间为毫秒的时间戳。

    SELECT
                case
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 0 then '0'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 1 then '1'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 2 then '2'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 3 then '3'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 4 then '4'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 5 then '5'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 6 then '6'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 7 then '7'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 8 then '8'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 9 then '9'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 10 then '10'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 11 then '11'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 12 then '12'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 13 then '13'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 14 then '14'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 15 then '15'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 16 then '16'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 17 then '17'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 18 then '18'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 19 then '19'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 20 then '20'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 21 then '21'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 22 then '22'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 23 then '23'
                end as name, count(id) as total
            FROM goods_t as g WHERE (DAY(FROM_UNIXTIME(g.time_s/1000, '%Y-%m-%d')) = DAY(now())) and g.consume = 1 group by
                case
                  when FROM_UNIXTIME(wc.time_s/1000, '%H') = 0 then '0'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 1 then '1'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 2 then '2'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 3 then '3'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 4 then '4'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 5 then '5'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 6 then '6'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 7 then '7'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 8 then '8'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 9 then '9'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 10 then '10'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 11 then '11'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 12 then '12'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 13 then '13'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 14 then '14'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 15 then '15'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 16 then '16'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 17 then '17'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 18 then '18'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 19 then '19'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 20 then '20'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 21 then '21'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 22 then '22'
                    when FROM_UNIXTIME(wc.time_s/1000, '%H') = 23 then '23'
                end
            order by null;
展开阅读全文

SQL 按时段统计

11-24

rn我要按照这种模式来统计,其基本格式是:rn[img=http://img.my.csdn.net/uploads/201211/24/1353740590_9076.jpg][/img]rn目前 我打算传2个参数:输入一个起始时间 和一个终止时间rn比如rn:exec '2012-11-24 00:00:00','2012-11-24 00:59:59'rn这样只会查询出一条结果,所以不符合上面的结果 想修改一下 ,没有具体的思路 问问大家怎么修改好rnrncreate procedure proc_hour(rn@sBeginDate varchar(18),--2012 11-11 00:00:00rn@sEndDate varchar(18)rn)rnASrndeclare @personviews int ,@orderuserviews int,@userviews int ,@singleorder int ,rn @monthorder int,@playall int,@playuser int,rn @freeplay int ,@kokplay int,@mvplay int,rn @zxychplay int ,@hjychplay int,@unsubscribe intrnrn--访问人数rnselect @personviews = count(distinct userid ) from userlog where accesstime between @sBeginDate and @sEndDate;rn--订购用户访问(这里除去免费的点播数用户就是订购用户的访问)rnselect @orderuserviews = count(*) from userlog where userid in(select userid from usercharge where fee <> '' ) and accesstime between @sBeginDate and @sEndDate;rn--用户总访问量rnselect @userviews = count(*) from userlog where accesstime between @sBeginDate and @sEndDate;rn--单点新增订购数(包含MV 和卡拉OK单点 1元)rnselect @singleorder =count(*) from usercharge where fee ='1000000074' and orderdesc like '%1元%' and accesstime between @sBeginDate and @sEndDate;rn--包月新增订购数(MV和卡拉OK包月新增用户)rnselect @monthorder = count(*) from usercharge where fee ='1000000074' and orderdesc like '%计费成功%' and accesstime between @sBeginDate and @sEndDate;rn--总点播次数(包含随便听听)rnselect @playall = count(*) from userplaysongdata where accesstime between @sBeginDate and @sEndDate;rn--点播用户数rnselect @playuser = count(distinct userid) from userplaysongdata where accesstime between @sBeginDate and @sEndDate;rn--随便点播次数(免费)rnselect @freeplay = count(*) from usercharge where fee ='1000000074' and accesstime between @sBeginDate and @sEndDate;rn--卡拉OK点播次数rnselect @kokplay =COUNT(1) FROM userplaysongdata arn INNER JOIN songlistdata b ON a.sourcefilename = b.sourcefilename where accesstime between @sBeginDate and @sEndDatern and b.videotype = 'xcjc' GROUP BY videotypern--MV点播次数rnselect @mvplay = COUNT(1) FROM userplaysongdata arn INNER JOIN songlistdata b ON a.sourcefilename = b.sourcefilename where accesstime between @sBeginDate and @sEndDatern and b.videotype = 'mvdg' GROUP BY videotypern--怀旧演唱会点播次数rnselect @hjychplay =COUNT(1) FROM userplaysongdata arn INNER JOIN songlistdata b ON a.sourcefilename = b.sourcefilename where accesstime between @sBeginDate and @sEndDatern and b.videotype = 'hjych' GROUP BY videotypern--最新演唱会点播次数rnselect @zxychplay =COUNT(1) FROM userplaysongdata arn INNER JOIN songlistdata b ON a.sourcefilename = b.sourcefilename where accesstime between @sBeginDate and @sEndDatern and b.videotype = 'zxych' GROUP BY videotypernrnselect @sBeginDate 时间,@personviews 访问人数 ,@userviews 用户总访问量,@orderuserviews 订购用户访问量 ,rn @playuser 点播用户数,@playall 总点播次数 ,@kokplay 卡拉OK点播次数,@mvplay mv点播次数 ,rn @zxychplay 最新演唱会点播次数 ,@hjychplay 怀旧演唱会点播次数, @freeplay 免费点播次数 ,rn @singleorder 单点新增订购数 ,@monthorder 包月新增订购数rn 论坛

没有更多推荐了,返回首页