首页 > 数据库 >尚硅谷SQL刷题练习

尚硅谷SQL刷题练习

时间：2023-02-19 23:13:08浏览次数：53  
标签：sku 10 order 2021 SQL date 硅谷 id 刷题

SQL 一：查询累积销量排名第二的商品

题目需求

查询订单明细表（order_detail）中销量（下单件数）排名第二的商品id，如果不存在返回null，如果存在多个排名第二的商品则需要全部返回。

期望结果如下：

sku_id
11

需要用到的表：

订单明细表：order_detail

order_detail_id (订单明细id) order_id (订单id) sku_id (商品id) create_date (下单日期) price (商品单价) sku_num (商品件数)
1 1 1 2021-09-30 2000.00 2
2 1 3 2021-09-30 5000.00 5
22 10 4 2020-10-02 6000.00 1
23 10 5 2020-10-02 500.00 24
24 10 6 2020-10-02 2000.00 5

题解：

select 
	nvl(sku_id,null) as sku_id
from (select 
     	sku_id, 
     	rank() over (order by sum(sku_num) desc) rk
     from order_detail
      group by sku_id
     ) t1
where rk = 2;

SQL 二：查询至少连续三天下单的用户

题目需求

查询订单信息表(order_info)中最少连续3天下单的用户id，期望结果如下：

user_id
101

订单信息表：order_info

order_id (订单id) user_id (用户id) create_date (下单日期) total_amount (订单金额)
1 101 2021-09-30 29000.00
10 103 2020-10-02 28000.00

题解：

select 
	user_id
from (
  SELECT
  	user_id, 
  	create_date, 
  	lead(create_date,2,null) over(partition by user_id order by create_date asc) lead_2_date,  -- 取后两行数据，如果没有就返回null
  	date_add(create_date,2) add_2_date -- 将当前时间 +2 相当于2天后
  from (
    select user_id, create_date from order_info group by user_id,create_date
  ) t1
) t2
where 
	lead_2_date = add_2_date -- 比较两天后的时间是否登录后两行数据
group by 
	user_id;

思路：按user_id分组和create_date升序 进行开窗，通过lead函数，取当前日期后两行的数据得到lead_2_date，通过date_add函数，对当前日期加2天得到add_2_date，两者相比较，如果相对即为连续3天下单

tip：为了避免统一用户当天多次消费，用 group by user_id,create_date 进行了去重操作

SQL 三：查询各品类销售商品的种类数及销量最高的商品

题目需求

从订单明细表(order_detail)统计各品类销售出的商品种类数及累积销量最好的商品，

期望结果如下：

category_id category_name sku_id name order_num sku_cnt
1 数码 2 手机壳 302 4
2 厨卫 8 微波炉 253 4
3 户外 12 遮阳伞 349 4

需要用到的表

订单明细表：order_detail

order_detail_id (订单明细id) order_id (订单id) sku_id (商品id) create_date (下单日期) price (商品单价) sku_num (商品件数)
1 1 1 2021-09-30 2000.00 2
2 1 3 2021-09-30 5000.00 5
22 10 4 2020-10-02 6000.00 1
23 10 5 2020-10-02 500.00 24
24 10 6 2020-10-02 2000.00 5

商品信息表：sku_info

sku_id (商品id) name (商品名称) category_id (分类id) from_date (上架日期) price (商品价格)
1 xiaomi 10 1 2020-01-01 2000
6 洗碗机 2 2020-02-01 2000
9 自行车 3 2020-01-01 1000

商品分类信息表：category_info

category_id (分类id) category_name (分类名称)
1 数码
2 厨卫
3 户外

题解：

select 
	category_id,
    category_name,
    sku_id,
    name,
    order_num,
    sku_cnt
from (
  SELECT
	category_id,
    category_name,
    sku_id,
    name,
    order_num,
    sku_cnt,
    rank() over(partition by category_id 
                order by order_num desc) as rk
  from (
    select 
      od.sku_id, 
      t1.category_id,
      t1.category_name,
      si.name,
      sum(od.sku_num) order_num,
      t1.sku_cnt
      from (
        select 
          ci.category_id,
          ci.category_name,
          count(*) as sku_cnt
		from 
         sku_info si 
        join category_info ci on si.category_id=ci.category_id
		group by 
         ci.category_id,
         ci.category_name
      ) t1 
      join sku_info si on t1.category_id = si.category_id
      join order_detail od on si.sku_id = od.sku_id 
      group by 
       od.sku_id,
       t1.category_id,
       t1.category_name,
       si.name,
       t1.sku_cnt
    ) t2	
  ) t3
 where rk = 1;

注意点：在hql中 group by中出现的字段，在select中要出现，要么是要查询的，要么用聚合函数包裹住

SQL 四：查询用户的累计消费金额及VIP等级

