SQL CASE expressions

CASE expressions are divided into simple expressions and search expressions. Search expressions can cover all the capabilities of simple expressions. I also recommend writing only search expressions, not simple expressions.

Simple expression:

SELECT CASE city
WHEN '北京' THEN 1
WHEN '天津' THEN 2
ELSE 0
END AS abc
FROM test

Search expression:

SELECT CASE
WHEN city = '北京' THEN 1
WHEN city = '天津' THEN 2
ELSE 0
END AS abc
FROM test

Obviously, the simple expression is only a special case of the search expression a = b , because no symbol can be written, as long as the condition is changed to a > b , it will not be able to do it, and the search expression can not only be easily competent, but also can write aggregate functions.

Aggregate functions in CASE expressions

Why can aggregate functions be written in CASE expressions?

Since the expression itself supports aggregate functions, such as the following syntax, we will not be surprised:

SELECT sum(pv), avg(uv) from test

SQL itself supports multiple different aggregation methods for simultaneous calculation, so it is natural to use it in CASE expressions:

SELECT CASE
WHEN count(city) = 100 THEN 1
WHEN sum(dau) > 200 THEN 2
ELSE 0
END AS abc
FROM test

As long as there is an aggregate function in the SQL expression, then the entire expression is aggregated, and there is no point in accessing non-aggregate variables. Therefore, in the above example, even if aggregation is used in the CASE expression, it is actually just aggregated once and then judged according to the conditions.

This feature can solve many practical problems, such as outputting the results of some complex aggregation judgment conditions with SQL structure, then it is likely to be written in the following way:

SELECT CASE
WHEN 聚合函数(字段) 符合什么条件 THEN xxx
... 可能有 N 个
ELSE NULL
END AS abc
FROM test

This can also be considered as a row-to-column process, that is, aggregates the results of rows to form new columns through CASE expressions.

Aggregate and non-aggregate cannot be mixed

We want to use the CASE expression to find those rows whose pv is greater than the average. The following is a wrong way of writing:

SELECT CASE
WHEN pv > avg(pv) THEN 'yes'
ELSE 'no'
END AS abc
FROM test

The reason is that as long as there is an aggregate expression in the SQL, the entire SQL is aggregated, so there is only one result returned, and we expect that the query result will not be aggregated, but the judgment condition uses the aggregated result, so we need to use subqueries .

Why does a subquery solve the problem? Because the aggregation of sub-queries occurs in the sub-queries without affecting the current parent query, understanding this, you will know why the following is correct:

SELECT CASE
WHEN pv > ( SELECT avg(pv) from test ) THEN 'yes'
ELSE 'no'
END AS abc
FROM test

This example also shows that subqueries can be used in CASE expressions. Because subqueries are calculated first, the query results can be used anywhere, and CASE expressions are no exception.

CASE in WHERE

WHERE can also be followed by a CASE expression to do some filtering that requires special enumeration processing.

For example the following example:

SELECT * FROM demo WHERE
CASE
WHEN city = '北京' THEN true
ELSE ID > 5
END

Originally, we wanted to query the data whose ID is greater than 5, but I want to treat the city of Beijing specially, so we can judge the CASE branch in the judgment condition.

This scenario is equivalent to creating a CASE expression field in the BI tool, which can be dragged into the filter condition to take effect.

CASE in GROUP BY

Unexpectedly, CASE expressions can be written in GROUP BY:

SELECT isPower, sum(gdp) FROM test GROUP BY CASE
WHEN isPower = 1 THEN city, area
ELSE city
END

The above example shows that when calculating GDP, for very developed cities, the aggregation results are viewed at the granularity of each district, that is, the granularity is finer, while for underdeveloped areas, the GDP itself is not high, and the aggregation is directly viewed at the granularity of the city. result.

In this way, the data is grouped and aggregated according to different conditions. Since the results of the returned rows are mixed, as in this example, whether the isPower field is 1 can be judged whether the aggregation is performed by city or region. If there are no other more significant identifiers, it may lead to inability to distinguish the aggregation granularity of different rows. Therefore, Use with caution.

CASE in ORDER BY

Similarly, ORDER BY uses the CASE expression to group the sorting results according to the CASE classification, and each group is sorted according to its own rules, such as:

SELECT * FROM test ORDER BY CASE
WHEN isPower = 1 THEN gdp
ELSE people
END

In the above example, the developed regions are sorted by gdp, otherwise, the population is sorted.

Summarize

In summary, CASE expressions have the following characteristics:

1. Support simple and search two writing methods, recommended search writing.
2. Aggregation and subqueries are supported, and you need to pay attention to the characteristics of different situations.
3. It can be written almost anywhere in the SQL query, as long as it is a place where a field can be written, it can basically be replaced by a CASE expression.
4. In addition to SELECT, CASE expressions are also widely used in INSERT and UPDATE. The magic of UPDATE is that there is no need to split SQL into multiple items, so there is no need to worry about the secondary impact of data changes on judgment conditions.

The discussion address is: Intensive Reading "SQL CASE Expression" Issue #404 ascoders/weekly

If you want to participate in the discussion, please click here , there are new topics every week, weekends or Mondays. Front-end intensive reading - help you filter reliable content.

Pay attention to front-end intensive reading WeChat public number

<img width=200 src="https://img.alicdn.com/tfs/TB165W0MCzqK1RjSZFLXXcn2XXa-258-258.jpg">

Copyright notice: Free to reprint - non-commercial - non-derivative - keep attribution ( Creative Commons 3.0 license )