SQL is one of the basic skills that all data practitioners must master. Solid SQL queries and appropriate tuning skills are two important criteria to test SQL ability. I have spent a lot of time to improve SQL ability, during the brush a large number of SQL topics, during this period also constantly groped and summed up some tips, today this article to share two of them, is almost the daily use of the most frequent two.
To illustrate these two tips, here’s the sample data used in this article, which was actually used in the previous tweet:
Sample student grade sheet
01 SQL data type implicit conversion
Many programming languages restrict data types. For example, in Python the string “0” is not equal to the value 0, and the Boolean True is not equal to the value 1. But in SQL, the implicit conversion of data types is built in, so the query select “0”=0 returns 1. This indicates that the string “0” is equal to the value 0, and that the Boolean value in SQL is automatically converted to either 1 or 0.
With this little trick, many tricks can be used to achieve many statistical indicators. For example, calculate the success rate of each student in the student achievement table, that is, the proportion of subjects with a score of 90 or more to their total subjects. To achieve this query, the normal operation is to count the number of subjects and total subjects for each student with a score of 90 or more, and then divide them to get the proportion.
In fact, based on the small example above, this requirement can be implemented as follows:
Among them, in the grouping of each student, the conditional judgment score>=90 returns a group of 0 or 1 results, and the value of 0/1 is averaged, which is equivalent to the proportion of subjects with scores >=90.
There are many functions built into SQL, but the if function should be particularly common and often used by a programmer. On the other hand, null is one of the magic properties of SQL. It has two main features: first, any value and any calculation of NULL will get null; The second is that null values are not evaluated in any aggregate function (for example, count counts the number of results other than null). The combination of the if function and null values can sometimes work well.
For example, according to the above student score table, the average score of each student above 80 points should be counted separately, that is, if the score >=80, the final mean calculation will be participated, otherwise not. With the if function and null features, it can be elegantly implemented as follows:
In fact, in the above query statement, a temporary field if(score>=80, score, NULL) is derived through the if function first, and its value is: the score value is taken when the score is ≥80; otherwise, it is set to NULL, which means that only the records with scores above 80 are retained. Then a layer of nested AVG function can directly calculate the average value of more than 80 points.
Of course, for this requirement, records with scores above 80 points can be filtered by using THE WHERE condition and then aggregated for statistics.