题目需求：

从订单信息表(order_info)中统计每个用户截止其每个下单日期的累积消费金额，以及每个用户在其每个下单日期的VIP等级。
用户vip等级根据累积消费金额计算，计算规则如下：
设累积消费总额为X，
若0=<X<10000,则vip等级为普通会员
若10000<=X<30000,则vip等级为青铜会员
若30000<=X<50000,则vip等级为白银会员
若50000<=X<80000,则vip为黄金会员
若80000<=X<100000,则vip等级为白金会员
若X>=100000,则vip等级为钻石会员

期望结果如下：

user_id (用户id) create_date (下单日期) sum_so_far <decimal(16,2)> (截至每个下单日期的累计下单金额) vip_level (每个下单日期的VIP等级)
101 2021-09-27 29000.00 青铜会员
101 2021-09-28 99500.00 白金会员
101 2021-09-29 142800.00 钻石会员
101 2021-09-30 143660.00 钻石会员
102 2021-10-01 171680.00 钻石会员
102 2021-10-02 177850.00 钻石会员
103 2021-10-02 69980.00 黄金会员
103 2021-10-03 75890.00 黄金会员
104 2021-10-03 89880.00 白金会员
105 2021-10-04 120100.00 钻石会员
106 2021-10-04 9390.00 普通会员
106 2021-10-05 119150.00 钻石会员
107 2021-10-05 69850.00 黄金会员
107 2021-10-06 124150.00 钻石会员
108 2021-10-06 101070.00 钻石会员
108 2021-10-07 155770.00 钻石会员
109 2020-10-08 24020.00 青铜会员
109 2021-10-07 153500.00 钻石会员
1010 2020-10-08 51950.00 黄金会员

需要用到的表：

订单信息表：order_info

order_id (订单id) user_id (用户id) create_date (下单日期) total_amount (订单金额)
1 101 2021-09-30 29000.00
10 103 2020-10-02 28000.00

题解：

select 
	user_id,
    create_date,
    sum_so_far,
    case
    	when sum_so_far < 10000 then "普通会员"
        when sum_so_far < 30000 then "青铜会员"
        when sum_so_far < 50000 then "白银会员"
        when sum_so_far < 80000 then "黄金会员"
        when sum_so_far < 100000 then "白金会员"
        else "钻石会员"
    end vip_level
from (
  select 
    user_id,
  	create_date,
    sum(total_amount) over(partition by user_id 
                           order by create_date) as sum_so_far 
  from 
  	order_info) t1
group by 
	user_id,
    create_date,
    sum_so_far;

要考虑一个用户可能同一天多次消费

SQL 五：查询首次下单后第二天连续下单的用户比率

题目需求：

从订单信息表(order_info)中查询首次下单后第二天仍然下单的用户占所有下单用户的比例，结果保留一位小数，使用百分数显示，

期望结果如下：

percentage
70.0%

需要用到的表：

订单信息表：order_info

order_id (订单id) user_id (用户id) create_date (下单日期) total_amount (订单金额)
1 101 2021-09-30 29000.00
10 103 2020-10-02 28000.00

题解：

select 
	concat(round(count(distinct t4.user_id) / count(distinct oi.user_id),1) * 100,'%') as percentage
from order_info oi,
(select 
	user_id
from (
  select 
      user_id,
      create_date,
      datediff(create_date,first_date) as diff
  from (
    select 
        user_id,
        create_date,
        first_value(create_date) over(partition by user_id 
                                      order by create_date) as first_date
    from (
      select 
        distinct user_id,create_date
      from order_info
    ) t1
  ) t2
)t3 
where t3.diff=1)t4;

先把每个订单中的用户首次交易日期找出来，然后通过2值计算，统计相差1的人数，最后计算百分比

SQL 六：每个商品销售首年的年份、销售数量和销售金额

题目需求：

从订单明细表(order_detail)统计每个商品销售首年的年份，销售数量和销售总额。

期望结果如下：

sku_id (商品id) year (销售首年年份) order_num (首年销量) order_amount <decimal(16,2)> (首年销售金额)
1 2020 2 4000.00
2 2020 26 260.00
3 2020 1 5000.00
4 2021 53 318000.00
5 2021 242 121000.00
6 2020 6 12000.00
7 2020 35 3500.00
8 2020 59 35400.00
9 2021 194 194000.00
10 2020 94 9400.00
11 2020 95 4750.00
12 2020 83 1660.00

需要用到的表：

订单明细表：order_detail

order_detail_id(订单明细id) order_id(订单id) sku_id(商品id) create_date(下单日期) price(商品单价) sku_num(商品件数)
1 1 1 2021-09-30 2000.00 2
2 1 3 2021-09-30 5000.00 5
22 10 4 2020-10-02 6000.00 1
23 10 5 2020-10-02 500.00 24
24 10 6 2020-10-02 2000.00

