商業智能（BI）實戰項目

期待您的關注

?大數據學習筆記

1.實現的功能

2.數據庫操作步驟

創建數據庫：create database card;創建表：create table card_apply
(
cid bigint primary key auto_increment
,apply_uid bigint
,apply_entrance varchar(90)
,apply_dt varchar(90)
,access_state varchar(90)
,issuccess varchar(90)
,source varchar(90)
,isact varchar(10)
,isfirst varchar(10)
);

環比：這個月減去上個月的和上個月的比

同比：今年本月減去去年同期和去年同月的比

插入數據：insert into card_apply values
(null,1,'廣告banner','2024-06-04','點擊','不成功','直銷','否','否')
,(null,2,'首頁con','2024-06-04','點擊','不成功','直銷','否','否')
,(null,3,'交叉銷售廣告位','2024-06-04','點擊','不成功','直銷','否','否')
,(null,4,'信用卡專區','2024-05-02','點擊','不成功','直銷','否','否')
,(null,5,'交叉銷售廣告位','2024-05-03','點擊','不成功','直銷','否','否')
,(null,6,'廣告banner','2024-05-03','點擊','不成功','直銷','否','否')
,(null,7,'廣告banner','2023-06-03','點擊','成功','直銷','是','是')
,(null,8,'交叉銷售廣告位','2023-06-03','點擊','成功','直銷','是','是');select * from card_apply;

3.計算本月信用卡申請人數及同比環比

打開Tableau 連接MySQL 雙擊新自定義SQL 寫入SQL語句

select
a.user_cnt curr_month_user_cnt,
(a.user_cnt-b.user_cnt)/b.user_cnt upper_month_user_cnt,
(a.user_cnt-c.user_cnt)/c.user_cnt last_year_user_cnt
from
(
select count(distinct(apply_uid)) user_cnt from card_apply where date_format(apply_dt,'%Y-%m')=date_format(current_date(),'%Y-%m')
)a
,(
select count(distinct(apply_uid)) user_cnt from card_apply where date_format(apply_dt,'%Y-%m')=date_format(date_sub(current_date(), interval 1 month),'%Y-%m')
)b
,(
select count(distinct(apply_uid)) user_cnt from card_apply where date_format(apply_dt,'%Y-%m')=date_format(date_sub(current_date(), interval 12 month),'%Y-%m')
)c

MySQL日期函數

然后跳轉到工作表

4.計算本月信用卡核卡成功人數及同比環比

為了方便計算 我們再插入幾條數據：insert into card_apply values
(null,9,'交叉銷售廣告位','2024-05-03','點擊','成功','直銷','是','是'),
(null,10,'信用卡專區','2024-06-03','點擊','成功','直銷','是','是'),
(null,11,'首頁con','2024-05-08','點擊','成功','直銷','是','是');

打開Tableau 連接MySQL 雙擊新自定義SQL 寫入SQL語句

SELECT
a.success_uid current_success_uid
,(a.success_uid-b.success_uid)/b.success_uid last_month_success_uid
,(a.success_uid-c.success_uid)/c.success_uid last_year_success_uid
FROM
(
select count(distinct(apply_uid)) success_uid from card_apply where issuccess = '成功' and date_format(apply_dt,'%Y-%M') = date_format(current_date(),'%Y-%M')
)a
,(
select count(distinct(apply_uid)) success_uid from card_apply where issuccess = '成功' and date_format(apply_dt,'%Y-%M') = date_format(date_sub(current_date(),interval 1 month),'%Y-%M')
)b
,(
select count(distinct(apply_uid)) success_uid from card_apply where issuccess = '成功' and date_format(apply_dt,'%Y-%M') = date_format(date_sub(current_date(),interval 12 month),'%Y-%M')
)c

最終結果

5.計算本月信用卡激活人數及同比環比

為了方便計算 我們再插入幾條數據：insert into card_apply values
(null,12,'交叉銷售廣告位','2024-06-23','點擊','成功','直銷','是','是'),
(null,13,'信用卡專區','2024-06-18','點擊','成功','直銷','是','是'),
(null,14,'首頁con','2024-05-08','點擊','不成功','直銷','是','是');

打開Tableau 連接MySQL 雙擊新自定義SQL 寫入SQL語句

SELECT
a.act_uid current_act_uid
,(a.act_uid-b.act_uid)/b.act_uid last_month_act_uid
,(a.act_uid-c.act_uid)/c.act_uid last_year_act_uid
FROM
(
select count(distinct(apply_uid)) act_uid from card_apply where isact = '是' and date_format(apply_dt,'%Y-%M') = date_format(current_date(),'%Y-%M')
)a
,(
select count(distinct(apply_uid)) act_uid from card_apply where isact = '是' and date_format(apply_dt,'%Y-%M') = date_format(date_sub(current_date(),interval 1 month),'%Y-%M')
)b
,(
select count(distinct(apply_uid)) act_uid from card_apply where isact = '是' and date_format(apply_dt,'%Y-%M') = date_format(date_sub(current_date(),interval 12 month),'%Y-%M')
)c

