SQL - 여러 선택 항목의 여러 열에 삽입

SQL - 여러 선택 항목의 여러 열에 삽입

저는 MariaDB 데이터베이스를 가지고 있습니다.

DB 안에는 다음 표 1이 있습니다.

|     id       |     timestamp     |  unit |  detector  |    value    |
 -------------------------------------------------------------------------
     UUID()         2020-12-02          1        1             0.1
     UUID2()        2020-12-02          1        2             0.2
     UUID3()        2020-12-02          2        1             0.3
     UUID4()        2020-12-02          2        2             0.4
     UUID5()        2020-12-03          1        1             0.5
     UUID6()        2020-12-03          1        2             0.6
     UUID7()        2020-12-03          2        1             0.7
     UUID8()        2020-12-03          2        2             0.8

데이터를 이 새 테이블, 표 2에 매핑하라는 요청을 받았습니다.

 |     id       |     timestamp     |  detector 11   |   detector 12   |  detector 21  |  detector 22 |
  ----------------------------------------------------------------------------------------------------
       UUI9()         2020-12-02            0.1              0.2             0.3             0.4
       UUID10()        2020-12-03            0.5              0.6             0.7             0.8

이 상황과의 유일한 차이점은 100개의 검출기와 장치 조합과 3600만 개의 행이 있다는 것입니다.나는 1개의 디텍터에 대해 원하는 값을 얻을 수 있는 코드를 작성했지만 여러 행 -> 열을 동시에 수행하는 방법을 찾을 수 없습니다.제가 수동으로 할 리가 없어요, 몇 주가 걸릴 거예요.

INSERT INTO table2
(id, timestamp, detector11)
SELECT UUID(), t1.timestamp, t1.value FROM table1 t1
WHERE t1.unit='1' AND t1.detector='1'
ORDER BY timestamp ;

이렇게 하면 타임스탬프가 좋은 표 1의 데이터가 성공적으로 (10진수=1, 단위=1) 열(10진수11)로 변환됩니다.그러나 이제 ID, 타임스탬프 및 디텍터11에서 NULL을 제외한 나머지 열은 모두 사용할 수 있습니다.

이상적으로, 누군가가 다음과 같은 것을 코딩하는 것을 도와줄 수 있습니다.

INSERT INTO table2
(id, timestamp, detector11, detector12, detector21, detector22)
SELECT UUID(), t1.timestamp, 
VALUES(t1.value FROM table1 t1
       WHERE t1.unit='1' AND t1.detector='1'
       ORDER BY timestamp,

       t1.value FROM table1 t1
       WHERE t1.unit='1' AND t1.detector='2'
       ORDER BY timestamp,

       t1.value FROM table1 t1
       WHERE t1.unit='2' AND t1.detector='1'
       ORDER BY timestamp,

       t1.value FROM table1 t1
       WHERE t1.unit='2' AND t1.detector='2'
       ORDER BY timestamp) ;

모든 열을 동시에 채울 수 있습니다.

별도의 테이블에만 가입할 수 있습니다.

문제는 가치를 결합하는 것입니다. 거기서 당신은 당신의 가치가ON조인에 대한 문이 너무 유효합니다.

CREATE TABLE table2 (id varchar(20), `timestamp` TIMESTAMP, detector11 DECIMAL(4,2), detector12 DECIMAL(4,2)
, detector21 DECIMAL(4,2), detector22 DECIMAL(4,2))

CREATE TABLE table1 (`timestamp`TIMESTAMP, value DECIMAL(4,2), unit INT,detector INT)

INSERT INTO table2
(id, timestamp, detector11, detector12, detector21, detector22)
SELECT UUID(),t1a.timestamp, t1a.detector11,t1b.detector12,t1c.detector21,t1d.detector22
FROM
(SELECT
t1.timestamp, t1.value as detector11 FROM table1 t1
WHERE t1.unit='1' AND t1.detector='1'
ORDER BY timestamp ) t1a
JOIN
  (SELECT t1.timestamp, t1.value AS detector12 FROM table1 t1
       WHERE t1.unit='1' AND t1.detector='2'
       ORDER BY timestamp) t1b
       ON t1a.timestamp = t1b.timestamp
