python - MySQL:自动化此查询链(来自值的新列)

Question

我有一个包含如下数据的表:

date          met       val

2012-10-18    avgt    63.3617
2012-10-16    avgt    65.7312
2012-10-19    avgt    66.4952
2012-10-17    avgt    67.3747
2012-10-18    cdd     53.3617
2012-10-17    cdd     55.3472
2012-10-19    cdd     66.8063
2012-10-16    cdd     67.3116
2012-10-18    maxt    43.3617
2012-10-19    maxt    47.4484
2012-10-16    maxt    65.9559
2012-10-17    maxt    66.2868
2012-10-19    mint    56.0447
2012-10-16    mint    65.0656
2012-10-18    mint    65.0656
2012-10-17    mint    66.4952

met 列只有 4 个可能的值(avgt、mint、maxt、cdd、hdd)，用于标记 val 列中的数据。我需要一个查询(可能是存储过程)来重新排列数据，如下所示:

 date           avgt        cdd         maxt        mint
 2012-10-16     65.7312     67.3116     65.9559     65.0656 
 2012-10-17     67.3747     55.3472     66.2868     66.4952 
 2012-10-18     63.3617     53.3617     43.3617     65.0656 
 2012-10-19     56.0447     66.8063     47.4484     56.0447 

以静态方式执行此操作很容易。但我希望动态完成此操作，这样无论 met 列中的值实际是什么，都能正确进行重新排列。

以下是我手动执行此操作的方法:

添加附加列:

ALTER TABLE MYTABLE  
ADD COLUMN  avgt FLOAT( 15, 5 ) NOT NULL AFTER  val , 
ADD COLUMN  mint FLOAT( 15, 5 ) NOT NULL AFTER  avgt , 
ADD COLUMN  maxt FLOAT( 15, 5 ) NOT NULL AFTER  mint , 
ADD COLUMN  cdd  FLOAT( 15, 5 ) NOT NULL AFTER  hdd

更新每行的新列:

UPDATE MYTABLE
SET avgt = val WHERE metric == 'avgt';
SET mint = val WHERE metric == 'mint';
SET maxt = val WHERE metric == 'maxt';
SET cdd  = val WHERE metric == 'cdd';

删除旧列

ALTER TABLE MYTABLE
DROP COLUMN met,
DROP COLUMN val;

我也了解如何获取唯一 met 值的列表:

SELECT DISTINCT met FROM MYTABLE

我之间缺乏逻辑。我需要迭代不同的值。如果有人能帮助我解决这个问题，我将不胜感激。

我会接受用纯 SQL(可能是存储过程)或 Python 编写的答案。

非常感谢!

Answer 1

由于您需要一个不依赖于 met 列中的值的灵活解决方案，因此最好的方法是在从数据库获取数据后使用 python 来实现，例如

data_str = """2012-10-18    avgt    63.3617
2012-10-16    avgt    65.7312
2012-10-19    avgt    66.4952
2012-10-17    avgt    67.3747
2012-10-18    cdd     53.3617
2012-10-17    cdd     55.3472
2012-10-19    cdd     66.8063
2012-10-16    cdd     67.3116
2012-10-18    maxt    43.3617
2012-10-19    maxt    47.4484
2012-10-16    maxt    65.9559
2012-10-17    maxt    66.2868
2012-10-19    mint    56.0447
2012-10-16    mint    65.0656
2012-10-18    mint    65.0656
2012-10-17    mint    66.4952"""

data = []
# convert to array data as it would be from sql
for line in data_str.split("\n"):
    row = line.split()
    data.append(row)

# ######## this is the code required to process sql output
import collections
date_map = collections.defaultdict(dict)
for date, met, val in data:
    date_map[date][met] = val

rows = []
for date, data in date_map.iteritems():
    row = [date]
    rows.append(row)
    values = data.items()
    values.sort()
    row.extend((v for met, v in values))
    print row

输出:

['2012-10-19', '66.4952', '66.8063', '47.4484', '56.0447']
['2012-10-18', '63.3617', '53.3617', '43.3617', '65.0656']
['2012-10-17', '67.3747', '55.3472', '66.2868', '66.4952']
['2012-10-16', '65.7312', '67.3116', '65.9559', '65.0656']