使用 GROUP BY 的 mysql 数据透视查询结果

2021-11-20 00:00:00 pivot csv mysql

我有一个数据表，我想将其导出为 CSV.理想情况下，我想切换行和列，以便更好地对数据进行分组.

I have a table to data that I want to export to a CSV. Ideally, I'd like to switch the rows and columns around, so that the data is grouped a little better.

进一步解释一下，目前，数据库看起来像这样..

To explain further, currently, the database looks like this..

data_id     data_timestamp         data_value    
--------------------------------------------
1           2011-07-07 00:01:00    0.400  
1           2011-07-07 00:02:00    0.500
1           2011-07-07 00:03:00    0.600
1           2011-07-07 00:04:00    0.700
2           2011-07-07 00:01:00    0.100  
2           2011-07-07 00:02:00    0.200
2           2011-07-07 00:03:00    0.250
2           2011-07-07 00:04:00    2.300

我想按照 data_timestamp 值对 data_value 进行分组，以便对时间戳进行分组，并且每个 data_id 的每个 data_value 都显示在一列而不是一行中.

What I'd like to do group the data_value by the data_timestamp value, so that the timestamps are grouped, and each data_value for each data_id is shown in a column, instead of a row.

data_timestamp         input_1    input_2     
--------------------------------------------
2011-07-07 00:01:00    0.400      0.100  
2011-07-07 00:02:00    0.500      0.200
2011-07-07 00:03:00    0.600      0.250
2011-07-07 00:04:00    0.700      2.300

下面是我正在使用的查询...

Below is the query i'm using...

SELECT d.data_timestamp, d.input_1, d.input_2
FROM (
    SELECT data_timestamp,
    IF(data_id=1,data_value,NULL) AS 'input_1',
    IF(data_id=2,data_value,NULL) AS 'input_2' FROM data
) AS d ORDER BY data_timestamp ASC

但这并不是我想要的，因为现在每当一个 data_id 没有值时就会有 NULL 值.GROUP BY 似乎也对 data_value 进行了分组，这不是我想要的.

But it's not quite what i'm wanting, as there are now NULL values whenever one data_id doesn't have a value. GROUP BY seems to group the data_value's as well, which isn't what I want.

有什么建议吗?

我已经尝试在外部查询中使用 WHERE d.input_1 IS NOT NULL，但无法完全得到结果..

I've already tried using WHERE d.input_1 IS NOT NULL in the outer query, but can't quite get the results..

在WHERE...之前

Before the WHERE...

data_timestamp         input_1    input_2     
--------------------------------------------
2011-07-07 00:01:00    0.400      NULL
2011-07-07 00:01:00    NULL       0.100  
2011-07-07 00:02:00    0.500      NULL
2011-07-07 00:02:00    NULL       0.200
2011-07-07 00:03:00    0.600      NULL
2011-07-07 00:03:00    NULL       0.250
2011-07-07 00:04:00    0.700      NULL
2011-07-07 00:04:00    NULL       2.300

添加 WHERE d.input_1 IS NOT NULL 将删除 input_2 值..

Adding WHERE d.input_1 IS NOT NULL will drop the input_2 values..

data_timestamp         input_1    input_2     
--------------------------------------------
2011-07-07 00:01:00    0.400      NULL
2011-07-07 00:02:00    0.500      NULL
2011-07-07 00:03:00    0.600      NULL
2011-07-07 00:04:00    0.700      NULL

此外，实际上，我有大约 20 个 ID 可以分组，因此对于 OR 所有这些 ID 也不是最好的想法..

Also, in reality, I have about 20 id's to group by, so wouldn't be the best of ideas to OR all of them either..

推荐答案

PIVOTing 做起来既不容易(也不好).我更喜欢使用 CASE:

PIVOTing is neither easy (nor nice) to do. I prefer using CASE:

SELECT d.data_timestamp
     , SUM( CASE WHEN data_id =  1 THEN data_value ELSE 0 END ) AS 'input_1'
     , SUM( CASE WHEN data_id =  2 THEN data_value ELSE 0 END ) AS 'input_2'
     ...
     , SUM( CASE WHEN data_id = 20 THEN data_value ELSE 0 END ) AS 'input_20'
FROM data 
GROUP BY data_timestamp
ORDER BY data_timestamp ASC

但是 IF 也适用于 MySQL:

but IF works in MySQL as well:

SELECT d.data_timestamp
     , SUM( IF(data_id =  1, data_value, 0) ) AS 'input_1'
     , SUM( IF(data_id =  2, data_value, 0) ) AS 'input_2'
     ...
     , SUM( IF(data_id = 20, data_value, 0) ) AS 'input_20'
FROM data 
GROUP BY data_timestamp
ORDER BY data_timestamp ASC

或者，您可以使用 20 级 JOIN:

Alternatively, you can use a 20-level JOIN:

SELECT d.data_timestamp
     , d01.data_value AS 'input_1'
     , d02.data_value AS 'input_2'
     ...
     , d20.data_value AS 'input_20'
FROM
  ( SELECT DISTINCT d.data_timestamp
    FROM data
  ) AS d 
  LEFT JOIN data AS d01
    ON d01.data_timestamp = d.data_timestamp
    AND d01.data_id =  1
  LEFT JOIN data AS d02
    ON d02.data_timestamp = d.data_timestamp
    AND d02.data_id =  2
  ...                                   --- 20 JOINs
  LEFT JOIN data AS d20
    ON d20.data_timestamp = d.data_timestamp
    AND d20.data_id = 20
ORDER BY d.data_timestamp ASC

相关文章