刚学数据库,不太清楚这个错误该怎么改
第一行为什么无法创建,那个on有什么问题吗
【相关推荐】
-- 按照商品种类和行为类型统计pv和uv
SELECT COUNT(*) AS pv, COUNT(DISTINCT UserID) AS uv
FROM user_behavior10
GROUP BY CategoryID, BehaviorType
WITH cat_stats AS(
SELECT CategoryID, BehaviorType, COUNT(*) AS pv, COUNT(DISTINCT UserID) AS uv
FROM user_behavior10
GROUP BY CategoryID, BehaviorType);
-- 将一维表转换成二维表,统计各个商品种类的不同行为下的pv总数
CREATE TABLE cat_stats2 AS(
SELECT CategoryID,
SUM(IFNULL(CASE WHEN BehaviorType = "pv" THEN pv ELSE NULL END,0)) AS "pv_cnts",
SUM(IFNULL(CASE WHEN BehaviorType = "fav" OR BehaviorType = "cart" THEN pv ELSE NULL END,0)) AS "cart_cnts",
SUM(IFNULL(CASE WHEN BehaviorType = "buy" THEN pv ELSE NULL END,0)) AS "buy_cnts"
FROM cat_stats
GROUP BY CategoryID);
通过上图可以看出,不同种类商品在购买量上存在较大的差异,整体数据分布呈现幂律化分布,其中8%的商品种类贡献超过80%的购买量,而92%的商品种类的累计购买量仅占总购买量的20%;