likes
comments
collection
share

SQL 思维训练 知识点练习 11

作者站长头像
站长
· 阅读数 15

MySQl 专栏持续更新 不说晦涩难懂的东西 尽量输出容易理解 和 使用的SQL技巧 和 初中级开发不是很常用的但很有用的知识

欢迎查看👉🏻👉🏻👉🏻SQL 专栏 查漏补缺 指教一二

SQL 思维训练 知识点练习 11

前言

表结构在上一篇文章 需要的可以前往复制

表结构

SQL 思维训练 知识点练习 11

需要了解的知识点

  • DATE_FORMAT() 函数用于以不同的格式显示日期/时间数 在进行时间操作的时候可以使用这个函数进行时间的格式化操作

例如： 下面的脚本使用 DATE_FORMAT() 函数来显示不同的格式。我们使用 NOW() 来获得当前的日期/时间：

DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')
DATE_FORMAT(NOW(),'%m-%d-%Y')
DATE_FORMAT(NOW(),'%d %b %y')
DATE_FORMAT(NOW(),'%d %b %Y %T:%f')

结果类似：

Dec 29 2022 14:45 PM
12-04-2022
29 Dec 08
29 Dec 2022 16:25:46.635

SQL 思维训练 知识点练习 11

这个在日常工作中 还算是比较有用的 因为 经常和时间进行打交道 如果不会使用这个函数 我们拿到数据到java 里面进行数据处理 肯定不是很好选择 还是需要去了解一下这个

  • over窗口函数

这个我基本呢没有使用过 可能是之前没有去了解过着地方 但是我经常看到很多人 写SQL 都会使用这个函数 简单的了解一下

OVER开窗函数必须与聚合函数或排序函数一起使用，聚合函数一般指SUM(),MAX(),MIN,COUNT(),AVG()等常见函数。排序函数一般指RANK(),ROW_NUMBER(),DENSE_RANK(),NTILE()等。

OVER的语法

OVER ( [ PARTITION BY column ] [ ORDER BY culumn ] )

PARTITION BY 子句进行分组；

ORDER BY 子句进行排序。

窗口函数OVER()指定一组行，开窗函数计算从窗口函数输出的结果集中各行的值。

这个学名就是窗口函数 主要的作用即就是所有的数据都在窗口中 看这个概念还是比较抽象的

这个其实主要就是把数据单独拎出来处理

SUM(price) OVER (PARTITION BY name)

只对PARTITION BY后面的列name进行分组，分组后求解price的和。

SUM(price) OVER (PARTITION BY name ORDER BY ID)

对PARTITION BY后面的列name进行分组，然后按ORDER BY 后的ID进行排序，然后在组内对price进行累加处理。

****SUM(price) OVER (ORDER BY ID)

只对ORDER BY 后的ID内容进行排序，对排完序后的price进行累加处理。

****SUM(price) OVER ()

实战练习

问题

计算2021年里每个创作者每月的涨粉率及截止当月的总粉丝量

补充：

  • 涨粉率=(加粉量 - 掉粉量) / 播放量。结果按创作者ID、总粉丝量升序排序。
  • if_follow-是否关注为1表示用户观看视频中关注了视频创作者，为0表示此次互动前后关注状态未发生变化，为2表示本次观看过程中取消了关注。

感觉这个难度还是比较大的

分析思路

  1. 窗口函数是基于group by的数据进行聚合，而不是原表数据

  2. 第一层先行，在已经group by的表上先进行计算，得出sum = 2

  3. 第二层为正式的窗口函数，相当于 sum(第一层的结果) over ()

类比 max(某一列) over ()

SQL 思维训练 知识点练习 11

  1. 为了让窗口函数更符合我们的结果输出，over的字句写上`partition by author，order by month

partition`让其对于用户进行分类汇总计算，order则表示以月份累加

SQL 思维训练 知识点练习 11

完整SQL

SELECT 
author,
month,
round(SUM(if_follow_num) / COUNT(*),3),
SUM(SUM(if_follow_num)) OVER (partition by author
                order by month) "total_fans"
FROM (
    SELECT 
    video_id,
    DATE_FORMAT(start_time,"%Y-%m") "month",
    CASE WHEN if_follow = 2 THEN -1
    ELSE if_follow END "if_follow_num"
    FROM tb_user_video_log vl
    WHERE YEAR(start_time) = 2021) as a
INNER JOIN tb_video_info vi
ON a.video_id = vi.video_id
GROUP BY author,month
ORDER BY author,total_fans

第二种解题思路

第一个条件： 截止当前

使用到的函数： 窗口函数、sum

难点：

  • 窗口函数 over 和 sum 以及一些函数的配合使用

1、统计每个用户的播放量、加粉量、掉粉量

  • [条件]：year(start_time) = 2021
  • [使用]：group by author,month；count

2、计算2021年里每个创作者每月的涨粉率及截止当月的总粉丝量

当月的总粉丝量 = (上个月)(加粉量 - 掉粉量) + 这个月(加粉量 - 掉粉量)

需要用到聚合窗口函数

  • [使用]：sum(follow_add-follow_sub) over(partition by author order by month) 这个难度主要是在窗口函数 Over 的理解和使用方法上面

编写SQL

SQL 思维训练 知识点练习 11

不了解 over 的使用这个是看不懂的