[MySQL] SQL SELF JOIN & DATE type format

To get to know about SELF JOIN & DATE type format, let's solve Leetcode problem in https://leetcode.com/problems/rising-temperature/ . If you want to know about SELF JOIN, you can go HERE.

---

What this asking for is, "Select 'ID' with a higher temperature than yesterday." And here's the Weather table.

Weather table:
+----+------------+-------------+
| id | recordDate | temperature |
+----+------------+-------------+
| 1  | 2015-01-01 | 10          |
| 2  | 2015-01-02 | 25          |
| 3  | 2015-01-03 | 20          |
| 4  | 2015-01-04 | 30          |
+----+------------+-------------+

If someone want to compare temperatures between today and previous day, what he/she need to do is SELF JOIN. But if we just do that, then, the structure of the table looks weird. Because the table gets attached to the side. How do we connect today's temperature to the previous day?

 

INNER JOIN WEATHER AS YESTERDAY ON YESTERDAY.(ID+1) = TODAY.ID. We can achieve the goal by doing this. When do SELF JOIN, it's good to distinguish the names differently. someone might said " I'm not confused. Because I'm super smart. LOL" But, I highly recommend you to just do this.)

SELECT * 
FROM WEATHER AS TODAY
INNER JOIN WEATHER AS YESTERDAY ON YESTERDAY.(ID+1) = TODAY.ID

 

If you declare above code, the result is as follows. (The red boldface is today's temp and yesterday's temp on the right is an inner join.)

| id | recordDate | temperature | id | recordDate | temperature |
| -- | ---------- | ----------- | -- | ---------- | ----------- |
| 2 | 2015-01-02 | 25 | 1 | 2015-01-01 | 10 |
| 3 | 2015-01-03 | 20 | 2 | 2015-01-02 | 25 |
| 4 | 2015-01-04 | 30 | 3 | 2015-01-03 | 20 |

---

Then, you can just declare 'WHERE today.tmp > yesterday.tmp' and print id, like this.

| id | recordDate | temperature | id | recordDate | temperature |
| -- | ---------- | ----------- | -- | ---------- | ----------- |
| 2 | 2015-01-02 | 25 | 1 | 2015-01-01 | 10 |
| 3 | 2015-01-03 | 20 | 2 | 2015-01-02 | 25 |
| 4 | 2015-01-04 | 30 | 3 | 2015-01-03 | 20 |

 

If you press submit whilest think, "Then, the result will come out like this, It will come out like 2, 4, right? As expected, I'm a genius." But the result is, you wrong. Because there is no premise that the id is directly proportional to the date, one-on-one correspondence. The questioner hid it in the test case! It's little bit mean, but also good for building our problem solving skills. Any way, we need to data format related function to solve this problem. 

---

DATE format related Function

- DATE format Add function : SELECT DATE_ADD(NOW(), INTERVAL 1 ___)

The words that can be entered in ___ are SECOND, MINUTE, HOUR, DAY, MONTH,YEAR. If we add another number instead of 1, we can add amount of ___, and if we add negative number, lt works as subtraction.

 

- DATE format Subtract function : SELECT DATE_SUB(NOW(), INTERVAL 1 ___)

Like ADD function, the words that can be entered in ___ are SECOND, MINUTE, HOUR, DAY, MONTH,YEAR. It means both add function and substract function can make subtraction. Use what you think comportable.

---

Problem Solving Using DATE type format

SELECT TODAY.ID 
FROM WEATHER AS TODAY
INNER JOIN WEATHER AS YESTERDAY ON DATE_ADD(YESTERDAY.RECORDDATE,INTERVAL 1 DAY) = TODAY.RECORDDATE
WHERE TODAY.TEMPERATURE > YESTERDAY.TEMPERATURE

As a result, code above is the answer to this problem.

 

Is there anyone who thinks, " I'll going to put a negative number in Subtract function to add a positive number!" Actually, this is me. You know, just for curiosity... at least we can check whether it's possible or not. An experiment have shown that it's working well. Regardless of the result, don't do this and just use ADD function to add something. I'm going to use the ADD function, too. That's all for the [MySQL] SQL SELF JOIN & DATE format type posting. Happy SQL.