Hive实战 --- 电子商务消费行为分析

男娘i 2024-03-26 16:35 97阅读 0赞

**目录**

数据结构

Customer表

Transaction表

Store表

Review表

上传数据

创建目录用于存放数据

把本地文件上传到HDFS上

创建外部表

创建数据库

创建表

数据清洗

对transaction\_details中的重复数据生成新ID

过滤掉store\_review中没有评分的数据

找出PII (personal information identification‘个人信息识别’) 数据进行加密

重新组织transaction数据按照日期YYYY-MM（按月）做分区

Customer分析

1.找出顾客最常用的信用卡

2.找出户客资料中排名前五的职位名称

3.在美国女性最常用的信用卡

4.按性别和国家进行客户统计

Transaction

1.计算每月总收入

2.计算每个季度的总收入

3.按年计算总收入

4.按工作日计算总收入

5.按时间段计算总收入(需要清理数据)

6.按时间段计算平均消费

7.按工作日计算平均消费

8.计算年、月、日的交易总数

9.找出交易量最大的10个客户

10.找出消费最多的前10位顾客

11.计该期间交易数量最少的用户

12.算每个季度的独立客户总数

13.算每周的独立客户总数

14.算整个活动客户平均花费的最大值

15.计每月花费最多的客户

16.计每月访问次数最多的客户

17.总价找出最受欢迎的5种产品

18.据购买频率找出最畅销的5种产品

19.据客户数量找出最受欢迎的5种产品

Store分析

1.按客流量找出最受欢迎的商店

2.根据顾客消费价格找出最受欢迎的商店

3.根据顾客交易情况找出最受欢迎的商店

4.根据商店和唯一的顾客id获取最受欢迎的产品

5.获取每个商店的员工与顾客比

6.按年和月计算每家店的收入

7.按店铺制作总收益饼图

8.找出每个商店最繁忙的时间段

9.找出每家店的忠实顾客

10.根据每位员工的最高收入找出明星商店

Review分析

1.在ext\_store\_review中找出存在冲突的交易映射关系

2.了解客户评价的覆盖率

3.根据评分了解客户的分布情况

4.根据交易了解客户的分布情况

5.客户给出的最佳评价是否总是同一家门店

--------------------

**[使用工具zeppelin][zeppelin]**

### 数据结构 ###

#### Customer表 ####

<table style="width:500px;"> 
 <thead> 
  <tr> 
   <th>customer_details</th> 
   <th>details</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td> <p>customer_id</p> </td> 
   <td> <p>Int,&nbsp;1&nbsp;-&nbsp;500</p> </td> 
  </tr> 
  <tr> 
   <td> <p>first_name</p> </td> 
   <td> <p>string</p> </td> 
  </tr> 
  <tr> 
   <td> <p>last_name</p> </td> 
   <td> <p>string</p> </td> 
  </tr> 
  <tr> 
   <td> <p>email</p> </td> 
   <td> <p>string,&nbsp;such&nbsp;as&nbsp;willddy@gmail.com</p> </td> 
  </tr> 
  <tr> 
   <td> <p>gender</p> </td> 
   <td> <p>string,&nbsp;Male&nbsp;or&nbsp;female</p> </td> 
  </tr> 
  <tr> 
   <td> <p>address</p> </td> 
   <td> <p>string</p> </td> 
  </tr> 
  <tr> 
   <td> <p>country</p> </td> 
   <td> <p>string</p> </td> 
  </tr> 
  <tr> 
   <td> <p>language</p> </td> 
   <td> <p>string</p> </td> 
  </tr> 
  <tr> 
   <td> <p>job</p> </td> 
   <td> <p>string,&nbsp;job&nbsp;title/position</p> </td> 
  </tr> 
  <tr> 
   <td> <p>credit_type</p> </td> 
   <td> <p>string,&nbsp;credit&nbsp;card&nbsp;type,&nbsp;such&nbsp;as&nbsp;visa</p> </td> 
  </tr> 
  <tr> 
   <td> <p>credit_no</p> </td> 
   <td> <p>string,&nbsp;credit&nbsp;card&nbsp;number</p> </td> 
  </tr> 
 </tbody> 
</table>

#### Transaction表 ####

