SQL面试题挑战08：补全缺失日的月销售累计

SQL面试题挑战08：补全缺失日的月销售累计

问题

问题：现有一张员工的销售记录表，表样式如下图。现在需要统计每个员工在2021年10月份，截止到每天的月累计销售额。注意：存在有的员工在某几天是没有销售记录的。

原来表：

sale_date	emp_id	emp_name	sale_amount
2021-10-02	101	张三	100
2021-10-03	101	张三	300
2021-10-05	101	张三	400
2021-10-01	102	李四	111
2021-10-03	102	李四	222
2021-10-08	102	李四	333

补全后

sale_date	emp_id	emp_name	sale_amount
2021-10-01	101	张三	0
2021-10-02	101	张三	100
2021-10-03	101	张三	400
2021-10-04	101	张三	400
2021-10-05	101	张三	800
2021-10-06	101	张三	800
…	…	…	…
2021-10-01	102	李四	111
2021-10-02	102	李四	111
2021-10-03	102	李四	333
…	…	…	…

建表语句

drop table if exists sales;
create table sales(
    sale_date date,
    emp_id varchar(20),
    emp_name varchar(20),
    sale_amount int
);
insert into sales values
('2021-10-02','101','张三',100),
('2021-10-03','101','张三',300),
('2021-10-05','101','张三',400),
('2021-10-01','102','李四',111),
('2021-10-03','102','李四',222),
('2021-10-08','102','李四',333)

解答

思路：

由于缺乏一些日期，所以我们先要把日期进行补全，使用递归查询生成日期范围，并和姓名连接，然后和sales左连接即可。

完整查询如下

with recursive dateList as (
    select '2021-10-01' as sale_date
    union all
    select sale_date + interval 1 day
    from dateList
    where sale_date < '2021-10-31'
),tmp as(
    select
        sale_date,
        emp_id,
        emp_name
    from
        dateList
        left join(
            select
                emp_id,
                emp_name
            from
                sales
            group by
                emp_id,
                emp_name
        ) t0 on 1

)

 select
    t.sale_date,
    t.emp_id,
    t.emp_name,
    sum(ifnull(sale_amount,0))over (partition by t.emp_id order by t.sale_date)sale_amount
 from
     tmp t
    left join sales s on t.emp_id=s.emp_id and t.sale_date=s.sale_date

注：在Hive中可以用lateral view posexplode补全日期。