题解：

select 
	distinct first_value(sku_id) over(partition by sku_id order by year) sku_id, -- 取首先数据
    first_value(year) over(partition by sku_id order by year) year,
    first_value(order_num) over(partition by sku_id order by year) order_num,
    first_value(order_amount) over(partition by sku_id order by year) order_amount
from (
  select 
      sku_id,
      year(create_date) as year,
      sum(sku_num) as order_num,
      sum(price*sku_num) as order_amount
  from order_detail
  group by sku_id,year(create_date) -- 统计每年的总和
)t1

思考：SQL优化，不要用distinct

SQL 七：筛选去年总销量小于100的商品

题目需求：

从订单明细表(order_detail)中筛选出去年总销量小于100的商品及其销量，假设今天的日期是2022-01-10，不考虑上架时间小于一个月的商品

期望结果如下：

sku_id （商品id） name （商品名称） order_num （销量）
1 xiaomi 10 49
3 apple 12 35
4 xiaomi 13 53
6 洗碗机 26

需要用到的表：

商品信息表：sku_info

sku_id(商品id) name(商品名称) category_id(分类id) from_date(上架日期) price(商品价格)
1 xiaomi 10 1 2020-01-01 2000
6 洗碗机 2 2020-02-01 2000
9 自行车 3 2020-01-01 1000

订单明细表：order_detail

order_detail_id(订单明细id) order_id(订单id) sku_id(商品id) create_date(下单日期) price(商品单价) sku_num(商品件数)
1 1 1 2021-09-30 2000.00 2
2 1 3 2021-09-30 5000.00 5
22 10 4 2020-10-02 6000.00 1
23 10 5 2020-10-02 500.00 24
24 10 6 2020-10-02 2000.00 5

题解：

select 
	t1.sku_id,
    si.name,
    t1.order_num
from (
  select 
	sku_id,
    sum(sku_num) as order_num
  from order_detail
  where datediff('2022-01-10',create_date)>30 and -- 不考虑小于一个月的数据
  year(create_date)='2021'
  group by sku_id
  having order_num<100 -- 对统计后的数据进行筛选
) t1
join sku_info si on t1.sku_id = si.sku_id;

思考：SQL优化 -> 据说Hive中要少用Having函数

SQL 八：查询每日新用户数

题目需求

从用户登录明细表（user_login_detail）中查询每天的新增用户数，若一个用户在某天登录了，且在这一天之前没登录过，则任务该用户为这一天的新增用户。

期望结果如下：

login_date_first （日期） user_count （新增用户数）
2021-09-21 1
2021-09-22 1
2021-09-23 1
2021-09-24 1
2021-09-25 1
2021-09-26 1
2021-09-27 1
2021-10-04 2
2021-10-06 1

需要用到的表：

用户登录明细表：user_login_detail

user_id(用户id) ip_address(ip地址) login_ts(登录时间) logout_ts(登出时间)
101 180.149.130.161 2021-09-21 08:00:00 2021-09-27 08:30:00
102 120.245.11.2 2021-09-22 09:00:00 2021-09-27 09:30:00
103 27.184.97.3 2021-09-23 10:00:00 2021-09-27 10:30:00

题解：

select 
	login_date_first,
    count(user_id) as user_count
from (
  select 
	date_format(login_date_first,'yyyy-MM-dd') as login_date_first,
    user_id
  from (
    select 
      distinct user_id,
      first_value(login_ts) over(partition by user_id order by login_ts) login_date_first -- 统计首次登录用户，即每条新增用户
    from user_login_detail
  )t1
)t2
group by login_date_first;

思考：SQL优化 -> 不要使用distinct

SQL 九：统计每个商品的销量最高的日期

题目需求

从订单明细表（order_detail）中统计出每种商品销售件数最多的日期及当日销量，如果有同一商品多日销量并列的情况，取其中的最小日期。

期望结果如下：

sku_id （商品id） create_date （销量最高的日期） sum_num （销量）
1 2021-09-30 9
2 2021-10-02 5800
3 2021-10-05 9
4 2021-10-07 10
5 2021-10-03 47
6 2021-10-03 8
7 2021-10-05 58
8 2020-10-08 59
9 2021-10-01 45
10 2020-10-08 94
11 2020-10-08 95
12 2021-10-03 20400

需要用到的表：

订单明细表：order_detail

order_detail_id(订单明细id) order_id(订单id) sku_id(商品id) create_date(下单日期) price(商品单价) sku_num(商品件数)
1 1 1 2021-09-30 2000.00 2
2 1 3 2021-09-30 5000.00 5
22 10 4 2020-10-02 6000.00 1
23 10 5 2020-10-02 500.00 24
24 10 6 2020-10-02 2000.00 5