<table style="width:500px;"> 
 <thead> 
  <tr> 
   <th>transaction_details</th> 
   <th>details</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td> <p>transaction_id</p> </td> 
   <td> <p>Int,&nbsp;1&nbsp;-&nbsp;1000</p> </td> 
  </tr> 
  <tr> 
   <td> <p>customer_id</p> </td> 
   <td> <p>Int,&nbsp;1&nbsp;-&nbsp;500</p> </td> 
  </tr> 
  <tr> 
   <td> <p>store_id</p> </td> 
   <td> <p>Int,&nbsp;1&nbsp;-&nbsp;5</p> </td> 
  </tr> 
  <tr> 
   <td> <p>price</p> </td> 
   <td> <p>decimal,&nbsp;such&nbsp;as&nbsp;5.08</p> </td> 
  </tr> 
  <tr> 
   <td> <p>product</p> </td> 
   <td> <p>string,&nbsp;things&nbsp;bought</p> </td> 
  </tr> 
  <tr> 
   <td> <p>date</p> </td> 
   <td> <p>string,&nbsp;when&nbsp;to&nbsp;purchase</p> </td> 
  </tr> 
  <tr> 
   <td> <p>time</p> </td> 
   <td> <p>string,&nbsp;what&nbsp;time&nbsp;to&nbsp;purchase</p> </td> 
  </tr> 
 </tbody> 
</table>

#### Store表 ####

<table style="width:500px;"> 
 <thead> 
  <tr> 
   <th>store_details</th> 
   <th>details</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td> <p>store_id</p> </td> 
   <td> <p>Int,&nbsp;1&nbsp;-&nbsp;5</p> </td> 
  </tr> 
  <tr> 
   <td> <p>store_name</p> </td> 
   <td> <p>string</p> </td> 
  </tr> 
  <tr> 
   <td> <p>employee_number</p> </td> 
   <td> <p>Int,&nbsp;在store有多少employee</p> </td> 
  </tr> 
 </tbody> 
</table>

#### Review表 ####

<table style="width:500px;"> 
 <thead> 
  <tr> 
   <th>store_review</th> 
   <th>details</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td> <p>stransaction_id</p> </td> 
   <td> <p>Int,&nbsp;1&nbsp;-&nbsp;8000</p> </td> 
  </tr> 
  <tr> 
   <td> <p>store_id</p> </td> 
   <td> <p>Int,&nbsp;1&nbsp;-&nbsp;5</p> </td> 
  </tr> 
  <tr> 
   <td> <p>review_store</p> </td> 
   <td> <p>Int,&nbsp;1&nbsp;-&nbsp;5</p> </td> 
  </tr> 
 </tbody> 
</table>

### 上传数据 ###

#### 创建目录用于存放数据 ####

%sh
    hdfs dfs -mkdir -p /shopping/data/customer
    hdfs dfs -mkdir -p /shopping/data/store
    hdfs dfs -mkdir -p /shopping/data/review
    hdfs dfs -mkdir -p /shopping/data/transaction

#### 把本地文件上传到HDFS上 ####

%sh
    cd /opt/stufile/storetransaction
    ls -al
    
    hdfs dfs -put ./customer_details.csv /shopping/data/customer
    hdfs dfs -put ./store_details.csv /shopping/data/store
    hdfs dfs -put ./store_review.csv /shopping/data/review
    hdfs dfs -put ./transaction_details.csv /shopping/data/transaction

### 创建外部表 ###

#### 创建数据库 ####

%hive
    -- 如果存在此表则删除
    drop database if exists shopping cascade;
    --创建数据库
    create database if not exists shopping;

#### 创建表 ####

%hive
    create external table if not exists ext_customer_detail
    (
        customer_id string,
        first_name  string,
        last_name   string,
        email       string,
        gender      string,
        address     string,
        country     string,
        language    string,
        job         string,
        credit_type string,
        credit_no   string
    )
        row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
        location '/shopping/data/customer'
        tblproperties ('skip.header.line.count' = '1');
    
    create external table if not exists ext_transaction_details
    (
        transaction_id string,
        customer_id    string,
        store_id       string,
        price          decimal(8, 2),
        product        string,
        purchase_date  string,
        purchase_time  string
    ) row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
        location '/shopping/data/transaction'
        tblproperties ('skip.header.line.count' = '1');
    
    drop table ext_store_details;
    create external table if not exists ext_store_details
    (
        store_id        string,
        store_name      string,
        employee_number string
    
    ) row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
        location '/shopping/data/store'
        tblproperties ('skip.header.line.count' = '1');
    
    drop table ext_store_review;
    create external table if not exists ext_store_review
    (
        store_id       string,
        transaction_id string,
        review_score   string
    ) row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
        location '/shopping/data/review'
        tblproperties ('skip.header.line.count' = '1');

