力扣刷题（sql）--零散知识点（2）
1.自定义分组后的分类统计问题（某组内无数据却仍要展示）
例题1：
查询每个工资类别的银行账户数量。 工资类别如下：
"Low Salary"：所有工资 严格低于20000美元。"Average Salary"： 包含 范围内的所有工资[$20000, $50000]。-
"High Salary"：所有工资 严格大于50000美元。
结果表 必须 包含所有三个类别。 如果某个类别中没有帐户，则报告 0。
输入： Accounts 表: +------------+--------+ | account_id | income | +------------+--------+ | 3 | 108939 | | 2 | 12747 | | 8 | 87709 | | 6 | 91796 | +------------+--------+ 输出： +----------------+----------------+ | category | accounts_count | +----------------+----------------+ | Low Salary | 1 | | Average Salary | 0 | | High Salary | 3 | +----------------+----------------+
这里主要难点就是把这类“Average Salary”组内没有符合条件的分组不能将其省略，可以通过以下方法，用union来操作，不是很高级的方法，但很完美实现了目的，适用于分组比较少的情况，真的很实用。
select 'Low Salary' category,count(*) accounts_count from Accounts
where income<20000
union
select 'Average Salary' category,count(*) accounts_count from Accounts
where income between 20000 and 50000
union
select 'High Salary' category,count(*) accounts_count from Accounts
where income>500002. 用union增加不好分组的组的信息
上面是一个例子，“Average Salary”这一组由于其没有满足的数据，所以我们用group by往往不好对其处理。那就对这组单独处理分组后union到之前正常能分组的结果中。
以下是另一个例子：
编写一个解决方案，找出在 2019-08-16 时全部产品的最新价格，假设所有产品在修改前的价格都是 10 。
示例 1:
输入： Products 表: +------------+-----------+-------------+ | product_id | new_price | change_date | +------------+-----------+-------------+ | 1 | 20 | 2019-08-14 | | 2 | 50 | 2019-08-14 | | 1 | 30 | 2019-08-15 | | 1 | 35 | 2019-08-16 | | 2 | 65 | 2019-08-17 | | 3 | 20 | 2019-08-18 | +------------+-----------+-------------+ 输出： +------------+-------+ | product_id | price | +------------+-------+ | 2 | 50 | | 1 | 35 | | 3 | 10 | +------------+-------+
这里product_id为3的产品在‘2019-08-16’前都没出现过，不方便加入其他id组的讨论中，那就只能将其单独构建除结果内容后用union和返回的其他产品id返回的结果拼接。
本题参考代码如下，还用到了row_number构建序号列配合排序和排序倒序为1找极值。
selectproduct_id, new_price as price
from(selectproduct_id,new_price,change_date,(row_number() over (partition by product_id order by change_date DESC)) r
from products
where date(change_date) <= '2019-08-16') a
where r='1'unionselectproduct_id, 10 as price
fromProducts
group byproduct_id
havingmin(change_date)>'2019-08-16';3. 起累加作用的语句
下面是根据turn列，来做累加的语句，又用到了over啥的，我们之前学习的row_number、lag函数已经提到过了，那这里也能不能用partition by呢，肯定也是可以的。
SUM(weight) OVER (ORDER BY turn)举例说明：
| athlete_id | athlete_name | weight | turn |
|---|---|---|---|
| 1 | John | 70 | 1 |
| 2 | Mike | 75 | 2 |
| 3 | Sarah | 65 | 3 |
| 4 | Linda | 80 | 4 |
| 5 | Paul | 68 | 5 |
SELECT athlete_name,weight,SUM(weight) OVER (ORDER BY turn) AS cumulative_weight
FROM weights
ORDER BY turn;
| athlete_name | weight | cumulative_weight |
|---|---|---|
| John | 70 | 70 |
| Mike | 75 | 145 |
| Sarah | 65 | 210 |
| Linda | 80 | 290 |
| Paul | 68 | 358 |
4. 经典例题：换座位
示例 1:
输入: Seat 表: +----+---------+ | id | student | +----+---------+ | 1 | Abbot | | 2 | Doris | | 3 | Emerson | | 4 | Green | | 5 | Jeames | +----+---------+ 输出: +----+---------+ | id | student | +----+---------+ | 1 | Doris | | 2 | Abbot | | 3 | Green | | 4 | Emerson | | 5 | Jeames | +----+---------+ 解释: 请注意，如果学生人数为奇数，则不需要更换最后一名学生的座位。
这题很经典，可以单独拿出来作为一个知识点。
我第一次没做出来，下面是两种很棒的思路。
1.将偶数 id 减 2 后重排即可
select
rank() over(order by if(id % 2 = 0,id-2,id)) as id,student
from seat这不是我写的代码，看起来很简洁有效，我懂了思路后，还要再进一步学习其窗口函数rank()的应用，以及窗口函数后面order by加上if配合的使用。这里应该是直接把处理好的id用于窗口函数order by的排序了，牛，我可能还要再来个中间表啥的。
2.更符合逻辑的思路（逻辑非常易懂）
SELECT (CASE WHEN MOD(id,2) = 1 AND id = (SELECT COUNT(*) FROM seat) THEN idWHEN MOD(id,2) = 1 THEN id+1ElSE id-1END) AS id, student
FROM seat
ORDER BY id;这里用到case来处理超过两种情况的条件判断。首先，明确这题的处理对象，最好是id，因为id是简单连续数字，可以做简单加减运算。
值得注意的是，如果最后一个学生序号为奇数，那就不要换了。所以要求最多的，可能为奇数的最后一个序号优先处理，放在case的第一个判断框，让它直接等于它本身；第二个放不是最后一个序号的技术序号，让他序号加1；最后只有偶数序号了，全部减1；完美的逻辑。
5. 注意date_format()里面的参数
终于实践代码的时候用到了date_format()函数，特别有用的函数，特别是在分类的时候。有几点注意事项，是关于参数的。
SELECT DATE_FORMAT(created_at, '%Y-%m-%d') AS full_date,DATE_FORMAT(created_at, '%Y') AS four_digit_year,DATE_FORMAT(created_at, '%y') AS two_digit_year,DATE_FORMAT(created_at, '%M') AS full_month_name,DATE_FORMAT(created_at, '%m') AS two_digit_month,DATE_FORMAT(created_at, '%D') AS day_with_suffix
FROM some_table;
结果如下：
执行上述查询后的结果如下：
| full_date | four_digit_year | two_digit_year | full_month_name | two_digit_month | day_with_suffix |
|---|---|---|---|---|---|
| 2024-10-01 | 2024 | 24 | October | 10 | 1st |
解释：
最常用的是%Y-%m-%d，尤其是%Y-%m，常用于具体年月的分组。
6. 单个union语句只能使用一个order by或limit
-
ORDER BY在UNION中的使用：ORDER BY应该放在整个UNION查询的最后，而不是每个子查询中。你可以在子查询内部使用ORDER BY，但通常要使用子查询包装。
-
LIMIT也需要注意：- 在
UNION中，你可以对每个子查询使用LIMIT，但不能在UNION之后直接再使用单独的ORDER BY。
- 在
举个例子，下面如果不加括号就会报错。
(selectu.name results
frommovierating m
order byu.name
limit 1)union all(selectmv.title results
frommovierating m
order bymv.title
limit 1);- 加括号明确了每个查询的范围，让每个查询都能独立执行自己的
ORDER BY和LIMIT，然后再用UNION合并。 - 不加括号时，数据库认为
ORDER BY和LIMIT试图对整个UNION操作生效，但 SQL 标准不允许这种用法。
值得一提的是，这里用到了union all，有的时候用的上，它不会去掉重复的返回行（重复指每一列的数据都一样），union会。
--------------------------------------------------------------------------------------------------------------
例题 :
输入： Customer 表: +-------------+--------------+--------------+-------------+ | customer_id | name | visited_on | amount | +-------------+--------------+--------------+-------------+ | 1 | Jhon | 2019-01-01 | 100 | | 2 | Daniel | 2019-01-02 | 110 | | 3 | Jade | 2019-01-03 | 120 | | 4 | Khaled | 2019-01-04 | 130 | | 5 | Winston | 2019-01-05 | 110 | | 6 | Elvis | 2019-01-06 | 140 | | 7 | Anna | 2019-01-07 | 150 | | 8 | Maria | 2019-01-08 | 80 | | 9 | Jaze | 2019-01-09 | 110 | | 1 | Jhon | 2019-01-10 | 130 | | 3 | Jade | 2019-01-10 | 150 | +-------------+--------------+--------------+-------------+ 输出： +--------------+--------------+----------------+ | visited_on | amount | average_amount | +--------------+--------------+----------------+ | 2019-01-07 | 860 | 122.86 | | 2019-01-08 | 840 | 120 | | 2019-01-09 | 840 | 120 | | 2019-01-10 | 1000 | 142.86 | +--------------+--------------+----------------+ 解释： 第一个七天消费平均值从 2019-01-01 到 2019-01-07 是restaurant-growth/restaurant-growth/ (100 + 110 + 120 + 130 + 110 + 140 + 150)/7 = 122.86 第二个七天消费平均值从 2019-01-02 到 2019-01-08 是 (110 + 120 + 130 + 110 + 140 + 150 + 80)/7 = 120 第三个七天消费平均值从 2019-01-03 到 2019-01-09 是 (120 + 130 + 110 + 140 + 150 + 80 + 110)/7 = 120 第四个七天消费平均值从 2019-01-04 到 2019-01-10 是 (130 + 110 + 140 + 150 + 80 + 110 + 130 + 150)/7 = 142.86
做了很久这道题，加上gpt的帮忙，才得到下面的答案：
selectvisited_on,total_amount amount,average_amount
from(SELECT visited_on,SUM(amount) OVER (ORDER BY visited_on RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW) AS total_amount,ROUND(AVG(amount) OVER (ORDER BY visited_on RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW), 2) AS average_amount
FROM (select customer_id,name,visited_on,sum(amount) amount from customer group by visited_on) c
ORDER BY visited_on) c2
where visited_on
IN(select distinct visited_on from customerwhere datediff(visited_on,(select min(visited_on) from customer)) >= 6);先介绍一下具体逻辑（由里到外）：
这里某个日期还有多个数据，先用groupby用日期分组，把相同日期的数据先加起来，让每个日期只有一个数据。
然后用sum、avg窗口函数配合range或者rows（等下会讲），计算从这行开始加上前面6行（总共七行）的总和和平均值，目前得到每一个单独日期的总和和平均值。
最后找出返回的日期，从1号开始，只有7号开始后的日期才有7天的周期可言。即7号的周期为1号到7号，8号的周期为2号到8号......返回7号之后的日期。这里对应着上面代码的where visited_on in（7号，8号......），最终就返回指定日期的总和和平均值，这里是7、8、9、10号。
7. 窗口函数的rows、range应用
下面是窗口函数中rows、range的语法及运用：
SUM(amount) OVER (ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)SUM(amount) OVER (ORDER BY visited_on RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW)其中rows那一行起到的作用是，配合sum对当前行以及当前行的前面六行总共七行进行求和，rows是基于物理行的，即只看行数；而range则类似，配合sum对当前行的日期以及当前行的日期的前六天，总共七天的内容进行求和，range是基于基于逻辑值的。这里是sum，当然也可以配合avg等其他函数使用，真的很好用。
8. interval的用法
Interval 在英文里表示：间隔、时间段，指两个时间或事件之间的距离。在 SQL 中，INTERVAL 用来表示日期和时间的时间间隔。它用于增加或减少日期时间，以便进行计算。
interval主要的两个常见用法：
1. 计算日期（用字段加减interval的时间段，来定义新的时间）
SELECT visited_on - INTERVAL 1 DAY AS previous_day
FROM customer;SELECT *
FROM orders
WHERE order_date > NOW() - INTERVAL 30 DAY;
2. 配合函数
SELECT DATE_ADD('2024-10-28', INTERVAL 1 YEAR + 2 MONTH) AS new_date;