题解：

select 
	distinct sku_id,
    first_value(create_date) over(partition by sku_id order by sum_num desc,create_date asc) as create_date,
    first_value(sum_num) over(partition by sku_id order by sum_num desc,create_date asc) as sum_num -- 按要求开窗，取第一个值
from (
  select 
	sku_id,
    create_date,
    sum(sku_num) as sum_num -- 统计每个商品一天的销量
  from order_detail
  group by sku_id,create_date
) t1;

SQL优化

select 
	sku_id,
    create_date,
    sum_num
from(
  select 
	sku_id,
    create_date,
    sum_num,
    row_number() over(partition by sku_id order by sum_num desc,create_date asc) as rn
  from (
    select 
      sku_id,
      create_date,
      sum(sku_num) as sum_num -- 统计每个商品一天的销量
    from order_detail
    group by sku_id,create_date
  ) t1
)t2
where rn = 1;

SQL 十：查询销售件数高于品类平均数的商品

题目需求：

从订单明细表（order_detail）中查询累积销售件数高于其所属品类平均数的商品

期望结果如下：

sku_id name sum_num cate_avg_num
2 手机壳 6044 1546
5 破壁机 242 194
7 热水壶 252 194
8 微波炉 253 194
12 遮阳伞 20682 5373

需要用到的表：

商品信息表：sku_info

sku_id(商品id) name(商品名称) category_id(分类id) from_date(上架日期) price(商品价格)
1 xiaomi 10 1 2020-01-01 2000
6 洗碗机 2 2020-02-01 2000
9 自行车 3 2020-01-01 1000

订单明细表：order_detail

order_detail_id(订单明细id) order_id(订单id) sku_id(商品id) create_date(下单日期) price(商品单价) sku_num(商品件数)
1 1 1 2021-09-30 2000.00 2
2 1 3 2021-09-30 5000.00 5
22 10 4 2020-10-02 6000.00 1
23 10 5 2020-10-02 500.00 24
24 10 6 2020-10-02 2000.00

题解：

select
	sku_id,
    name,
    sum_num,
    floor(cate_avg_num) as cate_avg_num -- 4.floor()向下取整
from(
  select
	*,
    avg(sum_num) over(partition by category_id
                     rows between unbounded preceding and unbounded following) as cate_avg_num -- 按品类开一个全窗口，统计平均值
  from (
    select
      sku_id,
      name,
      category_id,
      sum(sku_num) as sum_num -- 2.统计该品类下的对应sku_id的销售件数
    from (
        select
            od.sku_id,
            si.name,
            si.category_id,
            od.sku_num
        from order_detail od
        left join sku_info si on od.sku_id = si.sku_id -- 1.进行表关联，获取对应品类信息
    ) t1
    group by sku_id,name,category_id
  )t2
)t3
where sum_num > cate_avg_num; -- 3.筛选大于对应平均值的数据

SQL 十一：用户注册、登录、下单综合统计

题目需求

从用户登录明细表（user_login_detail）和订单信息表（order_info）中查询每个用户的注册日期（首次登录日期）、总登录次数以及其在2021年的登录次数、订单数和订单总额。

期望结果如下：

user_id (用户id) register_date (注册日期) total_login_count (累积登录次数) login_count_2021 (2021年登录次数) order_count_2021 (2021年下单次数) order_amount_2021 (2021年订单金额) <decimal(16,2)>
101 2021-09-21 5 5 4 143660.00
102 2021-09-22 4 4 4 177850.00
103 2021-09-23 2 2 4 75890.00
104 2021-09-24 4 4 4 89880.00
105 2021-10-04 1 1 4 120100.00
106 2021-10-04 2 2 4 119150.00
107 2021-09-25 4 4 4 124150.00
108 2021-10-06 2 2 4 155770.00
109 2021-09-26 3 3 2 129480.00

需要用到的表：

用户登录明细表：user_login_detail

user_id(用户id) ip_address(ip地址) login_ts(登录时间) logout_ts(登出时间)
101 180.149.130.161 2021-09-21 08:00:00 2021-09-27 08:30:00
102 120.245.11.2 2021-09-22 09:00:00 2021-09-27 09:30:00
103 27.184.97.3 2021-09-23 10:00:00 2021-09-27 10:30:00

订单信息表：order_info

order_id (订单id) user_id (用户id) create_date (下单日期) total_amount (订单金额)
1 101 2021-09-30 29000.00
10 103 2020-10-02 28000.00

题解：

select DISTINCT
  t1.user_id,
  t1.register_date,
  t1.total_login_count,
  t2.login_count_2021,
  t3.order_count_2021,
  t3.order_amount_2021
