mysql 쿼리 최적화 및 필드 값 조작

mysql 쿼리 최적화 및 필드 값 조작

mysql> select * from dts;
+----+------+------+--------+------+------+------+------+------+
| Id | key1 | key2 | serial | pr1  | pr2  | pr3  | pr4  | pr5  |
+----+------+------+--------+------+------+------+------+------+
|  1 |    1 |    1 |      1 |    0 |    0 |    1 |    0 |    2 |
|  2 |    1 |    1 |      2 |    0 |    0 |    0 |    0 |    0 |
|  3 |    1 |    1 |      3 |    0 |    0 |    0 |    1 |    0 |
|  4 |    1 |    1 |      4 |    1 |    0 |    1 |    1 |    3 |
|  5 |    1 |    2 |      5 |    0 |    0 |    0 |    2 |    5 |
|  6 |    1 |    2 |      6 |    0 |    0 |    0 |    0 |    1 |
|  7 |    1 |    2 |      7 |    0 |    1 |    0 |    0 |    0 |
|  8 |    2 |    2 |      1 |    1 |    1 |    1 |    1 |    2 |
|  9 |    2 |    2 |      2 |    0 |    0 |    0 |    0 |    0 |
| 10 |    3 |    2 |      3 |    0 |    0 |    0 |    0 |    0 |
| 11 |    3 |    3 |      1 |    1 |    1 |    0 |    0 |    1 |
| 12 |    3 |    3 |      5 |    0 |    0 |    1 |    1 |    0 |
+----+------+------+--------+------+------+------+------+------+
12 rows in set (0.00 sec)

여기서 구현하고자 하는 논리는 다음과 같습니다.

1. 테이블 dts의 필드(pr1-pr5)에 0이 아닌 데이터가 있는지 확인합니다.
2. 쉼표가 포함된 concat 필드 이름이 발견되면 모든 필드가 0이 아닌 경우 필드를 concat하고 동일한 키의 레코드 읽기를 중지합니다(실행 시간을 절약하기 위해 key1, key2의 조합, key1=2 및 key2의 위 표에서 8번째 행을 확인합니다. 모두 0이 아니므로 동일한 키로 다음 레코드 읽기를 중지합니다). 다음 key1, key2로 이동합니다.

작동하지 않는 것은 다음과 같습니다.

현재 제가 사용하고 있는 코드는 작동하고 있지만 충분한 필드가 발견되어도 읽기 행을 건너뛰지 않습니다. 현재 예제에서 볼 수 있듯이 12개의 행 X 5 Col = 60 Rows(내부 선택 문을 실행하면 60 행이 반환됨)를 생성한 다음 key1과 key2로 그룹화합니다.2-3백만 개의 레코드가 있는 테이블이 더 빨라지도록 효율적인 간단한 방법으로 수행할 수 있습니까?

예상 출력

+------+------+---------------------+
| key1 | key2 | prs                 |
+------+------+---------------------+
|    1 |    1 | pr1,pr3,pr4,pr5     |
|    1 |    2 | pr2,pr4,pr5         |
|    2 |    2 | pr1,pr2,pr3,pr4,pr5 |
|    3 |    2 | NULL                |
|    3 |    3 | pr1,pr2,pr3,pr4,pr5 |
+------+------+---------------------+

따라서 큰 테이블을 사용하여 성능을 향상시키기 위해 이것을 단순화할 수 있는 방법은 위에서 볼 수 있듯이 단순합니다. 키1, 키2의 각 조합에 대해 0이 아닌 값을 가진 필드(pr1-pr5)가 몇 개 있는지 알고 싶습니다.

코드 사용 중

SELECT 
    key1,
    key2,
    group_concat(distinct case when val > 0 then pr end order by pr separator ',') prs
FROM (
    SELECT 
        d.key1,
            d.key2,
            t.pr,
            CASE t.pr
                WHEN 'pr1' THEN pr1
                WHEN 'pr2' THEN pr2
                WHEN 'pr3' THEN pr3
                WHEN 'pr4' THEN pr4
                WHEN 'pr5' THEN pr5
            END val
    FROM
        dts d
    CROSS JOIN (
        SELECT 'pr1' pr UNION ALL 
        SELECT 'pr2' UNION ALL 
        SELECT 'pr3' UNION ALL 
        SELECT 'pr4' UNION ALL 
        SELECT 'pr5'
    ) t
) r
GROUP BY key1 , key2;

구조.

DROP TABLE IF EXISTS `dts`;
CREATE TABLE `dts` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `key1` int(11) DEFAULT '-99',
  `key2` int(11) DEFAULT '-99',
  `serial` int(11) DEFAULT '-99',
  `pr1` int(11) DEFAULT '-99',
  `pr2` int(11) DEFAULT '-99',
  `pr3` int(11) DEFAULT '-99',
  `pr4` int(11) DEFAULT '-99',
  `pr5` int(11) DEFAULT '-99',
  PRIMARY KEY (`Id`),
  KEY `main` (`key1`,`key2`,`serial`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;


LOCK TABLES `dts` WRITE;
INSERT INTO `dts` VALUES (1,1,1,1,0,0,1,0,2),(2,1,1,2,0,0,0,0,0),(3,1,1,3,0,0,0,1,0),(4,1,1,4,1,0,1,1,3),(5,1,2,5,0,0,0,2,5),(6,1,2,6,0,0,0,0,1),(7,1,2,7,0,1,0,0,0),(8,2,2,1,1,1,1,1,2),(9,2,2,2,0,0,0,0,0),(10,3,2,3,0,0,0,0,0),(11,3,3,1,1,1,0,0,1),(12,3,3,5,0,0,1,1,0);
UNLOCK TABLES;

블록 견적

SELECT key1, key2,
       CONCAT_WS(',',
         IF(pr1=0, NULL, 'pr1'),
         IF(pr2=0, NULL, 'pr2'),
         IF(pr3=0, NULL, 'pr3'),
         IF(pr4=0, NULL, 'pr4'),
         IF(pr5=0, NULL, 'pr5') ) AS prs
    FROM (
           SELECT key1, key2,
               SUM(pr1) AS pr1,
               SUM(pr2) AS pr2,
               SUM(pr3) AS pr3,
               SUM(pr4) AS pr4,
               SUM(pr5) AS pr5
            FROM dts
            GROUP BY key1, key2
         ) AS sums;

3.2 행에 "NULL"이 필요한 경우 다음을 추가할 수 있습니다.IFNULL고치기 위해.

언급URL : https://stackoverflow.com/questions/44393184/mysql-query-optimization-and-manipulation-of-field-value