> ***TIP:***
> 
> OpenCSVSerde 默认的分隔符 (separator)、quote 以及逃逸字符（escape characters ）分别为 、" 以及 '
> 
> 如果我们查看表结构的时候，我们会发现如果 row format serde 为 org.apache.hadoop.hive.serde2.OpenCSVSerde，不管你建表的时候指定字段是什么类型，其显示的都是 string 类型
> 
> tblproperties ('skip.header.line.count' = '1');从外部表导入数据跳过（忽略）首行。

### 数据清洗 ###

#### 对transaction\_details中的重复数据生成新ID ####

%hive
    use shopping;
    with basetb as (
        select row_number() over (partition by transaction_id order by transaction_id) as rn
             , transaction_id
             , customer_id
             , store_id
             , price
             , product
             , purchase_date
             , purchase_time
             , substr(purchase_date, 0, 7)                                             as purchase_month
        from ext_transaction_details),
         basetb2 as (
             select `if`(rn = 1, transaction_id, concat(transaction_id, '_fix', rn)) transaction_id
                  , customer_id
                  , store_id
                  , price
                  , product
                  , purchase_date
                  , purchase_time
                  , purchase_month
             from basetb)
    select *
    from basetb2
    where transaction_id like '%fix%';

因为查询的数据比较多，显示不全所有要在后面加一个查询

![932c2e68111646368e193880f8d39477.png][]

#### 过滤掉store\_review中没有评分的数据 ####

查出有评分的数据

%hive
    use shopping;
    create view if not exists vm_store_review as
    select *
    from ext_store_review
    where review_score <> '';
    
    
    select * from vm_store_review;

![8a827367596748e79d2cc9c8762491f8.png][]

#### 找出PII (personal information identification‘个人信息识别’) 数据进行加密 ####

%hive
    use shopping;
    drop view customer_detail;
    create view vm_customer_details as
    select 
    customer_id,first_name,unbase64(last_name)as last_name,unbase64(email) as email,
    gender,unbase64(address) as address,
    unbase64(concat(unbase64(credit_no),'hello')) as credit_no --二次加密
    from ext_customer_details;

![af719821f00e49ca92ede9b77f0ddda6.png][]

#### 重新组织transaction数据按照日期YYYY-MM（按月）做分区 ####

%hive
    use shopping;
    set hive.exec.dynamic.partition.mode=nonstrict;
    with basetb as (
        select row_number() over (partition by transaction_id order by transaction_id) as rn
             , transaction_id
             , customer_id
             , store_id
             , price
             , product
             , purchase_date
             , purchase_time
             , substr(purchase_date, 0, 7)                                             as purchase_month
        from ext_transaction_details)
    insert overwrite table transaction_details partition (purchase_month)
    select `if`(rn = 1, transaction_id, concat(transaction_id, '_fix', rn)) transaction_id
         , customer_id
         , store_id
         , price
         , product
         , purchase_date
         , purchase_time
         , purchase_month
    from basetb;
    
    
    show partitions transaction_details;

![d405d584094149c1b2eaf8fa25f5a189.png][]

### Customer分析 ###

#### 1.找出顾客最常用的信用卡 ####

%hive
    use shopping;
    select credit_type, max(credit_type) counts
    from ext_customer_details
    group by credit_type
    order by counts desc;

![adc084af9e9941b0ac9448a1e609675b.png][]

#### 2.找出户客资料中排名前五的职位名称 ####

select job, count(job) counts
    from ext_customer_details
    group by job
    order by counts desc
    limit 5;

![fa723606603d4dc796780d57b93f51f9.png][]

#### 3.在美国女性最常用的信用卡 ####

%hive
    use shopping;
    select credit_type, count(credit_type) counts
    from ext_customer_details
    where gender = 'Female' and country='United States'
    group by credit_type
    order by counts desc;

![2316c624f4c644279f6d410cf2c9a0d6.png][]

#### 4.按性别和国家进行客户统计 ####

%hive
    use shopping;
    select country, gender,count(customer_id)
    from ext_customer_details
    group by gender, country;

![3355e03620d045878d3246738f1b3fc5.png][]

### Transaction ###

#### 1.计算每月总收入 ####

%hive
    use shopping;
    select substr(purchase_date,0,7) month,round(sum(price),2) sum
    from ext_transaction_details
    group by substr(purchase_date,0,7);

![a597a54c3f17457db915e972c18e4c0e.png][]

![3308f95edaa1412aab5f0eee15b4afb5.png][]