from
  (
    select
      user_id,
      login_ts,
      date_format (
        first_value (login_ts) over (
          partition by
            user_id
          order by
            login_ts
        ),
        'yyyy-MM-dd'
      ) as register_date,
      count() over (
        PARTITION by
          user_id
      ) as total_login_count
    from
      user_login_detail
  ) t1
  inner join (
    select
      user_id,
      count(user_id) as login_count_2021
    from
      user_login_detail
    where
      year (login_ts) = '2021'
    group by
      user_id
  ) t2 on t1.user_id = t2.user_id
  inner join (
    select
      user_id,
      count(user_id) as order_count_2021,
      sum(total_amount) as order_amount_2021
    from
      order_info
    where
      year (create_date) = '2021'
    group by
      user_id
) t3 on t2.user_id = t3.user_id;
-------------------------------------------------------
select login.user_id,
    register_date,
    total_login_count,
    login_count_2021,
    order_count_2021,
    order_amount_2021
from (
     select user_id,
        -- 首次登陆（注册日期）
        min(date_format(login_ts, 'yyyy-MM-dd')) register_date,
        -- 累计登录次数
        count(1) total_login_count,
        -- 2021年登录次数
        count(if(year(login_ts) = '2021', 1, null)) login_count_2021
     from user_login_detail
     group by user_id
   ) login
     join
   (
     select user_id,
        -- 下单次数
        count(distinct(order_id)) order_count_2021,
        -- 订单金额
        sum(total_amount) order_amount_2021
     from order_info
     where year(create_date) = '2021'
     group by user_id
 	) oi
on login.user_id = oi.user_id

SQL 十二：查询指定日期的全部商品价格

题目需求

查询所有商品（sku_info表）截至到2021年10月01号的最新商品价格（需要结合价格修改表进行分析）

期望结果如下：

sku_id （商品id） price <decimal(16,2)> （商品价格）
1 2000.00
2 10.00
3 5000.00
4 6000.00
5 500.00
6 2000.00
7 100.00
8 600.00
9 1000.00
10 90.00
11 66.00
12 20.00

需要用到的表：

商品信息表：sku_info

sku_id(商品id) name(商品名称) category_id(分类id) from_date(上架日期) price(商品价格)
1 xiaomi 10 1 2020-01-01 2000
6 洗碗机 2 2020-02-01 2000
9 自行车 3 2020-01-01 1000

商品价格变更明细表：sku_price_modify_detail

sku_id(商品id) new_price(本次变更之后的价格) change_date(变更日期)
1 1900.00 2021-09-25
1 2000.00 2021-09-26
2 80.00 2021-09-29
2 10.00 2021-09-30

题解：

select
	si.sku_id,
    cast(if((si.from_date)>(t1.change_date),si.price,t1.new_price) as decimal(16,2)) as price -- 比较改动时间
from 
	sku_info si
left join (
  select
	sku_id,
    new_price,
    change_date,
    rank() over(partition by sku_id order by change_date desc) rk -- 为每行数据进行排序（采用row_number函数可能会好点）
  from sku_price_modify_detail
  where change_date<='2021-10-01' -- 筛选截至指定时间之前的数据
)t1
on t1.rk = 1 and si.sku_id = t1.sku_id; -- 筛选最新改动数据，并且进行连接操作

SQL 十三：即时订单比例

题目需求：

订单配送中，如果期望配送日期和下单日期相同，称为即时订单，如果期望配送日期和下单日期不同，称为计划订单。
请从配送信息表（delivery_info）中求出每个用户的首单（用户的第一个订单）中即时订单的比例，保留两位小数，以小数形式显示。

期望结果如下：

percentage <decimal(16,2)>
0.50

需要用到的表：

配送信息表：delivery_info

delivery_id （运单 id ） order_id （订单id） user_id （用户 id ） order_date （下单日期） custom_date （期望配送日期）
1 1 101 2021-09-27 2021-09-29
2 2 101 2021-09-28 2021-09-28
3 3 101 2021-09-29 2021-09-30

题解：

select
	cast(sum(order_status)/count(order_status) as decimal(16,2)) as percentage -- 统计计算比例
from (
  select
	if(order_date=custom_date,1,0) order_status -- 进行首单数据判断
  from (
    select
      user_id,
      order_date,
      custom_date,
      row_number() over(partition by user_id order by order_date) rn -- 按order_date排序，为每行数据赋值行号
    from 
      delivery_info
  )t1
  where rn=1 -- 筛选出行号为1的数据就是用户首单数据了
) t2

SQL 十四：向用户推荐朋友收藏的商品

题目需求

现需要请向所有用户推荐其朋友收藏但是用户自己未收藏的商品，请从好友关系表（friendship_info）和收藏表（favor_info）中查询出应向哪位用户推荐哪些商品。

期望结果如下：

user_id （用户id） sku_id （应向该用户推荐的商品id）
101 2
101 4
101 7
101 9
101 8
101 11
101 1

