day220521hm

 列转行

 行转列

拉线    长按shift

 双击

 讲义上有详细步骤,,,,,,,,,,,

jndi,,原来添加的不用删除&＃xff0c;以后直接添加新代码是吧

 cv大法,百度,自己代码,

ctrl&＃43;f,

官方文档(英文),百度,谷歌, 

ON 表1.字段 &＃61; 表2.字段  

 ON a.month -1 &＃61; b.month

 前n行,null设置为default,

use interview;
-- 1.求每个月每个省份的店铺销售额
# (自己做不来,抄的)
SELECT
# 每个月month(order_datetime) AS &＃96;month&＃96;,
# 每个省份province_name,
# 店铺销售额 OVER(PARTITION BY store_id)SUM(quantity*unit_price) AS &＃96;product_sum&＃96;
# SUM(quantity*unit_price) OVER(PARTITION BY store_name)AS &＃96;store_sum&＃96;FROM dim_province p
# 逻辑外键关联
JOIN dim_city c
ON p.province_id &＃61; c.province_id
JOIN dim_store s
ON c.city_id&＃61;s.city_id
JOIN fact_order_detail f
ON f.store_id &＃61; s.store_id
GROUP BY month,province_name
ORDER BY month;-- 2. 每个月的每个产品销售额及其在当月的销售额占比
# (自己做不来,抄的)
SELECT
# 每个月month(order_datetime) AS &＃96;month&＃96;,
# 每个产品product_name,
# 每个产品销售额SUM( quantity* unit_price) AS &＃96;sum_product&＃96;,
# 当月的销售额占比sum(quantity* unit_price)/sum(SUM( quantity* unit_price)) OVER () AS &＃96;zhanbi&＃96;
FROM dim_product d
JOIN fact_order_detail fod
ON d.product_id &＃61; fod.product_id
GROUP BY product_name, month
ORDER BY month;-- 3.求每个月的销售额及其环比
WITH a AS(
SELECT
# 每个月month(order_datetime) AS &＃96;month&＃96;,
# 销售额SUM(quantity* unit_price) AS &＃96;Monthly_sales&＃96;,
# 本月销售额
# 上一月销售额LAG(SUM(quantity* unit_price)) OVER (ORDER BY month(order_datetime)) AS &＃96;last_month_sales&＃96;
FROM fact_order_detail
GROUP BY month)
SELECT*,(Monthly_sales-last_month_sales)/last_month_sales AS &＃96;sequential&＃96;
FROM a;SELECT
# 每个月month(order_datetime) AS &＃96;month&＃96;,
# 本月销售额SUM(quantity* unit_price) AS &＃96;Monthly_sales&＃96;,
# 上一月销售额LAG(SUM(quantity* unit_price)) OVER () AS &＃96;last_month_sales&＃96;,(SUM(quantity* unit_price)-LAG(SUM(quantity* unit_price))OVER ())/LAG(SUM(quantity* unit_price))OVER () AS &＃96;环比&＃96;
FROM fact_order_detail
GROUP BY month;-- 4.求新增用户量及其留存率
SELECT
# 总共用户量
COUNT(DISTINCT user_id)
# 新增用户量
# LAG(COUNT(user_id))OVER()
# 留存用户量
# 留存率
# COUNT(user_id)OVER(ORDER BY MONTH(order_datetime) ROWS 1 PRECEDING)
FROM fact_order_detail
GROUP BY month(order_datetime);

(1)SELECT
QUANLITY *UNIT_PRICE AS &＃96;单个订单销售额&＃96;
FROM FACT_ORDER_DETAIL
JOIN DIM_STORE
ON STORE_ID
JOIN DIM_CITY
ON CITY_ID
JOIN DIM_PROVINCE
ON PROVINCE_ID
GROUP BY (F(X...)-ORDER_DATATIME)//30,PRIVICE_NAME

(2)QUANLITY *UNIT_PRICE 订单表,产品表,
单个产品销售额/当月销售额(1题) AS &＃96;占比&＃96;(3)多个产品*QUANLITY *UNIT_PRICE 每个月 AS&＃96;每月销售额&＃96;
FROM 产品表,订单表,店铺表
(每月销售额-LAG(每月销售额))/LAG(每月销售额)(4)新增用户 count() 用户表,订单表 上月无 本月有
留存用户 count() 用户表,订单表 上月有 本月有
留存率&＃61;留存用户/留存用户-新增用户

笔试题:手写,???

先把表关系清楚

staruml

 从哪里看这个表一个订单只有一个产品呀

 订单基本信息表

 订单详细信息表