SQL面试题-记录自己每次面试遇到的题目1

1、订单汇总表order_jd存储了所有订单明细信息，一个订单有多个商品，记录多行，提取如下数据：

1）统计2019年7月1日当日有效的下单量

2）2019年7月1日有效下单用户中用户级别为“铜牌会员”的用户数量

3）统计2019年7月各个级别下单金额（amount）最多的前10个用户的订单量和订单金额

有效标志，1：有效；0：无效

用户账号

用户级别=56——铜牌

下单日期

回答：

因为以上题目没有给出表结构，所以需要自己建表，这是我根据题意自己建立的表，也不知道对不对。让我比较纠结的是订单中如果存在部分商品被退货，那么这算不算一条有效订单，还是退货的商品为无效，正常的商品为有效。

我目前是不按照商品是否有效来做，还是以一个订单无论有多少个商品，要不全有效，要不全无效来做。

1）思考思路为，count取有效下单量之和，但是一个订单可能有多条记录，那么就会有多条有效下单的记录，需要根据订单id进行去重再计数

select 下单日期,count(distinct 订单id) as 有效下单量
from order_jd
where 有效标志=1 and 下单日期='2019-07-01'
group by 下单日期

得到如下结果：

2）思考思路为，count获取用户数量，但是用户账号也是存在重复的，还是需要distinct去重

select 下单日期,用户级别,count(distinct 用户账号) as 用户数量
from order_jd
where 有效标志=1 and 下单日期='2019-07-01' and 用户级别=56
group by 下单日期,用户级别

得到结果如下：

3）用row_number () over () 窗口函数

select a.*
from
(
    select a.月份,
           a.用户级别,
           a.用户账号,
           a.订单量,
           a.订单金额,
           row_number () over (partition by a.月份,a.用户级别 order by a.订单金额 desc) as r
    from
    (
        select date_format(下单日期,'%Y-%m') as 月份,
               用户级别,
               用户账号,
               count(distinct 订单id) as 订单量,
               sum(订单金额) as 订单金额
        from order_jd
        where 下单日期>='2019-07-01' and 下单日期<='2019-07-31'
          and 有效标志=1
        group by date_format(下单日期,'%Y-%m'),用户级别,用户账号
    ) as a
) as a
where a.r<=10