需要用到的表：

好友关系表：friendship_info

user1_id（用户1 id） user2_id（用户2 id）
101 1010
101 108
101 106

收藏表：favor_info

user_id(用户id) sku_id(商品id) create_date(收藏日期)
101 3 2021-09-23
101 12 2021-09-23
101 6 2021-09-25

题解：

SELECT 
	t1.user1_id user_id,t2.sku_id
FROM 
	friendship_info t1 
JOIN favor_info t2
ON t1.user2_id=t2.user_id --直接获得好友收藏记录
WHERE 
	concat(t1.user1_id,t2.sku_id) --将好友收藏的sku_id与自己关联
    NOT IN ( -- 筛选出好友收藏了，自己没收藏的记录
      SELECT concat(user_id,sku_id) --查找的是自己的收藏记录
      FROM favor_info
    )
GROUP BY t1.user1_id,t2.sku_id;

有点绕

SQL 十五：查询所有用户的连续登录两天及以上的日期区间

题目需求

从登录明细表（user_login_detail）中查询出，所有用户的连续登录两天及以上的日期区间，以登录时间（login_ts）为准。

期望结果如下：

user_id (用户id) start_date (开始日期) end_date (结束日期)
101 2021-09-27 2021-09-30
102 2021-10-01 2021-10-02
106 2021-10-04 2021-10-05
107 2021-10-05 2021-10-06

需要用到的表：

登录明细表：user_login_detail

user_id(用户id) ip_address(ip地址) login_ts(登录时间) logout_ts(登出时间)
101 180.149.130.161 2021-09-21 08:00:00 2021-09-27 08:30:00
102 120.245.11.2 2021-09-22 09:00:00 2021-09-27 09:30:00
103 27.184.97.3 2021-09-23 10:00:00 2021-09-27 10:30:00

题解：

select
	user_id,
    min(login_ts) as start_date, -- 取最小日期数据
    max(date_next) as end_date -- 取最大日期数据
from (
  select 
	user_id,
    login_ts,
    date_next,
    datediff(date_next,login_ts) as diff_date_next
  from (
    select 
      user_id,
      login_ts,
      lead(login_ts,1,login_ts) over(partition by user_id order by login_ts)as date_next
    from (
      select 
        distinct user_id,
        date_format(login_ts,'yyyy-MM-dd') as login_ts
      from user_login_detail
    )t1 -- 通过日期转换函数和distinct，对数据进行初步的过滤清洗
  )t2 -- 通过lead()取后一行的数据，不存在就当前行数据=》相邻日期
)t3 -- 通过datediff()计算相邻日期与当前日期的差值
where diff_date_next = 1 -- 筛选出相邻日期差值为1 的数据
group by user_id -- 按id分组

或者

select 
	distinct user_id,
	start_date,
	end_date
from(
    select 
        user_id,
        first_value(login_date)over(partition by user_id ,flag rows between unbounded preceding and unbounded following) start_date,
        last_value(login_date)over(partition by user_id ,flag rows between unbounded preceding and unbounded following) end_date,
        count(*)over(partition by user_id ,flag) flag2 -- 按user_id和flag分组，统计条数=》为连续登录的天数
    from(
        select 
            user_id,
            login_date,
            date_sub(login_date,rank()over(partition by user_id order by login_date asc)) flag -- 当前日期减去行号=》如果为连续登录，那么值应该是相同的
    	from(
            select 
                distinct user_id,
                date_format(login_ts,"yyyy-MM-dd") login_date
    		from user_login_detail )t1
    )t2
)t3
where flag2 >= 2; -- 筛选出连续登录大于2的数据

SQL 十六：男性和女性每日的购物总金额统计

题目需求

从订单信息表（order_info）和用户信息表（user_info）中，分别统计每天男性和女性用户的订单总金额，如果当天男性或者女性没有购物，则统计结果为0。

期望结果如下：

create_date （日期） total_amount_male <decimal(16,2)> （男性用户总金额） total_amount_female <decimal(16,2)> （女性用户总金额）
2020-10-08 51950.00 24020.00
2021-09-27 29000.00 0.00
2021-09-28 70500.00 0.00
2021-09-29 43300.00 0.00
2021-09-30 860.00 0.00
2021-10-01 0.00 171680.00
2021-10-02 0.00 76150.00
2021-10-03 89880.00 5910.00
2021-10-04 9390.00 120100.00
2021-10-05 109760.00 69850.00
2021-10-06 101070.00 54300.00
2021-10-07 54700.00 129480.00

需要用到的表：

订单信息表：order_info

order_id (订单id) user_id (用户id) create_date (下单日期) total_amount (订单金额)
1 101 2021-09-30 29000.00
10 103 2020-10-02 28000.00

用户信息表：user_info

