MySQL/MariaDB: 피벗 테이블 뷰로 동적 리포트 생성

MySQL/MariaDB: 피벗 테이블 뷰로 동적 리포트 생성

MySQL(MariaDB) 테이블은 다음과 같습니다.

+----+--------+-------+---------+
| id | date   |planned| actual  |
+----+--------+-------+---------+
|  1 |03-04-23| 40    |      15 |
|  2 |03-04-23| 15    |      17 |
|  3 |03-04-23| 60    |      19 |
|  4 |03-04-23| 20    |      20 |
|  1 |04-04-23| 10    |      22 |
|  2 |04-04-23| 15    |      32 |
|  3 |04-04-23| 65    |      50 |
|  4 |04-04-23| 22    |      55 |
|  1 |05-04-23| 18    |      40 |
|  2 |05-04-23| 36    |      65 |
|  3 |05-04-23| 44    |      70 |
|  4 |05-04-23| 47    |      57 |
+----+--------+-------+---------+

다음과 같은 보고서가 필요합니다.

+---------+--------------+--------------+---------------+
| user_id |    03-04-23  |    04-04-23  |   05-04-23    | 
+---------+--------------+--------------+---------------+
|         |Planned|Actual|Planned|Actual|Planned|Actual |
+---------+--------------+------------------------------+
|      1  |  40   |  15  |   10  |  22  |   18  | 40    |
|      2  |  15   |  17  |   15  |  32  |   36  | 65    |
|      3  |  60   |  19  |   65  |  50  |   44  | 70    |
|      4  |  20   |  20  |   22  |  55  |   47  | 57    |
+---------+--------------+--------------+---------------+

저는 날짜 범위를 제공해야 합니다.예: 01-04-2023 ~ 30-04-2023.

보고서는 각 날짜에 대해 동적으로 열을 생성해야 하며 데이터가 사용 가능한 날짜에 대해서만 데이터를 표시해야 합니다.

저는 다음 쿼리를 사용했으며 동적으로 열을 생성했습니다.

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT('MAX(CASE WHEN DATE(selected_date) = ''',
           DATE(selected_date),
           ''' THEN 1 ELSE NULL END) AS `',
           DATE(selected_date),
           '`')
  ) INTO @sql
FROM
    (SELECT 
        ADDDATE('1970-01-01', t4 * 10000 + t3 * 1000 + t2 * 100 + t1 * 10 + t0) selected_date
    FROM
        (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0, (SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1, (SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2, (SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3, (SELECT 0 t4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4) v
WHERE selected_date BETWEEN '2023-04-01' AND '2023-04-30';

SET @sql = CONCAT('SELECT ', @sql, ' FROM 
    (SELECT 
        ADDDATE(''1970-01-01'', t4 * 10000 + t3 * 1000 + t2 * 100 + t1 * 10 + t0) selected_date
    FROM
        (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0, (SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1, (SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2, (SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3, (SELECT 0 t4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4) v
WHERE selected_date BETWEEN ''2023-04-01'' AND ''2023-04-30''');

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

이 결과 세트를 기존 테이블과 어떻게 연결해야 할지 모르겠습니다.또한 메타베이스에서 이 쿼리를 바로 실행하여 보고서를 생성할 수 없습니다.그래서 절차를 만들고 메타베이스에서 절차를 부를까 생각중입니다.

앞으로 나아가는 가장 좋은 방법은 무엇입니까?

언급URL : https://stackoverflow.com/questions/76127057/mysql-mariadb-create-a-dynamic-report-with-a-pivot-table-view