Shulou(Shulou.com)06/03 Report--

This article mainly explains "what are the advanced SQL concepts that programmers need to know". The explanation in this article is simple and clear and easy to learn and understand. Please follow the editor's train of thought to study and learn what advanced SQL concepts programmers need to know.

1. Common table expressions (CTEs)

If you want to query a subquery, that's when CTEs does it-CTEs basically creates a temporary table.

Using common table expressions (CTEs) is a good way to modularize and decompose code in the same way that you break up an article into paragraphs.

Use a subquery in the Where clause to make the following query.

SELECT name, salary FROM People WHERE NAME IN (SELECT DISTINCT NAME FROM population WHERE country = "Canada" AND city = "Toronto") AND salary > = (SELECT AVG (salary) FROM salaries WHERE gender = "Female")

This may seem difficult to understand, but what if there are many subqueries in the query? This is where CTEs plays a role.

With toronto_ppl as (SELECT DISTINCT name FROM population WHERE country = "Canada" AND city = "Toronto"), avg_female_salary as (SELECT AVG (salary) as avgSalary FROM salaries WHERE gender = "Female") SELECT name, salaryFROM PeopleWHERE name in (SELECT DISTINCT FROM toronto_ppl) AND salary > = (SELECT avgSalary FROM avg_female_salary)

It is now clear that the Where clause is filtered in the Toronto name. If you notice that CTE is useful because you can break the code into smaller blocks, but they are also useful because it allows you to assign variable names (that is, toronto_ppl and avg_female_salary) to each CTE

Similarly, CTEs allows you to accomplish more advanced techniques, such as creating recursive tables.

two。 Recursive CTEs.

A recursive CTE refers to its own CTE, just like a recursive function in Python. Recursive CTE is particularly useful, which involves querying hierarchical data such as organization charts, file systems, link maps between web pages, and so on.

A recursive CTE has three parts:

Anchor component: initial query that returns the basic results of CTE

Recursive member: a recursive query that references CTE. This is all the alliance with anchor members.

Stop the termination condition of a recursive component

The following is an example of getting the recursive CTE of the manager ID of each employee ID:

