Mysql 轉置資料(行列互換)

Data

論文的某些資料透過爬蟲蒐集,結構大概類似:

|  id  | name |  value  | something_else... |      timestamp      |
|   1  |  Ben |   100   |         ...       | 2018-11-20 08:00:00 |
|   2  |  Tom |   110   |         ...       | 2018-11-20 08:00:01 |
...
|  100 |  Ben |   200   |         ...       | 2018-11-30 08:00:00 |
|  101 |  Tom |   210   |         ...       | 2018-11-30 08:00:01 |

是隨時間推進而不斷增加的流水帳。但是這樣除了 INSERT 比較方便以外,不管對視覺上還是跑分析都不太易讀,為了老闆的眼睛,以及我的畢業證書,必須要整理成類似時間軸的概念。

 

MySQL query

select
	p.name,
	MAX( case when p.timestamp between '2018-11-20 00:00:00' and '2018-11-20 23:59:59' then p.value else 0 end) as '2018-11-20',
	MAX( case when p.timestamp between '2018-11-21 00:00:00' and '2018-11-21 23:59:59' then p.value else 0 end) as '2018-11-21',
	MAX( case when p.timestamp between '2018-11-22 00:00:00' and '2018-11-22 23:59:59' then p.value else 0 end) as '2018-11-22',
	MAX( case when p.timestamp between '2018-11-23 00:00:00' and '2018-11-23 23:59:59' then p.value else 0 end) as '2018-11-23',
	MAX( case when p.timestamp between '2018-11-24 00:00:00' and '2018-11-24 23:59:59' then p.value else 0 end) as '2018-11-24'
from
	people p
group by
	p.name

 

透過上面query可以將 row 的資料聚合到 column ,並且使用 record 的某個值自訂 column name (例如 timestamp ),同時也可以結合 WHERE 以及 JOIN 等功能。

| name | 2018-11-20 | ... | 2018-11-30 |
|  Ben |     100    | ... |     200    |
|  Tom |     110    | ... |     210    |