1倘感、業(yè)務(wù)背景
Adventure Works Cycle是國(guó)內(nèi)一家制造公司留特，該公司生產(chǎn)和銷售金屬和復(fù)合材料自行車在全國(guó)各個(gè)市場(chǎng)。銷售方式主要有兩種孩哑，前期主要是分銷商模式栓霜，但是2018年公司實(shí)現(xiàn)財(cái)政收入目標(biāo)后，2019就開始通過(guò)公司自有網(wǎng)站獲取線上商戶進(jìn)一步擴(kuò)大市場(chǎng)横蜒。
2胳蛮、業(yè)務(wù)需求
2019年12月5日線上業(yè)務(wù)經(jīng)理，需要向公司CEO匯報(bào)2019年11月自行車銷售情況丛晌，所以數(shù)據(jù)部門要提供11月份線上自行業(yè)務(wù)數(shù)據(jù)分析報(bào)告仅炊。
2.1 業(yè)務(wù)分析指標(biāo)
- 從整體的角度：自行車整體銷售表現(xiàn)
- 從地域的角度：11月每個(gè)區(qū)域銷售量表現(xiàn)、11月TOP10城市銷售量表現(xiàn)
- 從產(chǎn)品的角度：11月類別產(chǎn)品銷售量表現(xiàn)澎蛛、11月細(xì)分產(chǎn)品銷售量表現(xiàn)
- 熱銷產(chǎn)品：11月TOP10產(chǎn)品銷量榜抚垄、11月TOP10銷量增速榜
- 用戶的角度：11月用戶年齡分布及每個(gè)年齡段產(chǎn)品購(gòu)買喜好、11月男女用戶分布以及男女產(chǎn)品購(gòu)買喜好
2.2 具體流程
3谋逻、數(shù)據(jù)分析
利用Python結(jié)合pandas和numpy從整體呆馁、地域、產(chǎn)品毁兆、熱銷產(chǎn)品和用戶5個(gè)角度來(lái)計(jì)算每一塊的業(yè)務(wù)指標(biāo)智哀，部分代碼如下
自行車整體銷售量表現(xiàn)部分：
自行車區(qū)域銷售量變現(xiàn)：
部分powerbi畫圖如下：
最終的分析報(bào)告見：
鏈接：https://pan.baidu.com/s/1_nHIZO8kuPuhENiCvsQjjA
提取碼：3txr
4、面對(duì)大數(shù)量的業(yè)務(wù)時(shí)怎么辦
隨著公司數(shù)據(jù)量增大荧恍，利用Python處理數(shù)據(jù)會(huì)變的很困難瓷叫，考慮使用hive來(lái)進(jìn)行數(shù)據(jù)聚合的操作。具體的流程如下
- 通過(guò)sqoop將數(shù)據(jù)導(dǎo)入hive數(shù)據(jù)庫(kù)
以導(dǎo)入訂單明細(xì)表為例sqoop_ods_sales_orders.sh文件如下送巡，在Linux服務(wù)器上執(zhí)行該文件摹菠。
hive -e "truncate table ods.ods_sales_orders"
sqoop import \
--hive-import \
--connect "jdbc:mysql://ip:port/adventure_ods?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&dontTrackOpenResources=true&defaultFetchSize=50000&useCursorFetch=true" \
--driver com.mysql.jdbc.Driver \
--username username \
--password password \
--query \
"select * from ods_sales_orders where "'$CONDITIONS'" " \
--fetch-size 50000 \
--hive-table ods.ods_sales_orders \
--hive-drop-import-delims \
--delete-target-dir \
--target-dir /user/hadoop/sqoop/ods_sales_orders \
-m 1
-
使用hive進(jìn)行數(shù)據(jù)處理
涉及訂單明細(xì)表ods_sales_orders和用戶表ods_customer，表結(jié)構(gòu)如下
訂單明細(xì)表.PNG
每日新增用戶表.PNG
計(jì)算了如下指標(biāo)：
用戶的回購(gòu)率和復(fù)購(gòu)率
-- 復(fù)購(gòu)率
SELECT
umonth
, COUNT(1) as customes_cons
, SUM(if(ct > 1, 1, 0)) / COUNT(1) AS fgl
FROM (
SELECT
customer_key,
date_format(create_date,'YYYY-MM') as umonth,
COUNT(customer_key) AS ct
FROM ods_sales_orders
GROUP BY customer_key,date_format(create_date,'YYYY-MM')
) t
group by umonth;
-- 回購(gòu)率
SELECT
a.umonth,
concat(round(COUNT(b.customer_key) / COUNT(a.customer_key) * 100, 2), '%') AS hgl
FROM (
SELECT
customer_key,
date_format(create_date,'YYYY-MM') AS umonth
FROM ods_sales_orders
GROUP BY customer_key, date_format(create_date,'YYYY-MM')
) a
left JOIN (
SELECT
customer_key,
date_format(create_date,'YYYY-MM') AS umonth
FROM ods_sales_orders
GROUP BY customer_key, date_format(create_date,'YYYY-MM')
) b
ON a.customer_key = b.customer_key
AND concat(a.umonth,'-01') = add_months(concat(b.umonth,'-01'),-1)
GROUP BY a.umonth;
統(tǒng)計(jì)各個(gè)省份所屬城市下最受歡迎的Top 3產(chǎn)品和其銷量
SELECT chinese_province
,chinese_city
,cpzl_zw
FROM (
SELECT chinese_province
,chinese_city
,cpzl_zw
,cp_count
,row_number() OVER (
PARTITION BY chinese_city
,chinese_city ORDER BY cp_count DESC
) AS cp_num
FROM (
SELECT t2.chinese_province
,t2.chinese_city
,t1.cpzl_zw
,count(t1.customer_key) AS cp_count
FROM ods_sales_orders t1
INNER JOIN ods_customer t2 ON t1.customer_key = t2.customer_key
GROUP BY t2.chinese_province
,t2.chinese_city
,t1.cpzl_zw
) t1
WHERE cp_count > 0
) t2
WHERE cp_num <= 3 ;
商品的銷售數(shù)量top10骗爆，排名需考慮并列排名的情況
SELECT product_key
,cp_count
,cp_rank
FROM (
SELECT product_key
,cp_count
,dense_rank() OVER (
ORDER BY cp_count DESC
) AS cp_rank
FROM (
SELECT product_key
,count(cpzl_zw) AS cp_count
FROM ods_sales_orders
GROUP BY product_key
) t1
) t2
WHERE cp_rank <= 10;
計(jì)算累計(jì)和(統(tǒng)計(jì)2019年1-12月的累積銷量次氨，即1月為1月份的值，2月為1摘投、2月份值的和煮寡，3月為1虹蓄、2、3月份的和幸撕，12月為1-12月份值的和)
SELECT date_format(create_date, 'YYYY-MM') AS umonth
,count(sales_order_key) AS ucount
,sum(count(sales_order_key)) OVER (
ORDER BY date_format(create_date, 'YYYY-MM') ASC rows BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS cumulative_amount
FROM ods_sales_orders
WHERE year(create_date) = '2019'
GROUP BY date_format(create_date, 'YYYY-MM')
ORDER BY umonth;
計(jì)算客戶平均購(gòu)買一次商品的間隔時(shí)間
SELECT customer_key
,avg(create_date2) AS avgdate
FROM (
SELECT customer_key
,create_date
,lead(create_date, 1, 0) OVER (
PARTITION BY customer_key ORDER BY create_date ASC
) AS create_date1
,datediff(lead(create_date, 1, 0) OVER (
PARTITION BY customer_key ORDER BY create_date ASC
), create_date) AS create_date2
FROM ods_sales_orders
) t1
WHERE create_date2 > 0
GROUP BY customer_key ;
- 通過(guò)sqoop將數(shù)據(jù)從hive導(dǎo)入MySQL數(shù)據(jù)庫(kù)
以導(dǎo)出訂單表dw_order_by_day為例
sqoop export --connect "jdbc:mysql://ip:port/database" \
--username username \
--password password \
--table dw_order_by_day \
--export-dir /user/hive/warehouse/ods.db/dw_order_by_day \
--input-null-string "\\\\N" \
--input-null-non-string "\\\\N" \
--input-fields-terminated-by "\001" \
--input-lines-terminated-by "\\n" \
-m 1
后面powerbi可連接MySQL數(shù)據(jù)庫(kù)進(jìn)行可視化工作薇组，最后生成數(shù)據(jù)分析報(bào)告。