JOIN       
  (SELECT t1.timestamp,t1.value AS detector21 FROM table1 t1
       WHERE t1.unit='2' AND t1.detector='1'
       ORDER BY timestamp) t1c
       ON t1a.timestamp = t1c.timestamp
JOIN
  (SELECT t1.timestamp,t1.value AS detector22 FROM table1 t1
       WHERE t1.unit='2' AND t1.detector='2'
       ORDER BY timestamp) t1d
  ON t1a.timestamp = t1d.timestamp

✓

db<>여기로 이동

다른 접근법을 시도할 수 있지만, 이 방법도 수백 개의 열과 함께 사용하지 않으므로 직접 테스트해야 합니다.

CREATE TABLE table1
    (`id` varchar(7), `timestamp` varchar(10), `unit` int, `detector` int, `value` DECIMAL(10,1))
;
    
INSERT INTO table1
    (`id`, `timestamp`, `unit`, `detector`, `value`)
VALUES
    ('UUID()', '2020-12-02', 1, 1, 0.1),
    ('UUID2()', '2020-12-02', 1, 2, 0.2),
    ('UUID3()', '2020-12-02', 2, 1, 0.3),
    ('UUID4()', '2020-12-02', 2, 2, 0.4),
    ('UUID5()', '2020-12-03', 1, 1, 0.5),
    ('UUID6()', '2020-12-03', 1, 2, 0.6),
    ('UUID7()', '2020-12-03', 2, 1, 0.7),
    ('UUID8()', '2020-12-03', 2, 2, 0.8)
;

SET SESSION group_concat_max_len=4294967295;
SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      "MAX(CASE WHEN `unit` = ",
      `unit`,
      " AND `detector` = ",`detector`," THEN `value` ELSE 0  END) AS 'Detector",
      
      `unit`,`detector`,"'"
    )
  ) INTO @sql
  
  FROM `table1`;

SET @sql = CONCAT("SELECT uuid(),`timestamp`, ",@sql," from table1
group by `timestamp`");

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

✓
✓
✓
✓
✓
uuid() | 타임스탬프 | 디텍터 11 | 디텍터 12 | 디텍터 21 | 디텍터 22:----------------------------------- | :--------- | ---------: | ---------: | ---------: | ---------:91b7521b-7298-11eb-a7c5-1f5a66f289d6 | 2020-12-02 | 0.1 | 0.2 | 0.3 | 0.491b752ed-7298-11eb-a7c5-1f5a66f289d6 | 2020-12-03 | 0.5 | 0.6 | 0.7 | 0.8
✓

db<>여기로 이동

이것은 데이터의 정규화를 해제하는 것입니다.단순한 크로스탭처럼 보입니다.

고려 사항:

TRANSFORM First(Table1.value) AS FirstOfvalue
SELECT Table1.timestamp
FROM Table1
GROUP BY Table1.timestamp
PIVOT "detector " & [unit] & [detector];

필드가 숫자 순서로 정렬되어 있는지 확인하려면 다음과 같이 하십시오.
PIVOT "detector " & Format([unit], "000") & Format([detector], "000");

해당 쿼리를 SELECT INTO의 소스로 사용합니다.
SELECT *.Query1 INTO Test FROM Query1
고유 식별자 필드는 나중에 추가해야 합니다.또는 테이블에 저장하지 않고 쿼리만 내보냅니다.

수백만 개의 행이 성능에 어떤 영향을 미칠지 모릅니다.

MySQL 테이블은 4096개 필드로 제한됩니다.당신의 생산량이 그것을 초과하지 않기를 바랍니다.

글쎄요, 피벗이 MySQL에서 사용할 수 없고 쿼리를 빌드하는 루프를 프로그래밍해야 한다는 것을 나타내는 조사를 했습니다.죄송합니다! 피벗은 액세스, SQL Server, Oracle에서 작동합니다.

언급URL : https://stackoverflow.com/questions/66230121/sql-insert-into-multiple-columns-from-multiple-selections