#### 2.计算每个季度的总收入 ####

with basetb as (
        select concat_ws('-', cast(year(purchase_date) as string),
                         cast(quarter(purchase_date) as string)) as year_quarter,
               price
        from transaction_details)
    select year_quarter ,sum(price) sumMoney from basetb group by year_quarter;

![640b9da9be9d4271b8a23ed0d7468d17.png][]

#### 3.按年计算总收入 ####

%hive
    use shopping;
    select year(purchase_date) years , round(sum(price),2) sum
    from ext_transaction_details
    group by year(purchase_date);

![57b1656fd28842a49ba157911975c236.png][]

#### 4.按工作日计算总收入 ####

%hive
    use shopping;
    with basetb as (
        select `dayofweek`(purchase_date) weekday, price
        from transaction_details)
    select case
               when (weekday - 1) = 1 then '星期一'
               when (weekday - 1) = 2 then '星期二'
               when (weekday - 1) = 3 then '星期三'
               when (weekday - 1) = 4 then '星期四'
               when (weekday - 1) = 5 then '星期五'
               end as weekday,
           sum(price) sum
    from basetb
    group by weekday
    having weekday between 2 and 6;

![a9c12b4463f04e62bacbd7f01272645d.png][]

#### 5.按时间段计算总收入(需要清理数据) ####

%hive
    use shopping;
    
    with basetb1 as (
        select price,
               purchase_time,
               case
                   when purchase_time like '%AM' then split(purchase_time, '\\s+')[0]
                   when purchase_time like '%PM' then concat_ws(':',cast(`if`(
                                                                        (cast(split(purchase_time, ':')[0] as int) + 12) == 24,0,          (cast(split(purchase_time, ':')[0] as int) + 12)) as string)
                       , split(split(purchase_time, ':')[1], '\\s+')[0])
                   else purchase_time
                   end time_format
        from transaction_details),
         basetb2 as (select price,
                            purchase_time,
                            (cast(split(time_format, ':')[0] as decimal(4, 2)) +
                             cast(split(time_format, ':')[1] as decimal(4, 2)) / 60) purchase_time_num
                     from basetb1),
         basetb3 as (select price,
                            purchase_time,
                            `if`(purchase_time_num > 5 and purchase_time_num <= 8, 'early morning',
                            `if`(purchase_time_num > 8 and purchase_time_num <= 11, ' morning',
                            `if`(purchase_time_num > 11 and purchase_time_num <= 13, 'noon',
                            `if`(purchase_time_num > 13 and purchase_time_num <= 18, 'afternoon',
                            `if`(purchase_time_num > 18 and purchase_time_num <= 22, 'evening','night'))))) as time_bucket
                     from basetb2)
    select time_bucket, sum(price) sum
    from basetb3
    group by time_bucket;

![6c4c1293227a4049918c509b2f9573e4.png][]

#### 6.按时间段计算平均消费 ####

%hive
    use shopping;
    
    with basetb1 as (
        select price,
               purchase_time,
               case
                   when purchase_time like '%AM' then split(purchase_time, '\\s+')[0]
                   when purchase_time like '%PM' then concat_ws(':',cast(`if`(
                                                                        (cast(split(purchase_time, ':')[0] as int) + 12) == 24,0,          (cast(split(purchase_time, ':')[0] as int) + 12)) as string)
                       , split(split(purchase_time, ':')[1], '\\s+')[0])
                   else purchase_time
                   end time_format
        from transaction_details),
         basetb2 as (select price,
                            purchase_time,
                            (cast(split(time_format, ':')[0] as decimal(4, 2)) +
                             cast(split(time_format, ':')[1] as decimal(4, 2)) / 60) purchase_time_num
                     from basetb1),
         basetb3 as (select price,
                            purchase_time,
                            `if`(purchase_time_num > 5 and purchase_time_num <= 8, 'early morning',
                            `if`(purchase_time_num > 8 and purchase_time_num <= 11, ' morning',
                            `if`(purchase_time_num > 11 and purchase_time_num <= 13, 'noon',
                            `if`(purchase_time_num > 13 and purchase_time_num <= 18, 'afternoon',
                            `if`(purchase_time_num > 18 and purchase_time_num <= 22, 'evening','night'))))) as time_bucket
                     from basetb2)
    select time_bucket, avg(price) avg
    from basetb3
    group by time_bucket;

![d656f7a7909d4ed9934bdb4383da1eaa.png][]

#### 7.按工作日计算平均消费 ####

