How to understand conditional expressions in the SELECT statement-CodePudding

I encountered a query as so:

SELECT
   (period_of >= '2022-05-01')   (period_of >= '2022-06-01')   (period_of >= '2022-07-01')   (period_of >= '2022-08-01') -1 as 'bucket',
    MAX(period_of) as max_dt, MIN(period_of) as min_dt
FROM table_a
WHERE project_id = 123
GROUP BY bucket

I don't understand this part of the SELECT statement: (period_of >= '2022-05-01') (period_of >= '2022-06-01') (period_of >= '2022-07-01') (period_of >= '2022-08-01') -1 as 'bucket'

Where can I find ref to this syntax style for the SELECT component?

How can I understand this kind of query?

CodePudding user response：

The value of a conditional expression is 1 if the condition is true, 0 if the condition is true. So

(period_of >= '2022-05-01')

will be either 1 or 0 depending on how period_of compares to that date.

For instance, if period_of = 2022-07-15, it will add 1 for 2022-05-01, 2022-06-01 and 2022-0701, and 0 for 2022-08-01, so bucket will be 3. But if period_05 = 2022-05-20 it will only add 1 for 2022-05-01, and 0 for the rest, so bucket will be 1. If period_of is before 2022-05-01 bucket will be 0.

So that expression is adding up how many of the 4 dates the period_of date is greater than or equal to. Then this total is used for grouping all the results.

This is equivalent to GROUP BY YEAR(period_of), MONTH(period_of), except that everything before May 2022 is grouped together in bucket 0 instead of separate buckets for each month.