MySQL记录

z樾

已于 2024-10-07 11:34:00 修改

阅读量584

点赞数 7

文章标签： mysql 数据库

于 2024-08-31 16:53:46 首次发布

本文链接：https://blog.csdn.net/weixin_45302337/article/details/141753769

版权

1.#每个月Top3的周杰伦歌曲#

描述

从听歌流水中找到18-25岁用户在2022年每个月播放次数top 3的周杰伦的歌曲

select month,ranking,song_name,play_pv
from (
  select
	  month,
	  row_number() over(partition by month order by play_pv desc,song_id) as ranking,
	  song_id,
	  song_name,
	  play_pv
  from (
	  select
		  month(fdate) as month,
		  a.song_id,
		  max(song_name) as song_name,
		  count(1)     as play_pv
	  from play_log a
	  inner join user_info b on a.user_id  = b.user_id
	  inner join song_info c on a.song_id = c.song_id
	  where year(fdate) = 2022
	  and age between 18 and 25
	  and singer_name = '周杰伦'
	  group by month(fdate), a.song_id
  ) a
  ) a
where ranking<=3
order by month, ranking

里面嵌套了2张表，最里面的这张将年份，年龄，作者先筛选出来；后按照先月份和曲名分组，也就是将2022年每个月中的歌曲排列出来。此处用的是inner join也就是两张表的信息都不丢

select
    month(fdate) as month,
    a.song_id,
    max(song_name) as song_name,
    count(1) as play_pv
from play_log a
inner join user_info b on a.user_id = b.user_id
inner join song_info c on a.song_id = c.song_id
where year(fdate) = 2022
and age between 18 and 25
and singer_name = '周杰伦'
group by month(fdate), a.song_id

inner join 和left join 区别：

INNER JOIN操作的作用是：INNER JOIN根据ON字段标识出来的条件，查出关联的几张表中，符合该条件的记录，合并成一个查询结果集。

LEFT JOIN操作中，比如A LEFT JOIN B，会输出左表A 中所有的数据，同时将符合 ON条件的右表B 中搜索出来的结果合并到左表A表中，如果A表中存在而在B表中不存在，则结果集中会将查询的B表字段值（如此处的P.punishment字段）设置为NULL。所以，LEFT JOIN的作用是：LEFT JOIN 从右表B 中将符合ON条件的结果查询出来，合并到A表中，再作为一个结果集输出。

count(1)的作用，就是统计在分组中，每一组对应的行数或项数。效率和作用和count(*)相同。

这里还有一点需要注意，max(song_name)

使用 `MAX(song_name)` 的原因

分组条件与非聚合列的冲突：
- SQL 的规则要求，在 GROUP BY 查询中，所有未被分组的列必须通过聚合函数（如 COUNT、SUM、MAX 等）处理，否则会引发错误。
- 在这个查询中，GROUP BY 的列是 month(fdate) 和 a.song_id，但 song_name 并没有出现在 GROUP BY 子句中。为了能在 SELECT 中包含 song_name，必须对其应用某种聚合函数。
确保唯一性：
- 如果你确信每个 song_id 对应唯一的 song_name，那么使用 MAX(song_name) 只是为了满足 SQL 的语法要求，而并不影响结果，因为每个 song_id 的 song_name 都是唯一的。
- 在这种情况下，MAX(song_name) 会返回这个唯一的 song_name。
MAX() 选择字符串：
- 虽然 MAX() 聚合函数通常用于数值，但它同样可以用于字符串列。对于字符串，MAX() 返回字母表中排序最大的值。
- 如果每个 song_id 都只对应一个 song_name，那么 MAX(song_name) 实际上就是返回那个唯一的 song_name。

select
    month,
    row_number() over(partition by month order by play_pv desc, song_id) as ranking,
    song_id,
    song_name,
    play_pv
from (
    -- 上述子查询
) a

使用 row_number() 窗口函数来生成排名，其中 partition by month 表示每个月内进行排名，order by play_pv desc, song_id 表示播放次数降序排列，相同播放次数的情况下按歌曲ID升序排列。

这个查询主要是增加了一个排序列，排序规则如 over(partition by ) rank给出

1. `ROW_NUMBER()`:

ROW_NUMBER() 是一个窗口函数，用于为查询结果集中的每一行生成一个唯一的行号。这个行号从1开始，并根据指定的排序规则递增。