user_id(用户id) gender(性别) birthday(生日)
101 男 1990-01-01
102 女 1991-02-01
103 女 1992-03-01
104 男 1993-04-01

题解：

SELECT
  create_date,
  sum(IF (t2.gender = '男', t1.total_amount, 0.0)) total_amount_male,
  sum(IF (t2.gender = '女', t1.total_amount, 0.0)) total_amount_female
FROM
  order_info t1
  JOIN user_info t2 ON t1.user_id = t2.user_id
GROUP BY
  create_date

SQL 十七：订单金额趋势分析

题目需求

查询截止每天的最近3天内的订单金额总和以及订单金额日平均值，保留两位小数，四舍五入。

期望结果如下：

create_date (日期) total_3d <decimal(16,2)> (最近3日订单金额总和) avg_3d <decimal(16,2)> (最近3日订单金额日平均值)
2020-10-08 75970.00 75970.00
2021-09-27 104970.00 52485.00
2021-09-28 175470.00 58490.00
2021-09-29 142800.00 47600.00
2021-09-30 114660.00 38220.00
2021-10-01 215840.00 71946.67
2021-10-02 248690.00 82896.67
2021-10-03 343620.00 114540.00
2021-10-04 301430.00 100476.67
2021-10-05 404890.00 134963.33
2021-10-06 464470.00 154823.33
2021-10-07 519160.00 173053.33

需要用到的表：

订单信息表：order_info

order_id (订单id) user_id (用户id) create_date (下单日期) total_amount (订单金额)
1 101 2021-09-30 29000.00
10 103 2020-10-02 28000.00

题解：

with t1 as (
  select 
  	create_date,
  	sum(total_amount) as total_amount
  from 
  	order_info
  group by create_date
) -- 创建一个统计好每天订单总金额的视图
select 
	create_date,
	sum(total_amount) as total_3d, -- 直接聚合统计
    cast(avg(total_amount) as decimal(16,2)) as avg_3d
from (
  select 
	t1.create_date,
    t2.total_amount
  from 
      t1 inner join t1 t2
      on t2.create_date<=t1.create_date and t1.create_date <= date_add(t2.create_date,2) -- 通过该条件筛选出了截至每天的最近3填内的订单金额
    -- t2.create_date<=t1.create_date确保了是过去数据
    -- t1.create_date <= date_add(t2.create_date,2)确保了是最近3天的数据
)t3
group by create_date;

SQL 十八：购买过商品1和商品2但是没有购买商品3的顾客

题目需求

从订单明细表(order_detail)中查询出所有购买过商品1和商品2，但是没有购买过商品3的用户，

期望结果如下：

user_id
103
105

需要用到的表：

订单信息表：order_info

order_id (订单id) user_id (用户id) create_date (下单日期) total_amount (订单金额)
1 101 2021-09-30 29000.00
10 103 2020-10-02 28000.00

订单明细表：order_detail

order_detail_id(订单明细id) order_id(订单id) sku_id(商品id) create_date(下单日期) price(商品单价) sku_num(商品件数)
1 1 1 2021-09-30 2000.00 2
2 1 3 2021-09-30 5000.00 5
22 10 4 2020-10-02 6000.00 1
23 10 5 2020-10-02 500.00 24
24 10 6 2020-10-02 2000.00 5

题解：

select 
	user_id
from (
  select 
	oi.user_id,
    collect_set(sku_id) as sku_id_set -- 通过集合封装
  from
      order_info oi left join order_detail od
      on oi.order_id = od.order_id
  group by oi.user_id
) t1 -- 统计每个user_id的购买sku_id记录
where array_contains(sku_id_set,'1') 
    and array_contains(sku_id_set,'2') 
    and not array_contains(sku_id_set,'3') -- array_contains()函数是用来判断集合是否存在某个元素的，返回的是boolean类型

SQL 十九：统计每日商品1和商品2销量的差值

题目需求

从订单明细表（order_detail）中统计每天商品1和商品2销量（件数）的差值（商品1销量-商品2销量）

期望结果如下：

create_date diff
2020-10-08 -24
2021-09-27 2
2021-09-30 9
2021-10-01 -10
2021-10-02 -5800
2021-10-03 4
2021-10-04 -55
2021-10-05 -30
2021-10-06 -49
2021-10-07 -40

需要用到的表：

订单明细表：order_detail

order_detail_id(订单明细id) order_id(订单id) sku_id(商品id) create_date(下单日期) price(商品单价) sku_num(商品件数)
1 1 1 2021-09-30 2000.00 2
2 1 3 2021-09-30 5000.00 5
22 10 4 2020-10-02 6000.00 1
23 10 5 2020-10-02 500.00 24
24 10 6 2020-10-02 2000.00 5

题解：

select 
	create_date,
    sku1_num - sku2_num as diff