With org_structure as (SELECT id, manager_id FROM staff_members WHERE manager_id IS NULL UNION ALL SELECT sm.id, sm.manager_id FROM staff_members sm INNER JOIN org_structure os ON os.id = sm.manager_id3. Temporary function

Check this if you want to know more about temporary functions, but knowing how to write temporary functions is an important reason:

It allows you to break down blocks of code into smaller blocks of code

It is suitable for writing clean code

It prevents repetition and allows you to reuse code similar to using functions in Python.

Consider the following example:

SELECT name, CASE WHEN tenure

< 1 THEN "analyst" WHEN tenure BETWEEN 1 and 3 THEN "associate" WHEN tenure BETWEEN 3 and 5 THEN "senior" WHEN tenure >

5 THEN "vp" ELSE "nhammera" END AS seniority FROM employees

Instead, you can use temporary functions to capture case clauses.

CREATE TEMPORARY FUNCTION get_seniority (tenure INT64) AS (CASE WHEN tenure

< 1 THEN "analyst"WHEN tenure BETWEEN 1 and 3 THEN "associate"WHEN tenure BETWEEN 3 and 5 THEN "senior"WHEN tenure >

5 THEN "vp" ELSE "nhammera" END); SELECT name, get_seniority (tenure) as seniorityFROM employees

With temporary functions, the query itself is simpler and more readable, and you can reuse the seniority function!

4. Pivot data using CASE WHEN

You will probably see a lot of problems requiring the use of CASE WHEN in your presentation simply because it is a versatile concept. If you want to assign a value or class based on other variables, it allows you to write complex conditional statements.

Less well known, it also allows you to pivot data. For example, if you have an one-month column and you want to create a single column for each month, you can use statements to retrace the data.

Example question: write an SQL query to reformat the table so that you have an income column each month.

Initial table: +-+ | id | revenue | month | +-+ | 1 | 8000 | Jan | | 2 | 9000 | Jan | | 3 | 10000 | Feb | | 1 | 7000 | Feb | | 1 | 6000 | Mar | +- -+ Result table: +-+-+ | id | Jan_Revenue | Feb_Revenue | Mar_Revenue |. | Dec_Revenue | +-- -+-+ | 1 | 8000 | 7000 | 6000 |... | null | | 2 | 9000 | null | null |. | null | | 3 | null | 10000 | null |. | null | +-+-+ 5.EXCEPT vs NOT IN

Except for almost different operations. They are used to compare rows between two queries / tables. There is a subtle difference between the two men.

First, in addition to filtering and deleting duplicates and returning different rows and different rows that are not in the.

Similarly, except for the same number of columns in the query / table, individual columns are no longer compared to each query / table.

6. Self-connection

A SQL table connects itself. You may think it's useless, but you'll be surprised how common it is. In many real life, data is stored in one large table rather than many smaller tables. In this case, self-connection may be needed to solve unique problems.

Let's look at an example.

Example question: given the following employee table, write a SQL query to find out the salaries of employees, who are paid more than their managers. For the above table, Joe is the only employee who earns more than his manager.

+-+ | Id | Name | Salary | ManagerId | +-- + | 1 | Joe | 70000 | 3 | 2 | Henry | 80000 | 4 | 3 | Sam | 60000 | NULL | | 4 | | Max | 90000 | NULL | +-+ Answer: SELECT a.Name as Employee FROM Employee as a JOIN Employee as b on a.ManagerID = b.Id WHERE a.Salary > b.Salary7.Rank vs Dense Rank vs Row Number |

It is a very common application to rank rows and values. Here are some examples of rankings that companies often use:

Customers who rank highest in terms of shopping, profits, etc.

Top products that rank the number of sales

Rank the top countries by the largest sales

Rank in the number of minutes watched, the number of different viewers, etc., the top videos watched.

In SQL, you can assign a "rating" to a row in several ways, which we will explore using examples. Consider the following Query and results:

SELECT Name, GPA, ROW_NUMBER () OVER (ORDER BY GPA desc), RANK () OVER (ORDER BY GPA desc), DENSE_RANK () OVER (ORDER BY GPA desc) FROM student_grades

ROW_NUMBER () returns the unique number at the beginning of each line. When a relationship exists (for example, BOB vs Carrie), ROW_NUMBER () allocates numbers arbitrarily if no second criterion is defined.

Rank () returns the unique number of each row starting at 1, except when related, the level () will be assigned the same number. Similarly, the gap will follow the level of repetition.

Dense_rank () is similar to a level (), except that there is no gap after repeating the level. Note that with dense_rank (), Daniel ranks third, not fourth ().

8. Calculate Delta value

Another common application is to compare values from different periods. For example, what is the delta between this month's and last month's sales? Or what is this month and this month last year?

This is when Lead () and LAG () play a role when comparing values for different periods of time to calculate Deltas.

Here are some examples:

# Comparing each month's sales to last month SELECT month, sales, sales-LAG (sales, 1) OVER (ORDER BY month) FROM monthly_sales # Comparing each month's sales to the same month last year SELECT month, sales, sales-LAG (sales, 12) OVER (ORDER BY month) FROM monthly_sales9. Calculate the total number of runs

If you know about row_number () and lag () / lead (), this may not be a surprise to you. But if you don't, this is probably one of the most useful window features, especially if you want to visualize growth!

Using the window function with SUM (), we can calculate the total number of runs. See the following example:

SELECT Month, Revenue, SUM (Revenue) OVER (ORDER BY Month) AS Cumulative FROM monthly_revenue

10. Date and time manipulation

You should certainly expect some kind of SQL problem involving date-time data. For example, you might need to group data or convert variable format from DD-MM-Yyyy to simple months. You should be aware of the blame for YYYY-MM-DD.

Some of the features you should know are:

Refining

Japanese yen

Date_add,date_sub.

Date_trunc.

Example question: given the weather table, write a SQL query to find the ID of all the hotter dates compared to its previous (yesterday) date.

+-+ | Id (INT) | RecordDate (DATE) | Temperature (INT) | +-+ | 1 | 2015-01-01 | 10 | 2 | 2015-01-02 | 25 | 3 | 2015-01-03 | 20 | 4 | 2015-01-04 | 30 | +-+ -+ Answer: SELECT a.Id FROM Weather a Weather b WHERE a.Temperature > b.Temperature AND DATEDIFF (a.RecordDate, b.RecordDate) = 1 Thank you for your reading The above is the content of "what are the advanced SQL concepts that programmers need to know?" after the study of this article, I believe that programmers need to know what advanced SQL concepts have a deeper understanding of this problem, the specific use of the need for practice to verify. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.