LeetCode_sql_day11（626.换座位，1907按分类统计薪水，1321餐馆营业额变化增长)-CSDN博客

本文链接：https://blog.csdn.net/weixin_54353942/article/details/140855758

描述：1907.按分类统计薪水

描述：626.换座位

编写解决方案来交换每两个连续的学生的座位号。如果学生的数量是奇数，则最后一个学生的id不交换。

按 id 升序返回结果表。

数据准备：

Create table If Not Exists Seat (id int, student varchar(255))
Truncate table Seat
insert into Seat (id, student) values ('1', 'Abbot')
insert into Seat (id, student) values ('2', 'Doris')
insert into Seat (id, student) values ('3', 'Emerson')
insert into Seat (id, student) values ('4', 'Green')
insert into Seat (id, student) values ('5', 'Jeames')

分析：

分析题目需要判断条件然后更改id，考虑使用case when函数

判断奇偶数：id %2 =1

但是要考虑最后一个id是否为奇数故嵌套了一个子查询 id = （select count(id） from Seat)

最后根据id排序

代码：

select
    case when id % 2 = 1 and id != (select count(id) from Seat) then id +1
    when id % 2 = 1 and id = (select count(id) from Seat) then id
    when id % 2 = 0 then id -1 end id,
    student
from Seat
order by id;

总结：

更改id的值时

使用case when格式为在本题中

case when 条件 then 列+1

描述：1907.按分类统计薪水

查询每个工资类别的银行账户数量。工资类别如下：

"Low Salary"：所有工资 严格低于 20000 美元。
"Average Salary"：包含范围内的所有工资 [$20000, $50000] 。

"High Salary"：所有工资 严格大于 50000 美元。

结果表必须包含所有三个类别。如果某个类别中没有帐户，则报告 0 。

按 任意顺序 返回结果表。

数据准备：

Create table If Not Exists Accounts (account_id int, income int)

Truncate table Accounts

insert into Accounts (account_id, income) values ('3', '108939')

insert into Accounts (account_id, income) values ('2', '12747')

insert into Accounts (account_id, income) values ('8', '87709')

insert into Accounts (account_id, income) values ('6', '91796')

分析：

当你明白
select 'Low Salary',count(*) from accounts;
显示结果时

就可以考虑通过不断的连接得到最后的数据

代码：

select 'Low Salary' category, count(if(income < 20000, 1, null)) accounts_count
from accounts
union all
select 'Average Salary', count(if(income between 20000 and 50000, 1, null))
from accounts
union all
select 'High Salary', count(if(income > 50000, 1, null))
from accounts;

总结：

select aa,bb from 表 aa可以不是表中的字段，

逗号连接的字段以行连接

union、union all 连接的字段以列连接

描述：1321.餐馆营业额变化增长

你是餐馆的老板，现在你想分析一下可能的营业额变化增长（每天至少有一位顾客）。

计算以 7 天（某日期 + 该日期前的 6 天）为一个时间段的顾客消费平均值。average_amount 要 保留两位小数。

结果按 visited_on 升序排序。

数据准备：

Create table If Not Exists Customer (customer_id int, name varchar(20), visited_on date, amount int)

Truncate table Customer

insert into Customer (customer_id, name, visited_on, amount) values ('1', 'Jhon', '2019-01-01', '100')

insert into Customer (customer_id, name, visited_on, amount) values ('2', 'Daniel', '2019-01-02', '110')

insert into Customer (customer_id, name, visited_on, amount) values ('3', 'Jade', '2019-01-03', '120')

insert into Customer (customer_id, name, visited_on, amount) values ('4', 'Khaled', '2019-01-04', '130')

insert into Customer (customer_id, name, visited_on, amount) values ('5', 'Winston', '2019-01-05', '110')

insert into Customer (customer_id, name, visited_on, amount) values ('6', 'Elvis', '2019-01-06', '140')

insert into Customer (customer_id, name, visited_on, amount) values ('7', 'Anna', '2019-01-07', '150')

insert into Customer (customer_id, name, visited_on, amount) values ('8', 'Maria', '2019-01-08', '80')

insert into Customer (customer_id, name, visited_on, amount) values ('9', 'Jaze', '2019-01-09', '110')

insert into Customer (customer_id, name, visited_on, amount) values ('1', 'Jhon', '2019-01-10', '130')

insert into Customer (customer_id, name, visited_on, amount) values ('3', 'Jade', '2019-01-10', '150')

分析：

法一：

①先找到每天总收入
select visited_on,
                   sum(amount)total
            from Customer
            group by visited_on
②通过窗口函数sum() ,avg()函数求出结果，over内容为（
order by visited_on range between interval 6 day preceding and current row
）

③使用limit过滤前六条（不太严谨）

法二：（参考leetcode大佬）

①窗口函数找出每个日期对应的总收入
SELECT visited_on, sum(amount) OVER ( ORDER BY visited_on RANGE interval 6 day preceding  ) AS sum_amount
FROM Customer
②求平均，此方法相比于avg的窗口函数效率较高
SELECT distinct visited_on,
       sum_amount AS amount,
       round(sum_amount/7, 2) AS average_amount
FROM (
    SELECT visited_on, sum(amount) OVER ( ORDER BY visited_on RANGE interval 6 day preceding  ) AS sum_amount
    FROM Customer) t
③过滤
WHERE DATEDIFF(visited_on, (SELECT MIN(visited_on) FROM Customer)) >= 6

代码：

#法一：
with t1 as (select visited_on,
                   sum(amount)total
            from Customer
            group by visited_on)
select visited_on,
       sum(total)
           over(order by visited_on range between interval 6 day preceding and current row)amount ,
     round(avg(total)
            over(order by visited_on range between interval 6 day preceding and current row),2)average_amount
from t1
limit 6,100;


#法二：
SELECT distinct visited_on,
       sum_amount AS amount,
       round(sum_amount/7, 2) AS average_amount
FROM (
    SELECT visited_on, sum(amount) OVER ( ORDER BY visited_on RANGE interval 6 day preceding  ) AS sum_amount
    FROM Customer) t
WHERE DATEDIFF(visited_on, (SELECT MIN(visited_on) FROM Customer)) >= 6