本文共 13767 字,大约阅读时间需要 45 分钟。
sql子查询示例
In the previous article we’ve , and today, we’ll continue with a few more SQL examples. The goal of this article is to start with a fairly simple query and move towards more complex queries. We’ll examine queries you could need at the job interview, but also some you would need in real-life situations. So, buckle up, we’re taking off!
在上一篇文章中,我们 ,而今天,我们将继续其他一些SQL示例。 本文的目的是从一个非常简单的查询开始,然后转向更复杂的查询。 我们将在面试时检查您可能需要的查询,但在现实生活中还会查询一些查询。 所以,系好安全带,我们要起飞了!
As always, let’s first take a quick look at the data model we’ll use. This is the same model we’re using in this series, so you should be familiar by now. In case, you’re not, just take a quick look at the tables, and how are they related.
与往常一样,让我们首先快速浏览一下我们将使用的数据模型。 这与我们在本系列中使用的模型相同,因此您现在应该已经很熟悉了。 如果不是这样,只需快速浏览一下表格及其之间的关系即可。
We’ll analyze 6 SQL examples, starting from a pretty simple one. Each example will add something new, and we’ll discuss the learning goal behind each query. I’ll use the same approach covered in the article Let’s start.
我们将从一个非常简单的示例开始分析6个SQL示例。 每个示例都会添加一些新内容,我们将讨论每个查询背后的学习目标。 我将使用“ 文中介绍的相同方法 开始吧。
We want to examine what is in the call table in our model. Therefore, we need to select all attributes, and we’ll sort them first by employee_id and then by start_time.
我们想检查模型中调用表中的内容。 因此,我们需要选择所有属性,我们将首先按employee_id对它们进行排序,然后再按start_time对其进行排序。
-- A list of all calls (sorted by employee and start time)SELECT *FROM callORDER BY call.employee_id ASC, call.start_time ASC;
This is a pretty simple query and you should understand it without any problem. The only thing I would like to point here is that we’ve ordered our result first by the id of the employee (call.employee_id ASC) and then by the call start time (call.start_time). In real-life situations, this is something you would do if you want to perform analytics during the time on the given criteria (all data for the same employee are ordered one after another).
这是一个非常简单的查询,您应该毫无问题地理解它。 我想在这里指出的唯一一件事是,我们首先按雇员的ID(call.employee_id ASC)对结果进行排序,然后按呼叫开始时间(call.start_time)进行排序。 在现实生活中,如果要在给定条件下执行分析(如果同一员工的所有数据都被依次订购),则可以执行此操作。
We need a query that shall return all call data, but also the duration of each call, in seconds. We’ll use the previous query as the starting point.
我们需要一个查询,该查询将返回所有呼叫数据,以及每个呼叫的持续时间(以秒为单位)。 我们将使用上一个查询作为起点。
-- A list of all calls together with the call durationSELECT call.*, DATEDIFF("SECOND", call.start_time, call.end_time) AS call_durationFROM callORDER BY call.employee_id ASC, call.start_time ASC;
The result returned is almost the same as in the previous query (same columns & order) except for one column added. We’ve named this column call_duration. To get the call duration, we’ve used the SQL Server DATEDIFF function. It takes 3 arguments, the unit for the difference (we need seconds), first date-time value (start time, lower value), second date-time value (end time, higher value). The function returns the time difference in the given unit.
返回的结果与上一个查询几乎相同(相同的列和顺序),只是添加了一个列。 我们已将此列命名为call_duration。 为了获得通话时间,我们使用了SQL Server DATEDIFF函数。 它需要3个参数,差的单位(我们需要秒),第一个日期时间值(开始时间,下限值),第二个日期时间值(结束时间,上限值)。 该函数以给定单位返回时差。
Now we want to return the total duration of all calls for each employee. So, we want to have 1 row for each employee and the sum of the duration of all calls he ever made. We’ll continue from where we stopped with the previous query.
现在,我们要返回每个员工的所有通话的总时长。 因此,我们希望每个员工都有1行,以及他所进行的所有呼叫的持续时间之和。 我们将从上一个查询停止的地方继续。
-- SUM of call duration per each employeeSELECT employee.id, employee.first_name, employee.last_name, SUM(DATEDIFF("SECOND", call.start_time, call.end_time)) AS call_duration_sumFROM callINNER JOIN employee ON call.employee_id = employee.idGROUP BY employee.id, employee.first_name, employee.last_nameORDER BY employee.id ASC;
There is nothing special to add regarding the result – we got exactly what we wanted. But let’s comment on how we achieved that. Few things I would like to emphasize here are:
关于结果,没有什么可添加的特殊内容–我们正是想要的。 但是,让我们评论一下我们是如何实现的。 我在这里要强调的几件事是:
For each employee, we need to return all his calls with their duration. We also want to know the percentage of time an employee spent on this call, compared to the total call time of all his calls.
对于每位员工,我们需要返回其所有通话及其持续时间。 我们还想知道某个员工花费在此呼叫上的时间占其所有呼叫的总呼叫时间的百分比。
-- % of call duration per each employee compared to the duration of all his callsSELECT employee.id, employee.first_name, employee.last_name, call.start_time, call.end_time, DATEDIFF("SECOND", call.start_time, call.end_time) AS call_duration, duration_sum.call_duration_sum, CAST( CAST(DATEDIFF("SECOND", call.start_time, call.end_time) AS DECIMAL(7,2)) / CAST(duration_sum.call_duration_sum AS DECIMAL(7,2)) AS DECIMAL(4,4)) AS call_percentageFROM callINNER JOIN employee ON call.employee_id = employee.idINNER JOIN ( SELECT employee.id, SUM(DATEDIFF("SECOND", call.start_time, call.end_time)) AS call_duration_sum FROM call INNER JOIN employee ON call.employee_id = employee.id GROUP BY employee.id) AS duration_sum ON employee.id = duration_sum.idORDER BY employee.id ASC, call.start_time ASC;
You can notice that we’ve achieved in combining row values with aggregated value. This is very useful because you could put such calculations inside the SQL query and avoid additional work later. This query contains a few more interesting concepts that should be mentioned:
您可能会注意到,我们已经实现了将行值与聚合值相结合。 这非常有用,因为您可以将此类计算放入SQL查询中,并避免以后进行其他工作。 此查询包含一些更有趣的概念,应予以提及:
From this example, we should remember that we can use subqueries to return additional values we need. Returning the aggregated value using a subquery and combining that value with the original row is one good example where we could do exactly that.
从这个例子中,我们应该记住,我们可以使用子查询来返回我们需要的其他值。 使用子查询返回汇总值并将该值与原始行合并是一个很好的例子,我们可以做到这一点。
We need two queries. First shall return the average call duration per employee, while the second shall return average call duration for all calls.
我们需要两个查询。 第一个应返回每个员工的平均通话时间,第二个应返回所有呼叫的平均通话时间。
-- average call duration per employeeSELECT employee.id, employee.first_name, employee.last_name, AVG(DATEDIFF("SECOND", call.start_time, call.end_time)) AS call_duration_avgFROM callINNER JOIN employee ON call.employee_id = employee.idGROUP BY employee.id, employee.first_name, employee.last_nameORDER BY employee.id ASC; -- average call duration - all callsSELECT AVG(DATEDIFF("SECOND", call.start_time, call.end_time)) AS call_duration_avgFROM call;
There is no need to explain this in more detail. Calculating the average call duration per employee is the same as calculating the SUM of call durations per employee (#3 SQL Example – DATEDIFF + Aggregate Function). We’ve just replaced the aggregate function SUM with AVG.
无需更详细地解释这一点。 计算每位员工的平均通话时间与计算每位员工的通话时间的总和相同(#3 SQL示例– DATEDIFF +汇总函数)。 我们刚刚将汇总函数SUM替换为AVG。
The second query returns the AVG call duration of all calls. Notice that we haven’t used GROUP BY. We simply don’t need it, because all rows go into this group. This is one of the cases when aggregate function could be used without the GROUP BY clause.
第二个查询返回所有呼叫的AVG呼叫持续时间。 注意,我们还没有使用GROUP BY。 我们根本不需要它,因为所有行都属于该组。 这是不使用GROUP BY子句而可以使用聚合函数的情况之一。
We need to calculate the difference between the average call duration for each employee and the average call duration for all calls.
我们需要计算每个员工的平均通话时间与所有通话的平均通话时间之差。
-- the difference between AVG call duration per employee and AVG call durationSELECT single_employee.id, single_employee.first_name, single_employee.last_name, single_employee.call_duration_avg, single_employee.call_duration_avg - avg_all.call_duration_avg AS avg_differenceFROM( SELECT 1 AS join_id, employee.id, employee.first_name, employee.last_name, AVG(DATEDIFF("SECOND", call.start_time, call.end_time)) AS call_duration_avg FROM call INNER JOIN employee ON call.employee_id = employee.id GROUP BY employee.id, employee.first_name, employee.last_name) single_employee INNER JOIN ( SELECT 1 AS join_id, AVG(DATEDIFF("SECOND", call.start_time, call.end_time)) AS call_duration_avg FROM call) avg_all ON avg_all.join_id = single_employee.join_id;
This query is really complex, so lets’ comment on the result first. We have exactly 1 row per employee with an average call duration per employee, and the difference between this average and average duration of all calls.
这个查询真的很复杂,因此让我们先对结果进行评论。 我们每位员工正好有1行,每位员工的平均通话时长,以及所有通话的平均通话时长与平均通话时长之间的差。
So, what we did to achieve this. Let’s mention the most important parts of this query:
因此,我们为实现这一目标所做的工作。 让我们提及此查询的最重要部分:
I hope you’ve learned a lot in today’s article. The main thing I would like you to remember after this one is that you can perform many statistical computations directly in SQL, and then use the web form or Excel to present results using shiny tables and graphs. We’ll continue practicing in the next article, so stay tuned.
希望您在今天的文章中学到了很多东西。 在此之后,我想让您记住的主要事情是,您可以直接在SQL中执行许多统计计算,然后使用Web表单或Excel使用闪亮的表和图形来呈现结果。 我们将在下一篇文章中继续练习,请继续关注。
Learn SQL: SQL Query examples |
学习SQL:SQL查询示例 |
翻译自:
sql子查询示例
转载地址:http://gwswd.baihongyu.com/