最終結果

6.計算本月信用卡首刷人數及同比環比

為了方便計算 我們再插入幾條數據：insert into card_apply values
(null,15,'交叉銷售廣告位','2024-06-26','點擊','成功','直銷','是','否'),
(null,16,'信用卡專區','2024-06-11','點擊','成功','直銷','是','是'),
(null,17,'首頁con','2023-05-08','點擊','不成功','直銷','是','是');

打開Tableau 連接MySQL 雙擊新自定義SQL 寫入SQL語句

SELECT
a.first_uid current_first_uid
,(a.first_uid-b.first_uid)/b.first_uid last_month_first_uid
,(a.first_uid-c.first_uid)/c.first_uid last_year_first_uid
FROM
(
select count(distinct(apply_uid)) first_uid from card_apply where isfirst = '是' and date_format(apply_dt,'%Y-%M') = date_format(current_date(),'%Y-%M')
)a
,(
select count(distinct(apply_uid)) first_uid from card_apply where isfirst = '是' and date_format(apply_dt,'%Y-%M') = date_format(date_sub(current_date(),interval 1 month),'%Y-%M')
)b
,(
select count(distinct(apply_uid)) first_uid from card_apply where isfirst = '是' and date_format(apply_dt,'%Y-%M') = date_format(date_sub(current_date(),interval 12 month),'%Y-%M')
)c

最終結果

7.求本月信用卡來源

為了方便計算 我們再插入幾條數據：insert into card_apply values
(null,15,'交叉銷售廣告位','2024-06-29','點擊','成功','網絡','是','是'),
(null,16,'信用卡專區','2024-06-18','點擊','成功','網絡','是','是'),
(null,17,'首頁con','2024-06-06','點擊','不成功','網絡','是','是'),
(null,18,'交叉銷售廣告位','2024-06-21','點擊','成功','分行','是','是'),
(null,19,'信用卡專區','2024-06-30','點擊','成功','分行','是','是'),
(null,20,'首頁con','2024-06-09','點擊','不成功','分行','是','是'),
(null,21,'信用卡專區','2024-06-30','點擊','成功','網絡外部平臺','是','是'),
(null,22,'首頁con','2024-06-09','點擊','不成功','網絡外部平臺','是','是'),
(null,23,'信用卡專區','2024-06-30','點擊','成功','網絡外部平臺','是','是'),
(null,24,'首頁con','2024-06-09','點擊','不成功','網絡外部平臺','是','是');

SELECT
count(*)
,source
FROM card_apply where date_format(apply_dt,'%Y-%M')=date_format(current_date(),'%Y-%M')
group by source;

8.求本月信用卡核卡成功用戶來源

SELECT
COUNT(*),source
FROM card_apply
WHERE DATE_FORMAT(apply_dt,'%Y-%M')=DATE_FORMAT(CURRENT_DATE(),'%Y-%M') and issuccess = '成功'
GROUP BY source

9.求本月APP信用卡申請入口分布

SELECT
COUNT(*),apply_entrance
FROM card_apply
WHERE DATE_FORMAT(apply_dt,'%Y-%M')=DATE_FORMAT(CURRENT_DATE(),'%Y-%M')
GROUP BY apply_entrance

10.求本月APP信用卡申請和提交用戶數

為了方便計算 我們再插入幾條數據：insert into card_apply values
(null,25,'交叉銷售廣告位','2024-06-26','提交','成功','直銷','是','否'),
(null,26,'信用卡專區','2024-06-11','提交','成功','直銷','是','是'),
(null,27,'首頁con','2023-05-08','提交','不成功','直銷','是','是'),
(null,28,'交叉銷售廣告位','2024-06-25','提交','成功','直銷','是','否'),
(null,29,'信用卡專區','2024-06-25','提交','成功','直銷','是','是'),
(null,30,'首頁con','2023-06-09','提交','不成功','直銷','是','是'),
(null,31,'交叉銷售廣告位','2024-06-25','提交','成功','直銷','是','否'),
(null,32,'信用卡專區','2024-06-24','提交','成功','直銷','是','是'),
(null,33,'首頁con','2023-06-09','提交','不成功','直銷','是','是'),
(null,34,'交叉銷售廣告位','2024-06-18','提交','成功','直銷','是','否'),
(null,35,'信用卡專區','2024-06-19','提交','成功','直銷','是','是'),
(null,36,'首頁con','2023-06-09','提交','不成功','直銷','是','是'),
(null,37,'首頁con','2023-06-21','提交','不成功','直銷','是','是'),
(null,38,'首頁con','2023-06-22','提交','不成功','直銷','是','是'),
(null,39,'首頁con','2023-06-23','提交','不成功','直銷','是','是'),
(null,40,'首頁con','2023-06-24','提交','不成功','直銷','是','是'),
(null,41,'首頁con','2023-06-20','提交','不成功','直銷','是','是')

SELECT
access_state
,apply_dt
,count(distinct(apply_uid)) user_cnt
FROM card_apply
WHERE DATE_FORMAT(apply_dt,'%Y-%M')=DATE_FORMAT(CURRENT_DATE(),'%Y-%M')
and (access_state = '點擊' or access_state = '提交')
group by apply_dt,access_state

