MariaDB: 항목당 월평균 선택(Pivot 테이블)

MariaDB: 항목당 월평균 선택(Pivot 테이블)

다음과 같은 MariaDB 테이블이 있습니다.

요소	타임스탬프	지속
구성 요소 1	2021-10-01 14:04:54	40
구성 요소 1	2021-11-01 14:04:45	10
구성 요소 1	2021-11-11 11:05:23	20
구성 요소 1	2021-12-01 13:04:43	20
구성 요소 1	2021-12-12 12:14:11	30
구성요소 2	2021-11-01 14:04:27	45
구성요소 2	2021-12-01 13:04:08	23

제가 하고 싶은 것은 지난 3개월 동안 각 구성 요소의 평균 기간을 보여주는 것입니다.다음과 같이 보여야 합니다.

요소	AVG 기간(10월)	AVG 기간(11월)	AVG 기간(12월)
구성 요소 1	40	15	25
구성요소 2		45	23

온라인에서 찾은 피벗 테이블을 만지작거렸지만 아래 쿼리는 여전히 여러 줄로 표시됩니다(매달 1개, 나머지 달은 해당 줄에 비어 있음).

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
               CONCAT('(IF(MONTHNAME(s.LOG_TIMESTAMP) = "', MONTHNAME(`LOG_TIMESTAMP`),'", AVG(`DURATION`),"")) AS ',MONTHNAME(LOG_TIMESTAMP) )
              ) INTO @sql
FROM table1;


SET @sql = CONCAT('SELECT s.COMPONENT,  ', @sql, ' 
                  FROM table1 s
                 GROUP BY s.COMPONENT, MONTHNAME(s.LOG_TIMESTAMP)
                 ORDER BY s.COMPONENT');
SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;

따라서 구성 요소 1에 대한 출력 쿼리를 실행하면 다음과 같습니다(예:

요소	AVG 기간(10월)	AVG 기간(11월)	AVG 기간(12월)
구성 요소 1	40
구성 요소 1		15
구성 요소 1			25

log_times 스탬프가 있는 그룹별 식일 수도 있지만, 이를 제거하면 concat 함수는 사용 가능한 첫 번째 달의 평균 기간(모든 달)만 기록합니다.저는 피벗 테이블에 대한 경험이 없어서 여기서는 좀 이해가 안 됩니다.어떤 도움이라도 주시면 감사하겠습니다.

아마도 다음과 같은 최종 쿼리일 것입니다.

SELECT Component,
       MAX(IF(mth=10,avgd,0)) AS 'October',
       MAX(IF(mth=11,avgd,0)) AS 'November',
       MAX(IF(mth=12,avgd,0)) AS 'December'
 FROM
(SELECT Component,
       MONTH(LOG_TIMESTAMP) mth,
       AVG(Duration) Avgd
FROM table1
GROUP BY Component, mth) v
GROUP BY Component;

그리고 다음과 같은 문장 구문을 준비했습니다.

SET @sql := NULL;

SELECT CONCAT('SELECT Component,',
       GROUP_CONCAT(CONCAT('
       MAX(IF(mth=',mth,',avgd,0)) AS "',mthname,'"') 
       SEPARATOR ','),
       '
FROM (SELECT Component,
       MONTH(LOG_TIMESTAMP) mth,
       AVG(Duration) Avgd
FROM table1
GROUP BY Component, mth) v
GROUP BY Component') INTO @sql
FROM
(SELECT DISTINCT MONTH(LOG_TIMESTAMP) mth, 
       MONTHNAME(LOG_TIMESTAMP) mthname
FROM table1) a;

SELECT @sql;

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

데모 피들

2단계로 수행합니다.

1. 쿼리를 작성하여 평균을 계산합니다.
2. '파생 테이블'의 1단계 쿼리를 사용하여 피벗할 쿼리를 작성합니다.

언급URL : https://stackoverflow.com/questions/70437054/mariadb-select-average-per-month-per-item-pivot-table