%hive
    use shopping;
    with basetb as (
        select `dayofweek`(purchase_date) weekday, price
        from transaction_details)
    select case
               when (weekday - 1) = 1 then '星期一'
               when (weekday - 1) = 2 then '星期二'
               when (weekday - 1) = 3 then '星期三'
               when (weekday - 1) = 4 then '星期四'
               when (weekday - 1) = 5 then '星期五'
               end weekday,
        avg(price) avg
    from basetb
    group by weekday
    having weekday between 2 and 6;

![d728cc6899694fdd9919484e4064a777.png][]

#### 8.计算年、月、日的交易总数 ####

%hive
    use shopping;
    select distinct purchase_date,
           purchase_month,
           year(purchase_date),
           count(1) over (partition by year(purchase_date))                                         years,
           count(1) over (partition by year(purchase_date),month(purchase_date))                    months,
           count(1) over (partition by year(purchase_date),month(purchase_date),day(purchase_date)) days
    from transaction_details;

![9ea571510b1e429da2210377ce88b32d.png][]

#### 9.找出交易量最大的10个客户 ####

%hive
    use shopping;
    select
    customer_id,count(1) as num
    from transaction_details
    group by customer_id
    order by num desc
    limit 10

![0e483b38c8884c9f84233e25fcec42e7.png][]

#### 10.找出消费最多的前10位顾客 ####

%hive
    use shopping;
    select customer_id,
           sum(price) as sum
    from transaction_details
    group by customer_id
    order by sum desc
    limit 10

![fe227ca5885d402d8e7eb9719403e28e.png][]

#### 11.计该期间交易数量最少的用户 ####

use shopping;
    select customer_id, count(transaction_id)
    from transaction_details
    group by customer_id
    order by count(transaction_id)
    limit 1;

#### ![7dfae58eb61c4992927c5addf53149f2.png][]   12.算每个季度的独立客户总数 ####

%hive
    use shopping;
    with basetb as (
        select distinct concat_ws('-', cast(year(purchase_date) as string),
                         cast(quarter(purchase_date) as string)) as year_quarter,
               customer_id
        from transaction_details)
    select  year_quarter, count(customer_id) counts
    from basetb
    group by year_quarter;

#### ![0c6b10ad85e349be9a9ed7c0a259ce68.png][]   13.算每周的独立客户总数 ####

%hive
    use shopping;
    with basetb as (
        select distinct concat(cast(year(purchase_date) as string), '-', cast(weekofyear(purchase_date)as string)) weeks,
                        customer_id
        from transaction_details)
    select weeks, count(customer_id) counts
    from basetb
    group by weeks;

#### ![a85235b0f6b740858b7110a7b479f517.png][]   14.算整个活动客户平均花费的最大值 ####

%hive
    use shopping;
    select customer_id,avg(price) avgs
    from transaction_details
    group by customer_id
    order by avgs desc
    limit 1;

#### ![af684180588b49f0ba1fd946b9d33998.png][]   15.计每月花费最多的客户 ####

%hive
    use shopping;
    with basetb as (
        select purchase_month,
               customer_id,
               sum(price) sum_price
        from transaction_details
        group by purchase_month, customer_id),
         basetb2 as (
             select purchase_month,
                    customer_id,
                    sum_price,
                    row_number() over (partition by purchase_month order by sum_price desc ) rn
             from basetb)
    select purchase_month, customer_id, sum_price
    from basetb2
    where rn = 1;

#### ![050fb1724254496abfe87413e810a24b.png][]   16.计每月访问次数最多的客户 ####

%hive
    use shopping;
    with basetb as (
        select purchase_month,
               customer_id,
               count(customer_id) counts
        from transaction_details
        group by purchase_month, customer_id),
         basetb2 as (
             select purchase_month,
                    customer_id,
                    counts,
                    row_number() over (partition by purchase_month order by counts desc ) rn
             from basetb)
    select purchase_month, customer_id, counts
    from basetb2
    where rn = 1;

#### ![32302e9b85124c8391e72eba948cd010.png][]   17.总价找出最受欢迎的5种产品 ####

select product,sum(price) sum
    from transaction_details
    group by product
    order by sum desc
    limit 5;

#### ![a96833dd4ff84b2caad6616e30dc4799.png][]   18.据购买频率找出最畅销的5种产品 ####

select product,
    count(1) counts
    from transaction_details
    group by product
    order by counts desc
    limit 5;

#### ![b89a64b0b68f4634a6091acdf78d58b2.png][]   19.据客户数量找出最受欢迎的5种产品 ####