由于數據量較小所以曲線不是那么圓順如果我們想要讓它變得更絲滑那么我們可以添加大量數據

11.汽車服務用戶分析的設計與實現

create database app;use app;create table app_log
(
log_id bigint primary key auto_increment
,access_dt varchar(90)
,user_id bigint
,browse_page varchar(10)
,app_status varchar(90)
,operator varchar(30)
,net_type varchar(20)
);

插入數據：insert into app_log values 
(null,'2024-06-01',1,'A','異常','移動','4G'),
(null,'2024-06-02',2,'B','異常','移動','4G'),
(null,'2024-06-01',2,'C','異常','移動','4G'),
(null,'2024-06-02',3,'D','異常','移動','4G'),
(null,'2024-06-02',4,'D','異常','移動','4G')

1）求每日新增用戶數

那么該如何求新增用戶數呢（使用連接查詢）

select 
'2024-06-02' dt
,count(distinct(a.user_id)) num
from 
(
select user_id from app_log where date_format(access_dt,'%Y-%m-%d') = '2024-06-02'
)a 
left join 
(
select user_id from app_log where date_format(access_dt,'%Y-%m-%d') < '2024-06-02'
)b
on a.user_id = b.user_id WHERE b.user_id is null

2）求新增用戶7日留存率

日期加減：date_sub(current_date(),interval 8 day)

select 
c.dt,
c.user_cnt/e.user_cnt remain_ration
from
(
select 
date_sub('2024-06-02',interval 7 day) dt,
count(distinct(a.user_id)) user_cnt
from 
(
select user_id from app_log where date_format(access_dt,'%Y-%m-%d') = '2024-06-02'
)a 
inner join 
(
select user_id from app_log where date_format(access_dt,'%Y-%m-%d') = date_sub('2024-06-02',interval 8 day)
)b
on a.user_id = b.user_id
)c 
,
(
select count(distinct(user_id)) user_cnt
from 
(
select user_id from app_log where date_format(access_dt,'%Y-%m-%d') = date_sub('2024-06-02',interval 8 day)
)b
)e

3）求每周新增用戶數

日期轉周：week(current_date())

select
count(distinct(a.user_id)) user_cnt
from 
(
select 
distinct(user_id) user_id
from app_log where week(access_dt) = 23
)a 
left join 
(
select 
distinct(user_id) user_id
from app_log where week(access_dt) = 22
)b 
on a.user_id = b.user_id where b.user_id is null

4）求新增用戶八周留存

select
d.week,
d.user_cnt/e.user_cnt remain_ration
from 
(
select 
week(date_add(current_date(),interval -9 week)) week,
count(distinct(a.user_id)) user_cnt
from 
(
select user_id from app_log where week(access_dt)=week(current_date())
)a 
inner join 
(
select user_id from app_log where week(access_dt)=week(date_add(current_date(),interval -9 week))
)b on a.user_id = b.user_id
)d,
(
select count(distinct(b.user_id)) user_cnt
from
(
select user_id from app_log where week(access_dt)=week(date_add(current_date(),interval -9 week))
)b
)e

5）求30日活躍用戶

select
count(distinct(user_id)) user_cnt
from app_log 
where access_dt between date_add(current_date(),interval -30 day) and current_date()

商業智能（BI）實戰項目

商業智能（BI）實戰項目

1.實現的功能

2.數據庫操作步驟

3.計算本月信用卡申請人數及同比環比

4.計算本月信用卡核卡成功人數及同比環比

5.計算本月信用卡激活人數及同比環比

6.計算本月信用卡首刷人數及同比環比

7.求本月信用卡來源

8.求本月信用卡核卡成功用戶來源

9.求本月APP信用卡申請入口分布

10.求本月APP信用卡申請和提交用戶數

11.汽車服務用戶分析的設計與實現

1）求每日新增用戶數

2）求新增用戶7日留存率

3）求每周新增用戶數

4）求新增用戶八周留存

5）求30日活躍用戶

相關文章

商城自動化測試實戰 —— 登錄+滑塊驗證

openEuler安裝docker

010、GPT-5：AI新紀元的曙光與挑戰

惠海H6392 2.6v升5V 3.7V升9V 4.2V升12V 升壓恒壓芯片小家電IC

使用Collections.shuffle打亂集合順序

單例模式實現方式

華為od-C卷200分題目4 -電腦病毒感染

二叉樹的題目

NextJs 系列文章

使用Java實現通用樹形結構轉換工具類：深入解析TreeUtil和TreeNode接口

基于vue腳手架創建的圖書商城

條件測試，if語句，case語句

解決json日期格式問題

Knife4j：快速入門

uniapp uniCloud云開發

大模型應用-多模態和大模型是如何相互成就的

【Docker0】網絡更改

c++用什么軟件編程？都有哪些？

深入 SSH：解鎖本地轉發、遠程轉發和動態轉發的潛力

mysqldump全備份之后，如何只恢復一個庫或者一個表