2. `OVER` 子句:

OVER 子句用于定义窗口函数的范围或分区，并指定排序规则。

3. `PARTITION BY month`:

PARTITION BY 用于将数据分组到不同的分区中。在这个例子中，数据是按 month（月份）进行分区的。
这意味着在 month 相同的数据行之间会被视为一个独立的分区，ROW_NUMBER() 函数将在每个分区内部生成独立的行号。
举例来说，如果有两个月的数据（1月和2月），ROW_NUMBER() 会分别为1月和2月的数据重新编号。

4. `ORDER BY play_pv DESC, song_id`:

ORDER BY 定义了如何在每个分区内对数据进行排序。
- play_pv DESC 表示按照播放次数（play_pv）降序排列。
- song_id 是次要排序条件，用于在 play_pv 相同的情况下按 song_id 升序排列，以确保排序的唯一性和稳定性。

5. `AS ranking`:

隔离性和 MVCC 的关系：

你的理解与改进：

你提到的“每个事务拥有一个系统版本”，可以理解为每个事务在运行过程中拥有自己的一组数据快照，这个快照对应了数据库在某个时间点的状态。这个时间点是事务启动的那一刻，事务基于这个快照进行操作。

然而，所有事务实际上还是访问相同的数据库，区别在于每个事务读取到的数据可能不一样，具体取决于事务的隔离级别和MVCC 机制。

进一步理解：

总结：你可以理解为每个事务在执行过程中有一个独立的数据版本，这个版本是基于事务启动时的快照生成的，只有当事务提交后，它的修改才会被其他事务看到，从而实现了事务隔离性。

总结：MVCC是多版本并发控制方法，即一份数据会存储多个版本，通过事务的可见性来保证事务能够看到自己应该看到的版本。通常会有一个全局的版本分配器来为每一行数据设置版本号，版本号是唯一的。

脏读：当前事务读到了其他事务未提交的数据，未提交意味着数据可能会回滚，也就是不存在。

不可重复读：同一事务内，不同的时刻读到的同一批数据可能是不一样的，可能会受到其他事物影响，比如其他事务改了这批数据并提交了。（并发事务导致的）

幻读：幻读是针对数据插入操作来说的。一个事务读取了几行数据,接着另一个并发事务插入了一些数据时。在随后的查询中,第一个事务就会发现多了一些原本不存在的记录,就好像发生了幻觉一样,所所以称为幻读。

丢失修改：一个事务读取了一个数据，另一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据，这样第一个事务内的修改结果就被丢失了；

AS ranking 将生成的行号命名为 ranking，作为查询结果中的一列。
首先，隔离性 确实要求每个事务在最终提交之前对其他事务的修改是不可见的。这是为了避免事务之间的冲突和不一致。然而，在 MVCC 中，隔离性是通过管理每个事务的数据版本来实现的，具体如下：

MVCC 细节：
每行记录都有多个版本：每当一个事务修改某行数据时，系统不会直接覆盖该行的原始数据，而是创建一个新的数据版本。旧版本会保留，以供其他尚未完成的事务访问。
事务视图（快照）：在事务开始时，它会获取一个当前系统的“数据快照”，这个快照是基于当前的数据版本和系统的事务版本号生成的。每个事务只能够看到在它开始时已经提交的事务修改，并且只能基于这个快照进行操作。
版本号与可见性：每个事务有一个唯一的版本号。当事务读取数据时，MySQL 会根据数据的版本号来决定哪些版本对该事务是可见的，哪些是不可见的。
- 如果数据的版本号比事务的版本号要大（即数据是其他事务在当前事务之后修改的），该数据对当前事务是不可见的。
- 如果数据的版本号比事务的版本号小或相等，则该数据是当前事务可见的。
在事务未提交前，其他事务只能看到在当前事务开始时已经提交的数据版本，即每个事务有自己的一组可见数据。
当事务提交后，其他事务才会看到它所做的修改。
在 MVCC 的机制下，不同的事务读取同一行记录时，可能会看到不同的版本，从而实现了事务的隔离性。
在 读已提交（Read Committed） 隔离级别下，每个事务读取到的是其他事务已提交的最新数据版本。
在 可重复读（Repeatable Read） 隔离级别下，事务从开始到结束的整个过程中，始终读取的是该事务开始时的快照，即使其他事务已提交了新的数据版本，这个事务也不会看到这些修改。