데이터베이스 열에서 행 생성

데이터베이스 열에서 행 생성

현재 데이터베이스에서 다음과 같이 이 데이터를 검색하고 있습니다.

+------------+--------------+-------+-----+-------------+-----------+------------------+-----------------+
    | Monitor ID | Casting Date | Label | AGE | Client Name |  Project  | Average Strength | Average Density |
    +------------+--------------+-------+-----+-------------+-----------+------------------+-----------------+
    |    1082    |  2018-07-05  |  b52  |  1  |  Trial Mix  | Trial Mix |      21.78       |      2.436      |
    |    1082    |  2018-07-05  |  b52  |  2  |  Trial Mix  | Trial Mix |      33.11       |      2.406      |
    |    1082    |  2018-07-05  |  b52  |  4  |  Trial Mix  | Trial Mix |      43.11       |      2.447      |
    |    1082    |  2018-07-05  |  b52  |  8  |  Trial Mix  | Trial Mix |      48.22       |      2.444      |
    |    1083    |  2018-07-05  |  B53  |  1  |  Trial Mix  | Trial Mix |      10.44       |      2.421      |
    |    1083    |  2018-07-05  |  B53  |  2  |  Trial Mix  | Trial Mix |       20.0       |      2.400      |
    |    1083    |  2018-07-05  |  B53  |  4  |  Trial Mix  | Trial Mix |      27.78       |      2.397      |
    |    1083    |  2018-07-05  |  B53  |  8  |  Trial Mix  | Trial Mix |      33.33       |      2.409      |
    |    1084    |  2018-07-05  |  B54  |  1  |  Trial Mix  | Trial Mix |      12.89       |      2.430      |
    |    1084    |  2018-07-05  |  B54  |  2  |  Trial Mix  | Trial Mix |      24.44       |      2.427      |
    |    1084    |  2018-07-05  |  B54  |  4  |  Trial Mix  | Trial Mix |      34.22       |      2.412      |
    |    1084    |  2018-07-05  |  B54  |  8  |  Trial Mix  | Trial Mix |      41.56       |      2.501      |
    +------------+--------------+-------+-----+-------------+-----------+------------------+-----------------+

제가 어떻게 테이블을 이런 것으로 바꿀 수 있을까요?

    +------------+--------------+-------+-----------+-----------+---------+-------------+---------+-------------+---------+-------------+---------+-------------+
| Monitor Id | Casting Date | Label |  Client   |  Project  |  1 Day  |             | 2 Days  |             | 4 Days  |             | 8 Days  |             |
+------------+--------------+-------+-----------+-----------+---------+-------------+---------+-------------+---------+-------------+---------+-------------+
|            |              |       |           |           | avg str | avg density | avg str | avg density | avg str | avg density | avg str | avg density |
|            |              |       |           |           |         |             |         |             |         |             |         |             |
|       1082 | 05/07/2018   | B52   | Trial Mix | Trial Mix | 21.78   | 2.436       | 33.11   | 2.406       | 43.11   | 2.44        | 48.22   | 2.444       |
|       1083 | 05/07/2018   | B53   | Trial Mix | Trial Mix | 10.44   | 2.421       | 20      | 2.4         | 27.78   | 2.397       | 33.33   | 2.409       |
|       1084 | 05/07/2018   | B54   | Trial Mix | Trial Mix | 12.89   | 2.43        | 24.44   | 2.427       | 34.22   | 2.412       | 41.56   | 2.501       |
+------------+--------------+-------+-----------+-----------+---------+-------------+---------+-------------+---------+-------------+---------+-------------+

나는 아래의 피위를 사용하여 데이터베이스에서 여러 테이블을 결합하여 데이터를 얻습니다. 이것은 데이터를 검색하고 포맷하기 위한 내 전체 코드입니다.

from lib.database import *
import matplotlib.pyplot as plt
from datetime import datetime,timedelta
from prettytable import PrettyTable
import numpy as np

#table to hold data
table = PrettyTable()
table.field_names = ['Monitor ID','Casting Date','Label','AGE','Client Name','Project', 'Average Strength','Average Density']

#interval of 2 weeks ago
int = datetime.today()-timedelta(days=14)

result = MonitorCombine.select(ResultCombine.strength.alias('str'),ResultCombine.density.alias('density'),ResultCombine.age,MonitorCombine.clientname,MonitorCombine.p_alias,MonitorCombine.monitorid, MonitorCombine.monitor_label,MonitorCombine.casting_date).join(ResultCombine, on=(ResultCombine.monitorid == MonitorCombine.monitorid)).dicts().where(MonitorCombine.casting_date > int).order_by(MonitorCombine.monitor_label,ResultCombine.age.asc())

for r in result: table.add_row([r['monitorid'],r['casting_date'],r['monitor_label'],r['age'],r['clientname'],r['p_alias'],r['str'],r['density']])
print(table)

데이터를 피벗해야 합니다. MariaDB에는 피벗이 없기 때문에 sql:

SELECT
    MonitorID,
    CastingDate,
    Label,
    ClientName,
    Project,
    SUM(IF(Age=1, AverageStrength, 0)) AS AvgStr1,
    SUM(IF(Age=2, AverageStrength, 0)) AS AvgStr2,
    SUM(IF(Age=4, AverageStrength, 0)) AS AvgStr4,
    SUM(IF(Age=8, AverageStrength, 0)) AS AvgStr8,
    SUM(IF(Age=1, AverageDensity, 0)) AS AvgDensity1,
    SUM(IF(Age=2, AverageDensity, 0)) AS AvgDensity2,
    SUM(IF(Age=4, AverageDensity, 0)) AS AvgDensity4,
    SUM(IF(Age=8, AverageDensity, 0)) AS AvgDensity8
FROM
    YourTable
GROUP BY MonitorID, CastingDate, Label, ClientName, Project, Age
ORDER BY MonitorID, CastingDate;

언급URL : https://stackoverflow.com/questions/51336853/create-rows-from-database-column