1. RB_OCUS_POS_TEST2테이블에 200,00개의 데이타를 넣는다.
2. 1초에 3,000개 이상의 데이타가 들어가는 횟수를 알고 싶다면?
3. 아래는 위의 문제를 구하기 위해서 구성한 쿼리이다.
mysql> show create table RB_OCUS_POS_TEST2;
+-------------------+-----------------------------------
| Table | Create
+-------------------+-----------------------------------
| RB_OCUS_POS_TEST2 | CREATE TABLE `RB_OCUS_POS_TEST2` (
`seqno` int(11) NOT NULL,
`account_no` varchar(10) NOT NULL,
`stock_symbol` varchar(20) NOT NULL,
`start_volume` varchar(10) DEFAULT NULL,
`start_amount` varchar(13) DEFAULT NULL,
`start_price` varchar(13) DEFAULT NULL,
`stock_type` varchar(2) DEFAULT NULL,
`trustee_id` varchar(2) DEFAULT NULL,
`regtime` varchar(30) DEFAULT NULL,
PRIMARY KEY (`seqno`,`account_no`,`stock_symbol`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------------+-----------------------------------
1 row in set (0.00 sec)
mysql>
mysql> desc RB_OCUS_POS_TEST2;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| seqno | int(11) | NO | PRI | NULL | |
| account_no | varchar(10) | NO | PRI | NULL | |
| stock_symbol | varchar(20) | NO | PRI | NULL | |
| start_volume | varchar(10) | YES | | NULL | |
| start_amount | varchar(13) | YES | | NULL | |
| start_price | varchar(13) | YES | | NULL | |
| stock_type | varchar(2) | YES | | NULL | |
| trustee_id | varchar(2) | YES | | NULL | |
| regtime | varchar(30) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
9 rows in set (0.00 sec)
mysql>
mysql> select * from RB_OCUS_POS_TEST2 limit 10;
+-------+------------+----------------------+--------------+---------------+---------------+------------+------------+------------------------+
| seqno | account_no | stock_symbol | start_volume | start_amount | start_price | stock_type | trustee_id | regtime |
+-------+------------+----------------------+--------------+---------------+---------------+------------+------------+------------------------+
| 1 | 0016100 | BBL | 0000001500 | 0000266962.22 | 000177.974813 | 02 | | 20190828 11:19:25(140) |
| 2 | 0016100 | BPP | 0000002000 | 0000042000.00 | 000021.000000 | 02 | | 20190828 11:19:25(141) |
| 3 | 0016100 | CK | 0000015375 | 0000296816.51 | 000019.305139 | 02 | | 20190828 11:19:25(142) |
| 4 | 0016100 | IEC | 0012286603 | 0000308909.54 | 000000.025142 | 02 | | 20190828 11:19:25(142) |
| 5 | 0016100 | IVL | 0000011769 | 0000309729.03 | 000026.317362 | 02 | | 20190828 11:19:25(142) |
| 6 | 0016100 | KTB | 0000005000 | 0000103785.51 | 000020.757102 | 02 | | 20190828 11:19:25(142) |
| 7 | 0055920 | ASAP | 0000001000 | 0000003106.87 | 000003.106870 | 02 | | 20190828 11:19:25(142) |
| 8 | 0055920 | BCP | 0000001000 | 0000033574.22 | 000033.574220 | 02 | | 20190828 11:19:25(142) |
| 9 | 0055920 | CBG | 0000000800 | 0000042844.70 | 000053.555875 | 02 | | 20190828 11:19:25(143) |
| 10 | 0055920 | CHAYO | 0000002000 | 0000008739.30 | 000004.369650 | 02 | | 20190828 11:19:25(143) |
+-------+------------+----------------------+--------------+---------------+---------------+------------+------------+------------------------+
10 rows in set (0.00 sec)
mysql>
mysql> select T2.t_cnt from DUMMY T1, (select substr(regtime,1,17) as t_regtime, count(1) as t_cnt from RB_OCUS_POS_TEST2 group by substr(regtime,1,17)) T2 limit 10;
+-------+
| t_cnt |
+-------+
| 2149 |
| 2556 |
| 3462 |
| 2635 |
| 2780 |
| 3051 |
| 2450 |
| 2310 |
| 2695 |
| 2716 |
+-------+
10 rows in set (0.22 sec)
mysql>
mysql> select T2.t_cnt from DUMMY T1, (select substr(regtime,1,17) as t_regtime, count(1) as t_cnt from RB_OCUS_POS_TEST2 group by substr(regtime,1,17)) T2 where T2.t_cnt > 3000;
+-------+
| t_cnt |
+-------+
| 3462 |
| 3051 |
| 3192 |
| 3761 |
| 3085 |
| 3418 |
| 3341 |
| 3066 |
| 3353 |
| 3287 |
| 3037 |
+-------+
11 rows in set (0.23 sec)
mysql>
mysql> select * from DUMMY;
+-------+------------+----------------------+--------------+---------------+---------------+------------+------------+
| seqno | account_no | stock_symbol | start_volume | start_amount | start_price | stock_type | trustee_id |
+-------+------------+----------------------+--------------+---------------+---------------+------------+------------+
| 1 | 0016100 | BANPU | 0000045000 | 0000645569.57 | 000014.345990 | 02 | |
+-------+------------+----------------------+--------------+---------------+---------------+------------+------------+
1 row in set (0.00 sec)
mysql>
mysql> select T2.t_regtime,T2.t_cnt from DUMMY T1, (select substr(regtime,1,17) as t_regtime, count(1) as t_cnt from RB_OCUS_POS_TEST2 group by substr(regtime,1,17)) T2 where T2.t_cnt > 3000;
+-------------------+-------+
| t_regtime | t_cnt |
+-------------------+-------+
| 20190828 11:19:27 | 3462 |
| 20190828 11:19:30 | 3051 |
| 20190828 11:19:38 | 3192 |
| 20190828 11:19:46 | 3761 |
| 20190828 11:19:47 | 3085 |
| 20190828 11:19:55 | 3418 |
| 20190828 11:20:00 | 3341 |
| 20190828 11:20:03 | 3066 |
| 20190828 11:20:12 | 3353 |
| 20190828 11:20:15 | 3287 |
| 20190828 11:20:20 | 3037 |
+-------------------+-------+
11 rows in set (0.19 sec)
mysql>
mysql> select T2.t_regtime,T2.t_cnt from DUMMY T1, (select substr(regtime,1,17) as t_regtime, count(1) as t_cnt from RB_OCUS_POS_TEST2 group by substr(regtime,1,17)) T2 where T2.t_cnt > 3000 order by T2.t_cnt desc;
+-------------------+-------+
| t_regtime | t_cnt |
+-------------------+-------+
| 20190828 11:19:46 | 3761 |
| 20190828 11:19:27 | 3462 |
| 20190828 11:19:55 | 3418 |
| 20190828 11:20:12 | 3353 |
| 20190828 11:20:00 | 3341 |
| 20190828 11:20:15 | 3287 |
| 20190828 11:19:38 | 3192 |
| 20190828 11:19:47 | 3085 |
| 20190828 11:20:03 | 3066 |
| 20190828 11:19:30 | 3051 |
| 20190828 11:20:20 | 3037 |
+-------------------+-------+
11 rows in set (0.00 sec)
mysql>
'데이타베이스 > MySQL' 카테고리의 다른 글
| MySQL ROWNUM(select @r:=@r+1 As rowNum) (0) | 2019.10.31 |
|---|---|
| TIP - SEQ증감 (0) | 2019.10.31 |
| FUNCTION 생성및 활용 (0) | 2019.10.31 |
| LINUX,프로그래밍을 통한 PROCEDURE 호출후 결과 (0) | 2019.10.30 |
| #LINUX,common.c(MySQL) (0) | 2019.10.30 |