目录
**eg. 和前一天相比数额减小的日子(计算用户留存)datediff函数
eg 查询出每个学生参加每一门科目测试的次数,结果按 student_id 和 subject_name 排序。
**eg 确认率(计算各种不同表内的不同筛选条件 计数平均值)
GROUP BY 【from gpt】很重要但是sql里比较复杂的
查询
基本结构:
SELECT XXX AS xxx
FROM XXX
WHERE XXX AND XXX
补充功能:
1. select distinct:唯一解
2. order by: [ASC|DESC]
3. 计算字符串中字符数:[from leetcode]
对于SQL表,用于计算字符串中字符数的最佳函数是 CHAR_LENGTH(str),它返回字符串 str 的长度。
另一个常用的函数 LENGTH(str) 在这个问题中也适用,因为列 content 只包含英文字符,没有特殊字符。否则,LENGTH() 可能会返回不同的结果,因为该函数返回字符串 str 的字节数,某些字符包含多于 1 个字节。
4. round(,n) n为小数点后几位
5. is null 需要单独判定
连接
SELECT t1.column1, t2.column2, ... FROM table1 t1 JOIN table2 t2 ON condition;
[from 菜鸟]
left 和 right join 时,另一个table没有的会直接显示null
1. CROSS JOIN
(交叉连接)
是 SQL 中的一种用法,它用于在两个表之间进行笛卡尔积操作。交叉连接会返回两个表的所有可能行组合。因此,如果一个表有 m
行,另一个表有 n
行,那么结果集将返回 m × n
行。
eg. null 计数
示例 1:
输入: Visits
+----------+-------------+ | visit_id | customer_id | +----------+-------------+ | 1 | 23 | | 2 | 9 | | 4 | 30 | | 5 | 54 | | 6 | 96 | | 7 | 54 | | 8 | 54 | +----------+-------------+Transactions
+----------------+----------+--------+ | transaction_id | visit_id | amount | +----------------+----------+--------+ | 2 | 5 | 310 | | 3 | 5 | 300 | | 9 | 5 | 200 | | 12 | 1 | 910 | | 13 | 2 | 970 | +----------------+----------+--------+ 输出: +-------------+----------------+ | customer_id | count_no_trans | +-------------+----------------+ | 54 | 2 | | 30 | 1 | | 96 | 1 | +-------------+----------------+
# Write your MySQL query statement below
select v.customer_id, count(customer_id) count_no_trans
from Visits v
left join Transactions t on v.visit_id=t.visit_id
where t.transaction_id is null
group by customer_id;
**eg. 和前一天相比数额减小的日子(计算用户留存)datediff函数
示例 1:
输入: Weather 表: +----+------------+-------------+ | id | recordDate | Temperature | +----+------------+-------------+ | 1 | 2015-01-01 | 10 | | 2 | 2015-01-02 | 25 | | 3 | 2015-01-03 | 20 | | 4 | 2015-01-04 | 30 | +----+------------+-------------+ 输出: +----+ | id | +----+ | 2 | | 4 | +----+ 解释: 2015-01-02 的温度比前一天高(10 -> 25) 2015-01-04 的温度比前一天高(20 -> 30)
# Write your MySQL query statement below
select w1.id
from Weather w1
join Weather w2 on datediff(w1.recordDate,w2.recordDate)=1
where w1.Temperature>w2.Temperature
eg.表内不同数据自计算
【from leetcode】. - 力扣(LeetCode)
1、思路一:
通过表链接,将activity_type拆成两张子表的不同数据列,然后做汇总相减运算:
select a.machine_id, round(avg(b.timestamp-a.timestamp), 3) as processing_time from(
select * from Activity where activity_type = 'start') a --只含开始时间的子表
left join (
select * from Activity where activity_type = 'end') b --只含结束时间的子表
on a.machine_id = b.machine_id and a.process_id = b.process_id
group by a.machine_id;
2、思路二:
既然需要做Activity_type列的相减运算,则考虑构建正负数求和:
select machine_id, round(sum(if(activity_type = 'start', -timestamp, timestamp)) / count(*)*2, 3) as processing_time
from Activity
group by machine_id;
--这里×2:主要是因为计算过程是按照行数平均,但是每一个活动对应的的开始和结束时间戳在表中呈现了两行。
if的用法
IF(condition, value_if_true, value_if_false)
eg 查询出每个学生参加每一门科目测试的次数,结果按 student_id
和 subject_name
排序。
查询结构格式如下所示。
示例 1:
输入: Students table: +------------+--------------+ | student_id | student_name | +------------+--------------+ | 1 | Alice | | 2 | Bob | | 13 | John | | 6 | Alex | +------------+--------------+ Subjects table: +--------------+ | subject_name | +--------------+ | Math | | Physics | | Programming | +--------------+ Examinations table: +------------+--------------+ | student_id | subject_name | +------------+--------------+ | 1 | Math | | 1 | Physics | | 1 | Programming | | 2 | Programming | | 1 | Physics | | 1 | Math | | 13 | Math | | 13 | Programming | | 13 | Physics | | 2 | Math | | 1 | Math | +------------+--------------+ 输出: +------------+--------------+--------------+----------------+ | student_id | student_name | subject_name | attended_exams | +------------+--------------+--------------+----------------+ | 1 | Alice | Math | 3 | | 1 | Alice | Physics | 2 | | 1 | Alice | Programming | 1 | | 2 | Bob | Math | 1 | | 2 | Bob | Physics | 0 | | 2 | Bob | Programming | 1
因为student里可能有没参加考试的,但是left join会减少同一人参加科目的次数,所以使用cross join先把student和subject连接起来,。
同时使用ifnull(xxx,0),将null都转换成0
拆开成不同的student 和subject的组合,然后和计数组装。
计数可以group by两列
# Write your MySQL query statement below
select s.student_id, s.student_name, sub.subject_name, ifnull(grouped.attended_exams,0) as attended_exams
from Students s
cross join Subjects sub
left join
( select student_id, subject_name,count(*) as attended_exams
from Examinations e
group by e.student_id, e.subject_name) grouped
on s.student_id=grouped.student_id and sub.subject_name=grouped.subject_name
order by s.student_id, sub.subject_name ASC;
eg 编写一个解决方案,找出至少有五个直接下属的经理。
以 任意顺序 返回结果表。
查询结果格式如下所示。
示例 1:
输入: Employee 表: +-----+-------+------------+-----------+ | id | name | department | managerId | +-----+-------+------------+-----------+ | 101 | John | A | Null | | 102 | Dan | A | 101 | | 103 | James | A | 101 | | 104 | Amy | A | 101 | | 105 | Anne | A | 101 | | 106 | Ron | B | 101 | +-----+-------+------------+-----------+ 输出: +------+ | name | +------+ | John | +------+
上述查询在分组后,可以直接使用 having 字句筛选下属大于 5 的经理,代码如下
同一个表根据不同的key可以变成两个意义不同的表,然后可以筛选连接
select Manager.Name as Name
from
Employee as Manager join Employee as Report
on Manager.Id = Report.ManagerId
group by Manager.Id
having count(Report.Id) >= 5
**eg 确认率(计算各种不同表内的不同筛选条件 计数平均值)
用户的 确认率 是 'confirmed'
消息的数量除以请求的确认消息的总数。没有请求任何确认消息的用户的确认率为 0
。确认率四舍五入到 小数点后两位 。
编写一个SQL查询来查找每个用户的 确认率 。
以 任意顺序 返回结果表。
查询结果格式如下所示。
示例1:
输入: Signups 表: +---------+---------------------+ | user_id | time_stamp | +---------+---------------------+ | 3 | 2020-03-21 10:16:13 | | 7 | 2020-01-04 13:57:59 | | 2 | 2020-07-29 23:09:44 | | 6 | 2020-12-09 10:39:37 | +---------+---------------------+ Confirmations 表: +---------+---------------------+-----------+ | user_id | time_stamp | action | +---------+---------------------+-----------+ | 3 | 2021-01-06 03:30:46 | timeout | | 3 | 2021-07-14 14:00:00 | timeout | | 7 | 2021-06-12 11:57:29 | confirmed | | 7 | 2021-06-13 12:58:28 | confirmed | | 7 | 2021-06-14 13:59:27 | confirmed | | 2 | 2021-01-22 00:00:00 | confirmed | | 2 | 2021-02-28 23:59:59 | timeout | +---------+---------------------+-----------+ 输出: +---------+-------------------+ | user_id | confirmation_rate | +---------+-------------------+ | 6 | 0.00 | | 3 | 0.00 | | 7 | 1.00 | | 2 | 0.50 | +---------+-------------------+
关键:avg里可以写函数
ROUND(IFNULL(AVG(c.action='confirmed'), 0), 2) AS confirmation_rate
还有注意left join仅代表左表全部存在,不代表右表会直接对着左表的key group by
# Write your MySQL query statement below
select s.user_id , round(ifnull(avg(c.action='confirmed'),0),2) as confirmation_rate
#c.action='confirmed'返回0/1
from Signups s
left join Confirmations c on s.user_id=c.user_id
group by s.user_id
聚合函数
eg 编写解决方案以查找每种产品的平均售价。
average_price
应该 四舍五入到小数点后两位。
返回结果表 无顺序要求 。
结果格式如下例所示。
示例 1:
输入: Prices table: +------------+------------+------------+--------+ | product_id | start_date | end_date | price | +------------+------------+------------+--------+ | 1 | 2019-02-17 | 2019-02-28 | 5 | | 1 | 2019-03-01 | 2019-03-22 | 20 | | 2 | 2019-02-01 | 2019-02-20 | 15 | | 2 | 2019-02-21 | 2019-03-31 | 30 | +------------+------------+------------+--------+ UnitsSold table: +------------+---------------+-------+ | product_id | purchase_date | units | +------------+---------------+-------+ | 1 | 2019-02-25 | 100 | | 1 | 2019-03-01 | 15 | | 2 | 2019-02-10 | 200 | | 2 | 2019-03-22 | 30 | +------------+---------------+-------+ 输出: +------------+---------------+ | product_id | average_price | +------------+---------------+ | 1 | 6.96 | | 2 | 16.96 | +------------+---------------+ 解释: 平均售价 = 产品总价 / 销售的产品数量。 产品 1 的平均售价 = ((100 * 5)+(15 * 20) )/ 115 = 6.96 产品 2 的平均售价 = ((200 * 15)+(30 * 30) )/ 230 = 16.96
均价只要价格之和/数量之和就行
# Write your MySQL query statement below
select product_id, ifnull(round(sum(sales)/sum(units),2),0 )as average_price
from
(
select p.product_id AS product_id,p.price * u.units as sales ,u.units AS units
from Prices p left join UnitsSold u on p.product_id=u.product_id
and(u.purchase_date between p.start_date and p.end_date)
) t
group by product_id;
嵌套聚合函数:AVG(SUM(sales) / SUM(units))是错的
。在 MySQL 中,无法直接在 AVG
函数内使用 SUM
函数
*为什么 between...and..前要用 and而不是用where呢
当使用where的时候,相当于对left join的结果又进行了一次筛选,如果Prices中含有UnitsSold没有的product_id,返回值为null,会被过滤掉。而在标准答案中UnitsSold中不存在的id应该返回为0(这个也是题目没有说清楚的一点)
而当使用and的时候,between...and..语句在left join作用的范围内,left join具有保留左侧表的特性,UnitsSold中不存在的id也能被保留
eg 1075. 项目员工 I
请写一个 SQL 语句,查询每一个项目中员工的 平均 工作年限,精确到小数点后两位。
以 任意 顺序返回结果表。
查询结果的格式如下。
示例 1:
输入: Project 表: +-------------+-------------+ | project_id | employee_id | +-------------+-------------+ | 1 | 1 | | 1 | 2 | | 1 | 3 | | 2 | 1 | | 2 | 4 | +-------------+-------------+ Employee 表: +-------------+--------+------------------+ | employee_id | name | experience_years | +-------------+--------+------------------+ | 1 | Khaled | 3 | | 2 | Ali | 2 | | 3 | John | 1 | | 4 | Doe | 2 | +-------------+--------+------------------+ 输出: +-------------+---------------+ | project_id | average_years | +-------------+---------------+ | 1 | 2.00 | | 2 | 2.50 | +-------------+---------------+ 解释:第一个项目中,员工的平均工作年限是 (3 + 2 + 1) / 3 = 2.00;第二个项目中,员工的平均工作年限是 (3 + 2) / 2 = 2.50
# Write your MySQL query statement below
select project_id , round((years/employee),2) as average_years
from
(
select p.project_id,count(p.employee_id) as employee, sum(e.experience_years) as years
from Project p
left join Employee e on p.employee_id=e.employee_id
group by p.project_id
) sub
//可以这样写的也可以不要嵌套
SELECT
project_id,
ROUND(AVG(e.experience_years),2) AS average_years
FROM
Project as p
LEFT JOIN
Employee as e
ON
p.employee_id = e.employee_id
GROUP BY
p.project_id;
不需要嵌套,experience_years的个数就是员工个数
编写解决方案统计出各赛事的用户注册百分率,保留两位小数。
返回的结果表按 percentage
的 降序 排序,若相同则按 contest_id
的 升序 排序。
返回结果如下示例所示。
1633. 各赛事的用户注册率
示例 1:
输入: Users
表: +---------+-----------+ | user_id | user_name | +---------+-----------+ | 6 | Alice | | 2 | Bob | | 7 | Alex | +---------+-----------+Register
表: +------------+---------+ | contest_id | user_id | +------------+---------+ | 215 | 6 | | 209 | 2 | | 208 | 2 | | 210 | 6 | | 208 | 6 | | 209 | 7 | | 209 | 6 | | 215 | 7 | | 208 | 7 | | 210 | 2 | | 207 | 2 | | 210 | 7 | +------------+---------+ 输出: +------------+------------+ | contest_id | percentage | +------------+------------+ | 208 | 100.0 | | 209 | 100.0 | | 210 | 100.0 | | 215 | 66.67 | | 207 | 33.33 | +------------+------------+ 解释: 所有用户都注册了 208、209 和 210 赛事,因此这些赛事的注册率为 100% ,我们按 contest_id 的降序排序加入结果表中。 Alice 和 Alex 注册了 215 赛事,注册率为 ((2/3) * 100) = 66.67% Bob 注册了 207 赛事,注册率为 ((1/3) * 100) = 33.33%
子查询可以很复杂,聚合函数里可以包含很多东西,不一定需要嵌套
统计一个表的种类列数,可以直接子查询count(*)
# Write your MySQL query statement below
select r.contest_id , round((100*count(r.user_id)/(select count(*) from users)),2) as percentage
from register r
group by r.contest_id
order by percentage desc,contest_id asc
GROUP BY 【from gpt】很重要但是sql里比较复杂的
用法
GROUP BY
子句用于将查询结果按一个或多个列进行分组。通常与聚合函数(如 COUNT
, SUM
, AVG
, MAX
, MIN
)结合使用,以对分组后的数据进行计算。
还可以同时group by两列 group by xxx,xxx
基本语法:
SELECT 列名1, 聚合函數(列名2)
FROM 表名
WHERE 篩選條件
GROUP BY 列名1
HAVING 聚合函數(列名2) 篩選條件;
SELECT 列名1, 聚合函数(列名2)
:指定要返回的列和应用的聚合函数。FROM 表名
:指定数据源表。WHERE 篩選條件
:筛选出满足条件的数据(可选)。GROUP BY 列名1
:指定进行分组的列。HAVING 聚合函數(列名2) 篩選條件
:基于聚合函数的结果进行过滤(可选)。
示例:
假设有一个名为 sales
的表,结构如下:
id | product | category | amount |
---|---|---|---|
1 | A | X | 100 |
2 | B | Y | 200 |
3 | A | X | 150 |
4 | B | Y | 100 |
5 | C | Z | 300 |
如果我们想按 category
列对销售总量进行汇总,可以使用以下查询:
SELECT category, SUM(amount) AS total_amount
FROM sales
GROUP BY category;
结果为:
category | total_amount |
---|---|
X | 250 |
Y | 300 |
Z | 300 |
注意事项
-
GROUP BY
列不可为聚合函数:GROUP BY
子句后面的列不能是聚合函数结果,只能是查询结果中的原始列。 -
SELECT
列与GROUP BY
列的一致性:在使用GROUP BY
的查询中,SELECT
语句中必须包含GROUP BY
中用到的列,或者它们必须是聚合函数的结果。SELECT 列名1, 聚合函數(列名2) FROM 表名 GROUP BY 列名1;
-
HAVING
子句的使用:HAVING
子句用于过滤分组后的结果集,而WHERE
子句则用于过滤分组前的数据。SELECT category, SUM(amount) AS total_amount FROM sales GROUP BY category HAVING SUM(amount) > 250;
-
与
ORDER BY
的结合:可以在GROUP BY
之后使用ORDER BY
对结果进行排序。SELECT category, SUM(amount) AS total_amount FROM sales GROUP BY category ORDER BY total_amount DESC;
-
性能考虑:在大数据表上使用
GROUP BY
可能会比较慢,因为需要进行大量的数据处理。可以考虑创建索引或者优化查询条件来提升性能。
示例:
使用聚合函数和 GROUP BY
的场景
示例:
-
子查询和聚合的使用场景
-
在 SQL 中,设计复杂查询时,合理使用子查询和聚合函数 (
GROUP BY
和聚合操作) 有助于提高查询的可读性和执行效率。以下是一些指导原则,帮助你决定哪些问题应该在子查询中解决,哪些问题应该用聚合函数和GROUP BY
来解决。使用子查询的场景
- 分步计算:当一个查询的步骤依赖于前一步的结果时,通常通过子查询来实现。
- 筛选条件:当你需要使用一个结果集作为另一个查询的筛选条件时,子查询非常有用。
- 复杂计算:当需要复杂的计算、派生字段或多个中间步骤来得到最终结果时,子查询是一个很好的选择。
- 分组后的筛选:有时需要对分组后的结果集进行筛选,
HAVING
子句除外的过滤通常在子查询中完成。 -
分步计算:
获取每个产品的总销售额和总销量,然后计算平均价格:
SELECT product_id, ROUND(total_sales / total_units, 2) AS average_price FROM ( SELECT product_id, SUM(price * units) AS total_sales, SUM(units) AS total_units FROM Sales GROUP BY product_id ) AS derived_table;
-
筛选条件:
获取销售额大于某个值的产品列表:
SELECT product_id FROM Sales GROUP BY product_id HAVING SUM(price * units) > 1000;
或使用子查询来实现:
SELECT product_id FROM ( SELECT product_id, SUM(price * units) AS total_sales FROM Sales GROUP BY product_id ) AS sales_summary WHERE total_sales > 1000;
- 总体统计:当你需要对整个数据集做统计,比如总和、平均值、最大值、最小值等。
- 分组统计:当你需要对数据进行分组,然后在每个分组内做统计计算时。
- 派生字段的计算:将聚合结果用作计算派生字段。
-
总体统计:
计算所有销售记录的总销售额:
SELECT SUM(price * units) AS total_sales FROM Sales;
-
分组统计:
获取每个产品的总销售量和销售额:
SELECT product_id, SUM(units) AS total_units, SUM(price * units) AS total_sales FROM Sales GROUP BY product_id;