主要內(nèi)容

1. 回訪用戶
2. 如何找到每個人每月消費的最大天數(shù)

---

一.回訪用戶

1.準(zhǔn)備工作

代碼如下（示例）:

drop database if exists db_1;create database db_1;use db_1;CREATE TABLE tb_visits (user_id INT,date DATE
);INSERT INTO tb_visits (user_id, date)
VALUES(1, current_timestamp() - interval 0 day),(1, current_timestamp() - interval 0 day),(1, current_timestamp() - interval 1 day),(1, current_timestamp() - interval 2 day),(1, current_timestamp() - interval 3 day),(1, current_timestamp() - interval 4 day),(2, current_timestamp() - interval 1 day),(4, current_timestamp() - interval 0 day),(4, current_timestamp() - interval 1 day),(4, current_timestamp() - interval 3 day),(4, current_timestamp() - interval 4 day),(4, current_timestamp() - interval 62 day),(4, current_timestamp() - interval 62 day),(5, current_timestamp() - interval 1 day),(5, current_timestamp() - interval 3 day),(5, current_timestamp() - interval 4 day)
;select * from tb_visits order by user_id, date;

2.目標(biāo)

• 說明 ：回訪用戶

• 返回連續(xù)訪問該頁面最?的3個用戶,按?短的倒序排列3個用戶

• 問題：在如下的數(shù)據(jù)庫表中，包含有關(guān)用戶訪問網(wǎng)頁的信息。完成SQL返回連續(xù)訪問該頁面最長的3個用戶，按長短的倒序排列3個用戶。

• 輸入
  

• 輸出
  

3.實現(xiàn)

代碼如下（示例）:

-- todo 第一步: 去重
with t1 as (selectdistinct user_id, datefrom tb_visits
),-- todo 第二步: 根據(jù) user_id 分堆, 再根據(jù) date 排序(正序)
t2 as (selectuser_id, date,row_number() over (partition by user_id order by date asc) as rnfrom t1
),-- todo 第三步: 偽代碼 dt2 = date -rn
t3 as (selectuser_id, date, rn,date_add(date, interval -rn day) as dt2from t2
),-- todo 第四步: 求每個用戶連續(xù)訪問的天數(shù), 連續(xù)訪問的開始日期和結(jié)束日期
t4 as (selectuser_id, dt2,count(1) as cnt,-- 連續(xù)天數(shù)min(date) as start_date,-- 開始日期max(date) as end_date-- 結(jié)束日期from t3group by user_id, dt2
),-- todo 第五步: 求每個人訪問的最大天數(shù) 先排序
t5 as (selectuser_id, dt2, cnt, start_date, end_date,row_number() over (partition by user_id order by cnt desc) as rn2from t4
),-- todo 第六步: 求每個人訪問的最大天數(shù) 再過濾 ... where rn2=1
t6 as (selectuser_id, dt2, cnt, start_date, end_date, rn2from t5where rn2=1
),-- todo 第七步: 求最大連續(xù)天數(shù)的top3 先排序
t7 as (selectuser_id, dt2, cnt, start_date, end_date, rn2,rank() over (order by cnt desc) as rn3
from t6
),-- todo 第八步: 求最大連續(xù)天數(shù)的top3 再過濾
t8 as (select*from t7where rn3<=3
)
select user_id, cnt, start_date, end_date from t8
;

---

二.如何找到每個人每月消費的最大天數(shù)

1.準(zhǔn)備工作

代碼如下（示例）:

drop database if exists db_1;create database db_1;use db_1;create table tb_card
(card_nbr varchar(32),c_date varchar(32),c_type varchar(32),c_atm int
);insert into tb_card
values(1, '2022-01-01', '網(wǎng)購', 150),(1, '2022-01-01', '網(wǎng)購', 100),(1, '2022-01-02', '網(wǎng)購', 200),(1, '2022-01-03', '網(wǎng)購', 300),(1, '2022-01-15', '網(wǎng)購', 100),(1, '2022-01-16', '網(wǎng)購', 200),(2, '2022-01-06', '網(wǎng)購', 500),(2, '2022-01-07', '網(wǎng)購', 800),(1, '2022-02-02', '網(wǎng)購', 200),(1, '2022-02-03', '網(wǎng)購', 300),(1, '2022-02-04', '網(wǎng)購', 300),(1, '2022-02-05', '網(wǎng)購', 300),(1, '2022-02-08', '網(wǎng)購', 800),(1, '2022-02-09', '網(wǎng)購', 900),(2, '2022-02-05', '網(wǎng)購', 500),(2, '2022-02-06', '網(wǎng)購', 500),(2, '2022-02-07', '網(wǎng)購', 800),(2, '2022-02-07', '網(wǎng)購', 850)
;select * from tb_card;

2.目標(biāo)

• 說明

• 有一張C_T (列舉了部分?jǐn)?shù)據(jù)）表示持卡人消費記錄，表結(jié)構(gòu)如下：

• 每個月每張卡連續(xù)消費的最大天數(shù)（如卡在當(dāng)月只有一次消費則為1）。

• 連續(xù)消費天數(shù)：指一樓時間內(nèi)連續(xù)每天都有消費，同一天有多筆消費算一天消費，不能跨月份統(tǒng)計。

• 輸入
  

• 輸出
  

3.實現(xiàn)

代碼如下（示例）:

with t1 as (selectdistinct card_nbr, c_date
from tb_card
),t2 as (selectcard_nbr,substr(c_date, 1, 7) as c_month,
c_date, 
--substr(c_date, 1, 7) as c_month：從消費日期中提取出年份和月份，形成一個新的字段c_month。這樣我們就可以按照月份進行分組。row_number() over (partition by card_nbr, substr(c_date, 1, 7) order by
from t1
),
t3 as (selectcard_nbr, c_month, c_date, rn1,date_add(c_date, interval -rn1 day) as dt2from t2
),
t4 as (selectcard_nbr, c_month, dt2,count(1) as cnt -- todo 連續(xù)消費的天數(shù)from t3group by card_nbr, c_month, dt2
)
selectcard_nbr, c_month,max(cnt) as 連續(xù)消費的最大天數(shù)
from t4
group by card_nbr, c_month
;

4.解釋

代碼如下（示例）:

以下是每個子查詢的解釋：1. 子查詢t1：從tb_card表中選擇不同的卡號和消費日期。2. 子查詢t2：從t1中選擇卡號、消費月份和消費日期，并使用row_number()函數(shù)為每個卡號和月份組合編號。3. 子查詢t3：從t2中選擇卡號、消費月份、消費日期、編號和消費日期減去編號天數(shù)的結(jié)果。4. 子查詢t4：從t3中選擇卡號、消費月份、消費日期和每個日期組合的連續(xù)消費天數(shù)，并使用count()函數(shù)計算連續(xù)消費天數(shù)。最后，查詢語句從t4中選擇卡號、消費月份和最大連續(xù)消費天數(shù)，并使用group by子句按卡號和月份分組。

---

總結(jié)

MySQL實戰(zhàn)1
以上是今天要講的內(nèi)容，實戰(zhàn)了：回訪用戶，如何找到每個人每月消費的最大天數(shù)。