select product,
    count(distinct customer_id) counts
    from transaction_details
    group by product
    order by counts desc
    limit 5

#### ![09d95a2c564b46e09485d813e72a3e64.png][] ####

### Store分析 ###

#### 1.按客流量找出最受欢迎的商店 ####

%hive
    use shopping;
    select store_name, count(distinct customer_id) counts
    from transaction_details td
             join ext_store_details esd on td.store_id = esd.store_id
    group by store_name
    order by counts desc;

![888ba6b58b394d889a0b7152e3ab76ca.png][]

#### 2.根据顾客消费价格找出最受欢迎的商店 ####

%hive
    use shopping;
    select store_name, sum(price) sums
    from transaction_details td
             join ext_store_details esd on td.store_id = esd.store_id
    group by store_name
    order by sums desc;

#### ![b739dc81dfff4afeabd9cd49732ac14b.png][] ####

#### 3.根据顾客交易情况找出最受欢迎的商店 ####

%hive
    use shopping;
    select store_name, count(td.store_id) counts
    from transaction_details td
             join ext_store_details esd on td.store_id = esd.store_id
    group by store_name
    order by counts desc;

#### ![8e64c06da2ed4f42a681b62d0299a4c9.png][] ####

#### 4.根据商店和唯一的顾客id获取最受欢迎的产品 ####

%hive
    use shopping;
    with basetb as (
        select store_id, product, count(distinct customer_id) counts
        from transaction_details
        group by store_id, product),
         basetb2 as (
             select store_id,
                    product,
                    counts,
                    rank() over (partition by store_id order by counts desc ) as rn
             from basetb)
    select store_name, product, counts
    from basetb2 tb2
             join ext_store_details esd on tb2.store_id = esd.store_id
    where rn = 1;

#### ![984f0b3b36384342b8b055024b1ae2e3.png][] ####

#### 5.获取每个商店的员工与顾客比 ####

%hive
    use shopping;
    with t1 as (select count(1) c1, store_id
                from transaction_details td
                group by td.store_id)
    select t1.store_id,
           esd.store_name,
           concat(substring(cast(esd.employee_number / t1.c1 as decimal(9, 8)) * 100.0, 0, 4), '%') proportion
    from t1
             join ext_store_details esd on t1.store_id = esd.store_id;

![ac7edcf33002407db62014bacdadd7a0.png][]

#### 6.按年和月计算每家店的收入 ####

%hive
    use shopping;
    select distinct *
    from (
             select store_id,
                    year(purchase_date)                                                     year,
                    sum(price) over (partition by year(purchase_date))                      sum_year,
                    month(purchase_date)                                                    month,
                    sum(price) over (partition by year(purchase_date),month(purchase_date)) sum_month
             from transaction_details
         ) tb;

![1babe3692c6e40ddb894b026536b1012.png][]

#### 7.按店铺制作总收益饼图 ####

%hive
    use shopping;
    select store_id,sum(price)
    from transaction_details
    group by store_id

![13eea8a98f814691ae50871d2a08ce19.png][]

#### 8.找出每个商店最繁忙的时间段 ####

%hive
    use shopping;
    with basetb1 as (
        select store_id,
               customer_id,
               purchase_time,
               case
                   when purchase_time like '%AM' then split(purchase_time, '\\s+')[0]
                   when purchase_time like '%PM' then concat_ws(':',
                                                                cast(`if`(
                                                                        (cast(split(purchase_time, ':')[0] as int) + 12) == 24,
                                                                        0,
                                                                        (cast(split(purchase_time, ':')[0] as int) + 12)) as string)
                       , split(split(purchase_time, ':')[1], '\\s+')[0])
                   else purchase_time
                   end time_format
        from transaction_details),
         basetb2 as (select store_id,
                            customer_id,
                            purchase_time,
                            (cast(split(time_format, ':')[0] as decimal(4, 2)) +
                             cast(split(time_format, ':')[1] as decimal(4, 2)) / 60) purchase_time_num
                     from basetb1),
         basetb3 as (select store_id,
                            customer_id,
                            purchase_time,
                            `if`(purchase_time_num > 5 and purchase_time_num <= 8, 'early morning',
                                 `if`(purchase_time_num > 8 and purchase_time_num <= 11, 'morning',
                                      `if`(purchase_time_num > 11 and purchase_time_num <= 13, 'noon',
                                           `if`(purchase_time_num > 13 and purchase_time_num <= 18, 'afternoon',
                                                `if`(purchase_time_num > 18 and purchase_time_num <= 22, 'evening',
                                                     'night'))))) as time_bucket
                     from basetb2)
    select esd.store_name,
           tb3.time_bucket,
           count(customer_id) counts
    from basetb3 tb3
             join ext_store_details esd on tb3.store_id = esd.store_id
    group by esd.store_name, time_bucket;

