MySQL高阶2020-无流量的账户数

目录

题目

准备数据

分析数据

总结

---

题目

编写SQL查询以报告在 2021 购买订阅但没有任何会话的帐 户数。

准备数据

Create table If Not Exists Subscriptions (account_id int, start_date date, end_date date)
Create table If Not Exists Streams (session_id int, account_id int, stream_date date)
    Truncate table Subscriptions
    insert into Subscriptions (account_id, start_date, end_date) values ('9', '2020-02-18', '2021-10-30')
    insert into Subscriptions (account_id, start_date, end_date) values ('3', '2021-09-21', '2021-11-13')
    insert into Subscriptions (account_id, start_date, end_date) values ('11', '2020-02-28', '2020-08-18')
    insert into Subscriptions (account_id, start_date, end_date) values ('13', '2021-04-20', '2021-09-22')
    insert into Subscriptions (account_id, start_date, end_date) values ('4', '2020-10-26', '2021-05-08')
    insert into Subscriptions (account_id, start_date, end_date) values ('5', '2020-09-11', '2021-01-17')
    Truncate table Streams
    insert into Streams (session_id, account_id, stream_date) values ('14', '9', '2020-05-16')
    insert into Streams (session_id, account_id, stream_date) values ('16', '3', '2021-10-27')
    insert into Streams (session_id, account_id, stream_date) values ('18', '11', '2020-04-29')
    insert into Streams (session_id, account_id, stream_date) values ('17', '13', '2021-08-08')
    insert into Streams (session_id, account_id, stream_date) values ('19', '4', '2020-12-31')
    insert into Streams (session_id, account_id, stream_date) values ('13', '5', '2021-01-05')

subscriptions表

streams表

分析数据

with t1 as (
    select s1.account_id,start_date,end_date,stream_date
    from subscriptions s1
        join streams s2 on s1.account_id = s2.account_id
)select count(distinct account_id) accounts_count from t1
where (year(start_date) = 2021 or year(end_date) = 2021) and year(stream_date) != 2021;

总结

就是判断是否是2021年的,使用到了日期函数year()

使用日期函数的前提是字段类型必须是日期型,如果是字符串型,必须使用str()提取年月日,例str(start_date,4)='2021',注意,字符串年份需要加上引号.