MySQL 구문 혼동 - 단일 행 삽입을 위한 간단한 데이터 병합

MySQL 구문 혼동 - 단일 행 삽입을 위한 간단한 데이터 병합

고객을 위한 특정 정보 세트를 집계하기 위해 MySQL 테이블을 구축하고 있습니다.많은 부분을 심플하게 조립하고 필터링했습니다.INNER JOIN공유 키/키 레코드의 명령어를 사용하지만 데이터의 피벗/축소로 인해 고정된 크기일지라도 여전히 쿼리에 대한 구문 혼란이 발생합니다.이 테이블의 스키마t_snapshots는 다음과 같이 표시됩니다.

+-------------+---------------------+------+-----+---------------------+----------------+
| Field       | Type                | Null | Key | Default             | Extra          |
+-------------+---------------------+------+-----+---------------------+----------------+
| id          | int(11)             | NO   | PRI | NULL                | auto_increment | 
| value_type  | tinyint(1) unsigned | YES  |     | NULL                |                |
| ch1_id      | varchar(20)         | YES  |     | NULL                |                |
| ch1_val     | float               | NO   |     | 0                   |                |
| ch2_id      | varchar(20)         | YES  |     | NULL                |                |
| ch2_val     | float               | NO   |     | 0                   |                |
| ch3_id      | varchar(20)         | YES  |     | NULL                |                |
| ch3_val     | float               | NO   |     | 0                   |                |
| ch4_id      | varchar(20)         | YES  |     | NULL                |                |
| ch4_val     | float               | NO   |     | 0                   |                |
| timestamp   | datetime            | NO   | MUL | current_timestamp() |                |
+-------------+---------------------+------+-----+---------------------+----------------+

간단히 말하면, 가장 최근에 갱신된 값을t_other_data0.ch[n]보내다t_snapshots.ch[n]_val최신 업데이트 ID를 선택합니다.t_id_pool.unit_id각각에 대해서t_id_pool.channel_num보내다t_snapshots.ch[n]_id.t_id_pool.channel_num컬럼의 n개 값과 관련이 있습니다.t_snapshots.ch[n]_val:

--EDIT--: 이상적으로는 소스 테이블의 샘플 데이터입니다.t_other_data0최신 정보를 찾고 있습니다.unit_id로부터 값을 매기다.t_id_pool위해서channel_num=1,2,3,4및 출력은 테이블로t_snapshots:

최신 프로세스 데이터 수집:t_other_data0. 이 경우 행은id5-8은 모두 구별되기 때문에 선택됩니다.value_type최신판timestamp.:

Table: t_other_data0
+----+------+------+------+------+------------+---------------------+
| id | ch1  | ch2  | ch3  | ch4  | value_type | timestamp           |
+----+------+------+------+------+------------+---------------------+
|  1 | 1.65 | 3.25 | 1.98 | 2.17 |          1 | 2021-07-22 16:26:40 |
|  2 | 3.12 | 2.33 | 6.42 | 3.22 |          2 | 2021-07-22 16:26:40 |
|  3 | 2.22 | 2.24 | 3.34 | 1.17 |          3 | 2021-07-22 16:26:40 |
|  4 | 1.52 | 1.34 |  1.9 | 2.01 |          4 | 2021-07-22 16:26:40 |
|  5 |  3.2 | 3.21 | 5.42 | 2.13 |          1 | 2021-07-22 16:26:50 |
|  6 | 1.55 | 1.92 | 4.32 | 4.12 |          2 | 2021-07-22 16:26:50 |
|  7 | 2.31 | 1.93 | 2.36 |  3.4 |          3 | 2021-07-22 16:26:50 |
|  8 | 1.78 | 2.17 | 5.62 | 2.34 |          4 | 2021-07-22 16:26:50 |
+----+------+------+------+------+------------+---------------------+

이 영구 채널들은 어떤 임시 장비에 연결되어 있는지 변경하기 때문에 우리는 전류를 결정합니다.unit_id각각에 대해서channel_num최신 사용unit_id부터t_id_pool:

Table: t_id_pool
+----+---------------------+-------------+---------+
| id | timestamp           | channel_num | unit_id |
+----+---------------------+-------------+---------+
|  1 | 2021-07-22 09:39:09 |           1 | S4251   |
|  2 | 2021-07-22 09:38:09 |           2 | S3552   |
|  3 | 2021-07-22 09:38:09 |           3 | S0001   |
|  4 | 2021-07-22 09:38:09 |           4 | S1001   |
|  5 | 2021-07-22 09:39:10 |           1 | P5251   |
|  6 | 2021-07-22 09:38:10 |           2 | P4552   |
|  7 | 2021-07-22 09:38:10 |           3 | P1001   |
|  8 | 2021-07-22 09:38:10 |           4 | P2001
+----+---------------------+-------------+---------+

출력처t_snapshots:

Table: t_snapshots
+-----+---------------------+------------+--------+---------+--------+---------+--------+---------+--------+---------+
| id  | timestamp           | value_type | ch1_id | ch1_val | ch2_id | ch2_val | ch3_id | ch3_val | ch4_id | ch4_val |
+-----+---------------------+------------+--------+---------+--------+---------+--------+---------+--------+---------+
| 211 | 2021-07-14 16:26:50 |          1 | P5251  |     3.2 | P4552  |    3.21 | P1001  |    5.42 | P2001  |    2.13 |
| 212 | 2021-07-14 16:26:50 |          2 | P5251  |    1.55 | P4552  |    1.92 | P1001  |    4.32 | P2001  |    4.12 |
| 213 | 2021-07-14 16:26:50 |          3 | P5251  |    2.31 | P4552  |    1.93 | P1001  |    2.36 | P2001  |     3.4 |
+-----+---------------------+------------+--------+---------+--------+---------+--------+---------+--------+---------+



 

테이블t_other_data0피벗 테이블인 것 같습니다.따라서 첫 번째 단계는 pivot을 해제하고 pivot을 결합하는 것이라고 생각합니다.t_id_pool최신 정보를 얻을 수 있는 테이블unit_id를 다시 표시합니다.다음과 같은 질문이 적용될 수 있습니다.

SELECT 0 id, tod.timestamp, value_type,
       MAX(case when channel_num=1 THEN unit_id else 0 END) AS ch1_id,
       SUM(case when channel_num=1 then chan_val else 0 END) as ch1_val,
       MAX(CASE WHEN channel_num=2 THEN unit_id ELSE 0 END) AS ch2_id,
       SUM(CASE WHEN channel_num=2 THEN chan_val ELSE 0 END) AS ch2_val,
       MAX(CASE WHEN channel_num=3 THEN unit_id ELSE 0 END) AS ch3_id,
       SUM(CASE WHEN channel_num=3 THEN chan_val ELSE 0 END) AS ch3_val,
       MAX(CASE WHEN channel_num=4 THEN unit_id ELSE 0 END) AS ch4_id,
       SUM(CASE WHEN channel_num=4 THEN chan_val ELSE 0 END) AS ch4_val
FROM   (SELECT value_type, ch1 AS chan_val, 1 AS chan_num, timestamp
        FROM   (SELECT *, Row_number() OVER (partition BY value_type ORDER BY id DESC) rn
                FROM t_other_data0) AS A
        WHERE  rn = 1 UNION ALL
        SELECT value_type, ch2, 2,
               timestamp
        FROM   (SELECT *, Row_number() OVER (partition BY value_type ORDER BY id DESC) rn
                FROM t_other_data0) AS A
        WHERE  rn = 1 UNION ALL
        SELECT value_type, ch3, 3,
               timestamp
        FROM   (SELECT *, Row_number() OVER (partition BY value_type ORDER BY id DESC) rn
                FROM t_other_data0) AS A
        WHERE  rn = 1 UNION ALL
        SELECT value_type, ch4, 4,
               timestamp
        FROM   (SELECT *, Row_number() OVER (partition BY value_type ORDER BY id DESC) rn
                FROM t_other_data0) AS A
        WHERE  rn = 1) AS tod
       JOIN (SELECT id, timestamp, channel_num, unit_id, 
                    Row_number() OVER (partition BY channel_num ORDER BY timestamp DESC) rn
             FROM t_id_pool) AS tip
         ON tod.chan_num = tip.channel_num AND tip.rn = 1
    GROUP BY tod.timestamp, value_type;

여기서 사용되는 기능 중 하나는ROW_NUMBER()행 번호를 할당할 목적으로1최근에value_type그리고.channel_number타임스탬프테이블은t_other_data0, 사용하고 있습니다.UNION ALL컬럼 뒤에 총 4개의 쿼리가 있습니다.ch1, ch2, ch3 & ch4각각 하드코드로 할당했습니다.chan_num어떤 칼럼을 쓰느냐에 따라 다르죠

칼럼은 잘 모르겠습니다.id무엇을 채울지는 알 수 없지만 질의의 주된 목적은INSERT다른 테이블로 옮긴다면, 아마도id열은 자동 증분입니다.

안타깝게도 어제부터 dbfiddle.uk를 사용할 수 없기 때문에 이 바이올린은 MariaDB 10.3이 아닌 MySQL v8.0용입니다.https://www.db-fiddle.com/f/xf1VmfYMbnGcabJS7dS6A1/1. 바이올린 결과에는 다음 행이 추가됩니다.t_other_data.id=8(@danblack에 의해 코멘트에서 언급됨)는 포함되지 않습니다.id=4조건이 "t_other_table0에서 최신 프로세스 데이터 수집"이기 때문입니다.하지만 당신이 예상한 결과들을 보면, 당신은 이 사건들을 넣지 않은 것 같아요.id=4아마 당신 설명에 오타가 있었을 거예요

언급URL : https://stackoverflow.com/questions/68479538/mysql-syntax-confusion-merging-together-simple-data-for-a-graceful-single-row