#### ![172e3b94268e48088eb8d2dc679e1e19.png][] ####

#### 9.找出每家店的忠实顾客 ####

购买次数大于5，认为他是忠实粉丝

%hive
    use shopping;
    select *
    from (
             select store_id, customer_id, count(1) counts
             from transaction_details
             group by store_id, customer_id) tb
    where tb.counts > 5;

![a16c0ec414e94617bcaa1542cfa549ea.png][]

####  ####

#### 10.根据每位员工的最高收入找出明星商店 ####

%hive
    use shopping;
    with base as
             (
                 select store_id, sum(price) s
                 from transaction_details
                 group by store_id
             )
    select base.store_id,
           base.s / store.employee_number en
    from base
             join ext_store_details store
                  on base.store_id = store.store_id
    order by en desc
    limit 1;

#### ![d640575f75584bfca56c34202deb8339.png][] ####

### Review分析 ###

#### 1.在ext\_store\_review中找出存在冲突的交易映射关系 ####

%hive
    use shopping;
    with basetb as (
        select row_number() over (partition by transaction_id) as row_number1, * from vm_store_review
    )
    select row_number1, a.transaction_id, a.store_id, b.store_id, a.review_score, b.review_score
    from basetb a
             join vm_store_review b on a.transaction_id = b.transaction_id
    where row_number1 > 1;

![3efbff8e5bb8439bb713f52a4b927aa8.png][]

#### 2.了解客户评价的覆盖率 ####

%hive
    use shopping;
    with t1 as (
        select count(1) c1
        from ext_store_review
        where review_score <> ''
    ),
         t2 as (
             select count(1) c2
             from ext_store_review
             where review_score = ''
         )
    select concat(cast((c1 - c2) / c1 * 100 as decimal(4, 2)), '%')  Coverage
    from t1 join t2;

![6a70251285f94cd6ad9399890a6f2c02.png][]

#### 3.根据评分了解客户的分布情况 ####

%hive
    use shopping;
    select concat(round(sum(case review_score when '1' then 1 else 0 end) / count(*) * 100, 2), '%') as one_score,
           concat(round(sum(case review_score when '2' then 1 else 0 end) / count(*) * 100, 2), '%') as two_score,
           concat(round(sum(case review_score when '3' then 1 else 0 end) / count(*) * 100, 2), '%') as three_score,
           concat(round(sum(case review_score when '4' then 1 else 0 end) / count(*) * 100, 2), '%') as four_score,
           concat(round(sum(case review_score when '5' then 1 else 0 end) / count(*) * 100, 2), '%') as five_score
    from ext_store_review;

![8f99cada459c4e41850d7f69c470cc85.png][]

#### 4.根据交易了解客户的分布情况 ####

根据总金额

%hive
    use shopping;
    select country,
           sum(price) sum_price
    from transaction_details td
             join ext_customer_details cd on td.customer_id = cd.customer_id
    group by cd.country;

![c2fe26fca5734748aa2836530bad7b73.png][]

#### 5.客户给出的最佳评价是否总是同一家门店 ####

%hive
    use shopping;
    select store_id, customer_id, count(customer_id) counts
    from transaction_details td
             join ext_store_review esr
                  on esr.transaction_id = td.transaction_id
    where esr.review_score = 5
    group by td.store_id, td.customer_id;

![3566e9b327cd4672b923a43c609bf6d1.png][]