from (
  select
	create_date,
    sum(if(sku_id=1,sku_num,0)) as sku1_num, -- 统计sku_id为1的下单件数
    sum(if(sku_id=2,sku_num,0)) as sku2_num -- 统计sku_id为2的下单件数
  from 
      order_detail
  where sku_id = 1 or sku_id = 2 -- 筛选出只有sku_id为1和2的信息
  group by create_date
  order by create_date
) t1

SQL 二十：询出每个用户的最近三笔订单

题目需求

从订单信息表（order_info）中查询出每个用户的最近三个下单日期的所有订单

期望结果如下：

user_id order_id create_date
101 2 2021-09-28
101 3 2021-09-29
101 4 2021-09-30
102 5 2021-10-01
102 6 2021-10-01
102 8 2021-10-02
103 9 2021-10-02
103 10 2021-10-02
103 12 2021-10-03
104 13 2021-10-03
104 14 2021-10-03
104 15 2021-10-03
105 17 2021-10-04
105 18 2021-10-04
105 19 2021-10-04
106 22 2021-10-05
106 23 2021-10-05
106 24 2021-10-05
107 25 2021-10-05
107 27 2021-10-06
107 28 2021-10-06
108 29 2021-10-06
108 31 2021-10-07
108 32 2021-10-07
109 33 2021-10-07
109 35 2021-10-08
109 36 2021-10-08
1010 37 2021-10-08
1010 38 2021-10-08
1010 39 2020-10-08

需要用到的表：

订单信息表：order_info

order_id (订单id) user_id (用户id) create_date (下单日期) total_amount (订单金额)
1 101 2021-09-30 29000.00
10 103 2020-10-02 28000.00

题解：

select 
	user_id,
	order_id,
	create_date
from (
    select 
        user_id,
        order_id,
        create_date,
        dense_rank() over(partition by user_id order by create_date desc) as dr
    from 
        order_info
)t1
where dr<=3
order by user_id,create_date;

......

标签：sku,10,order,2021,SQL,date,硅谷,id,刷题
From： https://www.cnblogs.com/Mr-Sponge/p/17135882.html

相关文章

  • sqlalchemy_one2many_一对多
    sqlalchemy_one_to_many_relationship-main/delete.pyfrommainimportPost,User,sessionprint(session.query(Post).all())print(session.query(User).all())#......
  • 算法刷题-放苹果-JAVA
    0x00引言为获取一个良好的算法思维，以及不再成为一个脚本小子，争取每天一道算法题，培养自己的逻辑思维，温顾各类型语言语法知识。题解只写自己理解的解法，其他解法不再增加。......
  • 算法刷题-尼科彻斯定理-JAVA
    0x00引言为获取一个良好的算法思维，以及不再成为一个脚本小子，争取每天一道算法题，培养自己的逻辑思维，温顾各类型语言语法知识。题解只写自己理解的解法，其他解法不再增加。......
  • SQL 根据指定的列找出约束名
    为了避免录入错误的数据，我们往往会给“列”加上各种约束条件。运行以下代码能够实现“反向”的功能：给出表名和列名，就能找出约束名。以下例句是找出给列“Class”设置的“默......
  • 算法刷题-字符串最后一个单词的长度-JAVA
    0x00引言为获取一个良好的算法思维，以及不再成为一个脚本小子，争取每天一道算法题，培养自己的逻辑思维，温顾各类型语言语法知识。题解只写自己理解的解法，其他解法不再增加。......
  • mysql 如何查看表的大小
    mysql查看表的大小方法：1.查看所有数据库容量大小selecttable_schemaas'数据库',sum(table_rows)as'记录数',sum(truncate(data_length/1024/1024,2))as'数据......
  • 常见SQL注入手法总结与技巧(一)
    前言SQL是StructuredQueryLanguage的缩写，中文译为“结构化查询语言”。SQL是一种计算机语言，用来存储、检索和修改关系型数据库中存储的数据。sql注入是最为常见也是破......
  • sqlit 自增id为null
    CREATETABLEproejct(idUNSIGNEDINTEGERAUTO_INCREMENT,nameVARCHAR(50)NOTNULL,typeCHAR(10)NOTNULL,PRIMARYKEY(id))以上是表的定义，不知道为......
  • mysql事务
    分为显示（有明显的开启和结束的标记）和隐示事务select@@autocommit：为0的时候为禁用setautocommit=0开启事务的方式starttransaction；————；......
  • 算法刷题-无重复字符的最长子串(哈希表、字符串)、数字 1 的个数(递归、数学)、对称二
    无重复字符的最长子串(哈希表、字符串)给定一个字符串，请你找出其中不含有重复字符的**最长子串**的长度。示例1:输入:s="abcabcbb"输出:3解释:因为无重复字符......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99