電商數(shù)據(jù)實例

原始數(shù)據(jù)中時間相關(guān)字段已時間戳格式進行存儲時，需要操作如下：

1.添加新字段用來存儲時間格式字段。

alter table [表名] add [字段名] [數(shù)據(jù)類型];

2.時間戳轉(zhuǎn)化成時間格式，更新新字段數(shù)據(jù)。

update [表名] set [新字段] = from_unixtime([要轉(zhuǎn)換字段]);

不同時段的累計銷售額(聚合函數(shù)進行開窗)

select

hour(addtime_new) as 時段,

sum(OrderAmount) as 銷售額,

sum(sum(OrderAmount)) over(order by hour(addtime_new)) as 累計銷售額

from orderinfo

group by hour(addtime_new);

-- 哪種支付方式可能導(dǎo)致用戶支付不成功而取消訂單 （利用邏輯判斷來計算）

select

PayTool,

sum(OrderState=3 and PayState=0) as 未支付而取消的訂單數(shù),

count(OrderState=3 and PayState=0) as 訂單數(shù),

avg(OrderState=3 and PayState=0) as 未支付而取消的訂單占比

from orderinfo

group by PayTool;

-- 不同品牌的總銷量（判斷按哪個表字段進行分組）

品牌數(shù)量不一致，按品牌主表進行分組

select count(SupplierID) from goodsbrand;-- 64

select count(goodsid) from goodsinfo;-- 10000

select count(distinct typeid) from goodsinfo;-- 69

select typeid,brandtype,sum(amount) 總銷量

from orderdetail

left join goodsinfo on orderdetail.goodsid=goodsinfo.goodsid

left join goodsbrand on typeid=SupplierID

group by typeid;

SQL計算復(fù)購率

https://blog.csdn.net/kejiayuan0806/article/details/106410139