[zeppelin]: https://blog.csdn.net/qq_57151931/article/details/129264398?spm=1001.2014.3001.5502
[932c2e68111646368e193880f8d39477.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/7c7ff45f92cc41bf80b9ffc03c5c13d6.png
[8a827367596748e79d2cc9c8762491f8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/c1006b5e98cc422a9db3192dc3dbb3b7.png
[af719821f00e49ca92ede9b77f0ddda6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/dfd66967a3d34ccc9758410b63465ee8.png
[d405d584094149c1b2eaf8fa25f5a189.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/99e1d44f90234b47847da9e0bb5889b5.png
[adc084af9e9941b0ac9448a1e609675b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/96404fd2e62749efb884d251e096a0e8.png
[fa723606603d4dc796780d57b93f51f9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/c1159dbd08714a0e819e196371148bd7.png
[2316c624f4c644279f6d410cf2c9a0d6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/734e0237bfc24808844e18d65d8924a5.png
[3355e03620d045878d3246738f1b3fc5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/d19cd5075f4b416985b021897d6a9c77.png
[a597a54c3f17457db915e972c18e4c0e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/8842960cbee14190acdbe75dbd5c783f.png
[3308f95edaa1412aab5f0eee15b4afb5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/841c1fc0d6644a85ba0197270e3453af.png
[640b9da9be9d4271b8a23ed0d7468d17.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/51a80758791d41aea1bd8d0dee228889.png
[57b1656fd28842a49ba157911975c236.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/03cd198ca18a4d01812665808b41f395.png
[a9c12b4463f04e62bacbd7f01272645d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/79abfe4c3e054ba78758e6ae4110f2dd.png
[6c4c1293227a4049918c509b2f9573e4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/3c759255131c4227888bcd59d58ca01b.png
[d656f7a7909d4ed9934bdb4383da1eaa.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/9dd0850703da429190b032481f4a3f9a.png
[d728cc6899694fdd9919484e4064a777.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/a349eef5e829458fb85457987ea7a312.png
[9ea571510b1e429da2210377ce88b32d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/823b4a37162f4a488b85da584763c118.png
[0e483b38c8884c9f84233e25fcec42e7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/9fbc8b441a724de59e444ed227306cba.png
[fe227ca5885d402d8e7eb9719403e28e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/a384825c55f4453092b5897f07cde01a.png
[7dfae58eb61c4992927c5addf53149f2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/927067cc8a6047fcb69f9ee363b9010d.png
[0c6b10ad85e349be9a9ed7c0a259ce68.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/a577957cd4894f55b6d5a989886644ca.png
[a85235b0f6b740858b7110a7b479f517.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/4138e97337fd451993c9198cb02aa4ca.png
[af684180588b49f0ba1fd946b9d33998.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/ccf37d8a8727451dba067b5282c0384c.png
[050fb1724254496abfe87413e810a24b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/3e7e2ead24804746b2134bed7a82635b.png
[32302e9b85124c8391e72eba948cd010.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/8f6c42329b044df9bfaa050258db9ea4.png
[a96833dd4ff84b2caad6616e30dc4799.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/0579d6bfcc8d4502ae7902ffe3672888.png
[b89a64b0b68f4634a6091acdf78d58b2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/ba6dfaaf3c04482fb036ae2e010c71a5.png
[09d95a2c564b46e09485d813e72a3e64.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/c523d744250c4e95924fd4dc8f16b2d4.png
[888ba6b58b394d889a0b7152e3ab76ca.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/7c67ae138f49429680f339353d9b178a.png
[b739dc81dfff4afeabd9cd49732ac14b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/3024f2440ec14fb7aaa12d8245c5a62c.png
[8e64c06da2ed4f42a681b62d0299a4c9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/125729edf8484bc6b9859fdd8d731bd2.png
[984f0b3b36384342b8b055024b1ae2e3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/29693ec1fbb14bd58803c3fb08e93fc5.png
[ac7edcf33002407db62014bacdadd7a0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/64ebee7865984e7bb5a7e28f894cc0ec.png
[1babe3692c6e40ddb894b026536b1012.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/e00b4c390d294078a8738584fc4be305.png
[13eea8a98f814691ae50871d2a08ce19.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/8d6778dd8b8e47849bd06d33619a69b6.png
[172e3b94268e48088eb8d2dc679e1e19.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/7a3b75a90c77422ba88922ddabcdf8ae.png
[a16c0ec414e94617bcaa1542cfa549ea.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/c10c22ef2d064d7db5087be8e65151f2.png
[d640575f75584bfca56c34202deb8339.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/593b98e6e52f47738d0b0ee22ea77ce6.png
[3efbff8e5bb8439bb713f52a4b927aa8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/f4119bf933874a3192a9a00884966315.png
[6a70251285f94cd6ad9399890a6f2c02.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/67113aa8038549c68ad2c888302df00a.png
[8f99cada459c4e41850d7f69c470cc85.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/32f665a850ac4f16bf31156b99a88559.png
[c2fe26fca5734748aa2836530bad7b73.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/fe5f75df681e45228009480b5eaed1c9.png
[3566e9b327cd4672b923a43c609bf6d1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/819e10651cae4548a53ae40038712bba.png