TIL day 57

1. 코딩테스트(괄호 회전하기)

import java.util.Stack;

class Solution {
    public int solution(String s) {
        int answer = 0;
        
        for (int i = 0; i < s.length(); i++) {
            Stack stack = new Stack<>();
            String str = s.substring(i,s.length()) + s.substring(0,i);

            for (int j = 0; j < str.length(); j++) {
                char c = str.charAt(j);
                
                if(stack.isEmpty()) {
                    stack.push(c);
                } else if (c == ']' && stack.peek().equals('[')) {
                    stack.pop();
                } else if (c == ')' && stack.peek().equals('(')) {
                    stack.pop();
                } else if (c == '}' && stack.peek().equals('{')) {
                    stack.pop();
                } else {
                    stack.push(c);
                }
            }
            
            if(stack.isEmpty()) {
                answer++;
            }
        }
        return answer;
    }
}

---

2. SQL 테스트(오랜 기간 보호한 동물(2))

SELECT o.ANIMAL_ID, o.NAME
FROM ANIMAL_OUTS o
INNER JOIN ANIMAL_INS i ON i.ANIMAL_ID = o.ANIMAL_ID
ORDER BY DATEDIFF(o.DATETIME,i.DATETIME) DESC
LIMIT 2

날짜 사이의 기간 값을 알고 싶으면 DATEDIFF()를 하면 된다.

---

3. MySQL EXPLAIN

mysql> explain select m.name, m.contact, m.address, t.transaction_id, t.price, t.state from transaction t inner join member m on m.idx = t.member_idx where t.state = 'failure';
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                  | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | ALL    | member_idx    | NULL    | NULL    | NULL                 |  300 |    10.00 | Using where |
|  1 | SIMPLE      | m     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test_db.t.member_idx |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql> create index state on transaction (state);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select m.name, m.contact, m.address, t.transaction_id, t.price, t.state from transaction t inner join member m on m.idx = t.member_idx where t.state = 'failure';
+----+-------------+-------+------------+--------+------------------+---------+---------+----------------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys    | key     | key_len | ref                  | rows | filtered | Extra |
+----+-------------+-------+------------+--------+------------------+---------+---------+----------------------+------+----------+-------+
|  1 | SIMPLE      | t     | NULL       | ref    | member_idx,state | state   | 137     | const                |  100 |   100.00 | NULL  |
|  1 | SIMPLE      | m     | NULL       | eq_ref | PRIMARY          | PRIMARY | 4       | test_db.t.member_idx |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+------------------+---------+---------+----------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

인덱싱 전후를 EXPLAIN으로 결과를 보여주고 있다. EXPLAIN ANALYZE를 통해서는 쿼리 실행에 소비되는 시간도 알 수 있다고 한다.