【学习笔记】-- 不同时段的登陆用户数：按小时统计 select hour(lastlogin_ne ...

是 否 +2 论坛币

k人 参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

送您一个全额奖学金名额~ !

立即领取

感谢您参与论坛问题回答

经管之家送您两个论坛币！

+2 论坛币

-- 不同时段的登陆用户数：按小时统计
select hour(lastlogin_new) as 时段,count(userid) as 用户数
from userinfo
group by hour(lastlogin_new)
order by hour(lastlogin_new);

-- 不同时段的累计销售额：按小时统计
alter table OrderInfo add AddTime_new datetime;
update OrderInfo set AddTime_new=from_unixtime(AddTime);

#先统计每个小时的订单总金额，然后开窗对每个小时的订单金额进行累计计算
select hour(AddTime_new) as 时段,round(sum(otherpay)) as 订单总金额,round(sum(sum(otherpay)) over (order by hour(AddTime_new) asc)) as 累计销售额
from OrderInfo
group by hour(AddTime_new)
order by hour(AddTime_new);

-- 各省市消费金额：省份名称、城市名称的订单金额（otherpay）
select * from OrderInfo;
select * from RegionInfo;

select a1.regionname 省份,a2.regionname 城市,sum(otherpay) 消费金额
from OrderInfo
left join RegionInfo as a1 on province=a1.regionid
left join RegionInfo as a2 on city=a2.regionid
group by province,city
order by a1.regionname
;

-- 不同支付方式的订单量
select PayTool,count(OrderID) 订单量
from OrderInfo
group by PayTool
;

-- 哪种支付方式可能导致用户支付不成功而取消订单
select PayTool,count(OrderID) 订单量
from OrderInfo
where OrderState=3 and PayState=0
group by PayTool
;
select PayTool,count(OrderID) 订单量 ,sum(OrderState=3 and PayState=0) 支付不成功取消,sum(OrderState=3 and PayState=0)/count(OrderID)*100 不成功率
from OrderInfo
group by PayTool
;
select PayTool,count(OrderID) 订单量 ,sum(OrderState=3 and PayState=0)/count(OrderState=3 and PayState=0) 不成功率
from OrderInfo
group by PayTool
;
#使用avg的方式的前提应该是需要字段里的值只有0和1这两个值
select PayTool,count(OrderID) 订单量 ,avg(OrderState=3 and PayState=0) 支付不成功率
from OrderInfo
group by PayTool
;
-- 不同品牌的总销量
select typeid,brandtype,sum(amount) 总销量
from OrderDetail as a
left join GoodsInfo as b  on a.GoodsID=b.GoodsID
left join GoodsBrand as c on b.typeid=c.SupplierID
group by typeid
;

-- 不同品牌的复购用户数
#不同品牌下每个用户的购买次数
select brandtype,userid,count(distinct orderid) 购买次数
from orderdetail
left join goodsinfo on orderdetail.goodsid=goodsinfo.goodsid
left join goodsbrand on supplierid=typeid
group by typeid,userid;

#不同品牌的复购用户，购买次数大于1：having count(distinct orderid)>1
select brandtype,userid 复购用户,count(distinct orderid) 购买次数
from orderdetail
left join goodsinfo on orderdetail.goodsid=goodsinfo.goodsid
left join goodsbrand on supplierid=typeid
group by typeid,userid
having count(distinct orderid)>1;

#不同品牌的复购用户数
select brandtype,count(复购用户) 复购用户数
from (
select brandtype,userid,userid 复购用户,count(distinct orderid) 购买次数
from OrderDetail as a
left join GoodsInfo as b  on a.GoodsID=b.GoodsID
left join GoodsBrand as c on b.typeid=c.SupplierID
group by typeid,userid
having count(distinct orderid)>1
) as a1
group by brandtype;

扫码加我 拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

分享0 收藏0 回帖

关键词：Select login Elect hour 学习笔记