作业：(满分：100分)

    一、建表、统计与程序异常分析(60分)

    某公司地区业务有两张门店相关的表（数据见：homework.xlsx）：

    一张为门店信息表：

    表名：area_table

    字段内容 字段名 字段类型 长度

    店铺id store_id varchar 10

    店铺位置 area varchar 20

    店长id leader_id int

    第二张为门店销售信息表：

    表名：store_table

    字段内容 字段名 字段类型 长度

    店铺id store_id varchar 10

    订单id order_id varchar 12

    销量 sales_volume int

    销售日期 salesdate date

    1.建表：(20分)

    为以上两张表建立Mysql表格，并导入数据

    2.统计(20分)

    2.1.统计每日每个店铺的销量(10分)

    2.2.统计每日每个区域的销量(10分)

    3.程序异常(20分)

    本公司有报表需要展示区域“alpha”的前一日所有店铺销量，示例如下：

    店铺 昨日销量

    store1 124

    store2 532

    store3 325

    store4 665

    某天由于“store3”停业，当日“store3”在store_table表中没有数据。

    当第二天需要出昨日报表数据时。

    报表原本预想展示的数据如下：

    店铺 昨日销量

    store1 168

    store2 480

    store3 0

    store4 720

    但是当日报表实际展示为：

    店铺 昨日销量

    store1 168

    store2 480

    store4 720

    后台代码为：

    select

    a.store_id

    ,IFNULL(sum(sales_volume),0) as sales_volumes

    from area_table a

    left join

    store_table b

    on a.store_id=b.store_id

    where a.area=’alpha’

    and b.salesdate=DATE_SUB(curdate(),INTERVAL 1 DAY)

    GROUP BY a.store_id

    order by a.store_id;

    注： DATE_SUB(curdate(),INTERVAL 1 DAY) 该函数意为：求出昨天的日期；

    实现过程：1.使用curdate()求出当天日期；

    1.             2.使用DATE_SUB(当天日期,INTERVAL 1 DAY)实现当天日期减一天的日期，即昨日日期；

    3.1.请分析代码为什么无法执行出期望结果？(10分)

    3.2.请修改代码，产出报表的设想结果。(10分)

    二、SQL语句阅读能力(40分)

    能够读懂业务SQL是一个数据分析师的基本技能。掌握SQL的执行顺序，才能正确地读懂SQL语句。

    执行顺序

    With as —> FROM(JOIN 部分一般先左后右) —> WHERE —> GROUP BY —> HAVING —> SELECT —> ORDER BY

    每个部分中，先执行子查询内部，再将子查询看做一个整体，按普通顺序执行。

    多个子查询嵌套，最先执行最内部的子查询。

    — 1.请按如下方式写出SQL语句的执行顺序

    — 例子：

    select uclass,sum(money) as uclass_money

    from user_table a

    join

    (select

    uid

    ,money

    from user_earn_table

    where money is not null

    ) b

    on a.uid = b.uid

    group by uclass

    order by uclass_money desc

    limit 100;

    拆解示意：

    1.from user_earn_table

    2.where money is not null

    3.select

    uid,money

    4.from user_table a

    5.join

    (select

    uid,money

    from user_earn_table

    where money is not null

    ) b

    on a.uid = b.uid

    6.group by uclass

    7.select uclass,sum(money) as uclass_money

    8.order by uclass_money desc

    9.limit 100;

    — 请拆解如下语句：

    — 本语句为某视频网站公司对于k-pop业务线的各渠道用户、新增用户数据抽取语句

    with temp_active_hour_table_kps as

    (

    select

    a0.dt

    ,product_id

    ,mkey

    ,substr(FROM_UNIXTIME(st_time),12,2) as hour

    ,a0.device_id

    from

    (select

    dt

    ,product_id

    ,st_time

    ,device_id

    from kps_dwd.kps_dwd_dd_view_user_active

    where dt=’${dt_1}’

    ) a0

    left join

    (select

    dt

    ,mkey

    ,device_id

    from kps_dwd.kps_dwd_dd_user_channels

    where dt=’${dt_1}’

    ) a1

    on a0.device_id = a1.device_id

    )

    select

    1. dt
    3. ,product
    5. ,product_id
    7. ,a1.mkey
    9. ,name_cn
    11. ,hour
    13. ,status
    15. ,dau
    17. ,new
    19. from 
    21. (select     
    23.     dt
    25.     ,'K-pop' as product
    27.     ,product_id
    29.     ,mkey
    31.     ,hour
    33.     ,status
    35.     ,count(distinct a.device_id) as dau
    37.     ,count(distinct if(b.device_id is not null,a.device_id,null)) as new
    39.     from 
    41.     (select
    43.         dt
    45.         ,product_id
    47.         ,mkey
    49.         ,hour
    51.         ,device_id
    53.         ,'active' as status
    55.         from temp_active_hour_table_kps
    57.         group by dt,mkey,product_id,device_id,hour
    59.         union all
    61.         select
    63.         dt
    65.         ,product_id
    67.         ,mkey
    69.         ,min(hour) as hour
    71.         ,device_id
    73.         ,'first' as status
    75.         from temp_active_hour_table_kps
    77.         group by dt,mkey,product_id,device_id
    79.     ) a 
    81.     left join 
    83.     (
    85.         select 
    87.         dt
    89.         ,device_id
    91.         from kps_dwd.kps_dwd_dd_fact_view_new_user
    93.         where dt='${dt_1}'
    95.         group by dt,device_id
    97.     )b on a.dt=b.dt and a.device_id = b.device_id
    99.     group by dt
    101.     ,product_id
    103.     ,mkey
    105.     ,hour
    107.     ,status
    109.     ) a1 
    111. left join 
    113. asian_channel.dict_lcmas_channel b1 
    115. on a1.mkey = b1.mkey;

    — 拆解步骤如下，请将下列横线处拆解内容补充完整

    — 以下题4个空，每空10分

    1.from kps_dwd.kps_dwd_dd_view_user_active

    2.where dt=’${dt_1}’

    3.select

    1.     dt
    3.     ,product_id
    5.     ,st_time
    7.     ,device_id

    4.from

    1. (……) a0

    5.left join

    1. (select 
    3.     dt
    5.     ,mkey
    7.     ,device_id 
    9.     from kps_dwd.kps_dwd_dd_user_channels 
    11.     where dt='${dt_1}'
    13.     ) a1
    15. on a0.device_id = a1.device_id

    6.__

    7.select

    1.         dt
    3.         ,product_id
    5.         ,mkey
    7.         ,hour
    9.         ,device_id
    11.         ,'active' as status
    13.         from temp_active_hour_table_kps
    15.         group by dt,mkey,product_id,device_id,hour

    8.__

    9.from

    1.     (……) a

    10.__

    11.from

    1. (select     
    3.     dt
    5.     ,'K-pop' as product
    7.     ,product_id
    9.     ,mkey
    11.     ,hour
    13.     ,status
    15.     ,count(distinct a.device_id) as dau
    17.     ,count(distinct if(b.device_id is not null,a.device_id,null)) as new
    19.     ……
    21.     group by dt
    23.     ,product_id
    25.     ,mkey
    27.     ,hour
    29.     ,status
    31.     ) a1

    12.__

    13.select

    1. dt
    3. ,product
    5. ,product_id
    7. ,a1.mkey
    9. ,name_cn
    11. ,hour
    13. ,status
    15. ,dau
    17. ,new